none
能查非聚集索引的大小吗 RRS feed

  • 常规讨论

  • 你好,我想问 一下能查能查非聚集索引的大小吗?我知道有一个存储过程 sp_spaceused  ,这索引,应该是聚集索引的大小吧,,要查非聚集索引,要如何操作
    2012年5月13日 5:09

全部回复

  • Method1 ,

    CREATE PROCEDURE [dbo].[IndexSize]

          @TableName NVARCHAR(256),

          @IndexName VARCHAR(256)

    AS

    BEGIN

          DECLARE @index_id INT

          DECLARE @index_size BIGINT SET @index_size = 0

          SELECT @index_id = index_id FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) AND name = @IndexName

          SELECT

                @index_size = @index_size + (avg_record_size_in_bytes * record_count)

          FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED')

          SELECT @index_size as IndexSizeBytes

    END

    Method2 ,

    SELECT i.name                  AS IndexName,
        SUM
    (s.used_page_count) * 8   AS IndexSizeKB
    FROM sys.dm_db_partition_stats AS
    JOIN sys.indexes                AS i
    ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    WHERE s.[object_id] = object_id('dbo.TableName')
    GROUP BY i.name
    ORDER BY i.name

    Method3 ,
    SELECT
        i
    .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
    ORDER BY i.name


    2012年5月13日 5:42
  • thanks your reply,thank you very much.
    2012年5月13日 9:33