November 3, 2009 at 4:32 pm
Anyone have a good way to monitor index growth? My end goal is to trend this growth in order to come up with an appropriate fill factor for index rebuilds.
I found this undocumented sp which looks like it would be beneficial to use to gather a metric on growth of indexes
I then wrote this little script to pull all indexes from a table
EXEC sp_MSforeachtable "exec sp_MSindexspace '?'"
I suppose you could then expand that to run on all db's
CREATE TABLE #INDEXSPACE
(
IndexID smallint,
index_name nvarchar(384),
INT,
comments nvarchar(128)
)
DECLARE @command VARCHAR(1000)
Select @command = 'Use [' + '?' + '] EXEC sp_MSforeachtable ' + '"exec sp_MSindexspace ' + +'''' + '@' + '''' + '"' + ', @replacechar = ' + '''' + '@' + '''' + ''
INSERT #INDEXSPACE EXEC sp_MSForEachDB @command
SELECT * FROM #INDEXSPACE ORDER BY index_name
DROP TABLE #INDEXSPACE
This is all a work in progress...I'm actually hoping there's a cleaner way to trend index growth.
Thanks ahead of time 🙂
November 3, 2009 at 4:47 pm
Jon.Morisi (11/3/2009)
My end goal is to trend this growth in order to come up with an appropriate fill factor for index rebuilds.
It's not growth that affects the fill factor. It's how fast the index fragments.
sys.dm_db-index_physical_stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2009 at 4:52 pm
Thanks Gail,
Is that Index space usage useful at all?
November 3, 2009 at 5:00 pm
For monitoring space usage yes.
Fill factor is reduced to alleviate page splits and fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply