Aug 17, 2014

Understanding Execution Plan

Cluster Index Scan
If there is no WHERE clause or it is on column which is not indexed, then SQL Server scans the entire table. If table has a Clustered Index it will do a Clustered Index Scan otherwise it will do a Table Scan.

Cluster Index Seek
If where clause is on cluster index then it will do cluster index seek.

Non Cluster Index Seek

CREATE NONCLUSTERED INDEX [IX_sku]
ON [dbo].[Products] ([sku])
GO

In following case SQL Server will ideally do Non Cluster Index Seek

SELECT ID, SKU FROM [dbo].[Products] WHERE SKU = '123456'

Non Cluster Index Seek and Key Lookup on the clustered index
If where clause is on non-cluster index column and you are selecting * or columns other than the column on which table has non-cluster and cluster index , then SQL Server first does Index Seek (nonclustered) and then do a key lookup (clustered). The other operation which happens is Nested Loops which joins results from Index Seek and the Key Lookup. Included columns should be columns that you don't search on, but will return.

SELECT ID, SKU,Description FROM [dbo].[Products] WHERE SKU = '123456'

You can improve performance in above scenario by Creating a Covering Index or by Creating an Index with Included Columns. This way SQL Server doesn't have to do Key Lookup.

CREATE NONCLUSTERED INDEX [IX_LastName]
ON [dbo].[Products] ([sku],[Description])

Joining on non-like datatypes frequently causes table scan even when indexes are present

2005 introduced Dynamic Management Views (DMVs) which can be used to see how sql server uses index. This can also be used for  Identifying Unused Indexes.

DMV - sys.dm_db_index_operational_stats


SELECT 
 OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
 I.[NAME] AS [INDEX NAME], 
 A.LEAF_INSERT_COUNT, 
 A.LEAF_UPDATE_COUNT, 
 A.LEAF_DELETE_COUNT 
FROM   
 SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 
INNER JOIN SYS.INDEXES AS I ON 
 I.[OBJECT_ID] = A.[OBJECT_ID] 
    AND I.INDEX_ID = A.INDEX_ID 
WHERE  
 OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
 AND OBJECT_NAME(A.[OBJECT_ID]) = 'Products'

SYS.DM_DB_INDEX_USAGE_STATS


SELECT 
 OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
 I.[NAME] AS [INDEX NAME], 
 USER_SEEKS, 
 USER_SCANS, 
 USER_LOOKUPS, 
 USER_UPDATES 
FROM   
 SYS.DM_DB_INDEX_USAGE_STATS AS S 
INNER JOIN 
 SYS.INDEXES AS I 
 ON I.[OBJECT_ID] = S.[OBJECT_ID] 
 AND I.INDEX_ID = S.INDEX_ID 
WHERE  
 OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
    AND S.database_id = DB_ID() 
 AND OBJECT_NAME(S.[OBJECT_ID]) = 'Products'  

  • The seeks refer to how many times an index seek occurred for that index.  A seek is the fastest way to access the data, so this is good.
  • The scans refers to how many times an index scan occurred for that index.  A scan is when multiple rows of data had to be searched to find the data.  Scans are something you want to try to avoid.
  • The lookups refer to how many times the query required data to be pulled from the clustered index or the heap (does not have a clustered index).  Lookups are also something you want to try to avoid.
  • The updates refers to how many times the index was updated due to data changes which should correspond to the first query above.

To manually remove a single plan or all plans from the cache, use DBCC FREEPROCCACHE
Reference:
https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

No comments:

Post a Comment