Getting To Know Your Indexes Microsoft SQL Server

Getting To Know Your Indexes Microsoft SQL Server 2005/2008

Who Am I? Jason Strate • SQL Server MVP • Enterprise Consultant with Digineer • Working with SQL Server since 1997 • jstrate@digineer. com • tinyurl. com/Strate. SQL • twitter. com/Strate. SQL

Session Agenda Session Goals Index Basics System Views Index DMVs New Information

Session Agenda Session Goals Index Basics System Views Index DMVs New Information

Session Goals 1. Identify methods to analyze current and potential indexes 2. Learn how to identify stress found on indexes 3. Demonstrate methods for tuning indexes • Usage patterns • Maintenance weight

Last Year on an Airplane… I Had Dream… What if… “I mix all index related DMVs with its metadata into a single query? ”

This Means Something… • Will we learn something new about indexes and their relationships? I Hope So

Session Agenda Session Goals Index Basics System Views Index DMVs New Information

Index Basics • Storage structure (B -Tree) associated with a table or view built from one or more columns (key values). The purpose of which is to improved data retrieval.

Index Basics Types • Clustered – data rows stored with key values • Non-Clustered – key values stored with pointer to data row • Heap – unordered structure Vary with properties of Filtered, Included, Unique Out-of-scope: Full-Text, Spatial, XML

Session Agenda Session Goals Index Basics System Views Index DMVs New Information

Check Out The Indexes 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

System Views DEMO

Session Agenda Session Goals Index Basics System Views Index DMVs New Information

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_group_stats

Is It Money? Methodology • Is it worth the maintenance? Objects • sys. dm_db_index_operational_stats

Did I Just Say Maintenance? Methodology • Tuning is better than troubleshooting Object • sys. dm_db_index_physical_stats

Index DMVs DEMO

Are You Burning Disk? Methodology • IO is resource intensive – are you managing it? Objects • sys. dm_os_buffer_descriptors • sys. allocation_units

Index DMVs DEMO

Session Agenda Session Goals Index Basics System Views Index DMVs New Information

Pull It Together Index Analysis • System Views • DMVs http: //indexanalysis. codeplex. com/

Should I Create The Index? Scenario • 2 nd index on table • 10 th index on table • 20 th index on table • Similarities It Depends

Should I Drop The Index? Scenario • Index is hardly used. • Maybe it’s used to determine your bonus • What’s the risk? It Depends

Code Word: It Depends • • Get the information you need Table A and Table B have different needs Build a methodology around your databases Best practices are guidelines

Build A Methodology Analyze Remove Add Monitor

Remember 1. Spend time getting to know your indexes regularly 2. Data changes. Usage changes. So indexing needs change 3. DMV statistics are based on maintenance and service start times

Complete the Evaluation Form & Win! • You could win a Dell Mini Netbook – every day – just for handing in your completed form! Each session form is another chance to win! Pick up your Evaluation Form: • Within each presentation room • At the PASS Booth near registration area Drop off your completed Form: • Near the exit of each presentation room • At the PASS Booth near registration area Sponsored by Dell

Thank you for attending this session and the 2009 PASS Summit in Seattle