sql - 如何查找表值函数中索引的大小

Question

In article about sys.indexes有句话说这个观点

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

我有兴趣找到这样一个索引的大小。

所以我创建了带有索引的函数:

create function fIndexSize()
returns @res table
(
  object_id int          not null
, name      varchar(128) not null
, primary key (object_id)
)
as
begin
    insert into @res
    select object_id, name
    from sys.objects
    where object_id > 255
return
end

在这里我们可以看到新索引的名称:

sys.indexes中还有一条记录:

通常我使用此查询获取索引的大小:

select
      o.schema_id
    , o.object_id
    , o.name
    , o.type_desc
    , sum (a.total_pages) * 8.00 / 1024 / 1024 as TotalSpaceGB
from sys.objects o
inner join sys.indexes i on o.object_id = i.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 a on p.partition_id = a.container_id
where (o.name = 'fIndexSize' or i.name like 'PK__fIndexSi%')
group by o.schema_id, o.object_id, o.name, o.type_desc

但这一次没有返回任何内容。

任何人都可以给我建议如何找到这样的索引的大小吗？

Answer 1

是的，您可以找到该索引的大小，但您应该考虑它仅在批处理中存在一段时间，并且您应该在 tempdb 中查找它(因为它是表变量):

    create function fIndexSize()
    returns @res table
    (
      object_id_xxxx int          not null
    , name      varchar(128) not null
    , primary key (object_id_xxxx)
    )
    as
    begin
        insert into @res
        select object_id, name
        from sys.objects
        where object_id > 255
    return
    end;

走

    select i.name,
           c.name,
           8 * SUM(au.used_pages) as size_kb
    from tempdb.sys.indexes i
         join tempdb.sys.columns c
            on i.object_id = c.object_id
         join tempdb.sys.partitions as p 
            on p.object_id = i.object_id and p.index_id = i.index_id
         join tempdb.sys.allocation_units as au 
            on au.container_id = p.partition_id        
    where c.name = 'object_id_xxxx' 
    group by  i.name,
              c.name

我在这里留下列名只是为了表明找到的索引就是我们要查找的内容，并且我选择了带有 xxxx 的列名以便更好区分