As already mentioned above are the primary source of information for Qure native SQL Server trace files and now it is time to select the trace file(s) to be included in the analysis. Note that the Qure demo-kit comes in two flavours that greatly vary in what you can later on get as recommendations from the analysis.
- A trace file containing a simulated workload for the ‘AdventureWorks’ database. This trace file enables you to run the whole analysis process, but reveals recommendations only for 5 pre-selected batches.
- An already prepared analysis file that is not limited in any way and exposes all recommendations and full functionality for the provided ‘AdventureWorks’ simulated workload.
For this review we will use a mixture of both. We will analysis the sample trace file, just to demonstrate the analysis process and then we will load the prepared analysis file to look at some of Qure’s recommendation in more detail. More on the latter later on.
You can instruct Qure to refrain from making recommendations regarding the currently existing indices. That means that all existing indices are kept with the exception of duplicate, disabled, or hypothetical indices which will always be included in recommendations. Choosing to analyse system trace events may increase analysis performance by including replication events and other system type events.
After all preparation work is done, Qure can go to work and do its magic. This is a process that can greatly vary in duration, depending on the size and complexity of the analysed database. First step in the analysis process is the schema analysis. In this step database objects such as tables, indices, views, stored procedures, functions, etc are analysed. Since this step only looks at metadata it is fast without utilizing many resources.
Next step is the trace file(s) analysis. It is not surprising that this step may take very long to run depending on the trace file size, the number of captured events, and the complexity of the contained batches. However during this step mostly resources on the machine running Qure are used.
In paralle to analysing the trace file, the statistical analysis of the data in the database is performed. The part naturally consumes most resources on the database server that hosts the analysed database and can also take a long time to complete if the database is very large.
Once the analysis part is done, Qure generates its recommendations based on the insights it gained from the analysis.
Based on the recommendations, Qure continues with a benchmark test where it test its recommendation against the batches caught in the trace file. However, since this trace file is captured on your production server, the batches may vary in duration and IO statistics from what they might look like on the analysis server. So, the first step in this benchmark process is for Qure to create a baseline for the comparison by re-running the batches on the analysis server.
After this baseline is created, Qure applies its recommendations against the analysis database and compares them against the baseline. The results are saved in the analysis file and create its final report.
This report can be viewed in various ways. More on this in just a moment. Up to this point we analysed the demo trace file, but now it is time to switch to the prepared full analysis to gain a deeper insight into Qure’s recommendations.
For that purpose we restart Qure and click on the “Open Previous Analysis” button.
Then we select the previously downloaded analysis file and can gain a deeper insight into Qure’s full potential.
Look at the above screen. From the summary tab you get the overall information of the analysis and a breakdown of all improved batches with numbers on the individual performance improvements. You can drill down to any recommendation either by clicking on the associated analysis ID in the upper grid or by clicking on a particular analysis category in the lower “Recommendation Summary”.
For example, let’s look at “Batch 276”. As soon as you click on this batch on the summary tab you are redirected to the “Batch” tab and there you can see all the individual figures for that particular batch. The above screen shows an improvement of 65% in duration with a reduction of physical reads of 82% and a reduction in logical reads of an impressive 99%. But what actually is “Batch 276”?
Before we actually answer this question, let us draw your attention to the lower red circle. There it says: “Difference detected in batch result set between original analysis DB and optimized DB.” What does that mean? Qure performs additional checks on the resultset of a batch to verify that the resultset is identical before and after the optimization. Specifically this means that Qure performs a checksum validation of the resultset. In many cases this difference is triggered by a change in execution plans that result in a change in the order in which the rows are returned. Another possible cause for this behaviour would be the use of non-deterministic functions such as NEWID(), GETDATE(), or RAND(). And, of course, this is especially true when the analysed batch does not contain an ORDER BY clause which would guarantee the order of the resultset. Different query plans may result in a different ordering and this is detected by Qure. So, this feature is actually an additional safety measure for the user and does not compromise the quality of the recommendation as one might think.
To find this out, just click on the script tab to find the SQL statement behind the aforementioned numbers.
On the “Recommendation” tab you can find Qure’s recommendations that could lead to the potential performance improvement that we’ve seen before
A click on the “All (3 Recommendations)” brings you to the “Recommendation” tab where you can inspect each recommendation. As you can see from the above screen, you can see the biggest performance boost is gained from adding tailored indexes for the batch. It is not really surprising to see that most of the recommendations pertain to missing and/or suboptimal indexes. This is usually the weakest point in databases and therefore it is that point by which you can gain most with relatively reasonable costs and efforts.
On the “Script Preview” tab you can see the actual script that would be applicable to the database. But look at the red circle. Qure can not only generate a change script, but also a rollback script that you can use in case something goes wrong and you need to revert the change.
As noted above, the biggest usually come from creating appropriate indexes to optimally support the database queries, but Qure can not only help you with this task, but can do a lot more. It can help you applying best practices. For example, in the above screen you notice the warning Qure issues when it detects a “SELECT *”. While a SELECT * might seem convenient, because one doesn’t have to worry about changing the statement later on when new columns are added, it is not so. Rarely you really need all columns from all tables in a query and while you may not notice a performance boost on the server side by removing a SELECT *, it certainly helps getting the data quicker to the client application because less bytes have to be transferred across the network.
Another example is the use of IN clauses. The clauses are notorious for being slow and limited.
Here you can see what Qure would suggest in such a case, which is a much better construct.
The last two examples are depicted in the above screenshot. By evaluating the actual data stored in the tables, Qure gains quite deep insights into the usage and this is expressed in the “Potential Unused Column” recommendation. In this case Qure found that in one column the same value is stored in every row. This suggests that it may not be necessary to store this value in the database at all, but rather derive that value either at runtime in the database or in the client application.
The “Potentially missing NOT NULL constraint on column…” is also something that Qure derives from looking at the actual data in the tables. If there is no NULL in that column of a table, it might be reasonable to create a NOT NULL constraint for that column. Such recommendation is not necessarily a recommendation for better performance, but rather one that helps you improving your data quality. However, it can also help reducing complexity of your queries, because when you can be sure that there never will be a NULL in a particular column because of the existing constraint, you don’t have to take this special SQL three-valued-logic into account. This can simplify queries which in turn can result in better usage of indices which then indeed can result in a performance gain. It may also help SQL Server’s Query Optimizer in making better estimations about index usefulness and picking a “better” execution plan.
Qure can not only find potentially missing indexes, but also suboptimal indexes that can be improved by rearranging columns and/or dropping or adding new columns to improve performance. This is quite remarkable since one of the commonly used weapons in the tuning arsenal is throwing additional indexes in the ring. However, blindly adding indexes is not always the best you can do since every index on a table has to be maintained for every data modification to its underlying table and for a table with a significantly high ratio of modifications this can rather prove detrimental to performance because the overhead of maintaining the indexes may outweigh the benefit of adding the index in the first place. Therefore is a tool that takes this into account and can suggest modifying existing index structures to provide a balance is worth its price in gold.
