SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server CPU Usage History Histogram


SQL Server CPU Usage History Histogram

Author
Message
Fatherjack
Fatherjack
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 627
Comments posted to this topic are about the item SQL Server CPU Usage History Histogram
fschreuder
fschreuder
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 200
Super!
Ad-hoc analysis for the cowboy DBA.

Might I suggest you extend this with Memory Usage History? :-)

Off course, in my opinion, there is no substitute for a well designed, thoroughly tested and verified monitoring system implemented by a big firm.
But that's just me ;-)

Thank you and keep rocking!

[/edit]: language difficulties
gpatterson-825050
gpatterson-825050
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 381
Thanks for the useful procedure.

One small correction:

I believe the "datas" CTE should use "ROW_NUMBER() OVER ( ORDER BY [Sample Time] DESC )". It is currently in ascending order, which is yielding incorrect results in my testing since it is classifying any recent CPU spikes as having occurred exactly 240 minutes ago.
Carolyn S. White
Carolyn S. White
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 25
hmmm, I have run this twice an hour apart and the results are always 0 in every cell.


Last n minutes range Avg SQL CPU Min SQL CPU Max SQL CPU
10 0 0 0
30 0 0 0
60 0 0 0
120 0 0 0
240 0 0 0
Fatherjack
Fatherjack
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 627
@ gpatterson - Yep, you could be right. Hope it proves useful for you. Smile
@ Carolyn - Lots to consider: Does the underlying query produce any values? Is the server in use? etc
webrunner
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4904 Visits: 3934
FYI I got this error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@ts_now".
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'AS'.

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Fatherjack
Fatherjack
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 627
You might be using this code against an earlier version of SQL Server so your results will be mixed. It looks like you need to declare the variable and then assign a value to it:

DECLARE @ts_now BIGINT
SET @ts_now = ( SELECT cpu_ticks / ( cpu_ticks
/ ms_ticks )
FROM [sys].[dm_os_sys_info]
);

That will fix the syntax problem but whether your version of SQL Server has the same DMVs with the same data will not be fixable if you find further errors, this code is only tested on versions from 2008 and up.
Iwas Bornready
Iwas Bornready
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16188 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search