Index maintenance is one of those things that you have to stay on top of, in order to keep your data flowing smoothly. In no particular order:
- Index defrags/rebuilds
- Removing unused indexes
- Optimizing existing indexes
- Saving index definitions (ever had someone complain an index “went missing?”)
- Move them to a separate filegroup/disk
- Etc…
A few months back, we were having issues with heavy I/Os, so one of our plans was to move all the indexes into a separate filegroup, located in a different disk. This way, we’d end up with separate threads: One for the update itself, and one for the index update. The snag was that, overtime, new indexes were added to the original data filegroup, so now they were all over the place.
In an attempt to figure out the lay of the land, I wrote the following query. This script will tell you the filegroup an index lives in, which will help me identify what needs to be moved where. It does not include heaps (which are not indexes) and system indexes (because you should probably leave those alone).
SELECT ds.name AS FileGroupName,
s.name AS schemaname,
o.name AS TableName,
i.name AS indexname
FROM sys.objects o
INNER JOIN sys.indexes i
ON o.object_id = i.object_id
INNER JOIN sys.data_spaces ds
ON ds.data_space_id = i.data_space_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped = 0
AND i.index_id > 1
ORDER BY ds.name,
o.name,
i.index_id;
Once I had them identified, it was easy to write some dynamic sql to move rebuild them into a new location. The syntax is easy:
CREATE INDEX [indexname]
ON table
WITH (ONLINE = ON, DROP_EXISTING = ON)
ON [filegroupname];
You have to write out the entire index definition as it currently stands, but the Drop_existing = ON will instruct SQL to recreate the index in the specified filegroup, instead of just complaining that the index already exists. Online = ON (if you’re on Enterprise edition) will allow you to run this without dropping the index first, or additional blocking.