/* Part I */
--Drop Table #indstats
IF exists (SELECT * FROM tempdb.sys.objects WHERE name like '%#indstats%')
BEGIN
DROP TABLE tempdb.dbo.#indstats
END
BEGIN
CREATE TABLE #indstats (
IndStatsID INT PRIMARY KEY CLUSTERED
,database_id BIGINT
,index_id BIGINT
,IndexSizeMB DECIMAL(16,1)
,OBJECT_ID BIGINT
);
END
INSERT INTO #indstats (IndStatsID,database_id,index_id,OBJECT_ID,IndexSizeMB)
SELECT Row_Number() OVER (ORDER BY OBJECT_ID) AS IndStatsID
,database_id,index_id,OBJECT_ID
,CONVERT(DECIMAL(19,2),(SUM(ps.page_count))) * 8 /1024 AS IndexSizeMB
FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'DETAILED') ps
GROUP BY database_id,OBJECT_ID,index_id;
/* Part II */
DECLARE @dbsize DECIMAL(19,2)
SET NOCOUNT ON
/*
** Summary data.
*/
BEGIN
SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
FROM sys.database_files
END
/* Part III */
BEGIN
WITH RegData AS (
SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
,FileGroupName = FILEGROUP_NAME(a.data_space_id)
,TableName = OBJECT_NAME(p.OBJECT_ID)
,NumRows = p.ROWS
,UsedPages = IsNull(a.used_pages,0)
,TotalPages = IsNull(a.total_pages,0)
,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
,UserUpdates = IsNull(us.user_updates,0)
,LastUpdate = IsNull(us.last_user_update,null)
,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
FROM sys.allocation_units a
INNER Join sys.partitions p
ON p.hobt_id = a.container_id
And a.type = 1
LEFT Outer Join sys.dm_db_index_usage_stats us
ON us.OBJECT_ID = p.OBJECT_ID
And us.index_id = p.index_id
And us.database_id = DB_ID()
LEFT Outer Join #indstats ps
ON p.index_id = ps.index_id
And ps.database_id = DB_ID()
And p.OBJECT_ID = ps.OBJECT_ID
--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
)
, LOBData AS (
SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
,FileGroupName = FILEGROUP_NAME(a.data_space_id)
,TableName = OBJECT_NAME(p.OBJECT_ID)
,NumRows = p.ROWS
,UsedPages = IsNull(a.used_pages,0)
,TotalPages = IsNull(a.total_pages,0)
,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
,UserUpdates = IsNull(us.user_updates,0)
,LastUpdate = IsNull(us.last_user_update,null)
,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
FROM sys.allocation_units a
INNER Join sys.partitions p
ON p.partition_id = a.container_id
And a.type = 2
LEFT Outer Join sys.dm_db_index_usage_stats us
ON us.OBJECT_ID = p.OBJECT_ID
And us.index_id = p.index_id
And us.database_id = DB_ID()
LEFT Outer Join #indstats ps
ON p.index_id = ps.index_id
And ps.database_id = DB_ID()
And p.OBJECT_ID = ps.OBJECT_ID
--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
)
, OverFlowData AS (
SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
,FileGroupName = FILEGROUP_NAME(a.data_space_id)
,TableName = OBJECT_NAME(p.OBJECT_ID)
,NumRows = p.ROWS
,UsedPages = IsNull(a.used_pages,0)
,TotalPages = IsNull(a.total_pages,0)
,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
,UserUpdates = IsNull(us.user_updates,0)
,LastUpdate = IsNull(us.last_user_update,null)
,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
FROM sys.allocation_units a
INNER Join sys.partitions p
ON p.hobt_id = a.container_id
And a.type = 3
LEFT Outer Join sys.dm_db_index_usage_stats us
ON us.OBJECT_ID = p.OBJECT_ID
And us.index_id = p.index_id
And us.database_id = DB_ID()
LEFT Outer Join #indstats ps
ON p.index_id = ps.index_id
And ps.database_id = DB_ID()
And p.OBJECT_ID = ps.OBJECT_ID
--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
), IndexSum AS (
SELECT a.OBJECT_ID
,AllDataSizeMB = SUM(CASE WHEN a.index_id in (0,1)
THEN IsNull(a.DataSizeMB,0) + IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0)
ELSE IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0)
END)
FROM RegData a
LEFT Outer Join LOBData p2
ON p2.container_id = a.container_id
LEFT Outer Join OverFlowData p3
ON p3.container_id = a.container_id
GROUP BY a.OBJECT_ID
), SummaryInfo AS (
SELECT
TableName = MAX(a.TableName)
,InRowDataSizeMB = SUM(IsNull(a.DataSizeMB,0))
,LOBDataSizeMB = SUM(IsNull(p2.DataSizeMB,0))
,OFlowDataSizeMB = SUM(IsNull(p3.DataSizeMB,0))
,NumRows = MAX(COALESCE(a.NumRows,p2.NumRows,p3.NumRows,0))
,AllUsedPages = SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0))
,AllPages = SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
,FreeDataSpace = CONVERT(DECIMAL(19,2),
SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
- SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0)))* 8 / 1024
,AllDataSizeMB = MAX(ids.AllDataSizeMB)
,IndexSizeMB = SUM(IsNull(a.IndexSizeMB,0))
+ SUM(IsNull(p2.IndexSizeMB,0)) + SUM(IsNull(p3.IndexSizeMB,0))
,UserRequests = AVG(IsNull(a.UserRequests,0)
+ IsNull(p2.UserRequests,0) + IsNull(p3.UserRequests,0))
,UserUpdates = AVG(IsNull(a.UserUpdates,0) + IsNull(p2.UserUpdates,0) + IsNull(p3.UserUpdates,0))
,LastUpdate = MAX(COALESCE(a.LastUpdate,p2.LastUpdate,p3.LastUpdate,null))
,DatabaseSize = @dbsize
FROM RegData a
LEFT Outer Join LOBData p2
ON p2.container_id = a.container_id
LEFT Outer Join OverFlowData p3
ON p3.container_id = a.container_id
LEFT Outer Join sys.indexes i
ON i.OBJECT_ID = a.OBJECT_ID
And i.index_id = a.index_id
LEFT Outer Join IndexSum ids
ON i.OBJECT_ID = ids.OBJECT_ID
GROUP BY a.OBJECT_ID
), TotalUnused AS (
SELECT SUM(FreeDataSpace) AS UnusedSpace
FROM SummaryInfo
)
SELECT TableName,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB
,AllUsedPages,AllPages
,FreeDataSpace,AllDataSizeMB,IndexSizeMB
,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace
,UserRequests,UserUpdates,LastUpdate
,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100
,DatabaseSize
,DataUsedSize = DatabaseSize - TU.UnusedSpace
,PercentofDataFileUsed = ((IndexSizeMB + AllDataSizeMB) / (DatabaseSize - TU.UnusedSpace)) * 100
FROM SummaryInfo SI
CROSS APPLY TotalUnused TU
ORDER BY PercentofDB DESC
END
