July 22, 2010 at 3:23 pm
I have ran profiler trace. In the column CPU it shows the time used in milliseconds? But how will this be helpfull to me in finding the query caused for CPU spike? Is there a way i can find out percentage of CPU used by each query? I wish profiler tool was much clear. I am not sure how to calculate and compare time with respect to percentage..please advice
July 22, 2010 at 3:44 pm
You can only derive the information from the CPU column. It doesn't provide a percentage.
What you can do to derive the percentage is calculate the entire CPU workload in the trace SUM(CPU) and then divide that by the CPU value. Add them all up and you should be close to 100%.
But the percentage doesn't necessarily matter. What matters is finding the high CPU values as well as the high Reads and Durations.
You will likely find a query that is missing an index as high sustained CPU utilization is typically caused by scans.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 22, 2010 at 5:59 pm
Each query has no idea how much percentage of the CPU was in use, and, given that a single query can run for minutes or hours, the average CPU used as a percentage of the total would be a fairly useless value. (Although as Tara's mentioned, it can be estimated by the CPU time that is presented by Profiler).
You generally don't need to do this, if you are monitoring Perfmon counters for the total CPU usage, and the usage performed by the sqlservr.exe process. This will tell you the total CPU usage a point in time, and whether it was SQL Server. If it was SQL Server, then you know how much of the total CPU was being used by the active queries at the time.
Profiler will give you each individual query that ran, and each individual query might not do a lot of CPU work. As Tara said, focus on the high CPU or high read queries, or those that have a high CPU as a percentage of their Duration.
July 22, 2010 at 8:59 pm
thanks for reply...sorry to ask this dumb question..but when can u say that CPU usage is high...i know sql server is using 98% of cpu...but lets say from the profiler trace i see that some query is using 3000 milli seconds...how would i know if this is high or not. My idea was that if i can convert time into % of CPU used...i can say that "x" % of total CPU was used by this query and this query needs to be looked into. How much would be the total time for CPU?
July 22, 2010 at 9:31 pm
It's not a dumb question. If you see a query that experienced 3000ms of CPU time, there are a few things to consider.
#1 - This query took 3000ms of time on the CPU(s) in total
#2 - How many CPUs were used by the query? Was the query processed parallel? If the Duration (difference between Start Time and End Time) is less than 3 seconds, then it is likely, but this isn't an accurate method of determining this, as I/O delays, or the processes being blocked by other queries can affect this.
#3 - If you have 8 processors, then you have 8000ms available per second of execution. Therefore, if your 3000ms query ran in 1 second of duration, then you were using 3/8ths or 37.5% of your CPU resources with that one query. If it took 3 seconds of duration, then you were using 1/8th of your CPU resources. If it took 9 seconds, then you were using 3000/(9*8000) = 3/72th = 0.04% (That's 9 seconds multiplied by 8000ms per second)
#4 - If your CPU was running at 100%, but 50% of that was from a virus scanner, then the available CPU resources to SQL Server would be 4000ms. This is why it's crucial to know if your 100% CPU usage is SQL Server's fault, or something else.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply