Monday 29 March 2010

TSQL : Which indexes have I disabled?

Have disabled several reporting indexes to load data faster.
(Yes, some ad-hoc reporting is being done on my processing system but I'm working on that...)
So, how to determine which indexes I have disabled again? >
SELECT 
 SCHEMA_NAME(schema_id) AS [SchemaName]
 , o.name  AS [TableName]
 , i.name AS [IndexName]
 , i.type_desc  AS [IndexType]
 , CASE i.is_disabled  WHEN 0 THEN 'Enabled' ELSE 'Disabled' END AS [IndexStatus]
 , i.fill_factor AS [FillFactor] 
 , 'DROP INDEX [' + i.name + '] ON [' + SCHEMA_NAME(schema_id)  + '].[' + o.name + '] ' AS [DropCommand]
FROM sys.indexes i
INNER JOIN sys.objects o
 ON i.object_id = o.object_id 
 AND o.is_ms_shipped = 0
WHERE is_disabled = 1 
ORDER BY 1,2,3

Disabling an index - ALTER INDEX IX_myindex ON schema1.table1 DISABLE
Rebuilding an index - ALTER INDEX IX_myindex ON schema1.table1 REBUILD

Links:
Solace : Indexes 101
MSSQLTips : Disabling Indexes in SQL 2005 and SQL Server 2008

No comments: