-- Just a few example of queries that can be used to view index properties etc.. -- Usable for SQL 2005 / 2008 -- Note: -- If a table has no clustered index, then -- index_id=0 :index id of the heap (is the table itself) -- index_id>1 :index id's of the non-clustered indexes (like 1, 2, 3 etc..) -- If a table has a clustered index, then -- index_id=1 :index id of the clustered index (the leaflevel of clustered index is the table itself) -- index_id>1 :index id's of the non-clustered indexes -- Note: the system "views" are visible in some of the lists as well, but if your database has large tables, -- they will not be in the top of the lists, so that they do not "stand out" so much. -- USE YOUR_DATABASE_NAME -- for example: USE SALES -- GO -- 1. Query to show tables and index names, number of rows and other properties: -- ----------------------------------------------------------------------------- -- Tables and Indexes and rows, ordered by the number of rows: Actually, this one works on 2000/2005/2008. SELECT substring(sysobjects.name,1,50) AS TABLENAME, substring(sysindexes.name,1,50) AS INDEXNAME, sysobjects.id, sysindexes.indid, sysindexes.groupid,sysindexes.rows FROM sysobjects, sysindexes WHERE sysobjects.id=sysindexes.id ORDER BY sysindexes.rows desc -- Show only tables with their number of rows, ordered by the number of rows: SELECT substring(sysobjects.name,1,50) AS TABLENAME, sysindexes.rows FROM sysobjects, sysindexes WHERE sysobjects.id=sysindexes.id ORDER BY sysindexes.rows desc -- 2. Just another view on indexes and their properties: -- ----------------------------------------------------- SELECT substring(object_name(id),1,30) as parent_object_name, id, indid, status, dpages, rowcnt FROM sys.sysindexes -- 3. Queries to show index fragmentation: -- --------------------------------------- -- Of all indexes in a Database. Below, you need to fill in YOUR_DATABASE_NAME. SELECT index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (DB_ID('YOUR_DATABASE_NAME'), null,null,null,'DETAILED') WHERE index_id<>0 -- Of a certain index in a certain Database. Below, you need to fill in YOUR_DATABASE_NAME and YOUR_INDEX_NAME. SELECT index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (DB_ID('YOUR_DATABASE_NAME'), OBJECT_ID('YOUR_INDEX_NAME'),null,null,'DETAILED') WHERE index_id<>0 -- 4. Query to get an idea about the effectiveness of your indexes: -- ---------------------------------------------------------------- SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads], user_updates - (user_seeks + user_scans + user_lookups) AS [Difference] FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1 AND s.database_id = DB_ID() AND user_updates > (user_seeks + user_scans + user_lookups) AND i.index_id > 1 -- not the heap or clustered indexes ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC; -- 5. Query to show operational statistics of indexes: -- --------------------------------------------------- -- Listing, using the view sys.dm_db_index_usage_stats: select object_name(object_id), index_id, user_seeks, user_scans, user_lookups from sys.dm_db_index_usage_stats order by object_id, index_id -- Listing, using the function sys.dm_db_index_operational_stats: -- > Of all indexes in a Database. Below, you need to fill in YOUR_DATABASE_NAME. SELECT database_id, object_name(object_id), index_id, leaf_insert_count, leaf_delete_count, nonleaf_delete_count, nonleaf_update_count FROM sys.dm_db_index_operational_stats(DB_ID('YOUR_DATABASE_NAME'), NULL,NULL,NULL) -- > Of a certain index in a Database. Below, you need to fill in YOUR_DATABASE_NAME and YOUR_INDEX_NAME. SELECT database_id, object_name(object_id), index_id, leaf_insert_count, leaf_delete_count, nonleaf_delete_count, nonleaf_update_count FROM -- many more columns could have been chosen sys.dm_db_index_operational_stats(DB_ID('YOUR_DATABASE_NAME'), OBJECT_iD('YOUR_INDEX_NAME'),NULL,NULL)