Tip: Use SQL Sentry's Free Plan Explorer to Find Nagging Query Issues
By Aaron Bertrand, SQL Server MVP
In 2010, SQL Sentry introduced to the SQL Server community a free tool called Plan Explorer. This tool is designed to make it much easier to interpret execution plans than you can do with the native functionality found in SQL Server Management Studio. While some of the features are instantly obvious, such as color highlighting of expensive nodes, some others are a little more obscure. And it is not necessarily automatic to correlate those details with the actual problem they're highlighting. So I thought I would take a few moments and point out a couple of nagging query issues that Plan Explorer calls out for you. Note that these are based on actual execution plans, not estimated.
How many times have you made a small change to a certain aspect of the database (schema, code, indexing etc.) and prayed that it wouldn’t have any unexpected side effects on your workload? In a recent article, ‘The Hidden Menace of CREATE INDEX’, I discussed one potential aspect of this issue, the unexpected and hard-to-predict impact of adding indexes. In this article, I will discuss the ‘Database Operations’ feature of Qure Optimizer . This sometimes-overlooked feature can be a great help in predicting the potential scope-of-effect for any recommended change you are about to apply to the database. This will help you avoid the potentially devastating ‘butterfly effect’, where even a small (but not carefully considered) change can have unfortunate consequences.
Tip: Comparing Database Workloads with "Qure Analyzer" - Part II
By Ami Levin, SQL Server MVP
The background
In Part I of this article, we discussed how Qure Analyzer came to be, and how it can come to your aid whenever you need to compare two production database workloads. We elucidated the simplicity of the various filtering, grouping and sorting controls that allow you to create comparison views to suit your interests. We also defined the differences between controlled and uncontrolled workloads. Although I’m sure you all remember the distinction, we offer it to you here again as a door into Part II:
Tip: Multi-dimensional Analysis of SQL Server Trace Files
By Ami Levin, SQL Server MVP
SQL Server traces can be a lot of fun to read and analyze in their native form. Natively, you can either use the profiler interface to browse through millions of events, or query a trace table using complex functions. But extracting meaningful data and seeing "the forest for the trees" can often be a challenge. Fortunately, Microsoft and 3rd party vendors have released tools to take some of the fun out of this task. Popular free tools include RML Utilities from the Microsoft CSS team, ClearTrace from fellow SQL Server MVP Bill Graziano, and our very own DBSophic Trace Analyzer. More recently, cloud-based offerings have emerged, including Quest Software's Project Lucy and an online version of ClearTrace AKA TraceTune.
Tip: Comparing Database Workloads with “Qure Workload Analyzer”
By Ami Levin, SQL Server MVP
After Qure Workload Analyzer's sister product, Qure Workload Optimizer, had been on the market for a while, some of its customers came to us asking for a way to compare workloads running in production. Here's how that came about: Qure Workload Optimizer, as you may (or may not) know, works in a strictly non-production setting, acting on an offline copy of the production database. It generates and automatically applies various changes that promise to optimize your database's performance, and then empirically measures how much of that promise has been realized. It does this by replaying the copy of the workload before and after the code changes. This actual measurement is extremely accurate when seen in the apples-to-apples comparison performed on the offline copy of your production database, and is a great predictor of what later happens on the real production database.
However, as good as that is, the results are still a prediction.