Index and Table Size

  • Hello Everyone

    Happy hump day to all.

    I am working on gather some stats for each table in a database. I am gathering row counts, which I already have. But my question is where can I find the data for the size of the table and the size of each index per each table? I like the way that one can see the index space, data space and row count for each particular table by selecting "Storage" under the properties of the table. Where may I find this information, so that I can select that data into another table for stats reporting?

    Thank you in advance for your assistance, suggestions and comments.

    Andrew SQLDBA

  • My object naming needs to be cleaned up a little but I think you are looking for something like this:

    IF OBJECT_ID('tempdb..#tableDataPrep') IS NOT NULL

    DROP TABLE #tableDataPrep;

    IF OBJECT_ID('tempdb..#tableData') IS NOT NULL

    DROP TABLE #tableData;

    IF OBJECT_ID('tempdb..#IndexData') IS NOT NULL

    DROP TABLE #IndexData;

    CREATE TABLE #tableDataPrep

    (id int identity primary key,

    name varchar(100) not null,

    [rows] varchar(100) not null,

    reserved_kb varchar(100) not null,

    data_kb varchar(100) not null,

    index_size_kb varchar(100) not null,

    unused_kb varchar(100) not null);

    CREATE TABLE #tableData

    (id int identity primary key,

    name varchar(100) not null,

    [rows] int not null,

    reserved_kb bigint not null,

    data_kb bigint not null,

    index_size_kb int not null,

    unused_kb int not null);

    CREATE TABLE #IndexData

    (id int identity primary key,

    [schema] varchar(100) not null,

    table_name varchar(100) not null,

    IndexName varchar(120) not null,

    IndexSizeKB int not null)

    -- get the table info

    EXEC sp_msforeachtable'

    INSERT INTO #tableDataPrep (name,[rows],reserved_kb, data_kb,index_size_kb,unused_kb)

    EXEC sp_spaceused[?];'

    -- Removing the KB from the record for easier aggregations

    INSERT INTO #tableData

    SELECT name,

    [rows],

    REPLACE(reserved_kb,' KB',''),

    REPLACE(data_kb,' KB',''),

    REPLACE(index_size_kb,' KB',''),

    REPLACE(unused_kb,' KB','')

    FROM #tableDataPrep;

    WITH IndexSizes AS

    (SELECTi.name AS IndexName,

    SUM(page_count * 8) AS IndexSizeKB

    FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s

    JOIN sys.indexes AS i

    ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

    GROUP BY i.name),

    IndexTableInfo AS

    (SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],

    T.[name] AS [table_name], I.[name] AS [index_name], I.[type_desc]

    FROM sys.[tables] AS T

    JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

    JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

    JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]

    WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP')

    INSERT INTO #IndexData ([schema], table_name, IndexName, IndexSizeKB)

    SELECTidt.[Schema],

    idt.table_name,

    idt.index_name COLLATE SQL_Latin1_General_CP1_CI_AS +'('+idt.type_desc+')' AS IndexName,

    ids.IndexSizeKB

    FROM IndexSizes ids

    RIGHT JOIN IndexTableInfo idt ON idt.index_name=ids.IndexName

    GROUP BY [Schema], table_name,

    idt.index_name COLLATE SQL_Latin1_General_CP1_CI_AS +'('+idt.type_desc+')',

    IndexSizeKB

    ORDER BY [Schema], table_name,

    idt.index_name COLLATE SQL_Latin1_General_CP1_CI_AS +'('+idt.type_desc+')';

    --Output

    SELECT * FROM #tableData;

    SELECT * FROM #IndexData;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Try starting with the sys.dm_db_partition_stats DMV. That will give you page counts per index, you can expand from there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Here is what I use

    Use {your database here};

    Go

    With partitionStats (object_id, rows, reserved, data, used)

    As (

    Select ps.object_id

    ,sum(Case When ps.index_id < 2 Then row_count Else 0 End) As [rows]

    ,sum(ps.reserved_page_count) As reserved

    ,sum(Case When ps.index_id < 2

    Then ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count

    Else ps.lob_used_page_count + ps.row_overflow_used_page_count

    End ) As data

    ,sum(ps.used_page_count) As used

    From sys.dm_db_partition_stats ps

    Group By

    ps.object_id

    )

    , internalTables (parent_id, reserved, used)

    As (

    Select it.parent_id

    ,sum(ps.reserved_page_count) As reserved

    ,sum(ps.used_page_count) As used

    From sys.dm_db_partition_stats ps

    Inner Join sys.internal_tables it On it.object_id = ps.object_id

    Where it.internal_type In (202, 204)

    Group By

    it.parent_id

    )

    Select a3.name As [schemaname]

    ,a2.name As [tablename]

    ,a1.rows As row_count

    ,(a1.reserved + isnull(a4.reserved, 0)) / 128.0 As 'Reserved (MB)'

    ,a1.data / 128.0 As 'Data (MB)'

    ,Case When (a1.used + isnull(a4.used, 0)) > a1.data

    Then (a1.used + isnull(a4.used, 0)) - a1.data

    Else 0

    End / 128.0 As 'Index (MB)'

    ,Case When (a1.reserved + isnull(a4.reserved, 0)) > a1.used

    Then (a1.reserved + isnull(a4.reserved, 0)) - a1.used

    Else 0

    End / 128.0 As 'Unused (MB)'

    From partitionStats a1

    Left Join internalTables a4 On a4.parent_id = a1.object_id

    Inner Join sys.all_objects a2 On a1.object_id = a2.object_id

    Inner Join sys.schemas a3 On a2.schema_id = a3.schema_id

    Where a2.type <> N'S'

    And a2.type <> N'IT'

    Order By

    'Data (MB)' desc;

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thank You to everyone

    Jeffery, Special thanks to you. That is exactly what I was in need of.

    Hope that you all have a great day.

    Andrew SQLDBA

  • I prefer this

    selectOBJECT_NAME(o.OBJECT_ID)AS TableName

    , ISNULL(i.name, 'HEAP')AS IndexName

    , i.index_idAS IndexID

    , i.[type]AS IndexType

    , i.is_disabledAS IsDisabled

    , FILEGROUP_NAME(i.data_space_id)AS FGName

    , p.partition_numberAS PartitionNo

    , p.[rows]AS [RowCnt]

    , p.data_compression_descAS CompressionType

    , au.type_descAS AllocType

    , au.total_pages / 128AS TotalMBs

    , au.used_pages/ 128AS UsedMBs

    , au.data_pages/ 128AS DataMBs

    from sys.indexes i

    INNER JOIN sys.objects o ON i.object_id = o.object_id

    INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

    INNER JOIN sys.allocation_units au ON

    CASE

    WHEN au.[type] IN (1,3) THEN p.hobt_id

    WHEN au.[type] = 2 THEN p.partition_id

    END = au.container_id

    WHERE o.is_ms_shipped <> 1

    ORDER BY p.rows desc

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply