SQL Workload Profiler

Introducing SQL Workload Profiler

SQL Workload Profiler is a free tool for capturing and logging workload performance information from SQL Server.  Performance statistics can be captured minute-by-minute (or more frequently if you want, up to every ten seconds, depending on the type/size of workload).  Data can be analysed as it is being collected and/or saved for later analysis.  Workload Profiler supports both (on-premise) SQL Server and Azure SQL Database.

Workload Profiler arose from some performance profiling work I was doing, mostly against Azure.  I have spent some time over the past few weeks tidying it up and putting in some more UI bits to make it more usable.  Even still, please regard it is a “beta” for the time being.  Feedback is welcome.

This page provides only the core information to help you get started.  The Workload Profiler Reference page explains a lot more.

Example Screenshots

Click on the screenshots to open full size versions.

Current Workload Information:
20140928_01_Delta
 Cumulative Session Totals:
20140928_02_Totals
Analysis Charts:
20140928_03_Chart
Details:
20140928_04_Details

Features

  • View statistics of running workloads in close to real time (e.g. minute by minute, or sub-minute, depending on the workload).
  • View cumulative statistics for the session in progress (e.g. to identify most resource intensive queries).
  • Workloads are broken down by procedure/batch and queries within each procedure/batch.
  • Statistics include execution counts, I/O activity, CPU time and row counts.
  • Captured data is saved into an analysis view* that can be used to walk-through the data either whilst data capture is in progress or after data collection has stopped.  (*by view here I mean an area/screens in the tool, not a SQL Server database view).
  • The analysis view includes both point in time and cumulative data tables.
  • The analysis view plots data on line charts for quick understanding.
  • Filter any of the analysis data tables or the analysis charts down to specific procedures, queries, etc.
  • Sort the data tables by any of the statistics.
  • View details of queries in both real time and analysis views.
  • Aggregate the data (raw = query – plan, higher levels: query and query hash).
  • Save granular data into a SQL Server table for later analysis, either by (1) reloading the data later into Workload Profiler, (2) simply using T-SQL or (3) loading the data into another application e.g. Excel, to aggregate and slice-and-dice the data in any way you want.
  • Run custom T-SQL data processing scripts against the captured data during data collection to perform any additional data processing / analysis you may require.
  • Free!

How SQL Workload Profiler Works

To be clear here:  Workload Profiler = the tool available from this page.  SQL Profiler = the tool provided by Microsoft as part of the SQL Server management tools.

Workload Profiler works differently to SQL Profiler.  SQL Profiler is a tracing tool that captures very low level information at the level of every single procedure/batch execution.  Profiling using SQL Profiler is often cumbersome.  Workload Profiler on the other hand uses the aggregate metrics available within the SQL Server Plan Cache – I am sure many of you already guessed this just by looking at the screenshots above :-).

Workload Profiler works by monitoring how the information in the plan cache changes over time.  By comparing snapshots* taken of the plan cache at regular intervals Workload Profiler is able to capture details of the running workload.  (*snapshot here = taking a copy of at a point in time, nothing to do with SQL Server Snapshots).

Using the Plan Cache in this way is somewhat experimental.  The Plan Cache is a very useful source of information in SQL Server.  Nonetheless, there are some limitations to using the SQL Server Plan Cache as the source of this information.  I expect many readers experienced with the Plan Cache don’t need these describing in detail.  Very briefly, as and when SQL Server recompiles query plans or clears the query cache, the corresponding statistics will be reset / cleared inside SQL Server.  Some types of request (e.g. some bulk operations) also don’t generate query plans.  Please see the Reference page if you would like to read more on this.  Anyone using the tool should take a few moments to understand the limitations of using the Plan Cache if they don’t already.

In summary, Workload Profiler aims to make the best use of the information available in the Plan Cache.  The figures may not be as exact as running a trace-based profile, but using these figures for profiling workload and performance usually reveals a lot of useful information.  With an understanding of the Plan Cache and a little experience of using the tool you can learn a lot about the workloads running inside your SQL Server.

SQL Workload Profiler vs. SQL Server Data Management Views

You may be wondering what Workload Profiler offers vs. directly using the underlying SQL Server Data Management Views (DMVs).  Very briefly…

A lot of useful workload performance statistics can be obtained from directly using the SQL Server DMVs.  Of course using T-SQL over the DMVs offers the most options in terms of analysis.  But the DMVs can be quirky to work with too.  Most of the DMVs provide totals from different start times (e.g. last compile time for stored procedures) that may make comparing numbers more difficult.  And data rolls out of the DMVs over time too.

SQL Workload Profiler monitors how the numbers in the DMVs change over time for fixed time intervals.  This makes make the numbers more complete and more easily comparable.  Showing the numbers in sortable/filterable tables and charts makes exploring and understanding the information easier too.

There is more discussion on this topic on the Reference page.

Instructions

Quick Start

The default settings should be OK for testing against a dev/test system, so:

  1. Download Workload Profiler (download link below) and unzip.
  2. Run Workload Profiler (double-click the executable, no install necessary, providing v4.5 of the .NET Framework is installed).
  3. On the “Options” tab, under the “Collect Query Stats From Database” heading, specify the connection string to the database to be monitored.  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. On the “Now” tab, click “Start”.
  5. Wait for the second data collection when workload performance data will appear in the UI.
  6. Explore the other tabs.

Some adjustments may be needed to the settings when working with your large diverse workloads.  Please see the Reference page for details.

More Instructions / Tutorials

For step-by-step usage instructions, please see the Instructions/Tutorials page.

Requirements

Client requirements:

  • .NET Framework v4.5
  • Up to 1 GB available RAM (configurable lower or higher)  to concurrently collect and analyse workload data.
  • Potentially more than 1 GB available RAM to retrospectively analyse large sets of workload data (chunking the data up for analysis is possible too).

SQL Server requirements:

  • SQL Server 2008+ or Azure SQL Database.
  • User requires View Server State permission.

Workload Profiler has been tested against a range of SQL Server Versions and Editions from SQL Server 2008 R2 Express to SQL Server 2014 Enterprise plus Azure SQL Database.  However not every possible version / edition permutation of SQL Server has been tested.

SQL Server 2008 Note

I believe the database selection / filtering in Workload Profiler, when used against SQL Server 2008, may not work fully.  This is because, I believe, the database_id column in the sys.dm_exec_sql_text DMV in SQL Server 2008 does not return a database id for ad-hoc SQL queries.  I believe there is a workaround for this, contact me if you wish to try it.

License

By using SQL Workload Profiler, you accept the following agreement:

You may install and use any number of copies of the SQL Workload Profiler on your devices.  The software is licensed and not sold.  This agreement only gives you some rights to use the software.  In doing so, you must comply with any technical limitations in the software that only allow you to use it in certain ways.  You may not work around any technical limitations in the binary versions of the software, reverse engineer the binary version of the software nor re-distribute the software claiming ownership, authorship or other commercial rights as your own.  Although significant effort and care has been taken in the development and testing of this software, it is licensed “as-is.”  You bear the risk of using it.  No express warranties, guarantees or conditions are given.  You cannot recover any damages, including consequential, lost profits, special, indirect or incidental damages incurred directly or indirectly from using this software.

Download

  • Current Version: 0.9.0 (i.e. a beta version)
  • Size: 0.7 MB
  • First Released: 1st October 2014
  • Expiry Date: 1st January 2018
  • Download Link:  Download

Workload Profiler expires primarily to enable older versions (potentially containing undiscovered bugs) to be forced from use in favour of newer versions.  Assuming that there is interest in the tool, before the expiry date a new version will be posted here.

As with any new tool, please don’t be so eager to try it that you rush to download it, double-click it and point it straight at your production box.  Please try Workload Profiler in a dev, test, QA, etc. environment first and then gradually test it with larger more complex workloads.

Support

Please post any questions or comments at the bottom of this page or at the bottom of one of the other pages here.

Alternatively, I can be contacted at:

cbailiss at gmail.com

If you encounter any errors whilst using the tool, particularly those that may be bugs, please feedback, via email including log details.  When errors occur in the tool they are recorded in the “Log” tab.  Individual entries in the “Log” tab can be opened by double clicking on them.  Alternatively the entire list can be copied to the clipboard from the right mouse button context menu on the log.

Further Information

For step-by-step usage instructions, please see the Instructions/Tutorials page.

Much more detailed information is available on the Reference page.

Advertisements

19 comments

  1. Hi Chris,

    This is an incredible tool, one Microsoft should be buying and releasing as the missing SQL Azure profiler. It gives much needed missing insights into queries running against a db.

    Thank you very much for sharing it.

    Denis

      • Hi Chris,

        Do you have any update on the release of this tool out of beta? January 2016 is fast approaching and I’ve found this tool to be very helpful in analyzing performance issues and I’d like to keep using it, at least until Microsoft hires you to build one for Azure SQL 🙂

        Thanks,

        Denis

    • (Replying to this comment rather than the Nov 4th one since WordPress UI has no reply button on that one…).

      I’ve kept the tool labelled as “beta” mainly because I haven’t made time to do another round of testing (which I always intended to do!). I’ll put another version up in advance of this one expirying. The expiry dates are really only there so that, should any significant bugs be discovered, the older versions will eventually be forced out of use. I have no plans to withdraw the tool…

      Chris

  2. Thanks a lot! It helped to identify and remove many bottlenecks in performance. Much more helpful than the Azure Auditing.

  3. Hello Chris, I’m using your great tool very often but today I’ve got the message that this version expired on 1st January 2016. Do you plan to either extend the validity or release some new version please?

    Thank you,

    BR
    Michael

  4. Hi Chris,
    Just installed your tool today – and works a treat – can now actually see whats going on my Azure Database.
    Much appreciated!
    Cheers,
    Niall

  5. What a great tool. Got caught out with Azure not supporting SQL Profiler. Azure tool is fine for finding your top 20 high load queries, but this lets me review the small queries that may be causing issues.


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