This article gives a real world explanation (and code examples) of indexing along with the tools necessary to be able to choose the right indexing strategy for your tables.
Archive of posts filed under the Indexing category.
Rebuild and Reorganize Fragmented Indexes
This script will automatically determine whether a rebuild or a reorganize should be used according to the fragmentation of the index. It will then execute the appropriate command. Note that performing index rebuilds online during production hours will cause contention.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(258);
DECLARE @objectname nvarchar(258);
DECLARE @indexname [...]
Reindex All Tables in a Database
Code for reindexing all tables in a database using dbreindex
Find Index Usage
The following DMV query retrieves the usage statistics for existing indexes.
User Seeks – A high number indicates a well utilized index.
User Scans – Number of times the index has been scanned. Could indicate improper ordering of the composite columns
User Lookups – Indicates a different index was used for criteria and the actual [...]
Missing Indexes DMV
This query will show the indexes that are missing ordered according to those having the most impact. It will also provide the create index script needed in order to help you create the index.
SELECT
mid.statement
,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
‘CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) [...]
Choosing the Best Clustered Index
One of the most common database performance issue most companies face is the improper choice of indexes or the lack of indexes. Most essential, is the clustered index. A well-chosen clustered index will save CPU, Memory, and IO. Choosing the clustered index for a table goes hand-in-hand with the writing of the [...]