August 30, 2011 at 7:36 am
Hi all
I have a SQL Server Active-Passive SQL Server 2005 Cluster where the CPU usage on the active server i between 90-99%. I can see that it is sqlserver.exe that take the CPU time and when I do a failover to the other server the CPU usage on the new active server i between 90-99% and on the inactive node the CPU usage goes down. I have restarted both nodes in the cluster but the CPU usage is still over 90% on the active nod. I have run SQL Server profiler to get trace fails but I can not figure out which of my SQL Server databases or process that take up most CPU time.
I get many exec sp_cursorfetch commands i the profiler log. What does that mean and how do I figure out which of my SQL Server databases or process that take up most CPU time?
September 2, 2011 at 1:27 am
Activity monitor is also the best tool to get performance related queries.
U can use in built report tool embedded into SSMS for getting various reports like
-> Top queries by Total CPU time
-> Top queries by Avg CPU time
-> Top queries by Total memory time
-> Top queries by Avg Memory time
-> CPU load database wise etc
U can try few DMVs for this purpose like sys.dm_exec_requests
U can try few catalog views for this purpose like sys.sysprocesses
SQLforU
info@sqlforu.com
For online training on SQL Server and Sybase, please contact
contact@sqlforu.com
www.sqlforu.com
September 2, 2011 at 2:02 am
in the sense of conventional cpu you cannot breakdown the 90% into %age per database. cpu is stored as ticks or cycles. Technically you could track back the processid back to perfmon and extract the cpu for that process - however trying to tie that in would be almost impossible unless all your queries run for long duration.
Technically it would only be your active processes which are using cpu as a help I suggest you install the performance dashboard http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=22602
I'm not sure how au-fait you are with running rdl reports against sql server? there's also the bulit in reports , I blogged those here http://sqlblogcasts.com/blogs/grumpyolddba/archive/2011/06/07/ssms-built-in-reports-for-server-and-database-monitoring.aspx this was for sql 2008 R2 but 2005 is similar
you can mail me direct if you need any help with the performance dashboard - essentially you have a bunch of procs and functions you put in msdb, and a set of rdls you can put on your workstation or server ( or both ) my work email is colin.leversuchroberts (((at))) regus.com I'm based in UK so here until 17:00 UK time today.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 2, 2011 at 11:36 am
You might be able to figure out what is happening by using wait stats. I'd start with this post[/url] by Brent Ozar.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply