Index pages

Wed Apr 30, 2008 by Peter Larsson in administration, sql-server-2000

SQL Server 2005 introduced the new DMV views. They are great and a real improvement to debug and optimize queries.

I find sys.dm_db_index_physical_stats very useful and often write this type of code

SELECT  index_id,
        page_count
FROM    sys.dm_db_index_physical_stats(DB_ID('MyDB'), OBJECT_ID('MyTable'), NULL, NULL, NULL)

to find out if the query optimizer has choosen the "right" index for the query.

This can be done in SQL Server 2000 too!

Use the DBCC SHOWCONTIG command. Maybe most of you have only used this with tables too see table fragmentation?

Well, you can use it for indexes too.

Use

DBCC SHOWCONTIG ('MyDB..MyTable') WITH ALL_INDEXES, TABLERESULTS