能查非聚集索引的大小吗

Question

你好，我想问 一下能查能查非聚集索引的大小吗？我知道有一个存储过程 sp_spaceused  ，这索引，应该是聚集索引的大小吧，，要查非聚集索引，要如何操作

Answer 1

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 s 
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

Answer 2

thanks your reply,thank you very much.