SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table

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 (http://blog.sqlauthority.com)

About these ads

18 thoughts on “SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table

  1. 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

  2. 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;

  3. –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

  4. 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

    • Hi Ashish,

      There is small problem with your script. Would you please look into it. There is incorrect details when I ran your query for table Sales.SalesOrderDetail

      Kind Regards,
      Pinal

  5. 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

  6. 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

  7. Pingback: SQL SERVER – Size of Index Table for Each Index – Solution Journey to SQL Authority with Pinal Dave

  8. Pingback: SQL SERVER – Size of Index Table for Each Index – Solution 2 Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER – Size of Index Table for Each Index – Solution 3 – Powershell Journey to SQL Authority with Pinal Dave

  10. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31 Journey to SQLAuthority

  11. 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.

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s