Instructions / Tutorials

SQL Workload Profiler Instructions / Tutorials

Contents

  • Introduction
  • How to:  Check the State of The SQL Server Plan Cache
  • How to:  Monitor Your Database / Collect Performance Data
  • How to:  Change Display Metrics / Units
  • How to:  Concurrently Collect and Analyse Performance Data
  • How to:  Stop Data Collection, End Analysis and Reset Workload Profiler
  • How to:  Save Performance Data to a SQL Server Table
  • How to:  Analyse Previously Saved Performance Data
  • How to:  Concurrently Perform Data Collection and Additional Data Processing

Introduction

This page describes step-by-step how to use SQL Workload Profiler for the most common usage scenarios.

For an overview of Workload Profiler, please see the SQL Workload Profiler page.  For more in-depth information, please see the Reference page.

How to:  Check the State of The SQL Server Plan Cache

Workload Profiler uses captures performance statistics from the SQL Server Plan Cache.  Under some circumstances this approach won’t be able to provide any meaningful statistics.  Most of the time some (or lots!) of useful information can be obtained.  A basic understanding of the Plan Cache will help in understanding what you are seeing.

The Plan Cache can be empty or close to empty at times e.g. just after a service restart or just after DBA use of the DBCC FREEPROCCACHE command.  Under some (usually rare) circumstances it can also stay empty or close to empty e.g. if the server is under heavy memory pressure.  If the Plan Cache is close to empty this means there will be very few aggregate statistics to capture.  At the other end of the scale, the Plan Cache can be extremely large, e.g. if polluted with a very large number of single use query plans.  In this scenario the volume of data to capture may be so large that it is difficult to capture and possibly to analyse too.

So, it is sensible, before starting a profiling session (i.e. before starting any data collection), to take a look at the high-level state of the plan cache.  The tool supports one simple way of doing this:

  1. Open a new instance of Workload Profiler.
  2. Go to the “Server Summary” tab.
  3. Click on the “Refresh Button”.
  4. A small summary table will be displayed.

If the number of query plans (Total Plan Count column) runs into the high thousands, tens of thousands, etc (most likely for the ad-hoc query plans) then the cause should be investigated.  A common answer is many single use queries – see the value in the “Total Plans Used Once Count” column.  As an aside, the Size MB plan columns also show how much server memory is devoted to the Plan Cache (i.e. that can’t be used for other purposes like the Buffer Pool).  Most of the query that forms the basis of this view was taken from a post on Kimberly Tripp’s blog.

Irrespective of the cause, a large Plan Cache can complicate data capture in the tool.  If this is the case, the first step is to ensure that the “Collection Method” setting under the “Collection Options” heading on the “Options Tab” is set to either collection method 1 or 3.  This will prevent the vast majority of the Plan Cache (which is effectively dormant/dead weight) from being collected by the tool during each data collection.

The Plan Cache is discussed in more detail on the Reference Page.

How to:  Monitor Your Database / Collect Performance Data

  1. Open a new instance of Workload Profiler.
  2. Go to the “Options” tab.
  3. Under the “Collect Query Stats From Database” heading, enter your SQL Server Connection String into the box.  Workload Profiler uses a standard .NET Framework SQL Server connection string.  To save trawling your memory, here is a bare bones SQL Server Connection String – add or remove keywords as appropriate for your server:
    Server=;Database=;User ID=;Password=;Trusted_Connection=;Encrypt=;Connection Timeout=;
  4. Review the settings under the “Collection Options” heading.  For a first run on a dev or test environment these settings will likely be fine.  For servers running large workloads (this term is defined better in the In Detail section below), the performance information may be quite large in size, in which case increasing the data collection interval may be advisable.
  5. Go to the “Now” tab.  Click “Start”.
  6. Wait.  A first baseline data collection will occur.  This won’t generate any output in the UI.
  7. After the second data collection, details of the workload the server has executed during the time between the first and second data collections is shown in the “Now” tab.
  8. After the third data collection, details of the workload the server has executed during the time between the second and third data collections is shown in the “Now” tab.  The “Total” tab shows the cumulative workload for this profiling session (i.e. in this example, the workload between the first and third data collections).

How to:  Change Display Metrics / Units

By default, Workload Profiler displays:

  • Totals (i.e. total counts of statistic X as measured during the last data collection period).
  • I/O in SQL Server Pages (i.e. units of 8 kilobytes).
  • Time in microseconds (though the source SQL Server DMV states these numbers are only accurate to milliseconds).

These display options mirror the underlying units used in the SQL Server DMVs.  You may prefer an alternative display, e.g. I/O in megabytes per second.

To change these display options:

  1. Start a data collection as described in the last how-to.
  2. On one of the data tables (e.g. on the “Now” tab) press the right mouse button.
  3. Select the appropriate option from the context menu.

Changing display options generally affects all of the data tables visible in the tool.  The only exception is the Aggregation Option (which is described on the Reference page).  Each data table can have a different aggregation level configured.

How to:  Concurrently Collect and Analyse Performance Data

  1. Follow steps 1 to 4 in the “How to:  Monitor Your Database / Collect Performance Data” section above.
  2. Still on the “Options” tab, ensure that the “Statistics Source” option is set to “Analyse statistics that are currently being collected”.
  3. Go to the “Now” tab.  Click “Start”.
  4. Wait for the second data collection to occur as described above.
  5. Go to the “Analysis” tab.  This shows aggregate metrics for each data collection (i.e. point in time).  Additional rows in this table will appear as data is collected.
  6. Go to the “Analysis Charts” tab.  This shows the same information as on the “Analysis” tab, but plotted on line charts.
  7. The data plotted on the charts can be modified using the options at the bottom of the tab.
  8. Go back to the “Analysis” tab.  Double click on a line in the chart.
  9. This will take you to the “Analysis Details” tab.  This shows the data that was collected at that point in time.  You can view the workload at that point in time (or more strictly speaking, the workload between the time of that data collection and the previous data collection – hence why it is called the “delta“).
  10. By changing the “View” option to “Cumulative” it is possible to see the workload that occurred from the time of the first data collection (i.e. the first row in the table on the “Analysis” tab) to that point in time.  Queries highlighted in orange were executed just now (i.e. the orange queries are the same queries as shown in the “Now” tab, but in this tab their session totals are shown).

Note:  Analysis data can quickly grow in volume.  By default, the tool is limited to consuming at most 1 GB of memory.  When this limit is reached, the oldest data in the Analysis view will be discarded.  The cumulative session totals in the Analysis view reflect the workload since the time of the first row in the Analysis view.  When the oldest row(s) are discarded, the session totals will be adjusted.  For more details about this memory limit, adjusting it, etc please see the Reference page.

Note 2:  Even if/when the oldest rows in the Analysis view are discarded, this has no effect on the data in the “Now”, “Total” and “Server” tabs, i.e. the “Total” tab will continue to show the cumulative workload for the entire time since data collection started.

Note 3:  To keep memory usage to a minimum whilst capturing a performance profile (e.g. if you plan to analyse the data later and not during data collection), on the “Options” tab, under the “Analysis Options” heading, change the “Statistics Source” option to “Disabled”.  This disables the Analysis view during data collection and keeps memory usage much lower.

How to:  Stop Data Collection, End Analysis and Reset Workload Profiler

This tutorial continues from the end of the previous tutorial.  I.e. it assumes a data collection is already in progress, concurrent with data analysis.  To stop data collection:

  1. Go to the “Now” tab.
  2. Click on the “Stop” button.

Data collection will have now been stopped.  The data collected during the session will still be available in the “Now”, “Totals”, “Server”, “Analysis”, “Analysis Chart” and “Analysis Details” tabs.  At this time, it is not possible to start a new session (i.e. more data collection) since all of the settings on the “Options” tab will still be disabled and the “Start” button on the “Now” tab will also be disabled.

Once analysis of the data has been completed, to re-enable all of the options and the “Start” button:

  1. Go to the “Analysis” tab.
  2. Click on the “End Analysis” button.

How to:  Save Performance Data to a SQL Server Table

  1. Follow steps 1 to 4 in the “How to:  Monitor Your Database / Collect Performance Data” section above.
  2. Still on the “Options” tab, next to the “Write Stats to Database” heading, tick the “Enabled” check box.
  3. Specify the database connection string to the database where the performance data will be saved.  It is a good idea to use a different database and server to that being profiled.
  4. Click the “Options” button at the right of the “Table Name” box.  Select an option from the menu to create the table that will hold the performance data.  This table must exist before data collection starts, so use one of the options on the menu to create it now.
  5. Leave the “Processing SQL” check box un-ticked for now.
  6. Go to the “Now” tab.  Click “Start”.
  7. After each data collection (except the first) the current workload (i.e. that occurred between that data collection and the previous data collection) will be written into the specified table.

How to:  Analyse Previously Saved Performance Data

  1. Open a new instance of Workload Profiler.
  2. Go to the “Options” tab.
  3. Under the “Analysis Options” heading, change the “Statistics Source” setting to “Analyse statistics that were previously collected and written to a database table”.
  4. Specify the connection string to the database containing the data.
  5. Specify the name of the table that contains the data.
  6. Click the “Load Data” button.  This will open the “Database Load Filter” window.
  7. For very large sets of performance data, use the options on this screen to either load a smaller time window or a filter the data as it is loaded (e.g. to a single stored procedure).
  8. Click “OK” to load the data.

Note:  Pay attention to the estimated memory required on the “Time Range” tab in the “Database Load Filter” window.  When loading previously saved analysis data, no memory usage limit is enforced by the tool.  Nonetheless, attempting to load very large sets of data (especially data sets so large that they won’t fit in RAM) is a bad idea as performance of the tool will be poor.

How to:  Concurrently Perform Data Collection and Additional Data Processing

  1. Follow steps 1 to 4 in the “How to:  Save Performance Data to a SQL Server Table” section above.
  2. Still on the “Options” tab, tick the “Processing SQL” check box.
  3. On the same line, click on the “Browse” button.  Select the file that contains your SQL Processing Script
  4. Go to the “Now” tab.  Click “Start”.
  5. During each data collection iteration, after the data has been written to the SQL Table, the data processing SQL script will be executed – against the database that contains the statistics table (not the database being monitored).

Note:  The intended use for this feature is something like:

  1. Workload Profiler collects current workload data.
  2. Workload Profiler writes current workload to QueryExecStats output table.
  3. Workload Profiler invokes your data processing SQL script.  Your data processing script does something like:
    1. Processes the data in the QueryExecStats output table (e.g. aggregating the data into a different output table).
    2. Clears the QueryExecStats table ready for the next data collection.
  4. Workload Profiler starts the next data collection – repeat from (1).

Note 2:  It is important that the data processing SQL script completes quickly, otherwise it will delay the next data collection.

 

 


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s