Using Qure Workload Optimizer for SQL Sentry
Last week at SQL Bits #8 in Brighton, England, SentryOne announced its partnership with DBSophic, makers of the Qure workload management suite. Qure Workload Optimizer is a tool aimed at analyzing a workload and providing suggestions to improve the overall performance of that workload. These suggestions can range from adding and dropping indexes, to changing schema, to completely re-writing queries and batches. How does Qure differ from other products out there aimed at improving performance? Qure works against a copy of your production database, makes actual changes, and tests the workload against those changes to make sure they lead to better performance – leading to the absolute best recommendations that have actually been proven to improve your workload. If you aren' already excited about the potential here, I suggest you read this paragraph again. 🙂
You can read more about the partnership in this press release from April 7th and also in the following blog post by Greg Gonzalez: SQL Sentry v6.1: Something Old, Several Things New.
The way that the base Qure product currently works is as follows:
- on the production server, you create a trace using a template provided by DBSophic, preferably covering a complete business cycle;
- on a server dedicated for the analysis, you restore a backup of the production database;
- once your trace is complete, you point Qure at the trace files (or table) and to the backup of the database, and run the analysis.
In the bundled version created exclusively for SQL Sentry, you bypass step 1 and simply point Qure at the SQL Sentry v6 repository that has been monitoring your server. So instead of having to set up a trace and run it yourself, you can use what SQL Sentry is already doing for you. Note that this is limited to the last 100,000 trace events, but will also include up to the last 100,000 QuickTrace events as well – so if you have been running QuickTraces on your server, this data will be factored in as well.
Before starting, it's a good idea to have SQL Sentry cover a complete business cycle with the "Top SQL" duration threshold tuned down to something lower than the default (5 seconds). Essentially you want to include as many events as possible; this way, the impact of any recommended changes can truly be tested. Even if an operation currently only takes 2 seconds, a change to benefit some other query might adversely affect some of these shorter queries.
That said, here is a step-by-step flow of how this works. (Click on any screen shot to show full size.)
When you launch the program, you will see the splash screen looks a little bit different:
Next, you'll choose "Start New Analysis" and you will be asked if you want to perform a full analysis or a trial analysis. For our purposes here, let's use a trial analysis. This will mean a limited set of recommendations will be provided (but I will show the results of a fully licensed analysis).
Next, you will be asked where you want to store the analysis file. By default this will go under the Qure folder in My Documents, but be aware that this file can be up to 4GB – so if your C:\ drive is getting skinny, you may want to choose a different location:
You will then be asked for the server where your database has been restored. There is an additional warning here that you should not be performing the analysis against a production database (and I strongly recommend you heed this warning). Please make sure this is a *COPY* of your production database and that the server is not a production server. I feel like I'm repeating myself, but it is *extremely important* to avoid running such an analysis against your production environment. The reason is that changes are made to the database in order to validate the recommendations being made will, in fact, improve performance.
Once you connect to a server, Qure will ask you which database(s) you want to analyze. You can select more than one (for a full analysis, you can select three; for a trial analysis, two), but in this case we will just select a single database:
The next screen asks you for your trace sources. In the base product, you can select any number of trace files, trace tables, or both, but in this custom version of Qure, you go down to Add Trace > Add SQL Sentry Trace:
You will then be asked to point to the SQL Sentry v6 repository, where Qure will analyze the trace data that has already been collected:
On the next screen, there are a couple of options to consider. One that I strongly recommend you select is the option to "Keep all existing indexes." The reason is this: no matter how long you have been collecting trace data or performance metrics, there is always the chance that an index you or software thinks is unnecessary will actually be needed tomorrow. My official advice here is to never drop an index unless you are absolutely sure it is redundant or no longer needed – and never let software do this automatically. Fellow MVP Paul Randal (twitter) recently published a horror story about a tool that recommended dropping "redundant" indexes without knowing the whole story (and failing to recognize the attributes that made the indexes different).
On the other hand, if you want to see what redundant indexes may be suggested by the tool, it is safe to leave this option unchecked. (Since it knows the workload may not represent a full business cycle, Qure Workload Optimizer will not, in fact, recommend dropping indexes based on use or lack of use – it will only recommend dropping duplicate, redundant, hypothetical or disabled indexes. The "Keep all existing indexes" checkbox here dictates whether redundant indexes are considered – all others will still recommend to be dropped.) When you need to be careful is when you get to the point of applying the recommendations, that you review that involve dropping indexes, and be sure that you want to accept those individual changes (you can easily reject any single recommendation).
The next screen is just a summary; you can click the Start Analysis button to get things going:
You can watch the process as it analyzes the server, database(s) and individual objects, and comes up with recommendations:
You will also see it perform a banchmark analysis, testing the performance before and after the recommendations are applied (don't worry, these changes are reverted):
Once finished, you will arrive at a success dialog:
When you click "View Report," you will be shown a screen which summarizes the recommendations. From here you can get a very quick view of the top batch improvements and how the improvements are measured (duration, I/O, CPU, or some combination):
If you scroll down on that same dialog, you can expand the "Recommendation Summary" to see a high-level view of what types of recommendations are being made, and what kind of impact they will have. Note that not all recommendations are deemed "applicable" – those that are applicable can be applied with minimal risk to behavior, and through scripts generated by Qure Workload Optimizer. The remaining recommendations are provided for guidance and will not be applied automatically – they should still be reviewed, as we'll see on upcoming screens.
If you switch to the Batches tab, you can see exactly how the recommended changes will affect each batch in terms of the overall workload – be it duration, physical or logical reads, writes, or CPU. You can sort here to focus on the batches with improvements in your area(s) of largest concern, for example if your system is very I/O-bound, you may want to focus on the changes with the greatest improvements in I/O, so sort by the physical reads column. Note that you may see cases where some attributes were affected negatively (e.g. physical reads went up), but overall duration went down. You can select individual or multiple batches here, or even select all to see the total impact of all recommended changes.
Next, you can move to the Recommendations tab. Here you can review all of the possible changes, and indicate which you want to test by highlighting them and clicking "Accept." Note that the following screen shot shows several recommendations that require manual rewrites of batches; you can't apply these directly to the database, since these were ad hoc queries submitted to SQL Server. But it can give you a good idea about how to improve the code you're sending that way… the example I've pointed out is actually part of my "Mishandling Date Range Queries" set of bad T-SQL habits to kick – applying a function to a column in a search predicate, preventing the use of an index.
You can change the filters on the left to highlight specific recommendations; once you have chosen which recommendations to accept, you can move to the Optimize tab, which will guide you through applying the changes (again, only to a *copy* of your production database). When you are happy with the changes you are about to apply, press the "Start Optimization" button:
This will start the "Apply Recommendations Wizard" – which again has sensible warnings about not applying these changes to production databases:
You will be asked again for the connection details of the copy / test database, and then you can choose "Start Applying":
When finished, you will see the following dialog (but don't forget that there are several changes Qure Workload Optimizer recommended but couldn't make automatically, such as changes to batches that are issued ad hoc or from applications):
Now you can test your changes in your test or QA server(s) and deem whether they are satisfactory for migration to your production environment. You can watch the newly changed database(s) on the test / QA server with SQL Sentry v6, run applicable workloads against the test server, and you should find a dramatic improvement in performance.
Once again, as a reminder, please be sure to thoroughly test changes suggested by any person or piece of software, especially in cases where the whole story may not be known – for example, if your collection has not covered a complete business cycle, or if you have filtered out a large number of individual events. A suggestion can only be as good as the information it's based on.
So if you are looking for a reliable and effective way to improve performance of your applications, you should absolutely consider taking Qure + SQL Sentry for a test drive! The software will be available for download soon. In the meantime, if you're not already using SQL Sentry, you can register for a trial download at http://www.sqlsentry.net/download-trial/trial.asp.