Dave Heavy Industries » statistics http://www.daveheavyindustries.com Dave Heavy Industries - blog Mon, 12 Aug 2013 00:24:10 +0000 en-US hourly 1 http://wordpress.org/?v=3.6 MS SQL Server – index fragmentation – amended http://www.daveheavyindustries.com/2011/04/29/ms-sql-server-index-fragmentation-amended/ http://www.daveheavyindustries.com/2011/04/29/ms-sql-server-index-fragmentation-amended/#comments Thu, 28 Apr 2011 23:39:00 +0000 admin http://wp.daveheavyindustries.com/?p=148 From http://wp.daveheavyindustries.com/2011/02/07/ms-sql-server-index-fragmentation/

Having had to use this on a very large database, I've made some small changes to the index fragmentation statistics script. this time to include table names (object_name) as well as database_name.

SELECT
ps.database_id,
ps.OBJECT_ID,
OBJECT_NAME(ps.OBJECT_ID,db_id()) as [object_name],
DB_NAME(ps.database_id) as [database_name],
ps.index_id,
b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b on ps.OBJECT_ID = b.object_id and b.index_id = ps.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.avg_fragmentation_in_percent
GO
]]> http://www.daveheavyindustries.com/2011/04/29/ms-sql-server-index-fragmentation-amended/feed/ 0