It is very easy to find out some basic details of any table using the following Stored Procedure.
USE AdventureWorks
GO
EXEC sp_spaceused [HumanResources.Shift]
GO
Above query will return following resultset
The above SP provides basic details such as rows, data size in table, and Index size of all the indexes on the table.
If we look at this carefully, a total of three indexes can be found on the table HumanResources.Shift.
USE AdventureWorks
GO
SELECT *
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Shift')
GO
The above query will give result with query listing all the index on the table.
There is a small puzzle for all of you here. The puzzle is to write a query that will return the size for each index that is listed in above query. We need a query that will return an additional column in the above listed query and it should contain the size of the index. In our case, we will have three different sizes, which should add up to a total of 40 KB as shown in earlier query, where the total size is displayed.
I will publish the solution with due credit on this blog.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
Hi Pinal, there is an undocumented stored proc called sp_MSIndexSpace that will list size of indexes. However this wil not include XML indexes. To include all there is a very long query that Microsoft supplies on their list of useful DMVs, the main DMV they use is sys.dm_db_partition_stats. I will cut and paste the query tomorrow, am not taking credit for it as it is pre written!
Malathi
Brilliant. I hadn’t known about this.
Cheers,
Adam
Just about everything you wanted to know about an index, certainly far more that you asked for.
IF EXISTS (SELECT 1 FROM sys.Objects WHERE OBJECT_ID = OBJECT_ID(‘MTG.PageCountToKB_MB_GB’) AND TYPE = ‘FN’)
DROP FUNCTION MTG.PageCountToKB_MB_GB;
GO
CREATE FUNCTION MTG.PageCountToKB_MB_GB
( @PageCount BIGINT
,@ConvertTo CHAR(2)
)
RETURNS DECIMAL(24,2)
AS
— ===================================================================
/*
Permission to use, copy, modify, distribute but not sell this software
and its documentation for any purpose is hereby granted without fee,
provided that this copyright notice appears in all copies and
that both the copyright notice and this permission notice appear in
supporting documentation.
No representations are made about the suitability of this software
for any purpose. It is provided “as is” without express or implied warranty.
*/
— ===================================================================
BEGIN
DECLARE @IncomingBytes DECIMAL(24,2);
DECLARE @KB DECIMAL(24,4)
DECLARE @MB DECIMAL(24,4)
DECLARE @GB DECIMAL(24,4)
SELECT @IncomingBytes = @PageCount * 8192
,@KB = 1024
,@MB = 1048576
,@GB = 1073741824;
RETURN CASE WHEN @ConvertTo = ‘KB’ THEN @IncomingBytes/@KB
WHEN @ConvertTo = ‘MB’ THEN @IncomingBytes/@MB
WHEN @ConvertTo = ‘GB’ THEN @IncomingBytes/@GB
ELSE 0
END;
END;
GO
DECLARE @DBCompatibilityLevel TINYINT;
SELECT @DBCompatibilityLevel = compatibility_level
FROM sys.databases
WHERE name = ‘MTGUtility’;
SELECT schemas.name SchemaName
,objects.name TableName
,indexes.name IndexName
,indexes.type_desc IndexType
,indexes.is_unique is_unique
,indexes.is_primary_key is_primary_key
,indexes.is_unique_constraint is_unique_constraint
,indexes.ignore_dup_key ignore_dup_key
,indexes.fill_factor fill_factor
,indexes.is_padded is_padded
,indexes.is_disabled is_disabled
,indexes.is_hypothetical is_hypothetical
,indexes.allow_row_locks allow_row_locks
,indexes.allow_page_locks allow_page_locks
,indexes.has_filter has_filter
,indexes.filter_definition filter_definition
,dm_db_partition_stats.partition_number partition_number
,dm_db_partition_stats.row_count row_count
,MTGUtility.MTG.PageCountToKB_MB_GB
(dm_db_partition_stats.in_row_data_page_count, ‘MB’) DataMB
,MTGUtility.MTG.PageCountToKB_MB_GB
( dm_db_partition_stats.in_row_used_page_count
+ dm_db_partition_stats.lob_used_page_count
+ dm_db_partition_stats.row_overflow_used_page_count
, ‘MB’) DataUsedMB
,MTGUtility.MTG.PageCountToKB_MB_GB
( dm_db_partition_stats.reserved_page_count, ‘MB’) DataTotalMB
,dm_db_index_usage_stats.user_lookups user_lookups
,dm_db_index_usage_stats.user_scans user_scans
,dm_db_index_usage_stats.user_seeks user_seeks
,dm_db_index_usage_stats.user_updates user_updates
,dm_db_index_usage_stats.last_user_lookup last_user_lookup
,dm_db_index_usage_stats.last_user_scan last_user_scan
,dm_db_index_usage_stats.last_user_seek last_user_seek
,dm_db_index_usage_stats.last_user_update last_user_update
,dm_db_index_usage_stats.system_lookups system_lookups
,dm_db_index_usage_stats.system_scans system_scans
,dm_db_index_usage_stats.system_seeks system_seeks
,dm_db_index_usage_stats.system_updates system_updates
,dm_db_index_usage_stats.last_system_lookup last_system_lookup
,dm_db_index_usage_stats.last_system_scan last_system_scan
,dm_db_index_usage_stats.last_system_seek last_system_seek
,dm_db_index_usage_stats.last_system_update last_system_update
,column01.name Column01
,ISNULL(column02.name, ”) Column02
,ISNULL(column03.name, ”) Column03
,ISNULL(column04.name, ”) Column04
,ISNULL(column05.name, ”) Column05
,ISNULL(column06.name, ”) Column06
,ISNULL(column07.name, ”) Column07
,ISNULL(column08.name, ”) Column08
,ISNULL(column09.name, ”) Column09
,ISNULL(column10.name, ”) Column10
,ISNULL(column11.name, ”) Column11
,ISNULL(column12.name, ”) Column12
,ISNULL(column13.name, ”) Column13
,ISNULL(column14.name, ”) Column14
,ISNULL(column15.name, ”) Column15
,ISNULL(column16.name, ”) Column16
,objects.create_date create_date
,objects.modify_date modify_date
,CASE WHEN indexes.has_filter = 0
AND indexes.is_primary_key = 0
AND indexes.is_unique = 0
AND column01.is_nullable = 1
AND @DBCompatibilityLevel > 99
AND ((column02.is_nullable IS NULL) OR (column02.is_nullable = 1))
AND ((column03.is_nullable IS NULL) OR (column03.is_nullable = 1))
AND ((column04.is_nullable IS NULL) OR (column04.is_nullable = 1))
AND ((column05.is_nullable IS NULL) OR (column05.is_nullable = 1))
AND ((column06.is_nullable IS NULL) OR (column06.is_nullable = 1))
AND ((column07.is_nullable IS NULL) OR (column07.is_nullable = 1))
AND ((column08.is_nullable IS NULL) OR (column08.is_nullable = 1))
AND ((column09.is_nullable IS NULL) OR (column09.is_nullable = 1))
AND ((column10.is_nullable IS NULL) OR (column10.is_nullable = 1))
AND ((column11.is_nullable IS NULL) OR (column11.is_nullable = 1))
AND ((column12.is_nullable IS NULL) OR (column12.is_nullable = 1))
AND ((column13.is_nullable IS NULL) OR (column13.is_nullable = 1))
AND ((column14.is_nullable IS NULL) OR (column14.is_nullable = 1))
AND ((column15.is_nullable IS NULL) OR (column15.is_nullable = 1))
AND ((column16.is_nullable IS NULL) OR (column16.is_nullable = 1))
THEN ‘SELECT COUNT(*) TotalTableRows’
+ CHAR(10) + ‘ ,SUM(CASE WHEN ‘ + column01.name + ‘ IS NULL’
+ CASE WHEN column02.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column02.name + ‘ IS NULL’ END
+ CASE WHEN column03.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column03.name + ‘ IS NULL’ END
+ CASE WHEN column04.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column04.name + ‘ IS NULL’ END
+ CASE WHEN column05.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column05.name + ‘ IS NULL’ END
+ CASE WHEN column06.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column06.name + ‘ IS NULL’ END
+ CASE WHEN column07.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column07.name + ‘ IS NULL’ END
+ CASE WHEN column08.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column08.name + ‘ IS NULL’ END
+ CASE WHEN column09.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column09.name + ‘ IS NULL’ END
+ CASE WHEN column10.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column10.name + ‘ IS NULL’ END
+ CASE WHEN column11.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column11.name + ‘ IS NULL’ END
+ CASE WHEN column12.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column12.name + ‘ IS NULL’ END
+ CASE WHEN column13.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column13.name + ‘ IS NULL’ END
+ CASE WHEN column14.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column14.name + ‘ IS NULL’ END
+ CASE WHEN column15.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column15.name + ‘ IS NULL’ END
+ CASE WHEN column16.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column16.name + ‘ IS NULL’ END
+ CHAR(10) + ‘ THEN 1’
+ CHAR(10) + ‘ ELSE 0’
+ CHAR(10) + ‘ END) FilteredRows’
+ CHAR(10) + ‘FROM ‘
+ schemas.name
+ ‘.’
+ objects.name
+ ‘;’
+ CHAR(10) + ‘CREATE NONCLUSTERED INDEX ‘
+ indexes.name
+ ‘ ON ‘
+ schemas.name
+ ‘.’
+ objects.name
+ CHAR(10)
+ ‘(‘
+ CHAR(9)
+ column01.name
+ CHAR(9)
+ ‘– MTG INDEX GENERATED ON ‘
+ CONVERT(VARCHAR(35), GETDATE(), 121)
+ CASE WHEN column02.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column02.name END
+ CASE WHEN column03.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column03.name END
+ CASE WHEN column04.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column04.name END
+ CASE WHEN column05.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column05.name END
+ CASE WHEN column06.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column06.name END
+ CASE WHEN column07.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column07.name END
+ CASE WHEN column08.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column08.name END
+ CASE WHEN column09.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column09.name END
+ CASE WHEN column10.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column10.name END
+ CASE WHEN column11.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column11.name END
+ CASE WHEN column12.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column12.name END
+ CASE WHEN column13.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column13.name END
+ CASE WHEN column14.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column14.name END
+ CASE WHEN column15.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column15.name END
+ CASE WHEN column16.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘,’ + column16.name END
+ CHAR(10) + ‘)’
+ CHAR(10) + CHAR(9) + ‘WHERE ‘ + column01.name + ‘ IS NOT NULL ‘
+ CASE WHEN column02.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column02.name + ‘ IS NOT NULL’ END
+ CASE WHEN column03.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column03.name + ‘ IS NOT NULL’ END
+ CASE WHEN column04.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column04.name + ‘ IS NOT NULL’ END
+ CASE WHEN column05.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column05.name + ‘ IS NOT NULL’ END
+ CASE WHEN column06.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column06.name + ‘ IS NOT NULL’ END
+ CASE WHEN column07.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column07.name + ‘ IS NOT NULL’ END
+ CASE WHEN column08.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column08.name + ‘ IS NOT NULL’ END
+ CASE WHEN column09.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column09.name + ‘ IS NOT NULL’ END
+ CASE WHEN column10.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column10.name + ‘ IS NOT NULL’ END
+ CASE WHEN column11.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column11.name + ‘ IS NOT NULL’ END
+ CASE WHEN column12.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column12.name + ‘ IS NOT NULL’ END
+ CASE WHEN column13.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column13.name + ‘ IS NOT NULL’ END
+ CASE WHEN column14.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column14.name + ‘ IS NOT NULL’ END
+ CASE WHEN column15.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column15.name + ‘ IS NOT NULL’ END
+ CASE WHEN column16.name IS NULL THEN ” ELSE CHAR(10) + CHAR(9) + ‘ AND ‘ + column16.name + ‘ IS NOT NULL’ END
+ CHAR(10) + CHAR(9) + ‘WITH ( DROP_EXISTING = ON’
+ CASE WHEN indexes.fill_factor > 0
THEN CHAR(10) + CHAR(9) + CHAR(9) + + CHAR(9) + CHAR(9) + ‘,FILLFACTOR = ‘ + CAST(indexes.fill_factor AS VARCHAR(3))
ELSE ”
END
+ CHAR(10) + CHAR(9) + CHAR(9) + + CHAR(9) + CHAR(9) + ‘,PAD_INDEX = ‘ + CASE WHEN indexes.is_padded = 1 THEN ‘ON’ ELSE ‘OFF’ END
+ CHAR(10) + CHAR(9) + ‘ )’
+ ‘;’
+ CHAR(10) + ‘GO’
ELSE NULL
END FilteredIndexDDL
FROM MTGUtility.sys.indexes indexes
JOIN MTGUtility.sys.objects objects ON indexes.object_id = objects.object_id
AND objects.type = ‘U’
JOIN MTGUtility.sys.dm_db_partition_stats dm_db_partition_stats ON indexes.object_id = dm_db_partition_stats.object_id
AND indexes.index_id = dm_db_partition_stats.index_id
LEFT JOIN MTGUtility.sys.dm_db_index_usage_stats dm_db_index_usage_stats ON indexes.object_id = dm_db_index_usage_stats.object_id
AND indexes.index_id = dm_db_index_usage_stats.index_id
AND dm_db_index_usage_stats.database_id = DB_ID()
LEFT JOIN MTGUtility.sys.schemas schemas ON objects.schema_id = schemas.schema_id
LEFT JOIN MTGUtility.sys.index_columns index_column01 ON indexes.object_id = index_column01.object_id
AND indexes.index_id = index_column01.index_id
AND index_column01.index_column_id = 1
LEFT JOIN MTGUtility.sys.columns column01 ON indexes.object_id = column01.object_id
AND index_column01.column_id = column01.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column02 ON indexes.object_id = index_column02.object_id
AND indexes.index_id = index_column02.index_id
AND index_column02.index_column_id = 2
LEFT JOIN MTGUtility.sys.columns column02 ON indexes.object_id = column02.object_id
AND index_column02.column_id = column02.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column03 ON indexes.object_id = index_column03.object_id
AND indexes.index_id = index_column03.index_id
AND index_column03.index_column_id = 3
LEFT JOIN MTGUtility.sys.columns column03 ON indexes.object_id = column03.object_id
AND index_column03.column_id = column03.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column04 ON indexes.object_id = index_column04.object_id
AND indexes.index_id = index_column04.index_id
AND index_column04.index_column_id = 4
LEFT JOIN MTGUtility.sys.columns column04 ON indexes.object_id = column04.object_id
AND index_column04.column_id = column04.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column05 ON indexes.object_id = index_column05.object_id
AND indexes.index_id = index_column05.index_id
AND index_column05.index_column_id = 5
LEFT JOIN MTGUtility.sys.columns column05 ON indexes.object_id = column05.object_id
AND index_column05.column_id = column05.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column06 ON indexes.object_id = index_column06.object_id
AND indexes.index_id = index_column06.index_id
AND index_column06.index_column_id = 6
LEFT JOIN MTGUtility.sys.columns column06 ON indexes.object_id = column06.object_id
AND index_column06.column_id = column06.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column07 ON indexes.object_id = index_column07.object_id
AND indexes.index_id = index_column07.index_id
AND index_column07.index_column_id = 7
LEFT JOIN MTGUtility.sys.columns column07 ON indexes.object_id = column07.object_id
AND index_column07.column_id = column07.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column08 ON indexes.object_id = index_column08.object_id
AND indexes.index_id = index_column08.index_id
AND index_column08.index_column_id = 8
LEFT JOIN MTGUtility.sys.columns column08 ON indexes.object_id = column08.object_id
AND index_column08.column_id = column08.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column09 ON indexes.object_id = index_column09.object_id
AND indexes.index_id = index_column09.index_id
AND index_column09.index_column_id = 9
LEFT JOIN MTGUtility.sys.columns column09 ON indexes.object_id = column09.object_id
AND index_column09.column_id = column09.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column10 ON indexes.object_id = index_column10.object_id
AND indexes.index_id = index_column10.index_id
AND index_column10.index_column_id = 10
LEFT JOIN MTGUtility.sys.columns column10 ON indexes.object_id = column10.object_id
AND index_column10.column_id = column10.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column11 ON indexes.object_id = index_column11.object_id
AND indexes.index_id = index_column11.index_id
AND index_column11.index_column_id = 11
LEFT JOIN MTGUtility.sys.columns column11 ON indexes.object_id = column11.object_id
AND index_column11.column_id = column11.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column12 ON indexes.object_id = index_column12.object_id
AND indexes.index_id = index_column12.index_id
AND index_column12.index_column_id = 12
LEFT JOIN MTGUtility.sys.columns column12 ON indexes.object_id = column12.object_id
AND index_column12.column_id = column12.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column13 ON indexes.object_id = index_column13.object_id
AND indexes.index_id = index_column13.index_id
AND index_column13.index_column_id = 13
LEFT JOIN MTGUtility.sys.columns column13 ON indexes.object_id = column13.object_id
AND index_column13.column_id = column13.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column14 ON indexes.object_id = index_column14.object_id
AND indexes.index_id = index_column14.index_id
AND index_column14.index_column_id = 14
LEFT JOIN MTGUtility.sys.columns column14 ON indexes.object_id = column14.object_id
AND index_column14.column_id = column14.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column15 ON indexes.object_id = index_column15.object_id
AND indexes.index_id = index_column15.index_id
AND index_column15.index_column_id = 15
LEFT JOIN MTGUtility.sys.columns column15 ON indexes.object_id = column15.object_id
AND index_column15.column_id = column15.column_id
LEFT JOIN MTGUtility.sys.index_columns index_column16 ON indexes.object_id = index_column16.object_id
AND indexes.index_id = index_column16.index_id
AND index_column16.index_column_id = 16
LEFT JOIN MTGUtility.sys.columns column16 ON indexes.object_id = column16.object_id
AND index_column16.column_id = column16.column_id
ORDER BY schemas.name ASC
,objects.name ASC
,column01.name ASC
,ISNULL(column02.name, ”) ASC
,ISNULL(column03.name, ”) ASC
,ISNULL(column04.name, ”) ASC
,ISNULL(column05.name, ”) ASC
,ISNULL(column06.name, ”) ASC
,ISNULL(column07.name, ”) ASC
,ISNULL(column08.name, ”) ASC
,ISNULL(column09.name, ”) ASC
,ISNULL(column10.name, ”) ASC
,ISNULL(column11.name, ”) ASC
,ISNULL(column12.name, ”) ASC
,ISNULL(column13.name, ”) ASC
,ISNULL(column14.name, ”) ASC
,ISNULL(column15.name, ”) ASC
,ISNULL(column16.name, ”) ASC
,indexes.name ASC;
–Input Table name
DECLARE @OBJECT_NAME VARCHAR(255) = ‘Project’;
–Create temp table to hold calculated values
DECLARE @temp TABLE
(
indexID BIGINT,
objectId BIGINT,
index_name NVARCHAR(MAX),
used_page_count BIGINT,
pages BIGINT
)
–Insert into temp table
INSERT INTO @temp
SELECT
P.index_id,
P.object_id,
I.name,
SUM (used_page_count),
SUM (
CASE
WHEN (p.index_id pages THEN (used_page_count – pages) ELSE 0 END) * 8, 15, 0) + ‘ KB’) INDEX_SIZE
FROM @temp T
Here it provides same result as we get in if we go in Reports Section of database and select Disk Usage By Partition….
select
OBJECT_NAME(i.[object_id]) AS Table_Name,
i.index_id,
i.name,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id(‘HumanResources.Shift’)
–and i.index_id = 1 — clustered index
order by
p.partition_number
go
Above query is provided by Microsoft as part of their DMV set, upon request.
USE AdventureWorks;
GO
EXEC sp_helpindex N’PK_Shift_ShiftID’;
GO
USE AdventureWorks;
GO
EXEC sp_helpindex N’AK_Shift_Name’;
GO
USE AdventureWorks;
GO
EXEC sp_helpindex N’AK_Shift_StartTime_EndTime;
GO
DECLARE
@objname nvarchar(776),
@id int,
@dbname sysname
SELECT
@objname = ‘TableToExamine’,
@dbname = ISNULL(parsename(@objname, 3),DB_NAME()),
@id = object_id(@objname)
SELECT
i.*,
CASE
WHEN ps.usedpages > ps.pages THEN (ps.usedpages – ps.pages)
ELSE 0
END * 8 indexsize
FROM sys.indexes i
INNER JOIN (
SELECT
OBJECT_ID,
index_id,
SUM (used_page_count) usedpages,
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)pages
FROM sys.dm_db_partition_stats
WHERE object_id = @id
GROUP BY object_id, index_id
) ps on i.index_id = ps.index_id
WHERE i.object_id = @id
Hank Freeman in Atlanta, GA used Adam Hutson version and likes it, I validated it against Sp_spaceused and the numbers match. So for me this is really good. Yes, I have modified it some, but the core is all Adam’s work.