sql server cou at 100% and will cpu column in sysprocesses table is enough to identify the sql process

Select from sysprocesses several times and compare how values change (esp. cpu).

Regards,
Dominik

The value is not changing, but i would like to know how many % from 100% of being use by sql server attach to this process. I know its DTS from the program_name column and its the most highest cpu usage, but i would like to know the percentage out of 100% utilisation

to identify why SQL server runs at high CPU, I would start using the sql profiler and catch all SQL with high reads (writes), and check those queries that are running often etc, and get those queries tuned (with indexes etc) using query analyzer + show execution plan on such a query.

Hi,
I found this kb at one of the micosoft site. but not to sure how should I filter,,,

http://support.microsoft.com/kb/q117559/

In my opinion it is impossible to get an accurate result but you may try to:
1) make a copy of sysprocesses
2) after let's say 5 seconds make another copy

select ((select cpu from copy2 where pid = @your_pid) - (select cpu from copy1 where pid = @your_pid)) /
((select sum(cpu) from copy2) - (select sum(cpu) from copy1)) * 100

Every process that was running when you were making copy1 and ended before making copy2
or started after making copy1 and before making copy2 will make the result less accurate.

Note that I assume that the values do change.

Regards,
Dominik

If you're looking for instantaneous numbers instead of average over some period, run this script:

https://www.experts-exchange.com/questions/22068094/What-to-do-when-SQL-server-CPU-spikes.html

against both sql servers when you have an extended period of high cpu utilization.  One of the results will have high cpu numbers and identify the specific sql (and you'll know which sql server you ran it against) that is the cpu hog.

Hi mastoo,
I have the query already thanks, btw when I use this query it does not return me a right CPu percentage which I do not know why...the cpu percent showing very less something like 3.2,1.6 and 1.5 but in windows tasks manage sqlserver.exe was consistent with 35% of cpu rate

Yes, it isn't perfect.  I've run across problems with Access clients that send a high volume of short queries on different connections, so the script misses them because no single connection accounts for a very high percentage during the 1 second interval the script uses.  But you said you are getting some high values for sysprocesses.cpu so that wouldn't be the case.  Could you give the spid, ecid, and cmd for the high cpu record and are there other records for the same ecid?

The cpu was at high rate due to very very heavy DTS job is running just now. but by look at sysprocesses.cpu I would like to know the percentage which sql server does not able to provide me, or what is the best way ti indicate this?? we know its high bcoz in windows tasks manager showing 100% with sqlserver.exe but we need to break them by process where I think would facilitiate dba while doing the troubleshooting

Would you mind trying a slight variation on that script and let me know if it is any better:

-- 5/6/2003 sp
-- 2/28/07 sp Quick hack to group by ecid for cpu also
-- Grabs two snapshots from sysperfinfo and presents a delta between them
-- to estimate sql server usage.

-- dbcc inputbuffer(spid) - shows the command that was run
-- spid = Sql Server process id.
-- kpid = Windows thread id.  The Thread Id shows the kpid for a given Sql Server thread.
-- blocked = spid of the blocking process.
-- waittime = ms.
-- dbid = database id
-- uid = user id
-- cpu = ms
-- physical io = physical reads and writes
-- memusage = number of pages in proc cache for this spid
-- last_batch = time of last exec or stored proc
-- ecid = identifies subthreads within a spid

Declare
  @datEnd      datetime,
  @datStart      datetime,
  @iSpidMax      integer,
  @sCmdMax      varchar(128),
  @sSql            varchar(128)

Set NoCount On

-- Get the two snapshots.
-- Exclude user = system.
Select 1 As SnapNum, spid, kpid, blocked,
    waittime, dbid, uid, cpu,
    physical_io, memusage, login_time, last_batch,
    ecid, status, hostname, program_name,
    cmd, net_address, loginame, GetDate() As SnapTime
  Into #SnapShot
  From master..sysprocesses
    Where uid >= 0

WaitFor Delay '00:00:01'

Insert Into #SnapShot
  Select 2 As SnapNum, spid, kpid, blocked,
    waittime, dbid, uid, cpu,
    physical_io, memusage, login_time, last_batch,
    ecid, status, hostname, program_name,
    cmd, net_address, loginame, GetDate() As SnapTime
    From master..sysprocesses
    Where uid >= 0

-- Get the time interval.
Select @datStart = Min( SnapTime ) From #SnapShot Where SnapNum = 1
Select @datEnd = Max( SnapTime ) From #SnapShot Where SnapNum = 2

-- Get the difference between the 2 snapshots, as percent ( / 1000 * 100 ).
Select spid, ecid,
    Sum( Case SnapNum When 2 Then waittime Else -waittime End ) * 0.1 As WaitPercent,
    Sum( Case SnapNum When 2 Then cpu Else -cpu End ) * 0.1 As CpuPercent,
    Sum( Case SnapNum When 2 Then physical_io Else -physical_io End ) As DeltaIo
  Into #PerfDelta
  From #SnapShot
  Group By spid, ecid
  Having Sum( Case SnapNum When 2 Then cpu Else -cpu End ) > 0

-- Get the command for the top few.
Create Table #DbccOutput ( spid integer, EventInfo varchar(256) )
Create Table #DbccOutputTemp ( EventType varchar(256), Parameters integer, EventInfo varchar(256))
Declare MyCursor Cursor For
  Select Distinct spid From #PerfDelta
Open MyCursor
Fetch Next From MyCursor Into @iSpidMax
While @@Fetch_Status = 0
Begin
  Set @sSql = 'DBCC inputbuffer(' + Convert( varchar(3), @iSpidMax ) + ')'
  Insert Into #DbccOutputTemp Exec ( @sSql )
  Insert Into #DbccOutput ( spid, EventInfo )
    Select @iSpidMax As spid, EventInfo From #DbccOutputTemp
  Delete #DbccOutputTemp
  Fetch Next From MyCursor Into @iSpidMax
End
Close MyCursor
Deallocate MyCursor

-- Show the results for the cpu hogs.
Set NoCount Off
Select PD.CpuPercent,
    PD.spid, PD.ecid, Convert( varchar(16), SS.loginame ),
    DO.EventInfo As Command,
    SS.hostname, SS.program_name,
    ( Select name From master..sysdatabases Where dbid = SS.dbid ),
    SS.status, SS.login_time, SS.last_batch,
    PD.DeltaIo, SS.cmd, SS.net_address
  From #PerfDelta As PD Join #SnapShot As SS
      On PD.spid = SS.spid
        And PD.ecid = SS.ecid
    Left Join #DbccOutput As DO
      On PD.spid = DO.spid
  Where SS.SnapNum = 2
  Order By CpuPercent Desc

  Drop Table #DbccOutput
  Drop Table #DbccOutputTemp

lblEnd:
  Drop Table #SnapShot
  Drop Table #PerfDelta

Hi Mastoo,
below is the result with cpu %, its execeeded >100%, do U have any idea how could I read these?

CPU             SPID
129.7      71
96.9      67
81.2      77
78.1      67
75.0      67
65.6      67
48.4      71
46.8      71
45.3      71
25.0      79
17.2      75
7.8      68
6.3      68
4.7      68
4.7      72
4.7      72
3.2      67
3.1      68
3.1      55
3.1      73
1.6      70
1.6      60
1.6      56
1.6      56
1.5      56
1.5      60

hmm I think the result was not correct at all, windows task manager only show me 28% cpu rate constantly but query with above statement return me as below

104.7      72
103.1      72
3.1      70

Can I get the ecid column also?  And how many processors show in task manager?  The modified script I posted is showing extra rows because I wanted to try and figure out why the original script wouldn't show proper results.  For your last example, I'm thinking the original script would only show one of the spid=72 rows, so if you've got a dual processor hyper-threaded server you would divide the 104.7 by 4 and get 25% overall utilization - agreeing with task manager.  If you tell me it isn't showing 4 processors then I'll have to say I don't know.   I've used the script for years at a quick means of identifying the top cpu hogs within sql server.

Hi Mastoo
I'm checking the processor and we have 8 processor, this so is that mean I have to divide by 8??? what is SYSPROCESSES.ECID means for?? is it worth it to consider that column?

Processor(s):              8 Processor(s) Installed.
                           [01]: x86 Family 15 Model 2 Stepping 5 GenuineIntel ~1993 Mhz
                           [02]: x86 Family 15 Model 2 Stepping 5 GenuineIntel ~1993 Mhz
                           [03]: x86 Family 15 Model 2 Stepping 5 GenuineIntel ~1993 Mhz
                           [04]: x86 Family 15 Model 2 Stepping 5 GenuineIntel ~1993 Mhz
                           [05]: x86 Family 15 Model 2 Stepping 5 GenuineIntel ~1993 Mhz
                           [06]: x86 Family 15 Model 2 Stepping 5 GenuineIntel ~1993 Mhz
                           [07]: x86 Family 15 Model 2 Stepping 5 GenuineIntel ~1993 Mhz
                           [08]: x86 Family 15 Model 2 Stepping 5 GenuineIntel ~1993 Mhz

CPU           SPID          ECID

73.4      56      97
70.3      56      98
70.3      56      95
68.8      56      96
11.0      56      89
6.3      56      112
6.2      56      114
4.7      55      0
3.2      56      88
3.2      56      113
3.1      60      0
3.1      56      87
3.1      56      111


Above is some infor grab, as u can see SPID 56 was divided with many rows with difference ECID, so in order toknow the CPU % can I add all the cpu within same spid and divide by 8 cpu?

ecid is kind of a subprocess of each spid.  My script has always just used the ecid = 0 for each spid but you've got me wondering if it should be summing the numbers to get a total for the spid.  And yes, divide by the number of cpus.  In your example:

104.7      72
103.1      72
3.1      70
 
My original script would give you 104.7 / 8 = 13%, but you said usage should be 28%.  So if we had the script sum numbers for both ecid's you would get 104 + 103 / 8 = 26% which is pretty close.  Similarly for your latest example:

73.4      56      97
70.3      56      98
70.3      56      95
68.8      56      96
11.0      56      89
6.3      56      112
6.2      56      114
4.7      55      0
3.2      56      88
3.2      56      113
3.1      60      0
3.1      56      87
3.1      56      111

Summing the cpu values for spid 56 and dividing by 8 gives 40%.  Did you note what task manager was reporting at the time?  If that seems correct, maybe early next week I can make a version 2.0 of the script for us.

Hi Mastoo,
It's almost correct with cpu percentage, I think that much more easier if the scripts will sums and dividing for us depending on cpu's installed ( if its possible ). In taks manager we can't see much since sqlserver.exe was consider as single process in windows but I think if we want to know in more details how much the cpu utilisation for every process that would be perfect by using ur scripts.
hmm I should be happy waiting for next release of mastoo_cpu_monitoring scripts soon...

Hi Mastoo,
I'm on vacation now and sure will do the test when at office, I think it should be Ok if I grab the nuber of cpu then run ur statement...
will give u an update very soon.. I'm  in ruin with the db performance now...

Hi again,
I still couldnt get the whole cpu count as per what windows taks manager is showing, do u think that if the DTS running or any other sql process running out from the sysprocesess?

Quick way to tell would be get rid of the two lines "where uid >= 0" and add this after add a line in front of the comment as below:

Select * from #SnapBoth
-- Get the difference between the 2 snapshots, ms per ecid

and post results along with what task manager is saying for the sql server process at the time (not just the total shown in the graph as there might be other processes involved).  That will show delta cpu in sql for every ecid and give us the answer.

Hi,
Is there any chance for me to run the above statement in sql2005

I believe it works the same.

Hi Mastoo,
Its work perfectly and at least it help me while do the troubleshooting... but do u know why sometimes the values it more than 100%??? does it because of the delay while this statement capturing the cpu processing?

CPU            SPID
56      79
50      92
1      65
1      81

Yes, I'm happy if it comes within 25% because that still gets the job done.  I might attribute the inaccuracies to the wait being somewhat variable, maybe the underlying data isn't perfect, and some spid and ecid's come and go during the timed interval.  Thanks for giving me the feedback to refine the script.

Dear Mr. Mastoo:

one of my client is also facing the same problem for which I have posted the question, i need you also to kindly look and let me know can i run the same script there and check, as i not having full information about SQL 2005,

Kindly help me ...

My question posted link:

 Title:
MS SQL 2005 server Slow
07.24.2008 at 10:32PM GST, ID: 23593264 | Points: 500


tanveer.

I'll take a look.  That script works fine in 2005 also, just adjust it for number of processors.  The script is less necessary in 2005 though as 2005 comes with several built-in performance reports that show the same type of info.