July 9, 2010 at 7:30 am
I have a busy OLTP database and have since noticed that i tend to have random cpu spikes at different times of the day, I would like to nail down the SQL causing the spikes. I was wondering how i can identify these
I can also see that i do have some parallel query plans, but looking at the query plans how can i know which is bad and which is good, the reason why i say so is that there are some ah-hoc sql query reports which also run on the server.
July 9, 2010 at 8:27 am
I would take a look at SQL Profiler.
July 9, 2010 at 8:37 am
And specifically in Profiler, you can profile completed queries using a certain amount of CPU time. Set a threshold for what would be a "long" query and set the filter to be a little smaller than that threshold and profile all completed events that exceed that threshold. Let that run for about 30-60 minutes and I suspect you'll find what you seek.
July 10, 2010 at 1:18 am
Dean Jones-454305 (7/9/2010)
I have a busy OLTP database and have since noticed that i tend to have random cpu spikes at different times of the day, I would like to nail down the SQL causing the spikes. I was wondering how i can identify theseI can also see that i do have some parallel query plans, but looking at the query plans how can i know which is bad and which is good, the reason why i say so is that there are some ah-hoc sql query reports which also run on the server.
1. As already mentioned you can use Profiler to check which queries use the most CPU, or you can query sys.dm_exec_query_stats to find the ratio between total_worker_time and total_elapsed time to find the most CPU intensive queries. There are also som reports in SSMS that can give you the information you are looking for.
2. Regarding parallel queries; there is a common misunderstanding that this is bad. The performance problems usually occur when number of rows processed by each worker in a parallel execution is unevenly distributed, and the amount of query memory assigned to each worker is to small, causing spill to tempdb. This is explained and demonstrated in detail in these webcasts :
Can't remember exactly which one it is, but I can highly recommend all nine of them.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply