SQL Profiler - SQL Server 2008

SQL Profiler is the primary tool for use in performance analysis and tuning for SQL Server. It can be used to capture queries and statement events that are sent to a specific server.

For example, imagine that you’re a DBA in a large organization and are tasked to find out why a particular database application is running slow. The application is an inventory management tool. This is aWin32 application that uses SQL Server as its data storage engine. Users have reported that most of the time the application works well, except for when they try to obtain a report for the quantity of items left in their inventory. You start troubleshooting this by launching Profilr, from the Performance Tools menu in SQL Server.

When Profiler is launched, you essentially have a blank screen and a menu bar. From here, you can start a new trace or load an existing trace. Since this is our first time troubleshooting in this example, let’s choose New Trace from the File menu. This will cause a Connection dialog box to pop up asking on which server you want to perform a trace. We make a connection to the server that contains the Products database, which is the database that the Inventory management tool is using. Once connected, you are presented with the Trace Properties dialog box.

General tab of the Trace Properties dialog box

General tab of the Trace Properties dialog box

Here, you give the trace a name and specify where the trace should be created, in a file or in a table. Traces can also be configured to stop at a certain time, so if you had an issue that occurred at irregular intervals, you could set up a trace to run for a certain period of time in hopes of capturing some useful information.

Predefined templates are also available to help sort out which events to listen to. Eight templates are available, and if none of these is right for you, you can create your own templates. For this example, we will use the Standard (Default) template. If you click the Events Selection tab, you’ll see which events and columns this particular template is using, as shown in Figure. You can add or remove events from this tab.

Events Selection tab of the Trace Properties dialog box

Events Selection tab of the Trace Properties dialog box

You can see all the available choices by checking the Show All Events and Show All Columns check boxes. To reduce the amount of unneeded data, you can optionally filter your columns by clicking the Column Filters button. Since we are looking for long-running queries in this example, let’s put a filter on the Duration column to show only events that are longer than 2 seconds in duration, as shown in Figure.

Edit Filter dialog box

Edit Filter dialog box

Now that we’ve defined the trace, we can click Run in the Trace Properties dialog box to start recording. Once we have Profiler tracing SQL Server, we can run the problematic database application.

Assume that we ran the database application and noticed the performance problem that our users were complaining about. If we took a look at our Profiler trace, we can see the problematic T-SQL statement. If you click the row, the complete T-SQL will be displayed at the bottom of the screen, as shown in Figure.

Profiler trace with 2-second filter applied

Profiler trace with 2-second filter applied

Now that we see that this SELECT statement was the problem, we can notify the developer of this issue or take a look at optimizing the schema using indexes.

In SQL Server, trace definitions are dynamically loaded from the server. This allows Profiler to adjust its event selection based on the server it’s connecting to. For example, if in a future service pack, a new event is created or modified, Profiler will be able to adapt these new settings to its list of available events. Having a dynamic trace definition also makes it easier to add new server types from which to capture traces. Profiler can also trace and replay events from Analysis Services. There could be times when a Profiler user would want to automate trace collection, trace manipulation, and trace replay. This would allow users to establish performance benchmarks against their applications. There is now a set of objects under the Microsoft.SqlServer.Management.Trace namespace that allows users to programmatically achieve this.

Performance Monitor Correlation

Profiler can import Performance Monitor data and correlate it to a specific trace. The combination of these two technologies gives users a useful way to observe system performance and trace events simultaneously.

As an example, suppose we want to see if sending large amounts of Database Mail affects the CPU process. First we set up a Profiler trace to capture the Database Mail stored procedure calls, just as we did previously in this chapter by selecting File ➤New Trace. On the Events Selection tab, make sure to include at least the SQL:BatchCompleted event.

Before we start the trace, we need to define the Performance Monitor counter log that will be used to capture our Performance Monitor objects. To create a new counter log, open the Performance Monitor tool and select New Log Settings from the Counter Log node. Since we are interested in processor utilization but aren’t quite sure which counter to include, let’s include the Processor object of the local server. Click the Add Objects button and select this object in the Add Objects dialog box. Also include the Database Mail objects using the same Add Objects dialog box.

Add Objects dialog box in Performance Monitor

Add Objects dialog box in Performance Monitor

At this point, we can now start both the Profiler trace and Performance Monitor logging. Our environment is now set up so that we can execute a Database Mail script that will send 100 e-mailmessages. After the script is executed, we stop the Performance Monitor log and save the trace file. Now that we have both a performance log and trace file, we can use Profiler to correlate these two logs based on time. To do this, we first load the trace file using Profiler.

To load Performance Monitor data, select File ➤Import Performance Data. Next, select the performance counter log file that was just created. Profiler asks us which counters we want to correlate at this point. For this example, we’ll select % Processor Time and SendMailRequests. Once this selection is made, Profiler will show both the trace file and performance counters on the same screen.

Profiler showing Performance Monitor correlation

Profiler showing Performance Monitor correlation

The vertical line in the performance counter window correlates with the time of the event that is selected in the event grid. For example, if you wanted to know which statement was being executed when the CPU was at its peak, you could click the peak, and the event grid would highlight the statement that executed at that time. The reverse case is also true: if you select an event in the grid, the red line will move to the time period in the Performance Monitor log that contained this event.

Users can also zoom in and out of the Performance Monitor graph. This can be done by clicking the start time and dragging the mouse to the desired end time location. Once the mouse button is released, the Performance Monitor graph will adjust and show only the selected time period. The context menu of the Performance Monitor graph gives you the ability to zoom out or to show all the Performance Monitor data available.

Showplan

Showplan is a feature of SQL Server that allows you to obtain information about the data-retrieval methods chosen by the SQL Server query optimizer. Showplan appears in the form of an execution plan that includes information such as the execution cost of specific statements and queries. The actual plan information comes in the form of XML from the SQL Server database engine. The Profiler user interface interprets this information and forms the familiar Showplan execution as seen in SQL Server Management Studio.

To enable the Showplan feature in Profiler, select the Showplan XML event under the Performance category. Once this event is selected, the Events Extraction Settings tab will show up in the Trace Properties dialog box. This tab allows you to save the XML Showplan data separately to a file. You can also load the Showplan data in Management Studio for further analysis.

Showplan inside Profiler

Showplan inside Profiler

To manually save the current trace file for offline access, select File ➤Save As ➤Trace XML File. Later, you can load this trace file and view the saved Showplan by selecting File ➤Open ➤Trace File.

Showplans can become quite large and are difficult to view in a single screen. When a Showplan exceeds the size of the visible screen, a + button appears toward the bottom-right side of the Showplan output. When clicked, a small pop-up window shows a bird’s-eye view of the entire plan. The image was not intended to be completely legible; rather, it’s designed to show an outline of the complete plan.

Showplan navigational aid

Showplan navigational aid

You can move the smaller rectangle around using the mouse, which allows you to select the part of the plan that you want to see. This is a small but useful feature for those larger-than-life plans.

Deadlock Visualization

When Profiler captures this event, it shows the <deadlock-list> tag in the TextData column. To display a graphical visualization, simply click the row, and it will appear at the bottom of the screen.

Deadlock visualization through Profiler

Deadlock visualization through Profiler

Each ellipse is a process within SQL Server. In this example, we can see that both service process (SP) IDs 54 and 53 are trying to acquire th same lock. In the end, SP ID 54 was chosen as the victim, and its actions were rolled back. The rectangles show the resource IDs and some additional information about the object. As with Showplan XML, the deadlock graph can also be saved to a file separately through the same Events Extraction Settings tab of the Trace Properties dialog box. SQL Server Management Studio can also load the graph in its user interface for further analysis.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics