2

I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error.

    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

And Error is

Msg 413, Level 16, State 1, Line 1
Correlated parameters or sub-queries are not supported by the inline function "SYS.DM_DB_INDEX_PHYSICAL_STATS".
Msg 413, Level 16, State 1, Line 1
Correlated parameters or sub-queries are not supported by the inline function "SYS.DM_DB_INDEX_PHYSICAL_STATS".

|improve this question
2

If you are in 80 compatibility mode, you need:

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;

For 100, you need:

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;

The function posted in another answer looks nice and convenient, gets the right answer, and it's a clear winner when you measure just estimated cost, but be careful - there are lots of hidden costs in multi-statement TVFs when you actually go ahead and use them (and in this case you can't use an inline TVF for the same reason as the OP is getting an error message with the built-in function). Here is the cost comparison:

enter image description here

(And just to show the cost of individual lookups as well as no filters):

enter image description here

As an aside, please be careful about case (don't upper-case system objects or column names), use sensible aliases, and get in the habit of using statement terminators.

|improve this answer
  • But I execute the same (query posted by me) on other server, and it's working fine. I face problem on only one server. What may be the reason? – user98113 Jun 20 '16 at 14:50
  • The server where it's working fine must be SQL Server 2012 or better. – Aaron Bertrand♦ Jun 20 '16 at 15:51
2

Create a function like this in your 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

and now run your main query:

 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

I have tested this on my SQL 2012 environment and it works fine.

Ref: http://sqlblogcasts.com/blogs/simons/archive/2006/11/28/dmvs-and-CROSS-APPLY.aspx

|improve this answer
  • 1
    @Martin you're right, I assumed that pushdown was an optimization made in 2012, when the function was first able to take correlated parameters, but just confirmed that it does push static parameters down just fine in 2008 R2 as well. But to be clear, there are several system objects that work as I described - they must perform all the work and don't filter until the end. – Aaron Bertrand♦ Jun 20 '16 at 16:29
  • @AaronBertrand thanks for clarifying, will delete obsolete comments. – Martin Smith Jun 20 '16 at 16:30
  • @Martin Sure. It's too bad only the multi-statement variation can take correlated parameters, because it kills performance compared to my WHERE clause variations. – Aaron Bertrand♦ Jun 20 '16 at 16:35
  • @AaronBertrand In the OP's specific use case here it looks like they are doing it for all indexes of all tables anyway so maybe just getting them all and joining on it would work just as well. – Martin Smith Jun 20 '16 at 16:37
  • 2
    @Martin Updated my answer to demonstrate potential impact. The function here does end up being a lot more expensive than just getting all the results and applying a where clause (not due to lack of pushdown but rather just due to the nature of multi-statement TVFs). – Aaron Bertrand♦ Jun 20 '16 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy