Monitor High CPU with Extended Events in SQL Server 2012 June 28th, 2012

Vinod Kumar

Extended Events has always been fascinating for me to learn in the previous editions too. But the only drawback of not having an effective UI means that this was tough to configure and more so to use. With SQL Server 2012, this limitation goes out of the door and now we can use some basic UI to work with Extended Events.

In this post, let me take a simple tour of creating a simple Extended Event via SSMS and then we will add appropriate filters to the collection to collect a specific event of some specified value. This is no way exhaustive of what Extended Events can do but surely can be a great starter.

What is Extended Events?

As per BOL, “Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.” – Read completely on this topic over TechNet.

Step 1: Initializing a session

To get started, Open SQL Server 2012 Management Studio and get to the Management Node. Expand and click on the “Extended Events” node and right click at the Sessions Folder. Select “New Session”.

image

Step 2: Session Name to start

The next step is to give a “Session Name” in the Text Box. Enter a value of “High_CPU” for example. This screen has multiple other Checkbox which allows you to start this Extended Event session upon SQL Server Startup etc. For this blog, we will not select any of them. Feel free to use these advanced features once you got a hang of Extended Events with SQL Sever 2012.

image

Step 3: Add an Event and Filter Values

Once the above is complete, now select the “Events” tab from the left-hand side menu. This will bring you the Events Library to select. Remember we want to get the Queries based on some high CPU value.

Under the “Event Library” text box, apply the filter for ShowPlan. This will get you 3 records like below. Select the “query_post_execution_showplan”. This occurs after a SQL statement is executed. This event returns an XML representation of the actual query plan and we will use the CPU values from this event to get badly performing queries. Once you Select use the small “Right Arrow” to move the event to “Selected Events” section. The wizard will look like below.

image

Step 4: Configure details

Once you reached this step, Click on the “Configure” button on the top right corner. This brings the Event Configuration Options. Select the “Filter (Predicate)” Tab. Apply some filters. For the demo, I am adding two filters on cpu_time and database_name as below. These can be different for you.

image

Step 5: Configure Storage Options

Now the once the event and filter conditions are configured, we need to next configure where we be collecting these events. For this demonstration, we have used a “event_file” option and are writing to “c:\temp” folder with the options like below.

image

This is the final step and click “OK” button at the bottom. We are all set now.

Step 6: Start the session now

Right click on the “High_CPU” node and select start session.

image

Now the configuration is complete and we are all set to start collecting data. Play around with the various options, events and destinations to configure your own collection.

===================================================================

Step 7: Generate a High CPU query

For this blog I am querying a very large table and doing an aggregate to simulate some CPU cycles. Once this is done. Just "double click” the file “c:\temp\High_CPU_x_xxxxx.xel”.

This will Open up the SSMS and you will be able to view the file with some interesting data.

image

This shows you that I have had a Stored Procedure – “High_CPU1” that is causing some 3313000 ms of CPU time and this qualified for the event capture and hence was collected. You can also use Live trace if you from one of the options of right-click on the session. That will be functioning like a typical Profiler sorts with a filteration of event.

Clicking on the Query Plan also reveals the query that was used inside the Stored Procedure that caused this CPU spike.

There are nice ways to now group, filter etc on Extended Events. But I will write it for a future post.

Conclusion

Want to wrap up saying, with SQL Server 2012 Extended Events are way more powerful than the conventional way of collecting data via Profiler. We will get into some more options and advanced data collection in the future. Do feel free to pass me your comments (if any) for future posts too.

Tags: , , , , , , ,

This entry was posted on Thursday, June 28th, 2012 at 22:01 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


11 Responses to “Monitor High CPU with Extended Events in SQL Server 2012”

  1. Jaime says:

    Great post!

    I didn’t know about Extended Events In SQL Sever. I was about to ask you about performance penalty considerations when I read the last comment from Jonathan.

    Do you know about performance risks while monitoring deadlocks with Extended Events?

    Thanks

    • Vinod Kumar says:

      Extended Events are not as bad as you might think. They surely are the way to go forward and knowing SQL Server Profiler is deprecated in SQL 2012. There will surely be investments and improvements to make sure they perform at the highest level. Thanks for your note Jaime.

  2. You should do performance testing and read this Connect item for this event:

    http://connect.microsoft.com/SQLServer/feedback/details/732870/sqlserver-query-post-execution-showplan-performance-impact

    If you turn the example session in this blog post on for a production server, it will reduce performance by 24%. I wouldn’t advocate anyone using this event until they fix the performance impact it has on SQL Server.

  3. Shyam Viking says:

    Nice in simplest words, and as usual best way of explaining. Thanks for sharing this, and also writing a technical post after a bit long time if my memory ain’t bad.

    • Vinod Kumar says:

      Ahhh Shyam – Thanks :) … I will surely write something technical every now and then for sure. Though the non-technical stuff will still be on, hehehehe.

  4. Vikrant S Patil says:

    Hi Vinod,

    This is really useful information explained in an easy to understand manner.
    Extended Events option seems to very lightweight but yet powerful when compared to profiler.

    Thanks,
    Vikrant S Patil

  5. Anup Warrier says:

    Good one Vinod.I have been using XEvents for Deadlock tracing and the UI makes life little more easy.

    Thanks for the info on query_post_execution_showplan.

Leave a Reply