Foreign Keys without an Index
A common performance problem stems from unindexed Foreign Keys. In addressing this, Paul Nielsen wrote (and kindly published the code for) a script to create composite non clustered indexes on foreign keys without a matching index. Thanks Paul! ... Source : Paul Nielsen - SQLBlog.com
Duplicate Indexes
Some great scripts from Paul to find duplicate (or overlapping) indexes ... Source : Paul Nielsen - SQLBlog.com
sp_indexinfo
A fantastic proc from Tibor that returns index information from a number of DMVs. Among other information, it returns the indexes for a table, the type of index (clustered or non clustered), the included columns, how often the index is used and the space taken by the index. Great stuff Tibor ... Source : karaszi.com - Article
Index Fragmentation
In this blog post, Kalen answers a couple of questions on Index Fragmentation giving a good insight into some of the finer details ... Source : Kalen Delaney - SQLblog.com - Blog Post
Online Indexing Operations in SQL Server 2005
Introduced in SQL Server 2005 Enterprise Edition, the online index feature provides a powerful way to perform maintenance operations such as rebuilding or creating indexes in a production system without sacrificing DML concurrency. This paper provides a detailed discussion of the index process and provides guidelines and best practices for implementing this feature in a production environment ... Source : Microsoft.com - Technet Article
Fragmentation in SQL Server - Everything you wanted to know …
A 7 part blog post from Chad Boyd on Fragmentation in SQL Server. What it is, how to avoid it and what to do with it ... Source : Chad Boyd - MSQLTIPS.com - Blog Post
Retrieve Index Usage Statistics
The sys.dm_db_index_usage_stats view allows you to determine which indexes are used only lightly (sum of user columns) and to determine which indexes are incurring maintenance overhead (user_updates column). You may want to consider dropping indexes that incur maintenance overhead, but are used infrequently for queries ... Source : Microsoft.com - Technet Article
Reorganising and Rebuilding Indexes
This BOL article shows how the sys.dm_db_index_physical_stats function can be used to measure fragmentation and discusses the various rebuild options based on the level of fragmentation ... Source : Microsoft.com - MSDN Article
Comparing Tables Organized with Clustered Indexes versus Heaps
This white paper summarizes the advantages and disadvantages, the difference in performance characteristics, and other behaviors of tables that are ordered as lists (clustered indexes) or heaps ... Source : Microsoft.com - Technet Article
DBCC SHOWCONTIG Improvements in SQL Server 2005 and comparisons to SQL Server 2000
This article describes the improvements of DBCC Showcontig in SQL 2005 and it's preferred alternative, the DMV's ... Source : Microsoft.com - Technet Article
How can SQL Server 2005 help me evaluate and manage indexes?
In this blog post, the SQL CAT team talk about using DMV's to determine the cost and effectiveness of indexes ... Source : Tom Davidson - SQL Server Customer Advisory Team - Blog Post
Analysing Indexes
A four part indexing special from Grumpy Old DBA covering index related DMV's, collecting index data, statistics, index size and clustered index selection ... Source : Grumpy Old DBA - Blog Post