May 14, 2009 at 6:21 am
My SQL server process is suddenly consuming up to (and sometimes more than) 90% of the CPU. I can't seem to find out what is causing it. My belief is that it is due to a query that one of our clients is running. Does anyone know of a good monitoring tool that can show real time stats and tell me exactly which SQL command is consuming the CPU? I'm running SQL Server 2005.
Thanks in advance for your help and advice!
May 14, 2009 at 6:35 am
The one you already have is SQL Server Profiler ! ("C:\Program Files\Microsoft SQL Server\90\Tools\Binn\PROFILER90.EXE")
Start a trace and analyse it.
3th party tools:
- Quest software : Spotlight for SQL Server
- Idera : SQL diagnostic manager
- Red Gate: SQL Response
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 14, 2009 at 6:47 am
you can't directly map cpu% ( e.g. from perfmon ) to a specific sql query very easily.
try this
--This query will list the top 50 queries which used the most CPU time based on average CPU time.
SELECT TOP 50 qs.total_worker_time/qs.execution_count as [Avg CPU Time],
substring (qt.text,qs.statement_start_offset/2,
(case
when qs.statement_end_offset = -1 then len (convert (nvarchar(max), qt.text)) * 2
else qs.statement_end_offset
end
- qs.statement_start_offset)/2)
as query_text,
qt.dbid, qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Avg CPU Time] DESC
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 14, 2009 at 6:51 am
Thank you, Colin - that's certainly a terrific start!
May 14, 2009 at 7:09 am
Colin - I have a question for you... you said it's not easy to map CPU% to a specific sql query. I'm not anywhere near a SQL guru (but learning quickly), so if it's possible, could you point me in a direction where I can find information and learn how to do so?
That would be SO great!
May 14, 2009 at 7:23 am
colin Leversuch-Roberts (5/14/2009)
...
I thought he was beyond that point.
Off course that is the way to start any investigation !
HDMan,
Keep in mind your largest consumer may actually have a small avg cpu time ! ( loops )
Play around with the provided query to refine your goals.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 14, 2009 at 7:35 am
I once had a similar situation where every once in a while the CPU usage would go crazy.
Because we couldn't predict when this would happen I created a job which would check the CPU usage of the SQL instance every minute and in case it got above 90% it would write the SQL statements for all active sessions into a logging table. It would also triger an alert, which would then send a mail to the DBA, so they could react as soon as possible.
It's not perfect, but it helped me a lot to trap such an intermittant issue we couldn't reproduce otherwise.
Here's most of the code I used:
-- First create a logtable
CREATE TABLE dbo.HighCpuSessions (
ExecTime smalldatetime,
Session_id smallint,
Status nvarchar(60),
Query nvarchar(max ),
Database_id smallint,
[Object_id] int,
Cpu_time bigint,
Duration_ms int,
Reads bigint,
Writes bigint,
Logical_reads bigint,
Scheduler_Id int)
-- This procedure returns the current value of CPU usage by the SQL Server instance
CREATE PROCEDURE dbo.usp_Current_CPU @output smallint OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms)
FROM sys.dm_os_sys_info
SET @output = (SELECT TOP 1 SQLProcessUtilization
FROM (
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
FROM (
SELECT TIMESTAMP, CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%%') AS x
) AS y
ORDER BY record_id DESC)
END
GO
-- Place the following code into a job and execute it every minute or so
DECLARE @output smallint
EXECUTE dbo.usp_Current_CPU @output OUT
IF @output > 90
BEGIN
-- Get all active sessions
INSERT INTO dbo.HighCpuSessions
SELECT GETDATE()
,r.session_id
,status
,SUBSTRING(qt.text,r.statement_start_offset/2,
(CASE
WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)
AS query_text -- this is the statement executing right now
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
-- You can add an RAISERROR statement to trigger an Alert which when send s a mail to the DBA whenever CPU Usage is too high
[font="Verdana"]Markus Bohse[/font]
May 14, 2009 at 7:45 am
Thanks Markus - I'll try that too.
For all of you offering help (which is HUGELY appreciated), I'm actually in the middle of this problem at this very minute. What Markus described is exactly what is happening to our servers. We have 2 production SQL Servers (SQL Server 2005), each with approximately 200 client databases. Both have been running fine for months and months. About 3 weeks ago, our developers had a HUGE release. Ever since then, the servers have been unreliable. Just looking at the Task manager, I can see the CPU usage from the SQL process jumping from 10%-15% to 80%-90% (sometimes higher), staying up there for a few minutes, then falling back down. Considering the fact that the recent release altered thousands of SQL procedures (and created new ones), and that's when the problems started, I think it's safe to say that someone released something that's not performing well. The problem is that there were WAY too many objects created/updated to be able to go through them one by one looking for performance enhancements. So I was asked to see if I am able to pinpoint the offending query/procedure at the time the CPU jumps up. My problem is that when it happens, obviously everyone wants an answer right that second, and I can't convince them that it takes time to find the answer. So I'm just trying to find out the fastest way to find out what's killing the CPU at any given moment.
Thanks again to all of you - I SO appreciate your help!
May 14, 2009 at 8:09 am
so .... after migration/implement of a new version of your dbs, things started going the wrong way.
Did you rebuild your databases indexes after the big maintenance ?
How are your current fragmentation rates ??
declare @DbId int
Select @DbId=db_id('yourdb') -- modify db name !
Select quotename(object_schema_name(object_id)) + '.' + quotename(object_name(object_id)) as TbName
, Page_count * 8 /1024 as Pages_MB
, *
from SYS.DM_DB_INDEX_PHYSICAL_STATS (@DbID,NULL,NULL,NULL,'detailed' )
order by Pages_MB desc, avg_fragmentation_in_percent desc
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 14, 2009 at 8:24 am
I have a maintenance script that runs once a week, and part of that rebuilds indexes. I also rebuilt the indexes immediately after the release, so they have been rebuilt a few times since the release. I just checked (just in case) and the fragmentation levels look great.
May 14, 2009 at 9:15 am
PROGRESS!!!! Thanks to your script MarkusB, I was able to identify a stored procedure that was running every time the CPU spiked. The developer ran tests on our testing server, and we could watch the CPU spike every time he ran the procedure, and drop back down when he stopped it (or it finished). He's reviewing the procedure now to improve the performance, so it's probable that we'll have a fix released sometime today. I won't say just yet that procedure was the ONLY culprit, but we're making progress!
Thank you so much - your script is great! And I learned a few things from it too!
Also, thanks to everyone else that offered help - I really appreciate all of you!
May 14, 2009 at 12:30 pm
Post the procs DDL if your dev cannot find an optimal solution 😉
You'll have a good chance people over here will !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 14, 2009 at 12:43 pm
The developer found some things and released a fix about 2 hours ago (he's a pretty sharp guy). I've been monitoring our servers closely ever since, and the CPU looks terrific.
Thanks again for the help, all!
May 14, 2009 at 12:55 pm
Can you update is on what was the issue? Might help someone else down the road.
May 14, 2009 at 12:58 pm
The developer has left for the day, so I'll see if I can get some info from him tomorrow and post it!
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply