Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2011-10-06 : 06:35:11
|
| the cpu on the server with sql is often peaking what is the best way for me to analyze what is causing it to peak so that I can optimize it?I really want to be able to log and then view what was using the cpu during times when the cpu is over 80%?Please advise |
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-10-06 : 09:06:08
|
| simple way is to see which processes are using cpu using task manager.You can also use perfmon>processes to see details of cpu used by various processes. |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2011-10-06 : 09:17:49
|
| it is sql server as the server is used only for sql server - but how do I know which tasks (queries,stored procedures) are tying up the cpu at points when it's at 100%I'd like to be able to log it and then look back at the times when it was 100% |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-10-06 : 09:38:06
|
| You can use sp_who or sp_who2 to give you an indication of the tasks running within SQL Server.If the CPU is running high it is likely to be poor performing queries. You can catch this is SQL Profiler.Junior DBA learning the ropes |
|
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-10-06 : 09:44:15
|
| You will need to run a server side trace that will keep on capturing which all queries were run. This does utilize some resources on the server, however will give you entire details of which all queries were executing during high cpu usage.Alternatively, you can also use sys.dm_exec_query_stats to take a look at top queries taking highest cpu or IO since the time SQL Server was started. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-06 : 09:57:26
|
| [url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]--Gail ShawSQL Server MVP |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2011-10-06 : 10:25:05
|
| the biggest problem is I can't always catch when it uses the cpu 100% as by the time I check it's down(but i get an alert of a timeout error)You will need to run a server side trace that will keep on capturing which all queries were run. This does utilize some resources on the server, however will give you entire details of which all queries were executing during high cpu usage.How would I know which times were high cpu usage? |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-10-06 : 11:00:04
|
| If you follow the steps outlined in Gail's article you'll be able to identify these.Junior DBA learning the ropes |
|
|
|
|
|
|
best way to analyze what is using cpu