| Author |
 Topic  |
|
|
Sonu619
Posting Yak Master
195 Posts |
 Posted - 11/07/2012 : 12:37:52
|
I need help to write a sql query, Here is my Query
to find out Fragmentation Level is more than 25% in the database
DECLARE @DBId INT
SET @DBId = DB_ID('ADVENTUREWORK')
SELECT DISTINCT O.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 O ON b.object_id = O.object_id
WHERE ps.database_id = DB_ID()
AND ps.avg_fragmentation_in_percent>25
Lets say if 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... |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
|
|
Sonu619
Posting Yak Master
195 Posts |
Posted - 11/08/2012 : 13:34:57
|
SELECT DISTINCT O.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 O ON b.object_id = O.object_id
WHERE ps.database_id = DB_ID()
AND ps.avg_fragmentation_in_percent>25
Here is the query i am using to find out which index has fragmentation > 25. I have few tables that has 50 fragmentation, if i run the script or do manullay rebuild the index it doesn't affect fragmentation is still same 50%. Please someone guide me why? or how i can fix this?
Thanks for help... |
 |
|
| |
Topic |
|
|
|
Query Help..
