December 8, 2010 at 11:54 pm
Hi
Could anyone please provide me a script (not stored procedure) for finding the avg index fragmentation for all the user databases in an instance ? Actually I have got some scripts from the web, but they work only for a current database using sys.dm_db_index_physical_stats ? Any help would be greatly appreciated.
--
SQLRocks
December 9, 2010 at 1:14 am
sqlrocks79 (12/8/2010)
HiCould anyone please provide me a script (not stored procedure) for finding the avg index fragmentation for all the user databases in an instance ? Actually I have got some scripts from the web, but they work only for a current database using sys.dm_db_index_physical_stats ? Any help would be greatly appreciated.
--
SQLRocks
you can use this proc:
exec sp_msforeachdb @command1='exec sp_helpdb ? '
The "?" will be each different db, dont change it.
replace my "exec sp_helpdb" with your procedure to check the index fragmentation.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
December 9, 2010 at 11:12 am
@Henrico
Thank you very much for your help. But still I am not able to able to make it work.
Is there no other way to find the Index fragmentation for all the dbs not for just single db using cursors or any thing similar ? I am using the following query but it needs to specify the current db
SELECT
db_name() AS DbName,
SCHEMA_NAME(B.schema_id) AS SchemaName
, B.name AS TableName
, C.name AS IndexName
, A.INDEX_DEPTH as Index_depth
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
, GetDate() as [TimeStamp]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0
ORDER BY A.avg_fragmentation_in_percent DESC
--
SQLRocks
December 9, 2010 at 2:48 pm
You can use sp_msforeachdb as Henrico mentioned
But the script will fail if there are any 80 compatibility mode dbs due to the DMV limitations. Please note that this may put significant impact on the SQL Server. So run it after business hours.
EXECUTE sp_msforeachdb 'use ?;SELECT
db_name() AS DbName,
SCHEMA_NAME(B.schema_id) AS SchemaName
, B.name AS TableName
, C.name AS IndexName
, A.INDEX_DEPTH as Index_depth
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
, GetDate() as [TimeStamp]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0 and A.INDEX_DEPTH >2
ORDER BY A.avg_fragmentation_in_percent DESC'
Thank You,
Best Regards,
SQLBuddy
December 10, 2010 at 7:17 am
I HIGHLY recommend the free maintenance suite at ola.hallengren.com. The index maintenance part can provide this information for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 10, 2010 at 9:14 am
@ SQLBuddy and Kevin,
Thanks a lot for your help. Now I am able to get the fragmentation info for all the DBs in a single shot.
---
SQLRocks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply