Good Info about Non-Clustered Indexes

Paul Randal talks about the fate of non-clustered indexes when the table structure changes (in both SQL Server 2000 and 2005). There are some interesting changes in behavior between the two versions. Basically, SQL Server 2005 does not automatically rebuild the non-clustered indexes quite as often as SQL Server 2000 does.

Speaking of indexes, here are a couple of good DMV queries:

-- Lists fragmentation information for a specific table by index
    SELECT a.database_id, a.object_id, a.index_id, b.name, a.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (DB_ID(), 
    object_id('YourTableName'), NULL, NULL, NULL) AS a
    INNER JOIN sys.indexes AS b 
    ON a.object_id = b.object_id 
    AND a.index_id = b.index_id
    WHERE a.database_id = db_id()
    ORDER BY a.object_id
-- Get most frequently updated indexes
    SELECT object_name(p.object_id) AS objname, * 
    FROM sys.dm_db_index_usage_stats AS p
    ORDER BY user_updates DESC

Technorati Tags: ,

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s