November 7, 2012 at 10:17 am
I need help to write a sql query, Here is my Query
to find out Fragmentation Level is more than 30% in the database
DECLARE @DBId INT
SET @DBId = DB_ID('ADVENTUREWORK')
SELECT Ob.name,PS.index_type_desc,b.name, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@DBId, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
INNER JOIN sys.objects Ob ON b.object_id = Ob.object_id
WHERE ps.database_id = DB_ID()
AND ps.avg_fragmentation_in_percent>30
Lets say i receive 20 index or so. I need help to write a query to Rebuild all index where Fragmentation level is more than 25%.
2) I would like to create a job, its a good idea to run this job once a day or twice?
Thanks for help...
November 7, 2012 at 10:58 am
rocky_498 (11/7/2012)
I need help to write a sql query, Here is my Queryto find out Fragmentation Level is more than 30% in the database
DECLARE @DBId INT
SET @DBId = DB_ID('ADVENTUREWORK')
SELECT Ob.name,PS.index_type_desc,b.name, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@DBId, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
INNER JOIN sys.objects Ob ON b.object_id = Ob.object_id
WHERE ps.database_id = DB_ID()
AND ps.avg_fragmentation_in_percent>30
Lets say i receive 20 index or so. I need help to write a query to Rebuild all index where Fragmentation level is more than 25%.
2) I would like to create a job, its a good idea to run this job once a day or twice?
Thanks for help...
While you can certainly do that and I encourage you to figure it out so that you under stand index fragmentation and how to remove it, there are a number of already available solutions that you can use to incorporate into your index maintenance plans.
The most commonly used one that I am aware of is this one: http://ola.hallengren.com/
You can also find others listed on Kimberly Tripp's blog here: http://www.sqlskills.com/blogs/kimberly/post/Database-Maintenance-Best-Practices-Part-I-e28093-clarifying-ambiguous-recommendations-for-Sharepoint.aspx
We run a heavily customized version of the one authored by Andrew Kelly here: http://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help. We run it daily and rebuild indexes fragmented more than 30%, reorganize those between 15% and 30% and ignore the rest.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply