Quickly energize your slow running SQL query
Hello Friends, I am back with a new post after so many days. In the meantime I received a few emails from people across the globe asking me why I don’t write on my blogs any more. First of all I will like to thank people who see value in my work. Second, I regret I was unable to do it as I was a bit involved in some or the other ongoing day to day activities and even after trying hard a few times to complete my posts my schedule didn’t permit me to complete it. I hope going forward I will keep posting some blogs.
So today’s post is to help you in tuning some of your queries. Let’s set some ground rules first: I am providing you very basic steps to tune your slow queries and this post is meant for people who don’t know how to tune the queries and this post is not to advise the SQL Pros. Second, try the suggested steps in your test/development environment first and then if it works for you then you may port it to the production environment.
Thank you very much for visiting this article. In case if you are not on the MSDN blogs then I would request you to please visit my blog at https://blogs.msdn.com/b/manisblog/ because at times I improve the existing articles after reading emails from people who enthusiastically provide their feedback. These improvements might not be reflected on the other blog sites who have indexed this blog post.
Let us take an example. Suppose the following query in AdventureWorks Sample database is slow then how will you tune it.
Sample Query
SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.OrderDate, SalesOrderHeader.SalesOrderNumber, SalesOrderHeader.PurchaseOrderNumber, SalesOrderHeader.CustomerID, SalesOrderDetail.ProductID, SUM(SalesOrderDetail.UnitPrice), SUM(SalesOrderDetail.UnitPriceDiscount), SUM(SalesOrderDetail.LineTotal)
FROM Sales.SalesOrderHeader, Sales.SalesOrderDetail WHERE SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
GROUP BY SalesOrderHeader.SalesOrderID, SalesOrderHeader.OrderDate, SalesOrderHeader.SalesOrderNumber, SalesOrderHeader.PurchaseOrderNumber, SalesOrderHeader.CustomerID, SalesOrderDetail.ProductID |
Step 1:
Now you will execute this query in SQL Server 2008 Management Studio.
Step 2:
Go the the query menu in the SQL Server Management Studio and click on the Option “Analyze Query in Database Engine Tuning Advisor”.
Step 3:
This will open the up the Database Engine Tuning Advisor (DTA), the radio button “Query” will be selected, your database will be selected in the grid below.
Step 4:
Click on the ‘Tuning Options’ Tab on the top and when you reach the tab make sure that you have selected appropriate options. You may select Indexes and Indexed Views option under Physical Design Structures and may wish to get recommendations on Filtered Indexes. For Filtered Indexes please refer to my post on Filtered Indexes.
You can also select the appropriate partitioning strategy if you want the DTA to provide you with the suggestions related to the partitions.
The third section is “PDS to keep in database”. I have chosen the option “Keep all existing PDS” otherwise DTA will ask you to drop the indexes and partitions that are not used. Please remember that you are analyzing a single query so it may not be appropriate to determine an unused partition or index based on a single query. It may be the case that other queries are using the indexes & partitions. The other options are helpful when you are actually feeding DTA will a workload file (which is out of the scope of this post).
When all the options are set properly, click on the “Start Analysis” button on the toolbar.
The analysis will go on for a few minutes like the screenshot provided below.
Step 5:
Once the analysis completes, you will be provided with a few suggestions, you can click on the “Save Recommendations” button to save the DTA recommendations as a SQL File.
Step 6:
Open the SQL file that you have saved in the SQL Server Management Studio. Go through the queries that the DTA has provided as suggestions, make the modifications that you think are required and then EXECUTE the queries.
Suggestions, I received from the DTA (You may receive different ones)
use [AdventureWorks] go
CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderDetail_10_642101328__K1_K5_4_7_8] ON [Sales].[SalesOrderDetail] ( [SalesOrderID] ASC, [ProductID] ASC ) INCLUDE ( [OrderQty], [UnitPrice], [UnitPriceDiscount]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] go
CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderHeader_10_754101727__K1_3_9_11] ON [Sales].[SalesOrderHeader] ( [SalesOrderID] ASC ) INCLUDE ( [OrderDate], [PurchaseOrderNumber], [CustomerID]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] go |
Wooo Hoo ! You have improved revitalized the SQL query to sprint which was walking earlier.
That’s it from now my friends, keep a watch on the blog and I will post something new soon.
-Ravi