May 19, 2011 at 12:24 pm
The CPU Usage % in the sys.dm_os_performance_counters is integer value that could spike up to thousands. For presenstation sake, we wanted to normalize this to reflect its the counter name. What is the factor were going to use for normalizing it?
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
May 20, 2011 at 2:41 am
Many of the counters don't work quite like that I'm afraid. I don't believe that the cpu counters in sys.dm_os_performance_counters actually record cpu like that. I capture cpu but from perfmon.
If you actually think about it there would be little point in sql server capturing server cpu figures as they wouldn't relate directly to sql server, because there's lots of other stuff running.
I admit I haven't gone through every new counter that's been added in 2008 and 2008 R2 so I might be wrong. I've long wished that we could have a documented list of perfmon counters and descriptions ( not only sql server )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 20, 2011 at 4:20 am
check cpu usage per query this will be helpfull to optimize the query as well and utilization of the CPU and RAM also
select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
order by cpu desc
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 20, 2011 at 8:18 am
cpu reported inside sql server dmvs has little bearing on anything you can relate to the cpu %age counter in perfmon. Most of these measurements are cpu milliseconds with some being "ticks" or cycles. You can use these values to compare, for instance I monitor queries over a certain duration and I can see the cpu used, for example one proc has a cpu value of 873, it ran in 9 seconds and used 20,663 page reads. I have another which has a cpu value of 34,710 this query took 38 seconds to run and 18.2 million page reads. None of this directly relates to the 30-40% cpu utilisation on my server - there's no obvious spike for the heavy query.
As I say the values are useful for a comparision, if you're tuning then making a change and watching how these values alter is useful.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply