SQL Indexes and Plans, Oh My!
Author: jason
Date: 2015-06-17 10:46:22
Category: Technical


Before we start
Indexes are unique and magical to each system. How each system is used will determine if indexes are needed or not. Also, we probably need to completely, routinely, re-evaluate the application’s usage, as user’s patterns change, and holistically implement those changes into the deployment of the application.


And here we go…
Well, before we get into Indexes we need to discuss Tables, maybe Pages too...


Pages & Extents


Tables – Heap & Clustered


How do I tell if a table is a Heap?


How do I tell if this table Heap is a problem?


How do I fix a Heap?


Indexes – Clustered & Non-clustered


And… What is the INCLUDE for?


So… If something runs fast for Customer A but slow for Customer B? (assumes different databases for each customer)


We need to create a Missing Index!!


SQL Execution Plans (Estimated & Actual)


Cached SQL Execution Plans


Missing Index Dynamic Management View


Code

SELECT DB_NAME(mid.database_id) DBName, mid.statement, migs.avg_user_impact, mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle


Note: sometimes SQL can by tricksy and show you a missing index, but there is already an index with those columns and maybe even with the same name. Remember, this is a Microsoft product, and is probably a Feature to some MS Developer, not a Bug.

References





jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009