Solved

What to do when SQL server CPU spikes

Posted on 2006-11-21
3
925 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

726 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