Title: SQL Query Performances
and Correct Indexes:
Author: Toraj Khavari
Date: December 14, 2016
Objective: Identify right
performance solution for SQL query using Indexes.
Article Body: Often we all
face SQL query performance issues. In my case, I have SSIS Packages with very
complex ETL from Microsoft
AX Dynamics to Enterprise Data Warehouse (EDW) Finance using SQL 2012 R2. One of the query modules
execution time increased from less than 5 minutes to more than 21 minutes when
a few enhancements were implemented to meet business need. Similar query is repeated two more times in
the EDW Finance ETL and CUBE build. The potential of 21 minutes execution times
repeated three times are not acceptable.
Design
changes enabled the execution time to reduce from 21 to 16 minutes. The next
step, I explored adding indexes. I strongly recommend, before just adding
Indexes in the query and your lake of data, identify the tables with the most
impacts after optimizing your code. Then very selectively, add indexes and measure
performance every step of the way. During every performance measurement, clear
the cache using the following DBCC procedures
Use Staging;
DBCC DROPCLEANBUFFERS;
DBCC FLUSHPROCINDB (5);
1-
Execute your query in the SQL Server Management Studio. After
execution, select Query > Analysis Query in Database Engine Tuning Advisor.
2-
Select your desired DB. In my case, Staging. I recommend you
leave the “Tuning Options” tab selection as default until you are 100% sure
about the ramifications of the selection.
3-
Select “Start Analysis”. This will take a few minutes. If you
have a large DB maybe it is not a bad idea to run it at the end of the work day
and see the result, next day.
4-
Index Analysis. This is where the fun begins.
5-
The “Data Engine Tuning Advisor” “Recommendation” tab
contains quite a few suggestions. At this point, exercise caution and make sure
to execute the items that are most important and have the largest impact on the
performance result. Copy the SQL Script Preview’s Recommendations using the “Copy
to Clipboard” button. Paste the Clipboard content to a New Query window and
execute it, if appropriate.
In my
case, I added 9 indexes to 4 SQL tables. The code execution time reduced to
less than a minute. Average 41 Seconds.
SQL 2012
Server Database Engine Tuning Advisor is a very powerful tool. Exercising
caution and careful implementation of the recommendations can have significant
positive impacts on SQL design and run time execution.
Happy
coding.
Toraj
References:
