[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

What to do when SQL server CPU spikes

Posted on 2006-11-21
3
Medium Priority
?
934 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 1000 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 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

649 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