SQL Server CPU Spikes

19 Jan 2006 6:04 PM

Amos Soma

We have a server running both SQL Server 2000 and SQL Server 2005. Shortly
after installing SQL Server 2005, we started seeing spikes in CPU usage (75%
to 99%). This appears to be random. It sometimes lasts for many minutes. I
have a couple questions:

1) Is there a way I can tell in Windows Task Manager which version of SQL
Server is using all the CPU time? I suspect it's SQL Server 2005 but all I
see is an entry for 'sqlserv.exe'.
2) Is there some way I can tell exactly what SQL Server is doing during the
time it is spiking the CPU usage?

Thanks Amos.

19 Jan 2006 6:28 PM

Tibor Karaszi

1) In task manager, you can show the ProcessID. You can also ask each SQL Server instance which
ProcessID:
SELECT SERVERPROPERTY('ProcessID')

2) Profiler. With Profiler 2005, you can even correlate a Profiler trace with a Perfmon log. Do a
trace and a perfmon log at the same time. Save each to file. Then open the saved Profiler trace in
2005's Profiler GUI. Then you do Import performance Data. You can now "move around" and see when you
had your peeks and what SQL were submitted at that time. This also work with a 200 trace.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quoteHide quote

"Amos Soma" <amos_j_s***@yahoo.com> wrote in message news:%23n92KLSHGHA.3624@TK2MSFTNGP09.phx.gbl...
> We have a server running both SQL Server 2000 and SQL Server 2005. Shortly after installing SQL
> Server 2005, we started seeing spikes in CPU usage (75% to 99%). This appears to be random. It
> sometimes lasts for many minutes. I have a couple questions:
>
> 1) Is there a way I can tell in Windows Task Manager which version of SQL Server is using all the
> CPU time? I suspect it's SQL Server 2005 but all I see is an entry for 'sqlserv.exe'.
> 2) Is there some way I can tell exactly what SQL Server is doing during the time it is spiking the
> CPU usage?
>
> Thanks Amos.
>