Identifying Querys causing CPU spike

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Identifying Querys causing CPU spike

Identifying Querys causing CPU spike

Rate Topic

Display Mode

Topic Options

Author

Message

speja01

Posted Thursday, December 13, 2012 11:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 1:52 PM
Points: 26, Visits: 253

Hi Everyone,

I have a situation where the CPU on my data warehouse server was pegged at 100% for approx. 10 minutes yesterday at 4:00pm. Is there anyway of finding out what sql was running against the database at that particular time yesterday? I know there are DMV's that will show you the top CPU consumers, but I want to be able to prove those querys were running at that specific time.

Post #1396320

j.miner

Posted Saturday, December 15, 2012 8:58 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 79, Visits: 280

Take a look at Brent Ozars article on "playing doctor".

It lists a bunch of free scripts out there to help identify problems such as high CPU.

http://www.brentozar.com/sql-server-training-videos/playing-doctor-with-dmvs/

Take a look at the script from Glenn Barry. It has a ton of diagnosis queries. There are two queries you want to search for. I enclosed just the description since I Glenn did all the work.

A - CPU

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure

B - MEMORY

-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure

Good luck on finding your issue.

John Miner
Crafty DBA
www.craftydba.com

Post #1396960

anthony.green

Posted Monday, December 17, 2012 2:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Poor Performing SQL Part 1 - http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
Poor Performing SQL Part 2 - http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397154

sharky

Posted Monday, December 17, 2012 8:53 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 1:40 AM
Points: 56, Visits: 361

The problem with DMV analysis as well as trace events, is that you can't find out which specific procedures/statements caused a spike at a certain time. The trace have to be running on the production server at the time to capture these.
It is possible that some of these events might have been caught with the system_health extended event session that's running by default, but not if it was only a standard CPU consuming query. It's worth to have a look anyway.

I would look at the sys.dm_exec_query_stats and get the average of cpu time per execution and try to find out the possible culprtis (total_worker_time/execution_count), and set up an extended event session on a test server to try to reproduce results, or set up a light weight session on production if you monitor it closely.
Since the introduction of the much more flexible and lightweight extended events I try to stay away from traces(most are avalable in XE) , and they will probably be phased out anyway. Look at http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/an-xevent-a-day-1-31-an-overview-of-extended-events.aspx for an excelent overview..

Post #1397300

j.miner

Posted Monday, December 17, 2012 2:51 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 79, Visits: 280

Hi Sharky,

I do not totally agree with you!

A - Did you even look at Glenn Barry's scripts?

-- Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost
-- Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure
-- Top Cached SPs By Total Logical Writes (SQL 2008). Logical writes relate to both memory and disk I/O pressure

All of these are based upon the 'sys.dm_exec_procedure_stats' DMV. While this will not tell you all the spids that are running at the time of the CPU spike, it will tell you the problem queries that are sucking up CPU, Memory & Disk. By improving these queries, the overall performance should increase.

B - A tool like SQL Sentry Performance monitor and event manager will find the needle in the hay stack at $1500-1995 dollars per server.

http://www.sqlsentry.com/solutions-sql-server.asp

C - A caution about extended events and SQL Server traces. Both do occur an overhead. A nice article by Jonathan Kehayias comparing the two.

http://www.sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events

D - Even if you have a SQL trace file, you still have to weed through all the connections (spids) and queries running at that time.

I suggest trying out Clear Trace to weed through the hay. http://www.sqlmag.com/article/performance/cleartrace

In short, I do not think a trace will show the smoking gun a very busy system without a bunch of analysis.

John

John Miner
Crafty DBA
www.craftydba.com

Post #1397438

sharky

Posted Tuesday, December 18, 2012 10:35 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 1:40 AM
Points: 56, Visits: 361

Hi John,

Dont disagree with you. Glen's DMV scripts is still the benchmark. Fair point about looking at memory consumers as well, as they might have a secondary CPU spike effect.

His problem was just that he knew about the DMV's but wanted to find out a specific without a trace or XE set and thats not possible. That's why I suggest looking at the default system_health XE. As you said, XE although more lightweight will still require overhead(as would an external tool). Better to use it on a test server.

Regards
Stan

Post #1397923

« Prev Topic | Next Topic »

Permissions