Solved

What to do when SQL server CPU spikes

Posted on 2006-11-21
3
916 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

746 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

13 Experts available now in Live!

Get 1:1 Help Now