|
|
0
0
0
OVERVIEW
The Execution plan is a cost-based computation of Query Optimizer to come up with the best way to execute a T-SQL statement/batch, accounting the usage of resources utilized – data size, CPU, I/O and Memory. The Query Optimizer executes a given query/batch in numerous ways, and assigns an estimated value to each execution plan known as Cost. The one with the lowest cost is chosen and stored in the plan cache for reuse.
Performance tuning for T-SQL queries has long been a pain and tedious process, so to say, it’s not simple to identify where exactly to begin with and spawn a fast and efficient approach, as there’s a constant variation on DB size and structure. There would be a lot of hits and trials on comparing the queries to get the best fit.
FEATURING – THE SHOWPLAN COMPARISON
To ease out, SQL Server 2016 comes with a cool side-by-side comparison tool integrated into SSMS (SQL Server Management Studio), highlighting the deciding parameters, with a dual Property windows grid display for the selected nodes from the execution plans compared. Evidently, this has eliminated the distractions of hopping between window panes in earlier versions.
Here’s a sample screenshot of comparing execution plans side-by-side in SQL Server 2016.
LET’S COMPARE
As a demo, we’ll run a query with simple join and select “Include Actual Execution Plan” before query execution and save it as “Join.showplan” for further comparison.
SELECT * FROM DimCustomer DC
JOIN DBO.DimGeography DG
ON DC.GeographyKey = DG.GeographyKey
ORDER BY DC.BirthDate
Figure 2.1 – Query1 with missing index and execution time of 1.906 sec.
It’s nice to see the Showplan feature has already begun the work by hinting/suggestion of query betterment about a missing index for column “GeographyKey” from table [dbo].[DimCustomer].
We’ll create Non-Clustered Index Key with the below query
CREATE NONCLUSTERED INDEX [NCI_GeographicKey] ON [dbo].[DimCustomer]
( [GeographyKey] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, ONLINE = OFF) ON [PRIMARY]
GO
Let’s proceed with the 2nd run after adding the missing index with a slight modification by retrieving only selected columns as from query below Query2.
SELECT DC.CustomerKey, DC.FirstName, DC.BirthDate, DC.EmailAddress, DC.DateFirstPurchase,
DC.Phone, DC.GeographyKey
FROM DimCustomer DC
JOIN DBO.DimGeography DG
ON DC.GeographyKey = DG.GeographyKey
ORDER BY DC.BirthDate
Various factors have been marked to provide a better understanding of the undergoing internal operations by SQL Engine.
1. Highlighted with Pink are the matching area/nodes of the compared plans – the Clustered Index Scan
2. Highlighted with Blue are the areas we selected, which is the context of comparison.
3. Dual property windows with side-by-side display for Clustered index scan in Query2 (Top) and Table Scan in Query1 (Bottom).
INFERENCE
The Showplan comparison feature is a relief for a DBA when working on performance tuning due to the simplistic nature with a wide variety of information provided. The above example worked with just a small number of rows, and hence there’s not much of time difference in query execution, but while working with huge tables, this would impact on a superior scale.
The deciding factors that prove the performance of query in the above demo
1. Retrieving only the required columns from a table instead of all the columns decreases the overhead on query.
2. Constructing the missing index on a column based on the requirement sorts the results and improves the search.
3. Query2 has an execution time of 718 millisecond and Query1 has 1.906 sec, and this is because of the addition of the missing index.
Clearly, Query2 has outrun Query1 just with the addition of a missing index which led to the reduction of I/O cost pushing it to execute faster.
REFERENCES