Menu
A while back when I was performing some bulk inserts of data into my SQL Server database, I disabled a number of indexes to improve the insert performance. I now need to go back and rebuild/re-enable them.
Unfortunately, I'm not sure exactly which indexes I disabled.
Is there a way I can query to identify which indexes are disabled and should be re-enabled?
218
Click the plus sign to expand the table on which you want to enable an index. Click the plus sign to expand the Indexes folder. Right-click the index you want to enable and select Rebuild. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to rebuild grid and click OK.
To determine which missing index groups a particular missing index is part of, you can query the sys. dm_db_missing_index_groups dynamic management view by equijoining it with sys. dm_db_missing_index_details based on the index_handle column. The result set for this DMV is limited to 600 rows.
Disabling an index prevents user access to the index, and for clustered indexes to the underlying table data. The index definition remains in metadata, and index statistics are kept on nonclustered indexes. Disabling a clustered index on a view or a nonclustered index physically deletes the index data.
select sys.objects.name as table_name, sys.indexes.name as index_name from sys.indexes inner join sys.objects on sys.objects.object_id = sys.indexes.object_id where sys.indexes.is_disabled = 1 order by sys.objects.name, sys.indexes.name If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
