Introduction
Performance is a key metric for databases. Modern enterprise-class SQL database systems are expected to handle the heaviest workload and still provide good and constant performance. The internal code in the database engine is highly optimized and tuned, but this doesn’t hold always true for the databases managed by those database systems. A poorly designed and/or developed database can easily bring even the fastest database management system to its knees. Therefore it is crucial that databases are designed and implemented by people who know their job. But even with knowledgeable people in charge there is still room for optimizations. However, query tuning and workload-optimization is a tedious and time-consuming task. This is the point where Qure from DBSophic comes into play.
Qure
Qure is a workload-tuning tool designed specifically for SQL Server applications. It uses a unique approach to analyse a databases’ workload by running its analysis against a copy of the production database on a non-production server. The main sources of information for Qure are SQL Server’s trace files. The remainder of the review will cover installation of Qure and a review of its key features.
Workload tuning
We just mentioned that Qure is a workload-tuning tool, but what actually does that mean? Here is the definition from a workload-tuning whitepaper on the DBSophic.com website:
Workload Tuning introduces a completely new approach to Database Performance Management, complementing Query Tuning as a means of diagnosing and repairing production problems.
The newness is summarized in two adjectives: holistic and large scale. First, “holistic”.
Workload Tuning is to Query Tuning what holistic medicine is to traditional medicine. If a man complains of a back ache, traditional medicine commonly addresses the precise location of the pain by prescribing muscle relaxants and physical therapy to stretch and strengthen the muscles. Relief can be obtained in this way, but the pain may return if the root cause of the back problem has not been addressed. Holistic medicine looks for the root cause. The holistic therapist may notice that the man’s gait is uneven, and determine that there is a problem with the left foot that is transferring to the back. By additionally treating the foot problem, the back problem is cured for the long term.
The second new characteristic is “large scale”.
Workload Tuning is to Query Tuning what production auto manufacturing is to custom car manufacturing. After years of doing all work piecemeal, and through diligent study of the process of manufacture, an automated approach was invented that can achieve a consistently high level of quality in each of thousands of units. A new expertise arose: that of designing and supporting the large-scale operation.
Now, what does that mean with respect to database performance tuning? Is does simply mean that Qure acts as the workhorse for the DBA by performing all the tedious, time-consuming, and, let’s face it, sometimes rather boring task of running and analyzing traces and/or querying the DMV’s to learn more about the database workload. It saves the DBA plenty of time that he can better invest in tasks that provide truly added value to his clients. He just needs to look at Qure’s recommendation and cherry-pick those ones that provide the biggest gain for the users.
So far with the promise, we now install Qure and test if it can keep up with this promise.
Installation, system requirement, and supported SQL Server versions
The following installation prerequisites are taken from Qure’s help file:
- Qure Installation Hardware Requirements:
- 50 MB free disk space for binary/executable/log files.
- Approximately 2 GB free disk space per database analysis file, up to a maximum size of 4 GB each.
- 1 GB RAM minimum, 2 GB RAM recommended.
- 2 GHz CPU, Dual core recommended.
- Qure Installation Software Requirements:
- Windows XP/Vista/Server 2003/Server 2003 R2/Server 2008, 32 or 64-bit.
- Microsoft .Net Framework 3.5 SP1.
- Microsoft SQL Server 2005/2008 client tools, 32 or 64-bit.
- Windows Installer 3.1 or later (version 4.5 is recommended).
The copy of the databases for analysis should be restored on a server with the following minimum specifications:
- Database Server Hardware Requirements:
- Free disk space equal to at least three times the total size of the analyzed databases for the data and log files.
- 1 GB RAM minimum, 2 GB RAM recommended.
- 2 GHz CPU, Dual core recommended.
- For accurate analysis results, the client’s and server’s physical hardware resources should be sterile of any concurrent activity. A dedicated (non-virtual) server is highly recommended.
- Database Server Software Requirements:
- Windows Server 2003/Server 2003 R2/Server 2008, 32 or 64-bit.
- Microsoft SQL Server 2005/2008/2008R2, 32 or 64-bit.
By studying the requirements you may have noted that SQL Server version from 2005 onwards are supported. If you still happen to run SQL Server 2000, then you cannot use Qure.
You can download Qure as trial version for free from its DBSophic product homepage at http://www.dbsophic.com/download after a quick registration. The latest publicly available version is 2.0. The download size is ~17.8 MB and once the download is completed you can start the MSI package and a wizard will guide you through the rest of the installation process, which is straight forward. A couple of minutes later you are ready to use Qure.
Using Qure
As noted before are SQL Server trace files the primary source of information for Qure. You can either use already captured traces or, and this is possibly even easier to start with, use the profiler templates that ships with the product. These templates capture all events with all needed columns to provide best analysis results. For more information on creating efficient traces for Qure, refer to this document: http://www.dbsophic.com/assets/files/PrepareTrace.pdf
Upon first start of Qure the above shown wizard comes up and it is possibly a good choice to let the wizard guide you until you are familiar with the tool. At any time you can skip the wizard by clicking on the “Start Using Qure!” button and stop the wizard from being launched first after application start by unchecking the “Show this at next startup” box in the lower left corner.
The wizard introduces you to the way Qure works and the concepts behind it as you can see from the above screenshot. For the purpose of this review, we will skip the wizard and go straight in medias res.
The above screen is Qure’s main entry point. From here you can either create new analysis or open existing ones. Since this is the first usage, we will start a new analysis.
Now it is possibly time to clarify an important point that may be not so clearly pointed out on Qure’s website. The nice folks over at DBSophic promised to revise the webpage soon to make it clearer. The trial version of Qure and the accompanying trial analysis can be used on any database and any workload. Here is a breakdown on the differences between the full and the trial version of Qure:
| Full Analysis | Trial Analysis |
|---|---|
| Requires a valid license | Free – No license required |
| Full or sample data analysis | Sample data analysis only |
| Unlimited trace sources | Maximum 10 trace sources |
| Unlimited number of batches | Maximum 50,000 batch instances |
| Maximum 3 databases per analysis | Maximum 2 databases per analysis |
| All recommendations available | Recommendations shown for 5 batches only |
Though you can run the trial version of Qure against any database, for the purpose of this review we will use the AdventureWorks sample database. The reason for this is simple: The results and Qure’s recommendations in this review will either be identical or quite close to the ones that you will get when you run the sample analysis against your copy of AdventureWorks hosted on one of your machines. We could have used one of databases, but that would make this review more abstract and possibly less realistic for you
That being said, next step you take is to create an analysis file to which Qure will store all its information to. In this case we call the file “MyAnalysis”.
Once done you specify the server hosting the to-be analysed database. As you can nicely see from the above screenshot, Qure issues a warning when it detects that this database is hosted on the same server than what it assumes to be the production server. In our case the AdventureWorks database is hosted on the only instance on our machine. So we deliberately ignore Qure’s warning. For a serious analysis you certainly would want to run the product from a client machine against your analysis database server. At least bigger companies have separated environment for production and development. If you are lucky you may also have a dedicated user acceptance testing environment, which is almost if not completely identical to the production environment. Such an environment would be the ideal candidate for the analysis server.
After the server to connect to is determined, the next step is to specify the database that will be analysed.
