Last week, we looked at some of the nifty features of SQL Server Management Studio, namely in its Object Explorer and Query Editor windows. This week, I want to introduce you to its Database Engine Tuning Advisor. This is another great feature of SQL Server Management Studio that analyzes your query and provides suggestions on indexes, statistics, indexed views and partitioning strategies that may help you in achieving increased performance of your database workloads.
But, before we get into that, let me set a few disclaimers, upfront, so that I can try to minimize the number of hate mail I’ll get from all the Database Administrators (DBAs) out there.
- Do not blindly execute the suggestions provided by this tool. While SQL Server will guide you to the right paths most of the time, its recommendations may not be suitable for your particular use case. This tool is not a DBA replacement. Use its recommendations either to validate your own conclusions or as a starting point to your database optimization endeavors.
I also understand the reality on the ground. Many organizations don’t have a dedicated DBA on staff that can help you in analyzing your database with your specific application in their purview and provide you with customized guidance, specific to your use-case. On the other hand, most developers don’t have the in-depth knowledge of the inner-workings of a relational database system to avoid pitfalls and architect solutions that will provide optimal performance even under the most strenuous workloads. This tool can be a great asset in your toolbox, in such cases.
Tuning Your First Query
A quick way to fire up the Database Engine Tuning Advisor is to right-click on a query in the Query Editor window and selecting the “Analyze Query in Database Engine Tuning Advisor” option from the context-menu that appears.
Once the Database Engine Tuning Advisor window pops up, you will be asked to login again. Once logged in, you’ll be placed in the General tab of the tuning tool. Here you can specify a session name and also select the database and tables that you want to use in your tuning.
The “Tuning Options” tab provides additional options such as the type of recommendations that you want SQL Server to make for the query that you have selected.
There is also an “Advanced Options” dialog where you can specify whether to get online recommendations or offline (ones where you need exclusive access to the database system) and specify the amount of the storage that the tool can use for generating recommendations.
Once you have made your selections, press the “Start Analysis” button. After the tool completes its work, you’ll be presented with a list of recommendations. In the case of my demo query against my demo Northwind database, the tuning tool came back with a single recommendation – to add a non-clustered index to the Employees table.
In this particular instance, SQL Server estimates that there will be a 77% improvement to my birthdate query if I add this index. Clicking on the index definition link will open up a SQL Script Preview box containing the SQL Script for creating the said index. I can copy the script to my clipboard for further inspection and experimentation.
Alternatively, I can choose to Apply Recommendations or to Save the recommendations as a SQL file that I can share with someone else.
Is SQL Server Utilizing My Index?
Here’s a bonus tip – SQL Server Management Studio also has great tooling that will give you further insights into the under-the-hood operations of SQL Server when executing a particular query. Although, we won’t go into the details of it here, I’ll simply note that when you’re executing a query, you can specify to also display the Execution Plan that the engine takes. Let’s turn that option on and execute a query involving the BirthDate column of the Employees table. I have created an index on this column, as was the suggestion from Database Tuning Advisor, earlier. In order to do so, you can press the Ctrl + M keyboard shortcut from within a query window or press the associated button in the toolbar.
Now, when I run a query that involves a filtering on this column and view the execution plan, you’ll see that SQL utilized that index to fetch the results.
Conclusion
The Database Engine Tuning Advisor is a great tool in learning about how SQL Server and other relational database engines work. It gives you great insight into how indexes are utilized by the engine to return results from even large database tables containing millions of records in sub-second times.
Today, we also briefly got introduced to the Execution Plan viewer in SQL Server Management Studio. In a future installment, we’ll dig into Execution Plans at a deeper level, gaining a understanding of all the under-the-hood steps the database system takes to furnish the results of our queries.