(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:
Post a Comment