Improving SQL Server Query Performance with Indexes

SQL Server 2012 includes several Dynamic Management Views (DMVs) that enable you to fine-tune queries. DMVs are useful to surface execution statistics for a particular query such as the number of times it has been executed, number of reads and writes performed, amount of CPU time consumed, index query usage statistics, and so on.

You can use the execution statistics obtained through DMVs to fine-tune a query by refactoring the T-SQL code to take advantage of parallelism and existing indexes, for example. You can also use them to identify missing indexes, indexes not utilized, and identify indexes that require defragmentation.

For example, explore the existing indexes in the FactInternetSales table from the AdventureWorks database. As shown in Figure 1 the FactInternetSales table has been indexed fairly well.

Figure 1. FactInternetSales Table

To illustrate the query tuning process, run through a series of steps to generate execution statistics that can surface through DMVS:

1.   Drop the existing ProductKey and OrderDateKey indexes from the FactInternet Sales table as follows:

USE [AdventureWorks]
GO
-- Drop ProductKey index
IF  EXISTS (SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID(N’[dbo].[FactInternetSales]’) AND
name = N’IX_FactInternetSales_ProductKey’)
DROP INDEX [IX_FactInternetSales_ProductKey] ON [dbo].[FactInternetSales]
GO
-- Drop OrderDateKeyIndex
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[FactInternetSales]’)
AND name = N’IX_FactInternetSales_OrderDateKey’)
DROP INDEX [IX_FactInternetSales_OrderDateKey] ON [dbo].[FactInternetSales]
GO

2.   Execute the following script three times, like so:

/*** Internet_ResellerProductSales ***/
SELECT
 D.[ProductKey],
 D.EnglishProductName, 
 Color,
 Size,
 Style,
ProductAlternateKey,
 sum(FI.[OrderQuantity]) InternetOrderQuantity,
 sum(FR.[OrderQuantity]) ResellerOrderQuantity,
 sum(FI.[SalesAmount]) InternetSalesAmount, 
 sum(FR.[SalesAmount]) ResellerSalesAmount    
FROM [FactInternetSales] FI
 INNER JOIN DimProduct D
  ON FI.ProductKey = D.ProductKey
 INNER JOIN FactResellerSales FR
  ON FR.ProductKey = D.ProductKey
WHERE
 FI.OrderDateKey BETWEEN 20000101 AND 20041231
 AND FR.OrderDateKey BETWEEN 20000101 AND 20041231
GROUP BY
 D.[ProductKey],
 D.EnglishProductName,
 Color,
 Size,
 Style,
 ProductAlternateKey

 Figure 2 displays T-SQL script executed along with results and an execution time of 11 seconds. Your execution results may vary depending on the resources available to your machine.

Figure 2. Sample T-SQL Script Execution

3.   Run the following script to analyze the execution statistics of the previous query.

SELECT TOP 10
 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
 ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT)
 ELSE qs.statement_end_offset
 END - qs.statement_start_offset)/2)+1) QueryText,
 qs.last_execution_time,
 qs.execution_count ,
 qs.last_logical_reads,
 qs.last_logical_writes,
 qs.last_worker_time,
 qs.total_logical_reads,
 qs.total_logical_writes,
 qs.total_worker_time,
 qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
 qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
 qp.query_plan
FROM
 sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY
 qs.last_execution_time DESC,
 qs.total_logical_reads DESC

Figure 3 shows the execution statistics reported mainly by the sys.dm_exec_query_stats DMV.

Figure 3. Sample Execution Stats

From this DMV you can observe that there was a large number of reads and long period of time the processor was busy executing the query. Keep these baseline numbers in mind. At the end of this example, you can reduce these numbers.

4.   Query the sys.dm_db_missing_index_details DMV to check if missing indexes are reported like so:

SELECT
 DatabaseName = DB_NAME(database_id,)
 [Number Indexes Missing] = count(*)
FROM
 sys.dm_db_missing_index_details
GROUP BY
 DB_NAME(database_id)
ORDER BY 2 DESC;

Figure 4 shows the results of the sys.dm_db_missing_index_details DMV. The sys.dm_db_missing_index_details DMV is a great way to quickly identify if you need indexes. If you have a substantial number of missing indexes, you may want to run the Database Tuning Advisor to let the Query Optimizer recommend which indexes to implement.

Figure 4. Sample DMV Results

5.   Continuing with your query tuning endeavor, create the ProductKey and OrderDateKey indexes on the FactInternetSales table as follows:

USE [AdventureWorks]
GO
IF  EXISTS
 (SELECT * FROM sys.indexes
  WHERE object_id = OBJECT_ID(N’FactInternetSales’)
  AND name = N’IX_FactInternetSales_OrderDateKey’)
  DROP INDEX IX_FactInternetSales_OrderDateKey ON
  FactInternetSales
GO
IF NOT EXISTS
 (SELECT * FROM sys.indexes
  WHERE object_id = OBJECT_ID(N’[dbo].[FactInternetSales]’) AND
  name = N’IX_FactInternetSales_ProductKey’)
 

  CREATE NON-CLUSTERED INDEX IX_FactInternetSales_ProductKey ON
  FactInternetSales
 (ProductKey ASC)
  WITH
  (PAD_INDEX = OFF,
   STATISTICS_NORECOMPUTE = OFF,
   SORT_IN_TEMPDB =  OFF,
   DROP_EXISTING = OFF,
   ONLINE = OFF,
   ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON
  ) ON [PRIMARY]
GO

6.   Execute the Internet_ResellerProductSales query defined in step 2 three more times. Figure 5 shows that execution time went down to 8 seconds! That is a 28% improvement in query execution simply by adding the appropriate index.

Figure 5. Sample Query Results