SimonS Blog on SQL Server Stuff

dmvs and CROSS APPLY

In SQL 2005 there is a new feature by which you can extend a result set with the results of another resultset. You might think thats nothing new because thats exactly what a join is, and with the combination of derived tables you have full control, and you can use table valued functions.

However there is one gotcha. If your table valued function takes a parameter(s), that parameter(s) has to be fixed for the query. It can't take the value from a column in another resultset as you do with a JOIN clause.

select *
from myCustomers
join dbo.getCustomersForOrders (myCustomers.customerid)

This is not possible with a JOIN.

Whats new in SQL 2005 is that a new clause CROSS APPLY has been introduced that does exactly what you would expect from the code above.

This will join the customers with the rowset returned from the function for each different customerid.

So what does this mean for dmvs. Well many dmvs are table valued functions and so can be used in this way i.e to get query plans for a plan you can use sys.dm_exec_query_plan(null) however if you try to get index stats for a table an try and use sys.dm_db_index_physical_stats.You will get an error.

This is because some of the dmvs are written using a new mechanism that allows them to be used like table valued functions, i.e. in cross apply. However others are built using an older mechanism that doesn't allow this as shown above.

So whats the solution, well as table valued functions are allowed why not wrap the dmv in a table valued function. The code is cumbersome because you have to define the table the function returns but it works

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
)
begin
insert into @results
select *
from sys.dm_db_index_physical_stats (@db_id, @object_id, @index_id ,@partition_number ,@mode )
return
end