SELECT SCH.name AS SchemaName
,OBJ.name AS ObjName
,OBJ.type_desc AS ObjType
,INDX.name AS IndexName
,INDX.type_desc AS IndexType
,PART.partition_number AS PartitionNumber
,PART.rows AS PartitionRows
,STAT.row_count AS StatRowCount
,cast(((STAT.used_page_count * 8)/1000000) as decimal(18,2)) AS UsedSizegb,
cast(((STAT.used_page_count * 8)/1000) as decimal(18,2)) AS UsedSizemb,
cast(((STAT.reserved_page_count * 8)/1000000) as decimal(18,2)) AS RevervedSizegb,
cast(((STAT.reserved_page_count * 8)/1000) as decimal(18,2)) AS RevervedSizeMb,
STAT.reserved_page_count * 8 AS RevervedSizeKB
FROM sys.partitions AS PART
INNER JOIN sys.dm_db_partition_stats AS STAT
ON PART.partition_id = STAT.partition_id
AND PART.partition_number = STAT.partition_number
INNER JOIN sys.objects AS OBJ
ON STAT.object_id = OBJ.object_id
INNER JOIN sys.schemas AS SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS INDX
ON STAT.object_id = INDX.object_id
AND STAT.index_id = INDX.index_id
-- ) vw
order by STAT.used_page_count desc,STAT.row_count desc
-- geting non indexed column
select
t.name,sum(i.index_id)
from sys.tables t
LEFT OUTER join sys.indexes i on t.object_id=i.object_id
group by t.name,i.index_id
Leave a Reply