Solved

What to do when SQL server CPU spikes

Posted on 2006-11-21
3
920 Views
Last Modified: 2008-01-09
Hello experts -

I am by no means a SQL expert, so your assistance is appreciated.  We have two instances of SQL Server on a Windows 2003 Server...a 2000 version and a 2005 version.  We have just a handful of databases being hosted right now.  When the CPU spikes to 100% on that server, and I can see that at least one of the instances of sqlservr.exe is the problem....what can I do to determine which specific database is causing the spike?   Or at a minimum, how can I tell if it is the 2000 or the 2005 instance?  The process list simply shows two line items of sqlservr.exe.

Thanks!
0
Comment
Question by:rvthost
3 Comments
 
LVL 28

Accepted Solution

by:
imran_fast earned 250 total points
ID: 17987300
>>which specific database is causing the spike?   Or at a minimum, how can I tell if it is the 2000 or the 2005 instance?

You have to run profiler (Sql Tool) and see the cpu usage for sql quries this will help you a lot to identity the server the database and the table itself which is causing problem
This is how to run the trace using profiler
To create a trace
On the File menu, point to New, and then click Trace.
In the SQL Server list, select the server to be traced, and then select a connection.
Security Note  When possible, use Windows Authentication.
in the Trace name box, type a name for the trace, and then do the following:
In the Trace SQL Server list, select a server for the trace to run on.


In the Template name list, select a trace template on which the trace will be based.
Do one of the following:
click Save to file to capture the trace to a file in another location.


click Save to table to capture the trace to a database table.
Optionally, select the Enable trace stop time check box to specify a stop date and time.


To complete other trace properties, click the Events, Data Columns, or Filters tabs and set the options on these tabs.


Click Run when ready to start the trace.
0
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 250 total points
ID: 17987508
If the spikes are long enough that you want to see what sql is currently taxing the cpu, you can run the following against both sql instances for a rough idea:

-- 5/6/2003 sp
-- 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, 1 second apart.
-- 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 and ecid = 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 and ecid = 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,
    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
  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 Top 3 spid From #PerfDelta Order By CpuPercent Desc
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, 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
    Left Join #DbccOutput As DO
      On PD.spid = DO.spid
  Where SS.ecid = 0
    And SS.SnapNum = 2
  Order By CpuPercent Desc

  Drop Table #DbccOutput
  Drop Table #DbccOutputTemp

lblEnd:
  Drop Table #SnapShot
  Drop Table #PerfDelta

0
 
LVL 11

Author Comment

by:rvthost
ID: 18000337
Thanks both!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now