主页 / dba / 问题 / 141722
Accepted
user98113
Asked: 2016-06-21 06:04:57 +0800 CST

sys.dm_db_index_physical_stats 错误

  • 772

我正在执行一个 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

错误是

消息 413，级别 16，状态 1，行 1
内联函数“SYS.DM_DB_INDEX_PHYSICAL_STATS”不支持相关参数或子查询。
消息 413，级别 16，状态 1，行 1
内联函数“SYS.DM_DB_INDEX_PHYSICAL_STATS”不支持相关参数或子查询。

sql-server-2008-r2 dmv

2 个回答

  1. Best Answer

    如果您处于 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 收到内置函数的错误消息相同）。以下是费用对比：

    在此处输入图像描述

    （并且只是为了显示单个查找的成本以及没有过滤器）：

    在此处输入图像描述

    顺便说一句，请注意大小写（不要大写系统对象或列名），使用合理的别名，并养成使用语句终止符的习惯。

    • 2

  2. 在您的数据库中创建这样的函数： 

    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

    • 2

相关问题