How To Identify What Consumes Cpu In Sql Server

Jun 1, 2007

Hi all
I use 64 bit 2005 server with 8cpu and 8G of memory.
This server is accessed by large number of intensive or not so intensive programs.
I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good.
But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it.
If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good.
Task manager shows that all of it 75% attributed to sql server.
So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves.

Thank you, Gene.

View 3 Replies


ADVERTISEMENT

SQL Server Consumes 100% CPU!!!

May 16, 2004

The problem is that our production SQL Server ALWAYS consumes the full CPU capacity when in operation. It also does not utilize the full 4G memory alloted to it, but only consistently uses 1.7G. Now, the application using the DB does use a lot of server-side cursors and since the software developer is a 3rd-party vendor, we can only do so much to recommend application optimization. Also, we've discovered that the application modules that were identified as mostly participating in high-CPU utilization does not always consume it highly, although they are the ones mostly involved in it. So, in relation to the limited usage of the 4G-alloted memory, we have theorized that perhaps the SQL processes may be hanging & waiting for each other to complete processing whenever more memory is needed, thus contributing to the server slow down, but this is memory not CPU utilization. We have been wracking our brains for ways to prevent the server slow down due to CPU utilization.

Any feedback will be greatly appreciated.

View 12 Replies View Related

SQL Server Gradually Consumes All Memory And Brings Down NT

Mar 10, 2000

Hello,

After launching MS SQL Server 7.0 SP 1 on a Compaq Proliant 5500 with 1GB memory the sqlservr.exe process gradually consumes all available memory and brings down NT.
This happens when there's appearently no activity on SQL Server.

What's going on?

Stef

View 5 Replies View Related

SQL Server Freezes : Consumes High Disk Space

Dec 19, 2005

Hi,

The SQL Server Database hangs overnight and also consumes high disk space on one of our servers. This has been recurring for quite a few weeks and occurs daily.

Can somebody assist me in trouble-shooting the same

Thanks in Advance
Deepak

View 3 Replies View Related

Consumes All Memory/uses One Processor

Feb 13, 2001

Hi,
I have a new problem .I have a DB on server which is using only one processor and it keeps using up all memory on server.
Any thoughts appreciated!
TIA
PD

View 2 Replies View Related

Database Consumes Available Free Space?

Jul 12, 2012

I inherited a SQL server (2005) that sits on a Windows 2003 box (upgrading to 2008 R2 and Win2008, ASAP) and on our SQL Data Drive (260GB - 22GB free) we have 16 databases (that are associated with our primary application). One database in particular is causing me quite a bit of pain - on Tuesdays. This table will consume all available free space (save 700kb) and place it within it's 'reserved' space. It's not being used and I can reclaim it by shrinking. I have done this for 3 consecutive weeks now and I have always thought that shrinking is not really a best practice.

Based on a job I scheduled to spit out various database stats I discovered the following:

Somewhere after 11pm on Monday (and on or before 11:30pm) the reserved space goes from 9GB down to 23 then 37MB on this particular database.

After 1:30am on Tuesday (and on or before 2:00am) the database consumes nearly all available disk space and thus expands its reserved space to 28GB

I believe it has something to do with the Indexes being rebuilt - per the Maintenance Plan the Index Rebuild is supposed to occur every Monday @ 11pm. On 6/18 it ran from 11pm - 11:44pm with no problems. Starting on 6/26 (and subsequently 7/2 and 7/9) they have started at 11 and ended anywhere between 2 - to 3 hours later. And with the following error:

Failed-1073548784) Executing the query "ALTER INDEX [PK_activityLog] ON [dbo].[activityLog] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF ) " failed with the following error: "Could not allocate a new page for database 'FA_PROD_SDDS' because of insufficient disk space in filegroup 'PRIMARY'.

Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The database 'FA_PROD_SDSS' is the one that I am having issues with.

View 2 Replies View Related

Service Broker Consumes More Memory

Dec 11, 2007

Hi,

I have implememted service broker in my application.My sqlservr.exe comsumes more than 2 gb of memory which eventually cause my computer to hang.I unable to track the problem.
Can anyone help me out of this.
Looking at the SQL Server logs i found following error message logged multiple times ;


The query notification dialog on conversation handle '{09B96045-99A4-DC11-802F-0013EFF142EF}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
-Aravind cheziyan

View 1 Replies View Related

What Is Being Stored In ASPNETDB.MDF -- It Consumes ~1/2 My Sites Space!

Mar 14, 2007

Using VS2005, VB backend and javascript,
I have developed a relatively simple site - its got a few (12) simple aspx pages but its mostly client side javascript. Keeping disk storage costs down is a big concern with this my site. The disk usage for the site is ~24M. Since this was larger than I expected I started inspecting the files comprising my site and found that the "ASPNETDB.MDF" in my "App_data" folder is consuming 10.2M by itself. The thing is that site only has a few pages with calls to SQL Server - but I never did anything (that I know of) with ASPNETDB.MDF. Through VS2005, I opened up the MDF file and poked around, everything that I looked at was empty (NULL). 
So my questions are:

What is causing the ASPNETDB.MDF to consume 10.2M even thought I can't see any data stored in it?
Is there anyway for my to reduce the size of this file? If so, how?
Can someone give me some pointers as to where to read up on what the ASPNETDB.MDF does?

View 4 Replies View Related

DtsDebugHost Processes Consumes Huge Amounts Of Memory, Then Fails

Jul 18, 2006

I gave up on the ScriptTasks, but desided to use Custom tasks instead. Problem again. My code opens a 400M file and reads it line by line using StreamReader. Each line is approximately the same length. For each line, there is some processing, and then the line is written into another file using StreamWriter. I am watching the DtsDebugHost process with TaskManager open and here is what happens:

Initially its all good. Then when it read through first 150M+ of the input file, the memory usage of the DtsDebugHost shoots up dramatically - about 1Gig (both virtual and physical memory). Then the task fails with OutOfMemoryException. I thought the problem is with my code, but it still happens even if I only read a line from one file and write it to another, without any processing!

When I invoke the same code from Execute Process task, its all good - no problems at all whatsoever.

Any ideas?









View 1 Replies View Related

SQL Server 2008 :: Capture Database / Server Name In A Derived Column To Identify Source Of Data?

Feb 1, 2012

I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.

When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.

The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.

For Example I have Products table on Server 1 and 2:

Server 2 has more Products and would like to join the two together to create a staging table.

I want see the following:

Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2

get database name and server name in DATA FLOW only (without using a for each in Control Flow)

View 5 Replies View Related

SQL Server 2008 :: Identify If Remote Login Resolved Database Server Via IP / Servername Or FQDN

Feb 16, 2015

Is it possible to view the Connection String information of a remote login/session? I want to know if the login is looking-up the database server via IP address, servername (NetBIOS name) or fully-qualified domain name (FQDN).

Using these DMVs I can get a lot of relevant information:

sys.dm_exec_sessions
Program Name (eg. Microsoft SQL Server Management Studio),
Client Interface Name (eg. .Net SqlClient Data Provider)
sys.dm_exec_connections
Net Transport (eg. TCP),
Client Net Address and TCP Port

but not how the server's IP address was resolved. Is the connection string ever sent by the client to the server, or just used for DNS lookup?

View 0 Replies View Related

How To Format Identify Column In SQL Server?

Mar 21, 2008

I don't wnat SQL's Identify column's format ( which is 1�2�3 ...)I want my Prikey column is looks like starts in 0000000001�0000000002�0000000003....I set the Prikey columns type is Char(10) not nullis it possible to setting my identify column as I want?

View 15 Replies View Related

Identify Edition Of SQL Server And Benefits

Mar 31, 1999

Hi

How can I find out which edition of SQL Server is running on my server ?

Also what would be the difference in terms of features between standard edition and enterprise edition ?

Thanks

Vijay

View 1 Replies View Related

How To Identify Locks In SQL Server And Kill Them

Mar 9, 2007

I have to find locks which have been caused by some particular users before the scheduled jobs kick off and kill the locked processes so that the scheduled job executes without failing. How to do this in SQL server? please help

Thanks in Advance

View 10 Replies View Related

SQL Server Admin 2014 :: How To Identify The Job With The Code

Apr 7, 2015

I got query from sysproccesses and the result for one spid is : SQLAgent - TSQL JobStep (Job 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61 : Step 1

Which job has this code : 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61

View 1 Replies View Related

SQL Server 2012 :: Identify Sets Of Rows

Jul 15, 2015

I have the following sets of records:

ColA ColB
----- -----
21 A
22 A
23 A
24 B
25 B
26 D

What I want is to be able to identify a set sequence (1,2,3) based upon ColB such that I'd get the following result:

ColA ColB ColC
----- ----- -----
21 A 1
22 A 1
23 A 1
24 B 2
25 B 2
26 D 3

I know that I should be able to get it using ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA), but instead of getting the sequence (1,1,1,2,2,3) I get (1,2,3,1,2,1). Using DENSE_RANK gave me the same results.

View 4 Replies View Related

How To Identify Service Pack 2 In Sql Server Is Installed Or Not

Mar 6, 2008

when i run sql server 2005
select @@version

Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

how to determine that i have installed sql server service pack 2


do i have to restart the server to see the effect, how will it show that it is service pack 2

View 8 Replies View Related

SQL Server 2008 :: Identify Incoming DB Links To Server

May 10, 2015

We are doing a review of a SQL 2008 server. Though we can identify what are the linked servers to the database.

However is there a sure shot way to identify, incoming DB links to the database server. I know the successful connections are easy to identify, but I wish to know all possible incoming DB links to server.

Will the DB logs support in identifying all attempted and successful DB link connections.

View 1 Replies View Related

SQL Server 2012 :: Condition To Identify Values That Are +/- Than 2 Cents?

Jul 23, 2014

I want to identify rows that go negative but only for 2 cents or more as well as identify rows that 2 or more.

I have this expression that does not work how I want it to work:

CASE
WHEN (SUM(FavUnfavCostChange) < (2/100) THEN 'Less'
WHEN SUM(FavUnfavCostChange) > (2/100) THEN 'More'
ELSE NULL
END AS 'Flag'

But I get:

0.00000815000000000000More -- this is not more than 2 cents, is just a positive number
-0.00094700000000000000Less -- this is not less than 2 cents, is just negative number
-0.00222000000000000000Less -- this is not less than 2 cents, is just negative number
-0.00012250000000000000Less -- this is not less than 2 cents, is just negative number
0.00000000000000000000NULL -- this is zero so null is fine
0.01188576000000000000More -- this is not more than 2 cents, is just a positive number

View 3 Replies View Related

SQL Server 2008 :: Identify Columns And / Or Tables From Records

Mar 3, 2015

I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.

In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.

Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR

In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).

As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.

I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?

View 2 Replies View Related

SQL Server 2012 :: Data Compare To Identify Change

Mar 3, 2015

I am in process to develop TSql code to identify change in data.

I read about Binary_checksum and hashbyte. Some people say hashbyte is better than binay_checksum as chances of collision are less.

But if we may consider following, chances exist in hashbyte too. My question is what is the best way to compare data to identify change (I can't configure CDC) ?

select HASHBYTES('SHA','121'+'34'), HASHBYTES('SHA','12'+'134'),BINARY_CHECKSUM('121','34'),BINARY_CHECKSUM('12','134');

View 2 Replies View Related

SQL Server Admin 2014 :: Identify Who Dropped A Table

Jun 30, 2015

I'm getting a replication error that an object (Table) was not found. Any script that can capture this information?

View 3 Replies View Related

SQL Server 2008 :: Identify DB Objects That Depend On Particular Table?

Jul 7, 2015

Any SQL that can be used to identify all objects that depend on a particular table? For example, sprocs, functions, views, etc.

View 3 Replies View Related

SQL Server Admin 2014 :: How To Identify Data Leakage In A Database

Dec 29, 2014

how to identify the data leakage in a database , as I heard in one of my environment?

what is the meaning for data leakage ?

View 3 Replies View Related

SQL Server Admin 2014 :: How To Identify Empty Data File

Jan 21, 2015

I was running an operation to shrink file/emptyfile a data file, and then remove it.

It blocked and caused a huge mess, I suspect on the removal part. But I want to confirm that the emptyfile completed (and that the engine isn't going to try to put more data in there for when I schedule the removal part again a week or more from now).

How does the engine know not to put any more data in there, and how long does that situation last?

View 3 Replies View Related

SQL Server 2008 :: Identify Which Indices Are Replicated - Transactional Replication

Feb 20, 2015

Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....

View 0 Replies View Related

How To Identify Permissions For SQL Server Tables && Stored Proc. Via VB Code

Jul 20, 2005

I'm writing an application using VB 6.0 as the front-end GUI, and theMSDE version of SQL Server as the back-end (it's a program for areally small # of users --- less then 3-4).I'm trying to determine, through the Visual Basic interface, thepermissions of each user that's using the application on his/hermachine.For example, let's say I'm user "Michael" that's sitting down at mymachine using the app. I've written. The security for logging intoSQL Server will be setup using Windows Security (Trusted Connection)as opposed to Windows & SQL Server security. When Michael accesses aparticular form in the VB 6.0 GUI, I want to run some code thatautomatically checks Michael's permission levels on the underlyingtable (actually, a stored procedure supplying the data from the table)that supplies the data to the form he's looking at and then give himsome feedback on the form as to what type of permissions he has whilehe's browsing through the data shown in the form.For example, Michael opens a particular form, code in the backgroundis run to identify that this is Michael accessing the form, the codereturns a value that identifies what type of permissions he has on thedata in the form, and a text box on the form informs Michael (forexample) that he only has read-only permissions to the data he isviewing and cannot edit any of the data.As another example, user Karen sits down at her computer, logs intothe application, opens the same form that Michael just opened, thecode is run in VB to detect the level of permissions she has on thedata being displayed in the form, and the text box on the form informsher that she has editing permissions on the data in the underlyingtable.Etc...If anyone can post an example of the code they use in accomplishingthis task in an application they've written, I'd really appreciate apoint in the right direction or a real-world example that's beenimplemented by one of you. I've written several apps. thus far usingMSDE as the back-end, but the previous apps. I've written were forclients that didn't care about restricting access to theapplication... everyone could pretty much use the application as theydesired and do anything they desired to the data.The current client I'm writing the app. mentioned here for wants tohave security in place to where various users access the applicationwith various levels of permissions to do stuff (or *not* do stuff) tothe data in the application.Thanks very much in advance for any assistance / code provided!Sincerely,Brad McCollumJoin Bytes!

View 1 Replies View Related

SQL Server 2014 :: Identify Difference Between Two Table Columns And Output Only The Differences

Sep 4, 2015

IF Object_id('GoldenSecurity') IS NOT NULL DROP TABLE dbo.GoldenSecurity;
IF Object_id('GoldenSecurityRowVersion') IS NOT NULL DROP TABLE dbo.GoldenSecurityRowVersion;

CREATE TABLE dbo.GoldenSecurity (securityID INT, CompanyId INT, Securityname VARCHAR(50), issuedate SMALLDATETIME, currencyID INT)

[Code] ......

View 6 Replies View Related

SQL Server Admin 2014 :: Identify Table Made On Temporary Basis Are Not Used?

Sep 9, 2015

In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?

View 4 Replies View Related

SQL Server 2008 :: Identify Size Of Data Cache Allocated From Buffer Pool

Oct 20, 2011

How do i check the size of the datacache allocated from the buffer pool by sql server?

DMV or anything to show me the pool allocation sizes for the various pools in sql server i think i may be able to work from there.

View 9 Replies View Related

SQL Server Admin 2014 :: Schema Change History - Identify Who Dropped A Table?

Jun 30, 2015

How can I easily identify who dropped a table?

View 8 Replies View Related

SQL Server 2014 :: SSAS Stored Procs (CLR) - Identify Real Data Type Of MDX Value Returned From Expression

Feb 13, 2015

I have a SSAS stored procedure with a signature:

public Set DoSomthing(Set toBeProcessed, Set measuresToWorkWith)The set measurseToWorkWith is passed as {[Measures].[Measure1], [Measures].[Measure2] ...}

with the measures being real or query-scoped calculated members.

To get the value of the measure for each tuple in the set toBeProcessed, I create an Expression for each tuple (measure) in the set measuresToWorkWith then for each tuple in toBeProcessed call expression.Calculate(tuple) which returns a MDXValue.

My problem is that in order to make the code generic I need to get the real (.NET) data type of the MDXValue. The class only has explicit conversion methods ToInt16() etc which implies that the data type is known at design time.

However, if one of the measures is a query-scoped calculation then it could return a .NET double, int, bool or string.

If the measure is real then I can look up its metadata. However, it appears that if it is a formula (scoped member) then are all bets are off?

View 0 Replies View Related

SQL Server 2012 :: Identify Sets That Have Same Items (where Set ID And Items In Same Table)

Feb 25, 2015

I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:

SetIdItemCode
1A
1B
24
28
26
310
312
410

[code]....

You can see that there are some sets that have the same members:

- 1 and 10
- 2 and 11
- 7, 8 & 9

What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved