How To Gather a SQL Trace & Simple Query Suggestions

Version 5

    DISCLAIMER

    The following article contains information on manipulating your database.

    It is CRITICAL that you have a full backup of your database prior to making any modifications to the database.

    Failure to backup your database may result in permanent loss of some, or even all data.

     

    Please consult with your DBA where applicable.

     

    Microsoft - Create a Full Database Backup (SQL Server)

     

    How to backup a database - Microsoft SQL Management Studio

     

    Purpose

     

    This article aims to cover capturing a basic SQL trace, and querying out useful items.

     

    Note - The examples used here are on SQL Server 2014. The steps may deviate slightly on different versions, but should still be pretty close regardless.

     

     

     

    Create Trace DB

     

    After we run our SQL trace, we will want to save it as a table so we can use query it for relevant data.

    One DB can be created to save all future traces to.

    You do not need a new DB for every trace.

     

    • Open SQL Server 2014 Management Studio
    • Expand the SQL Servers name
    • Right click 'Databases'
    • Choose 'New Database...'

     

    1-make new db.png

     

    • Provide a Database name to identify the new DB

    2-newdb name.png

     

    • Click OK to save the new DB

     

    3-newdb created.png

     

    SQL Trace

     

    Create Template

    For your convenience, you may download and import the trace template attached to this document that is already configured according to the below instructions.

    • Open SQL Server Profiler
      • Start | All Programs | Microsoft SQL Server 2014 | Performance Tools | SQL Server 2014 Profiler
      • "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\PROFILER.EXE"
    • Click File | Templates | New Template...

    1-new template.png

     

    • Leave 'Select server type' as default
    • Enter a new Template Name

     

    2-name template.png

     

    • Click the Events Selection tab
    • Check Show All Events
    • Check Show All Columns
    • Locate the TextData column
      • You might need to scroll to the right for it to be visible.

    3-event selections.png

     

    • Scroll down the list, expanding every category, and checking every TextData box that is presented.
    • The last category listed is User Configurable - this does not need the TextData boxes checked.

    Tip - Rather than using your mouse to click each check box, you can click the first box, then use the down arrow to navigate, and the space bar to expand a category and/or check a box.

     

    • Click Save

     

    4-check all boxes.png

    Start Trace

     

    • In SQL Server Profiler click File | New Trace

    1-new trace.png

     

    • When prompted to connect to a SQL Server, enter your authentication info and click Connect

     

    2-login.png

     

    Note: As soon as you click Run, the trace will begin. It is advisable to have your test scenario prepared so you capture only information directly related to the event you are testing.

     

    3-templatename.png

     

    • The window will scroll as trace data is captured. When you have completed your test scenario, click the Stop icon in the tool bar to stop the trace.

    4-stop trace.png

     

     

     

    Save the Trace

     

    SQL Traces can be saved in a variety of formats. The 2 commonly used for our needs are a Trace Table, or a Trace File.

    • Trace Table - Use this option if looking at the trace locally in your environment.
    • Trace File - Use this option if sending a trace to be reviewed in a different environment.

    Trace Table

    • Click File | Save As | Trace Table...

    1-save.png

     

    • When prompted to connect to a SQL Server, enter your authentication info and click Connect

    2-login.png

     

    • In the Destination Table window, click the Database drop down and select the previously created TraceTable DB
    • Give the trace a name in the Table field.
    • Click Ok

    2-name-table.png

    • Open Microsoft SQL Server Management Studio and login
    • Expand the SQL Server name | Databases | Trace Database you created | Tables
    • The trace just saved should be listed as a table now, and available for querying out data

    3-table_in_db.png

     

     

    Trace File

    • Click File | Save As | Trace File...

    1-save.png

     

    • In the Save As window, provide a File Name, and leave the Save as type as a SQL Server Profiler trace files (*.trc)file
    • Click Save

    2-filename.png

     

    The saved *.trc file can be opened within SQL Server 2014 Profiler and re-saved as a Trace Table  for querying data in a different environment.

     

     

    Query for Useful Data

     

    Note: The below information is offered as a means to help understand from a high level querying from the saved trace. The specifics of what query you will vary depending on your needs.

     

    When tracing SQL queries that are used against your database, they will show up within the tables TextData column of your table.

    This means we want information from the TextData column.

     

    Select TextData
    From MyTraceEvent
     

     

    However SQL traces grow rapidly, and the longer they run, the more data that will be in the trace.

    Example - After running trace during 1 task that lasted several seconds, our sample trace contained >68,000 rows.

     

    We can narrow down the results for what we are after by using a WHERE clause in our query

     

    Select TextData
    From MyTraceEvent
    Where TextData like '%Something we care about%'
     

     

    This will help reduce the overall results found with the query however it requires that you have something to query for such as a Task IDN, or a Device Name.

     

    Typically queries submitted by LDMS will look something like this:

    exec sp_executesql....
     

     

    We can use this to refine our query results for only those events that may be queries we care about captured during our trace.

     

    Select TextData
    From MyTraceEvent
    Where TextData like '%exec sp_executesql%'
     

     

    Example - In our sample trace of > 68,000 rows, the above query returned 530 rows. Getting better.

     

    If still uncertain what specifically might be of use, we can narrow it further.

    Looking at the remaining results of the above query, you may notice lots of duplicates.

     

    We can use the DINSTICT keyword to retrieve only unique records.

    If however you try simply using the DISTINCT operator in the query above, you'll get this error:

     

    Select Distinct(TextData)
    From MyTraceEvent
    Where TextData like '%exec sp_executesql%'
    
    Msg 421, Level 16, State 1, Line 2
    The ntext data type cannot be selected as DISTINCT because it is not comparable.
     

     

    The TextData column is stored as ntext, which can't be compared with a DISTINCT statement.

    To correct this, we can cast it into a type that is comparable.

     

    select Distinct CAST ([textdata] as NVARCHAR(MAX)) AS [textdata]
    from HistoryReview
    where textdata like '%exec sp_executesql N%'
     

     

    Example - In my trace of > 68,000 rows, the above query reduced it down to ~30 results. Much easier to sort through to identify relevant queries.