SQL SERVER – Size of Index Table for Each Index – Solution

Earlier I have posted small question on this blog and requested help from readers to participate here and provide solution. Please read the original Puzzle here.

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

The puzzle was to write a query that will return the size for each index that is on any particular table. We need a query that will return an additional column in the above listed query and it should contain the size of the index.

So far I have found two potential solutions. I have done some good amount of testing of both the script and they both are really giving accurate results 99.9% of the time. There were couple of instances where it provided incorrect results but for the most of the time, it just worked like charm. Again, it may be a bit of rounding up logic and I will not hold it against the script.

I want to thank Gaurav Sharama and Adam Hutson for their participation.

Solution from Gaurav Sharma

DECLARE @OBJECT_NAME VARCHAR(255) = 'HumanResources.Shift';
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 < 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
)
FROM sys.dm_db_partition_stats P INNER JOIN sys.indexes I ON I.index_id = P.index_id AND I.OBJECT_ID = P.OBJECT_ID
WHERE p.OBJECT_ID = OBJECT_ID(@OBJECT_NAME)
GROUP BY P.index_id, I.Name, P.OBJECT_ID;
SELECT index_name INDEX_NAME,
LTRIM (STR ((CASE WHEN used_page_count > pages THEN (used_page_count - pages) ELSE 0 END) * 8, 15, 0) + ' KB') INDEX_SIZE
FROM @temp T
GO

Solution from Adam Hutson

DECLARE
@objname NVARCHAR(776),
@id INT,
@dbname sysname
SELECT
@objname = 'HumanResources.Shift',
@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
GO

If you have different script or have suggestions on this script, please let us know.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

9 thoughts on “SQL SERVER – Size of Index Table for Each Index – Solution

  1. Hi Pinal,

    I found something wrong with both of the queries. When it comes to heap and clustered index it tends to report less. Please find my script which tends to agree with sp_spaceused. Your ccoments please

    select
    object_name(i.object_id) as TableName,
    i.name as IndexName,
    i.index_id as IndexID,
    8 * SUM(a.used_pages) as ‘Indexsize(KB)’
    FROM sys.indexes as i
    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
    JOIN sys.allocation_units as a ON a.container_id = p.partition_id
    Group by i.object_id,i.index_id,i.name
    Order By object_name(i.object_id),i.index_id

    Rama Mathanmohan

    Like

  2. Use undocumented procedure.

    USE AdventureWorks
    EXEC sp_MSindexspace ‘HumanResources.Shift’

    for code

    EXEC sp_helptext ‘sp_MSindexspace’

    Like

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

  4. Any one test if the following from Nimit Parikh works?

    —————————————————–
    USE AdventureWorks
    EXEC sp_MSindexspace ‘HumanResources.Shift’

    for code

    EXEC sp_helptext ‘sp_MSindexspace’
    ———————————————————–

    Like

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

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « 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