Query Correlation to Resource Usage - % of X

Last Post 29 Mar 2011 08:17 AM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQL_Jr
New Member
New Member

--
29 Mar 2011 06:48 AM
Hi,
         I know there must be a way, so I hope you can help.  How can I derive a percentage for queries of how much of the resources they use?  For example, we see at 5PM, the CPU usage is > 90%, at the same time, 10 queries are running - what percentage of the CPU at that time is being used by QueryX?  Same for memory and IO.  Can you provide a script that captures this information?  TIA!
gunneyk
New Member
New Member

--
29 Mar 2011 08:17 AM
That is very difficult to do per se. The counters in the DMV's themselves are not a percentage but a snapshot of the amount of CPU for a given connection since it last connected. You can take a snapshot view of everything that is running at that time and do a corelation to a previous snapshot to see the difference in CPU for each and then extrapolate the percentage relative to each otehr and the total of all of them. But I would recommend starting with Adam Machanics store proc called sp_whoisactive to get a picture of what is happening on your server at any given time. You can find it here: http://sqlblog.com/files/folders/be...34433.aspx
You are not authorized to post a reply.

Acceptable Use Policy