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.
Click on the screenshots to open full size versions.
|Current Workload Information:
|| Cumulative Session Totals:
- 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.
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.
The default settings should be OK for testing against a dev/test system, so:
- Download Workload Profiler (download link below) and unzip.
- Run Workload Profiler (double-click the executable, no install necessary, providing v4.5 of the .NET Framework is installed).
- 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=;
- On the “Now” tab, click “Start”.
- Wait for the second data collection when workload performance data will appear in the UI.
- 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.
- .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.
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.
- 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.
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.
For step-by-step usage instructions, please see the Instructions/Tutorials page.
Much more detailed information is available on the Reference page.