Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Index pages

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

Print | posted on Wednesday, April 30, 2008 9:38 AM | Filed Under [ Administration SQL Server 2000 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET