May 15, 2009 at 12:43 am
HDMan (5/14/2009)
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!
Glad I could help and that you learned something from it as well.
Good Luck
[font="Verdana"]Markus Bohse[/font]
August 10, 2009 at 1:18 pm
How can I modify your script to group or order by TbName?
August 11, 2009 at 12:44 am
bpowers (8/10/2009)
How can I modify your script to group or order by TbName?
I'm not sure what you mean. My script has no column called tbname.
If you mean tablename, you need to convert the qt.objectid to the name and add an order by. But since the script runs under the context of the master database you need to use a subquery to get the correct obejct_name.
[font="Verdana"]Markus Bohse[/font]
August 11, 2009 at 7:01 am
Thanks
August 12, 2009 at 9:26 am
colin Leversuch-Roberts (5/14/2009)
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
I ran the above query on my DB but I'm trying to see if this query will provide me with information as to how long the query took to run, etc? Or is this question irrelevant to what's being discussed (CPU usage, etc.)
--
:hehe:
August 13, 2009 at 3:00 am
The query you mention helps you to find longrunning queries which use a lot of CPU time, but it won't tell you the overall % CPU usage at that specific moment. The query I provided is intended to raise an alarm as soon as the CPU usage goes over a certain threshold.
While (longrunning) queries might use a lot of CPU, most of the time the CPU usage stays low. The query might take a while, but it has little or no negative effect on the overall performance of the system. You might still want to tune them, but it's a different issue from the one discussed a the beginning of this thread.
[font="Verdana"]Markus Bohse[/font]
August 13, 2009 at 7:30 am
Hi Markus
What executable (e.g., sqlservr.exe) does your script monitor for CPU usage?
thanks
August 14, 2009 at 4:02 am
rew (8/13/2009)
Hi MarkusWhat executable (e.g., sqlservr.exe) does your script monitor for CPU usage?
thanks
Yes, the script uses the DMV sys.dm_os_ring_buffers, but it's basically the same as if you monitor the performance counter: Process\%Processortime used\sqlservr.
[font="Verdana"]Markus Bohse[/font]
August 14, 2009 at 7:43 am
Thanks Markus for that clarification.
August 15, 2009 at 1:58 pm
Mark the record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS the path is this a specific path somewhere. Did you set this up in master or just any "DBSYSTEMDB"
Cheers
August 16, 2009 at 5:49 am
TRACEY (8/15/2009)
Mark the record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS the path is this a specific path somewhere. Did you set this up in master or just any "DBSYSTEMDB"Cheers
Tracey, if you do a simple select * from sys.dm_os_ring_buffers you will see that it contains a column "record" which contains XML data. The path above is used in a xquery expression to get the value for the counter ProcessUtilization.
So no, it's not a path you have to configure anywhere in your system. Unfortuantely sys.dm_os_ring_buffers is not officially documented and MS reserves the right to change it in future versions, but at least for 2008 it's still the same.
[font="Verdana"]Markus Bohse[/font]
August 16, 2009 at 8:27 am
Thanks Mark
September 1, 2009 at 7:05 am
Hi,
I'm a little late to this but am trying to get a baseline CPU trace of one of my servers and found the thread.
Can anyone advise me whether the dm_os_ring_buffers data is instance specific or for the whole hardware please?
I have 2x Dual Core CPUs and two SQL Std instances;
If I see 20% SQLProcessUtilization , 60% SystemIdle by running a query does that mean the remaining 20% is the other SQL instance and all 'other' processes?
regards
Jonathan
September 1, 2009 at 8:48 am
jonathan allen (9/1/2009)
Hi,I'm a little late to this but am trying to get a baseline CPU trace of one of my servers and found the thread.
Can anyone advise me whether the dm_os_ring_buffers data is instance specific or for the whole hardware please?
I have 2x Dual Core CPUs and two SQL Std instances;
If I see 20% SQLProcessUtilization , 60% SystemIdle by running a query does that mean the remaining 20% is the other SQL instance and all 'other' processes?
regards
Jonathan
Hi Jonathan,
yes you're right. The value for SQLProcessUtilization is per Instance and if you have two instances in your example than it save to assume that the "missing" 20 % are used by the second instance. Of course there could be other processes as well using CPU, but on a dedicated SQL Server these shouldn't play a big role.
[font="Verdana"]Markus Bohse[/font]
November 26, 2009 at 9:37 pm
Hi
Please evaluate @nalytics Performance Free Data Collector for Microsoft SQL Server &
Windows Server, this tool can help you to solve your performance problems and get
performance archive history information
Regards
@Analytics Peformance - Microsoft SQL Server & Windwos Server Data Collector
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply