SQL Server Profiler

Online learning

​fREE

SQL DBA SCHOOL

Founder Abubeker Refaw

​​This chapter explores the SQL Server Profiler, one of SQL Server’s most powerful auditing and analysis tools. The SQL Server Profiler gives you a basic understanding of database access and helps you answer questions such as these. Do you have all the following question?

  •  Which queries are causing table scans on my invoice history table?
  • Am I experiencing deadlocks, and, if so, why?
  • What SQL queries is each application submitting?
  • Which were the 10 worst-performing queries last week?
  • If I implement this alternative indexing scheme,
  • how will it affect my batch operations? 

SQL Server Profiler records activity that occurs on a SQL Server instance. The tool has a great deal of flexibility and can be customized for your needs. You can direct SQL Server Profiler to record output to a window, file, or table. You can specify which events to trace, the information to include in the trace, how you want that information grouped, and what filters you want to apply.


What’s New with SQL Server Profiler
The SQL Server 2008 Profiler is essentially the same as the SQL Server 2005 profiler. This is not surprising because many new features that were added with SQL Server 2005 addressed gaps identified in previous versions. The changes made in SQL Server 2008 are generally minor and include several new trace events, one new trace column, and several other minor changes to the profiler GUI screens.


SQL Server Profiler Architecture
SQL Server 2008 has both a server and a client-side component for tracing activity on a server. The SQL trace facility is the server-side component that manages queues of events initiated by event producers on the server. Extended stored procedures can be used to define the server-side events that are to be captured. These procedures, which define a SQL trace, are discussed later in this chapter, in the section, “Defining Server-Side Traces.” The SQL Profiler is the client-side tracing facility. It comes with a fully functional GUI that allows for real-time auditing of SQL Server events. When it is used to trace server activity, events that are part of a trace definition are gathered at the server. Any filters defined as part of the trace definition are applied, and the event data is queued for its final destination. The SQL Profiler application is the final destination when client-side tracing is used. The basic elements involved in this process are shown in the below figure.

















This above picture illustrates the following four steps in the process when tracing from the SQL Server Profiler:

1. Event producers, such as the Query Processor, Lock Manager, ODS, and so on, raise events for the SQL Server Profiler.

2. The filters define the information to submit to SQL Server Profiler. A producer will not send events if the event is not included in the filter.

3. SQL Server Profiler queues all the events.

4. SQL Server Profiler writes the events to each defined consumer, such as a flat file, a table, the Profiler client window, and so on.


In addition to obtaining its trace data from the event producers listed in step 1, you can also configure SQL Profiler so that it obtains its data from a previously saved location. This includes trace data saved in a file or table. The “Saving and Exporting Traces” section, later in this chapter, covers using trace files and trace tables in more detail.


Creating Traces
Because SQL Server Profiler can trace numerous events, it is easy to get lost when reading the trace output. You need to roughly determine the information you require and how you want the information grouped. For example, if you want to see the SQL statements that each user is submitting through an application, you could trace incoming SQL statements and group them by user and by application. When you have an idea about what you want to trace, you should launch the SQL Server
Profiler by selecting Start, then SQL Server 2008, then Performance Tools, and finally SQL Server Profiler. You also can launch it from within SSMS from the Tools menu. When you launch the Profiler, you are presented with an application window that is basically empty. To start a new trace, you select the File menu and choose New Trace. In the connection dialog that is displayed, you can enter the connectivity information for the server you want to trace. After the connection is established, the General tab of the Trace Properties window as shown in the below picture is displayed.

















​​The first place you should look when creating a new trace is at the trace templates. These templates contain predefined trace settings that address some common auditing needs. They have preset events, data columns, and filters targeted at specific profiling scenarios. The available trace templates, found in the template drop-down on the General tab of the Trace Properties window, as shown in the following 


















































































Source taken from msdn.microsoft.com/en-us/library/ms190176.aspx


​Keep in mind that the templates that come with SQL Server 2008 are not actual traces. They simply provide a foundation for you in creating your own traces. After you select a template, you can modify the trace setting and customize it for your own needs. You can then save the modified template as its own template file that will appear in the template drop-down list for future trace creation. Trace Name is another property you can modify on the General tab. Trace Name is a relatively unimportant trace property for future traces. When you create a new trace, you can specify a name for the trace; however, this trace name will not be used again. For instance, if you have a trace definition you like, you can save the trace definition as a template file. If you want to run the trace again in the future, you can create a new trace and select the template file that you saved. You will not be selecting the trace to run based on the trace name you entered originally. Trace Name is useful only if you are running multiple traces simultaneously and need to distinguish between them more easily.


Events

The events and data columns that will be captured by your Profiler trace are defined on the Events Selection tab. An example of the Events Selection tab is shown in Figure 6.3. The Events Selection tab consolidates the selection of events, data columns, and filters on one screen. One of the biggest advantages of the SQL Server 2008 Events Selection tab is that you can easily determine which data columns will be populated for each event by looking at the columns that have check boxes available for the event. For example, the Audit Login event has check boxes for Text Data, ApplicationName, and others but does not have a check box available for CPU, Reads, Writes, and other data columns that are not relevant to the event. For those data columns that have check boxes, you have the
 



















optionof unchecking the box so that the data column will not be populated for the event when the trace is run. You may find that adding events in SQL Server 2008 is a bit confusing. When you select a template, the event categories, selected events in those categories, and selected columns are displayed in the Events Selection tab. Now, if you want to add additional events, how do you do it? The answer to this question lies in the Show All Events check box in the lower-right corner of the Events Selection tab. When you click this check box, all the available event categories are listed on the screen.


The events and columns that you had previously selected may or may not be visible on the screen. They are not lost, but you may need to scroll down the Events Selection tab to find the event categories that contain the events you had selected prior to selecting the Show All Events check box. You will also notice that all the events in the categories in which you had events selected are displayed. In other words, if you had only 2 events selected in the Security Audit category and then selected the Show All Events check box, you see all 42 events listed. The only 2 events selected are the ones you had selected previously, but you need to wade through many events to see them. One upside to this kind of display is that you can easily view all the events for a category and the columns that relate to the events. One possible downside is that the Events Selection tab can be very busy, and it may take a little extra time to find what you are looking for.
 

If you capture too many events in one trace, the trace becomes difficult to review. Instead, you can create several traces, one for each type of information that you want to examine, and run them simultaneously. You can also choose to add or remove events after the trace has started. Keep in mind that you can pause a running trace, change the selected events, and restart the trace without losing the output that was there prior to pausing the trace.


Filters

Filters restrict the event data returned in your trace output. You can filter the events captured by the SQL Profiler via the Column Filters button on the Events Selection tab. An example of the Edit Filter window is shown in the below figure which shows how to filter Database. All the available columns for the trace are shown on the left side of the Edit Filter window. Those columns that have filters on them have a filter icon displayed next to the column in the column list.


 

 ​















The filtering options in SQL Server 2008 are similar to those available in SQL Server 2005. Which options are available depends on the type of column you are filtering on. The different filtering options are as follows:

  • Like/Not Like—This option enables you to include or exclude events based on a wildcard. You should use the % character as your wildcard character. When you have completed a filter definition you can press Enter to create an entry space for another filter definition. For example, with the ApplicationName filter, you can specify Like Microsoft%, and you get only those events related to applications that match the wildcard, such as Microsoft SQL Server Management Studio. This filtering option is available for text data columns and data columns that contain name information, such as NTUserName and ApplicationName
  • Equals/Not Equal To/Greater Than or Equal/Less Than or Equal—Filters with this option have all four of these conditions available. For the Equals and Not Equal To conditions, you can specify a single value or a series of values. For a series of values, you hit enter after each value is entered and a new entry space is created for you to enter the next value. For the other conditional types, a single value is supplied. For example, you can filter on DataBaseID and input numeric values under the Equals To node of the filtering tree. This filtering option is available for numeric data columns such as Duration, IndexId, and ObjectId.
  • Greater Than/Less Than—This type of filtering option is available only on timebased data columns. This includes StartTime and EndTime filters. These filters expect date formats of the form YYYY-MM-DD or YYYY-MM-DD HH:MM:SS. 


Each data column can use one of these three filtering options. When you click the data column available for filtering, you see the filtering options for that column displayed in the right pane of the Edit Filter window. You enter the values on which you want to filter in the data entry area on the filter tree. This input area is shown when you select a specific filtering option. For multiple filter values, you press the Enter key after you enter each value. This causes a new data entry area to appear below the value you were on.


Filters applied to columns that are not available or selected for an event do not prevent the event data from being returned. For example, if you place a filter on the ObjectName column and choose the SQL:StmtStarting event as part of your trace, the event data is not filtered because ObjectName is not a valid column for that event. This behavior may seem relatively intuitive, but it is something to consider when you are receiving output from a trace that you believe should have been filtered out. Also, be careful when specifying multiple filter values and consider the Boolean logic applied to them. When you specify multiple values for the Like filter, the values are evaluated with an OR condition. For example, if you create a filter on ObjectName and have a Like filter with values of A%, B%, and C%, the filter returns object names that start with A or B or C. When you use the Not Like filter, the AND condition is used on multiple values. For example, Not Like filter values for ObjectName of A% and C% result in objects with names that do not start with A and object names that do not start with C.


Executing Traces and Working with Trace Output
After you define the events and columns you want to capture in a trace, you can execute the Profiler trace. To do so, you click the Run button on the Trace Properties window, and the Profiler GUI starts capturing the events you have selected. The GUI contains a grid that is centrally located on the Profiler window, and newly captured events are scrolled on the screen as they are received. Figure 6.9 shows a simple example of the Profiler screen with output from an actively running trace.


The Profiler GUI provides many different options for dealing with an activelytracrunninge. You can turn off scrolling on the trace, pause the trace, stop the trace, and view the properties of an actively running trace. You can find strings within the trace output, and you can even move the columns around in the display so that they are displayed in a different order. These options provide a great deal of flexibility and allow you to focus on the output that is most important to you. 


Saving the Profiler GUI Output

Another option for saving trace output occurs after trace output has been generated to the Profiler GUI and the trace has been stopped. Similar to the save options for an executing trace, the GUI output can be saved to a file or table. You access the options to save the GUI output by selecting File, Save As. The Trace File and Trace Table options are used to save to a file or table consecutively. With SQL Server 2008, you can also save the output to an XML file. The Trace XML File and Trace XML File for Replay options generate XML output that can be edited or used as input for replay with the SQL Server Profiler.


Two distinct save operations are available in the SQL Profiler. You can save trace events to a file or table as just described, or you can save a trace definition in a template file. The Save As Trace Table and Save As Trace File options are for saving trace events to a file. The Save As Trace Template option saves the trace definition. Saving a trace template saves you the trouble of having to go through all the properties each time to set up the events, data columns, and filters for your favorite traces.


Importing Trace Files

A trace saved to a file or table can be read back into SQL Profiler at a later time for more detailed analysis or to replay the trace on the same SQL Server or another SQL Server instance. You can import data from a trace file or trace table by choosing File, Open and then selecting either a trace file or trace table. If you choose to open a trace file, you are presented with a dialog to locate the trace file on the local machine. If you choose to import a trace table, you are first presented with a connection dialog to specify the SQL Server name, the login ID, and the password to connect to it. When you are successfully connected, you are presented with a dialog to specify the database and name of the trace table you want to import from. After you specify the trace file or trace table to import into Profiler, the entire contents of the file or table are read in and displayed in a Profiler window.


You may find that large trace files or trace tables are difficult to analyze, and you may just want to analyze events associated with a specific application or table, or specific types of queries. To limit the amount of information displayed in the Profiler window, you can filter out the data displayed via the Properties dialog. You can choose which events and data columns you want to display and also specify conditions in the Filters tab to limit the rows displayed from the trace file or trace table. These options do not affect the information stored in the trace file or trace table—only what information is displayed in the Profiler window.


Importing a Trace File into a Trace Table

Although you can load a trace file directly into Profiler for analysis, very large files can be difficult to analyze. Profiler loads an entire file. For large files, this process can take quite awhile, and the responsiveness of Profiler might not be the best. Multiple trace output files for a given trace can also be cumbersome and difficult to manage when those files are large. You can use the trace filters to limit which rows are displayed but not which rows are imported into Profiler. You often end up with a bunch of rows displayed with no data in the columns you want to analyze.


In addition, while the filters allow you to limit which rows are displayed, they don’t really provide a means of running more complex reports on the data, such as generating counts of events or displaying the average query duration. Fortunately, SQL Server 2008 provides a way for you to selectively import a trace file into a trace table. When importing a trace file into a trace table, you can filter the data before it goes into the table as well as combine multiple files into a single trace table.


When the data is in a trace table, you can load the trace table into Profiler or write your own queries and reports against the trace table for more detailed analysis than is possible in Profiler. Microsoft SQL Server also includes some built-in user-defined functions for working with Profiler traces. The fn_trace_gettable function is used to import trace file data into a trace table. Following is the syntax for this function:


​fn_trace_gettable([ @filename = ] filename ,[ @numfiles = ] number_files )


This function returns the contents of the specified file as a table result set. You can use the result set from this function just as you would any table. By default, the function returns all possible Profiler columns, even if no data was captured for the column in the trace. To limit the columns returned, you specify the list of columns in the query. If you want to limit the rows retrieved from the trace file, you specify your search conditions in the WHERE clause. If your Profiler trace used rollover files to split the trace across multiple files, you can specify the number of files you want it to read in. If the default value of default is used, all rollover files for the trace are loaded. Listing 6.1 provides an example of creating and populating a trace table from a trace file, using SELECT INTO, and then adding rows by using an INSERT statement. Note that this example limits the columns and rows returned by specifying a column list and search conditions in the WHERE clause.


Creating and Inserting Trace Data into a Trace Table from a Trace File
/******************************************************************** ** NOTE - you will need to edit the path/filename on your system if **        you use this code to load your own trace files *********************************************************************/


select  EventClass,

           EventSubClass,

          TextData = convert(varchar(8000), TextData),

          BinaryData,

          ApplicationName,

          Duration,

          StartTime,

           EndTime,

           Reads,

          Writes,

          CPU,

          ObjectID,

          IndexID,

          NestLevel

          intoTraceTable

          FROM ::fn_trace_gettable(‘c:\temp\sampletrace_ 20090510_0622.trc’, default)

           where TextData is not null  

                      or EventClass in (16, —  Attention

                                                 25, — Lock:Deadlock

                                                  27, — Lock:Timeout

                                                  33, — Exception

                                                   58, — Auto Update Stats

                                                   59, — Lock:Deadlock Chain

                                                   79, — Missing Column Statistics

                                                   80, — Missing Join Predicate

                                                   92, — Data File Auto Grow

                                                   93, — Log File Auto Grow

                                                   94, — Data File Auto Shrink

                                                   95) — Log File Auto Shrink

Insert into TraceTable (EventClass, EventSubClass,

                 TextData, BinaryData,

                 ApplicationName, Duration, StartTime, EndTime, Reads, Writes,

                 CPU, ObjectID, IndexID, nestlevel)

       select EventClass, EventSubClass,

                 TextData = convert(varchar(7900), TextData), BinaryData,

                  ApplicationName, Duration, StartTime, EndTime, Reads, Writes,

                 CPU, ObjectID, IndexID, nestlevel

          FROM ::fn_trace_gettable(‘c:\temp\sampletrace_ 20090510_0205.trc’, -1)

          where TextData is not null

                 or EventClass in (16, —  Attention

                                              25, — Lock:Deadlock

                                              27, — Lock:Timeout

                                              33, — Exception

                                              58, — Auto Update Stats

                                              59, — Lock:Deadlock Chain

                                              79, — Missing Column Statistics

                                              80, — Missing Join Predicate

                                              92, — Data File Auto Grow

                                              93, — Log File Auto Grow

                                              94, — Data File Auto Shrink

                                              95) — Log File Auto Shrink

go

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

After the trace file is imported into a trace table, you can open the trace table in Profiler or run your own queries against the trace table from a query editor window in SSMS. For example, the following query returns the number of lock timeouts encountered for each table during the period the trace was running:
       select object_name(ObjectId), count(*)

       fromTraceTable

       where EventClass = 27 — Lock:Timout Event

       group by object_name(ObjectId)

go


 Analyzing Trace Output with the Database Engine Tuning Advisor

In addition to being able to manually analyze traces in Profiler, you can also use the Database Engine Tuning Advisor to analyze the queries captured in a trace and recommend changes to your indexing scheme. The Database Engine Tuning Advisor is a replacement for the Index Tuning Wizard that was available in SQL Server 2000. You can invoke it from the Tools menu in SQL Profiler. The Database Engine Tuning Advisor can read in a trace that was previously saved to a table or a file. This feature allows you to capture a workload, tune the indexing scheme, and re-run the trace to determine whether the index changes improved performance as expected.

Because the Database Engine Tuning Advisor analyzes SQL statements, you need to make sure that the trace includes one or more of the following events:

SP:StmtCompleted

SP:StmtStarting

SQL:BatchCompleted

SQL:BatchStarting

SQL:StmtCompleted

SQL:StmtStarting


One of each class (one SP: and one SQL:) is sufficient to capture dynamic SQL statements and statements embedded in stored procedures. You should also make sure that the trace includes the text data column, which contains the actual queries. The Database Engine Tuning Advisor analyzes the trace and gives you recommendations, along with an estimated improvement-in-execution time. You can choose to create indexes now or at  later time, or you can save the CREATE INDEX commands to a script file. 


​​Replaying Trace Data
To replay a trace, you must have a trace saved to a file or a table. The trace must be captured with certain trace events to enable playback. The required events are captured by default if you use the Profiler template TSQL_Replay. You can define a trace to be saved when you create or modify the trace definition. You can also save the current contents of the trace window to a file or table by using the Save As Trace File or Save As Trace Table options in the File menu. To replay a saved trace, you choose File and then Open to open a trace file or trace table. After you select the type of trace to replay, a grid with the trace columns selected in the original trace is displayed. At this point, you can either start the replay of the trace stepby-step or complete execution of the entire trace. The options for replaying the trace are found under the Replay menu. When you start the replay of the trace, the Connect to Server dialog is displayed, enabling you to choose the server that you want to replay the traces against. When you are connected to a server, a Replay Configuration dialog.


The first replay option, which is enabled by default, replays the trace in the same order in which it was captured and allows for debugging. The second option takes advantage of multiple threads; it optimizes performance but disables debugging. A third option involves specifying whether to display the replay results.


You would normally want to see the results, but for large trace executions, you might want to forgo displaying the results and send them to an output file instead. If you choose the option that allows for debugging, you can execute the trace in a manner similar to many programming tools.


You can set breakpoints, step through statements one at a time, or position the cursor on a statement within the trace and execute the statements from the beginning of the trace to the cursor position.


​Automating testing scripts is another important use of the SQL Profiler Save and Replay options. For instance, a trace of a heavy production load can be saved and rerun against a new release of the database to ensure that the new release has similar or improved performance characteristics and returns the same data results. The saved traces can help make regression testing much easier.


​NEXT PAGE>>>>>>>>