programming4us
programming4us
DATABASE

SQL Server 2012 : Tuning Queries (part 2) - Gathering Query Information with Extended Events

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
6/17/2014 3:29:02 AM

2. Gathering Query Information with Extended Events

Extended Events are a mechanism for gathering and viewing detailed information about the queries being executed on your system, among other things. The events provide a means to gather this information in an automated fashion so that you can use them to identify long-running or frequently called procedures. You can also gather other types of real-time information such as users logging into or out of the system, error conditions such as a deadlocks, locking information, and transactions. But the primary use is in gathering information about stored procedures as they are executed and doing this over a period of time.

Extended Event sessions are created through T-SQL commands. You can learn the T-SQL necessary to set up the commands, but you can also take advantage of the graphical user interface that was introduced in SQL Server 2012. Extended Events offer a number of methods for output of the information gathered, and we’ll focus in the two most common here. You can set up Extended Events to output to a live feed that you can watch through SSMS. You can also, in addition, set up the session to output to a file so that you can gather information over time and then report on it later at your leisure.

Extended Events operate, as we’ve already mentioned, within a construct called a session. A session is simply the definition of which events are being collected and what output you want for the events. Each event collects a default set of columns that represent information about itself. There are some columns that you can add in addition to the default columns as well as additional sets of data called actions. These can be expensive operations from a performance standpoint, and using them inappropriately can seriously impact your servers. Our suggestion is to stay away from actions until you’re 100 percent sure you’re collecting the right kind of information. The standard events and their columns will provide most of what you need anyway.

There are two graphical user interfaces that you can take advantage of for setting up Extended Event sessions. Both create the same sets of events and will start the same types of sessions. One is the standard interface used to create and edit sessions. The other is a wizard that walks you through the process of creating a session. We’ll discuss both, but we’ll spend most of our time working with the wizard. To get started with the wizard, navigate through the Object Explorer window in SSMS to the Management folder. Open that folder. Inside it is another folder called Extended Events. Expand this folder to see the folder inside labeled Sessions. Right-click the Sessions folder, and select New Session Wizard from the context menu to open the wizard shown in Figure 8.

images

Figure 8. New Session Wizard for Extended Events

First, you supply a name for the session. This is a standard description and shouldn’t require much thought. We’re calling our session Gather Query Performance Metrics. You also have the option of starting a session when you start SQL Server. You can do this for gathering query metrics; just be prepared to deal with a lot of data. Once you’ve supplied a name, you can click the Next button to open the Choose Template page, shown in Figure 9.

images

Figure 9. Choosing a template for the Extended Events session

On this page of the wizard, you can decide if you want to use one of the Microsoft-supplied templates or put together your own particular session. The sessions supplied by Microsoft are a great foundation to get started with. Plus, you can edit the settings that they make for you, so you’re not locked in. For a basic session that gathers query metrics, I think the Query Batch Sampling template that I have selected in Figure 9 is more than adequate as a starting point. You can even read about the session in the description just below the drop-down menu. Once you’ve decided whether or not you’re using a template and selected a template if you need one, you can click the Next button. You’ll see the Select Events to Capture window, shown in Figure 10.

images

Figure 10. Selecting events for your Extended Events session

Three events are already selected on the right side of the screen: error_reported, rpc_completed, and sql_batch_completed. Below each of the events is a description, so you don’t have to try to decipher the names if they’re not completely clear to you. On the left side of the screen is a list of all the possible events you could capture. You can use the Search Events text box to find events and filter the information in different ways by selecting from the drop-down box next to it. If you do select additional events, you can use the little arrow buttons in the middle of the screen to move events in and out of the Selected Evens list.

The next screen is Capture Global Fields, and it contains the actions we suggested earlier that you avoid. Click Next again to open the Set Session Event Filters window, shown in Figure 11.

images

Figure 11. Set Session Event Filters in the Extended Events wizard

Three filters are already created for the three events in the template. You can add additional filters using the wizard page shown in Figure 11. You can’t edit the filters for the template within the wizard, but you can edit them once the session is created using the standard session editor. If you hover your mouse over the Predicate column for the events, you can get a look at the filters already applied, as shown in Figure 13.

images

Figure 12. Extended Event filter

The filter shown in Figure 12 tried to capture events only where the session_id is divisible by 5, therefore eliminating a substantial number of events but still capturing a representative sample. This is a valid method for gathering performance metrics on the system while keeping the overall amount of data collected low. If you’re looking for more accurate information, you’d need to edit this filter after it is created. The rest of the filter is eliminating calls to the system databases.

Clicking the Next button will open the Specify Data Storage window, visible in Figure 13.

images

Figure 13. Determining where the session will output through the Specify Data Storage window

Finally, you need to determine where the information gathered during the Extended Events session will go. You can specify output to a file, to the window, or both at the same time. If you’re trying to collect performance metrics about your system, you should plan on having this information go out to a file. You can then load that information into tables at a later date for querying and aggregating the information to identify the most frequently called or most resource-intensive query.

Clicking Next will bring up a summary window where you can see all the choices that have been made while setting up this session. The back button is always available, and you can use the window choices on the left side of the screen to go back to a previous step and edit it.

When you click the Finish button, the session is created, but it is not started. This means you can decide when you want to start the session. You do get the opportunity to start it from the wizard, but having the session created, but not started, is a great way to allow you to get into the session and make any adjustments you want without having to deal with data that doesn’t meet your requirements. For the purposes of this example, we’ve started the session and launched the viewing window. The output is visible in Figure 14.

images

Figure 14. Extended Events session output

The window is divided into two parts. At the top is a series of events as they occur. Selecting a particular event will open the details in the lower window. There, you can see all the metrics that make Extended Events so incredibly useful for performance tuning. You can see the query that was called in the batch_text column. And you can see the rows returned, the number writes and reads, and the duration of the query—all necessary pieces of information when determining if the query is running fast enough or not.

Other  
  •  SQL Server :ONE-WAY ENCRYPTION - Creating the Interface (part 2) - Setting and Verifying Permissions to the Stored Procedures
  •  SQL Server :ONE-WAY ENCRYPTION - Creating the Interface (part 1) - Creating the View, Creating the Stored Procedures
  •  SQL Server : Implementing One-Way Encryption (part 1) - Populate the Hash Columns, Verify the Implementation, Drop the Unencrypted Column
  •  SQL Server : Implementing One-Way Encryption (part 1) - Create the Primary Hash Column,Create a Secondary Hash Column for Searching
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - Hierarchical Table Indexing Strategies
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - Populating the Hierarchy (part 3) - The GetAncestor Method
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - Populating the Hierarchy (part 2) - The ToString Method
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - Populating the Hierarchy (part 1)
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - The hierarchyid Data Type, Creating a Hierarchical Table
  •  Personal Cloud WD My Cloud EX2 Review
  •  
    programming4us
     
     
    programming4us