CPU and Scheduler Performance Monitoring using SQL Server and Excel

Question

CPU and Scheduler Performance Monitoring using SQL Server and Excel

derek.colley

SSCrazy Eights

Points: 8040
More actions
October 3, 2012 at 9:47 pm

#268748

Comments posted to this topic are about the item CPU and Scheduler Performance Monitoring using SQL Server and Excel
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mike Stuart Right there with Babe Points: 760 More actions · Answer 1

In your proc GetProcessorUsage, you're referring to a column 'cpu_ticks_in_ms' which doesn't exist in sys.dm_os_sys_info in SQL 2008 - http://msdn.microsoft.com/en-us/library/ms175048.aspx

According to this article, the column has been removed from post-SQL 2005, and provides a solution of sorts - http://connect.microsoft.com/SQLServer/feedback/details/292638/katmai-give-us-back-the-cpu-ticks-in-ms-column-in-sys-dm-os-sys-info

HTH.

Mike

Mike Stuart Right there with Babe Points: 760 More actions · Answer 2

I spoke too soon - try this instead: http://blogs.msdn.com/b/vascov/archive/2008/09/30/using-performance-dashboard-with-sql-server-2008.aspx

I'm a complete neophyte with DMVs - any additional insight would be gladly welcomed!

Mike

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

Hi Mike, thanks for the links and your comments, I'll check them out tomorrow. Its always nice to learn something new!

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 4

Thank you for the article! Very clever (and free) way to have a monitoring dashboard. Assuming you use it yourself, I have a question about the overhead, especially since, as you pointed out, the data can potentially be collected every second. I suspect that on a busy server this can translate into something noticeable.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 5

Overhead will depend on how well you optimise the SQL-side stored procedure, I suppose. My version runs OK but might contribute to a performance problem. You could use techniques like WITH (NOLOCK), but this won't have much effect unless you're hammering the DMVs for some other purpose. Or adjust the data collection time interval to suit - i.e. if you could use the data every 30 seconds rather than every 5 seconds, you'll lower the load.

I adapted the techniques I explained in this article only the other day to build a browser-based view of the CPU/scheduler activity. We use Spotlight here, so the info complements the graphs and monitoring that Spotlight offers. It's interesting to watch load build on each scheduler and correlate that to increased CPU activity as shown by Spotlight. I've also used looping techniques to automatically refresh the browser window.

Attached is a screendump of the end result. The formatting is achieved by using VBScript to write a HTML file with CSS3 styling. The bar chart blocks are actually filled-in table cells. Because I use this at work I'm probably prohibited from sharing the code as-is, but if I get around to generalising it I'll publish it in the scripts section.

Thanks for your feedback.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Neha05 Default port Points: 1494 More actions · Answer 6

Neha05

Default port

Points: 1494

December 25, 2012 at 8:29 am

#1571429

Nice article.