Two of the cool features in SQL Server 2005 are CROSS APPLY and DMVs (Dynamic Management Views). Now, far be it for me to get my hands dirty explaining developer stuff like CROSS APPLY :-) but I was having a discussion with Colin Leversuch-Roberts in the UK about the composability limitations of the sys.dm_db_index_physical_stats DMV. (Btw – you should check out Colin’s blog post series on Analysing Indexes – lots of useful stuff).


So CROSS APPLY lets you do join-like functionality with table-valued functions that take parameters – which you can’t do using JOIN. This works for most of the DMVs, but some of them are written to an older internal implementation that doesn’t support CROSS APPLY, and sys.dm_db_index_physical_stats is one of them. If you try it you’ll get an error like:



Msg 413, Level 16, State 1, Line 26


Correlated parameters or sub-queries are not supported by the inline function “master.sys.dm_db_index_physical_stats”.


Fortunately there’s a way around this – wrap the DMV in an artificial TVF of your own, and then CROSS APPLY to that. Here’s an example:


 



CREATE FUNCTION my_index_physical_stats (



@database_id INT,


@object_id INT,


@index_id INT,


@partition_number INT,


@mode INT)


RETURNS @result 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)


BEGIN



INSERT INTO @result SELECT * FROM



sys.dm_db_index_physical_stats (@database_id, @object_id, @index_id, @partition_number, @mode)


RETURN


END;


GO

Related Posts