May 26, 2011 at 11:28 am
I am trying to monitor index fragmentation over time. So I want to run this daily, store the results, and look for trends.
But when I run this on a 300GB SharePoint database, it takes about 15 minutes! This is on a very robust server (16 cores, 72GB RAM, highly optimized, etc).
And there are many smaller databases on this server I would like to monitor as well, so that would be even more time.
Any idea why this takes so long to run, or how I could make it go faster?
SELECT TOP 25
object_id,
avg_fragmentation_in_percent,
page_count,
record_count,
avg_page_space_used_in_percent,
fragment_count,
index_type_desc
FROM sys.dm_db_index_physical_stats (DB_ID( 'myVLDB' ), NULL, NULL, NULL , 'LIMITED')
WHERE
sys.dm_db_index_physical_stats.avg_fragmentation_in_percent > 30 --see values, below.
AND sys.dm_db_index_physical_stats.page_count > 1000 --Adjust as needed. Generally only need to worry about fragmentation on Indexes with over 1000 pages.
ORDER BY sys.dm_db_index_physical_stats.avg_fragmentation_in_percent DESC
/*
avg_fragmentation_in_percent value Corrective statement
---------------------------------- --------------------
> 5% and < = 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*
*/
May 26, 2011 at 11:32 am
Does all that and way more...
May 26, 2011 at 11:36 am
Might be a good read for you too... todays featured article!
http://www.sql-server-performance.com/2011/index-maintenance-performance/
May 26, 2011 at 11:55 am
Ninja's_RGR'us (5/26/2011)
Does all that and way more...
Thanks for the link! And I think this might be an even more robust Index maintenance script (and other things)...
http://ola.hallengren.com/Documentation.html#IndexOptimize
Unfortunately in my production environment I am not permitted to run such a complex script downloaded off the internet, without essentially rewriting it myself. :pinch:
May 26, 2011 at 11:56 am
Ninja's_RGR'us (5/26/2011)
Might be a good read for you too... todays featured article!http://www.sql-server-performance.com/2011/index-maintenance-performance/
Now that article is perfectly on point!
Unfortunately his answer is not good... there is no simple fix. Looks like I have my work cut out for me.........
May 26, 2011 at 2:29 pm
jpSQLDude (5/26/2011)
Ninja's_RGR'us (5/26/2011)
Does all that and way more...Thanks for the link! And I think this might be an even more robust Index maintenance script (and other things)...
http://ola.hallengren.com/Documentation.html#IndexOptimize
Unfortunately in my production environment I am not permitted to run such a complex script downloaded off the internet, without essentially rewriting it myself. :pinch:
If it helps I've used Michelle's script for over 6 months in production and never had any issues whatsoever.
What you need to figure out is what settings are the best for your environement(s). That means testing the script over a few weeks. But you'll have to do that anyways... so you might as well start with something proven to work.
May 26, 2011 at 2:32 pm
jpSQLDude (5/26/2011)
Ninja's_RGR'us (5/26/2011)
Does all that and way more...Thanks for the link! And I think this might be an even more robust Index maintenance script (and other things)...
http://ola.hallengren.com/Documentation.html#IndexOptimize
Unfortunately in my production environment I am not permitted to run such a complex script downloaded off the internet, without essentially rewriting it myself. :pinch:
I can't vouch for this one as I've not tested it. Yet I've only had good comments about it. The main difference I'm aware of is that Ola's script defaults at 5% for reorganize while Michelle's is 10%.
The same rule applies here... test inyour environement to see what works best for you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply