sys.dm_db_index_physical_stats错误

Question

我正在执行select语句来检索索引的碎片百分比，并面临以下错误。

    SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent 
      FROM SYS.TABLES A 
      INNER JOIN SYS.INDEXES B ON A.OBJECT_ID=B.object_id
      CROSS APPLY SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),
        A.OBJECT_ID,B.INDEX_ID,0,DEFAULT) C
    WHERE B.NAME IS NOT NULL

而错误是

内嵌函数“”不支持Msg 413、级别16、状态1、第1行相关参数或子查询。内联函数“”不支持 Msg 413、级别16、状态1、行1 相关参数或子查询。

Answer 1

如果您处于80兼容模式，则需要：

DECLARE @dbid int = DB_ID();

SELECT t.name, i.name, ps.avg_fragmentation_in_percent 
  FROM sys.tables AS t         -- why A???
  INNER JOIN sys.indexes AS i  -- why B???
    ON t.[object_id] = i.[object_id]
  CROSS APPLY sys.dm_db_index_physical_stats
    (@dbid,NULL,NULL,0,DEFAULT) AS ps -- why C???
WHERE ps.index_id = i.index_id
AND ps.[object_id] = t.[object_id]  
AND i.name IS NOT NULL;

100岁时，你需要：

SELECT t.name, i.name, ps.avg_fragmentation_in_percent 
  FROM sys.tables AS t         -- why A???
  INNER JOIN sys.indexes AS i  -- why B???
    ON t.[object_id] = i.[object_id]
  CROSS APPLY sys.dm_db_index_physical_stats
    (DB_ID(),NULL,NULL,0,DEFAULT) AS ps -- why C???
WHERE ps.index_id = i.index_id
AND ps.[object_id] = t.[object_id]  
AND i.name IS NOT NULL;

在另一个答案中发布的函数看上去很好，很方便，得到了正确的答案，而且当您度量刚刚估计的成本时，它是一个明显的赢家，但是要小心--当您实际使用它们时，在多语句TVF中有很多隐藏的成本(在这种情况下，您不能使用内联TVF，原因与OP获得内置函数的错误消息的原因相同)。以下是成本比较：

(仅仅是为了显示单个查找的成本以及没有过滤器)：

另外，请小心办案 (不要大写的系统对象或列名)，使用敏感别名，并养成使用语句终止符的习惯。

Answer 2

在DB中创建这样的函数：

create function dm_db_index_physical_stats_tvf 
( 
@db_id int 
,@object_id int 
,@index_id int 
,@partition_number int 
,@mode int 
) 
returns @results TABLE ( 
[database_id] [smallint] NULL, 
[object_id] [int] NULL, 
[index_id] [int] NULL, 
[partition_number] [int] NULL, 
[index_type_desc] [nvarchar](60) NULL, 
[alloc_unit_type_desc] [nvarchar](60) NULL, 
[index_depth] [tinyint] NULL, 
[index_level] [tinyint] NULL, 
[avg_fragmentation_in_percent] [float] NULL, 
[fragment_count] [bigint] NULL, 
[avg_fragment_size_in_pages] [float] NULL, 
[page_count] [bigint] NULL, 
[avg_page_space_used_in_percent] [float] NULL, 
[record_count] [bigint] NULL, 
[ghost_record_count] [bigint] NULL, 
[version_ghost_record_count] [bigint] NULL, 
[min_record_size_in_bytes] [int] NULL, 
[max_record_size_in_bytes] [int] NULL, 
[avg_record_size_in_bytes] [float] NULL, 
[forwarded_record_count] [bigint] NULL, 
[compressed_page_count] [bigint] NULL
) 
begin 
insert into @results 
select * 
from sys.dm_db_index_physical_stats (@db_id, @object_id, @index_id ,@partition_number ,@mode ) 
return 
end

现在运行您的主要查询：

 SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent 
      FROM SYS.TABLES A 
      INNER JOIN SYS.INDEXES B ON A.OBJECT_ID=B.object_id
      CROSS APPLY dm_db_index_physical_stats_tvf(DB_ID(),
        A.OBJECT_ID,B.INDEX_ID,0,DEFAULT) C
    WHERE B.NAME IS NOT NULL  

我已经在我的SQL 2012环境中测试过它，它运行得很好。

参考文献：http://sqlblogcasts.com/blogs/simons/archive/2006/11/28/dmvs-and-CROSS-APPLY.aspx