strategies for sql server index analysis
TRANSCRIPT
INTRODUCTION
Jason Strate
b: www.jasonstrate.com
t: StrateSQL
Resources jasonstrate.com/go/indexing
INTRODUCTION
IntroductionCatalog
Views
Dynamic Management
ObjectsIndex Analysis
Analysis Case Studies
GOALS
Provide a basis for tools in index
analysis
Introduce index analysis stored
procedure
Demonstrate index analysis case studies
CATALOG VIEWS
IntroductionCatalog
Views
Dynamic Management
ObjectsIndex Analysis
Analysis Case Studies
INDEX METADATA
• Methodology:
• Heaps or Clustered?
• Identical Indexes
• Overlapping Indexes
• Objects
• sys.indexes
• sys.index_columns
• sys.dm_db_partition_stats
FOREIGN KEYS?
Methodology
• Index your foreign key columns.
Objects:
• sys.foreign_keys
• sys.foreign_key_columns
Primarily an OLTP best practice
DYNAMIC MANAGEMENT OBJECTS
IntroductionCatalog
Views
Dynamic Management
ObjectsIndex Analysis
Analysis Case Studies
CAPTAIN OBVIOUS
Methodology
• Keep the indexes that you use
Objects
• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats
CAPTAIN OBVIOUS, AGAIN
Methodology
• Build indexes that your queries will find useful
Objects
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
DID I JUST SAY MAINTENANCE?
Methodology
• Tuning is better than troubleshooting
Object
• sys.dm_db_index_physical_stats
INDEX ANALYSIS
IntroductionCatalog
Views
Dynamic Management
ObjectsIndex Analysis
Analysis Case Studies
INDEX ANALYSIS
• Stored procedure
• SQL Server 2005 – 2014
• SQL Database
• Compiles Catalog/DMO in single view
• Filter by tables
• Provides single look at indexes
• Missing indexes
• Missing foreign key indexes
• Multiple outputs
• Detailed
• Realign
• Duplicate
• Overlapping
ANALYSIS CASE STUDIES
IntroductionCatalog
Views
Dynamic Management
ObjectsIndex Analysis
Analysis Case Studies
ALIGNMENT ISSUES
• Excessive key lookups
• Exceed number of seeks
• Problems
• Increased CPU
• Increased IO
• Increased duration
ALIGNMENT ISSUES
• Ignore the problem
• Choose high key lookup rate
• Move clustered index
• Maybe the key is wrong
• Add included columns
• Improve NC indexes
CASE STUDY 2
• What’s wrong?
• Do you see the pattern?
• What do you do?
• Move clustered index
• Add included columns
SCAN HEAVY INDEXES
• High scans on large indexes
• Exceed seeks
• Problems
• Increased IO
• Increased locking• Potential deadlocks
• Affect page life expectancy
• Impact memory distribution
CASE STUDY 1
• Metric is high, so what?
• How do you tie into performance counter?
• Sys.dm_db_index_ usage_stats
• Plan cache
Access Methods:Full Scans/sec
CASE STUDY 2
• What’s wrong?
• Why aren’t the other indexes used?
• Are the right indexes in place?
• What do you do?
• Examine execution plans
• Add in correct indexes
MISSING INDEXES
• Optimize through materialization
• Index versus statistics
• Three options
• Dynamic management views
• Database Engine TuningAdvisor
• Plan Cache
INDEXING FOREIGN KEYS
• Foreign keys enforcement
• INSERT
• UPDATE
• DELETE
• Performed in background
• Unseen until a problem
INDEXING FOREIGN KEY ISSUES
• Unindexed foreign key
• Constraint columns
• Performance issues
• Scan of column values
• Increased locking
• Blocking likely
• Potential deadlocking
THANK YOU
Jason Strate
b: www.jasonstrate.com
t: StrateSQL
Resources jasonstrate.com/go/indexing