September 17, 2008 at 1:06 pm
Consider a scenario with the following indexes:
- Index A is 20,000 pages and 5% fragmented.
- Index B is 10,000 pages and 10% fragmented.
- Index C is 5,000 pages and 20% fragmented.
- Index D is 2500 pages and 40% fragmented.
Will rebuilding or reorganizing Index A give me more performance improvement vs. maintaining the others? Or is it a toss up in this scenario?
I have a handful of DBs with 6000+ tables and am trying to find a decent way to prioritize my maintenance given small maintenance windows. Right now, we are doing index maintenance based on logical fragmentation % but I think that is not efficient because it doesn't take into account the absolute # of pages for each index (very small indexes aren't worth the maintenance) or the % of fragmentation relative to the # of pages.
Thanks,
Rob
September 17, 2008 at 1:24 pm
Just my 2 cents, but personally I'd say it depends on how much each index is being used and if it is a clustered index or not. In 2005 there's some good system views for determining how much your indexes get used such as:
sys.dm_db_index_usage_stats
or you can use the reports in Management Studio (SP2).
September 17, 2008 at 1:29 pm
From http://msdn.microsoft.com/en-us/magazine/cc135978.aspx:
Identifying the Most Costly Indexes
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
[Maintenance cost] = (user_updates + system_updates)
,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
INTO #TempMaintenanceCost
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
AND (user_updates + system_updates) > 0 -- Only report on active rows.
AND s.[object_id] = -999 -- Dummy value to get table structure.
;
-- Loop around all the databases on the server.
EXEC sp_MSForEachDB 'USE [?];
-- Table already exists.
INSERT INTO #TempMaintenanceCost
SELECT TOP 10
[Maintenance cost] = (user_updates + system_updates)
,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL -- Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND (user_updates + system_updates) > 0 -- Only report on active rows.
ORDER BY [Maintenance cost] DESC
;
'
-- Select records.
SELECT TOP 100 * FROM #TempMaintenanceCost
ORDER BY [Maintenance cost] DESC
-- Tidy up.
DROP TABLE #TempMaintenanceCost
Run the above script at frequently set intervals and store the results in a permanent table for analysis.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 18, 2008 at 8:21 am
Thx. That's a good point that it depends on how much the index is used.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply