sys.dm_db_index_physical_stats
It seems my comments in my SQLBits presentation about dropping sys.dm_db_index_physical_stats into a table valued function so it can be used within queries caught the eye of an important person. Now I'd not really given much thought to what I did, I was working on the index analysis and tried to join the view, which isn't a view after all, to a table variable. I got an error message, throught " oh dear!! " said to myself " well it's not a tvf so lets try make it one ".
When I was writing my analysing indexes post 2, I added this into my documentation which is how it made it into the SQLBits presentation. Until I was emailed I'd not given any thought to my being wrong in what I had done. If you're now totally lost and wondering what I'm talking about - SQL 2005 introduced the APPLY operator, http://technet.microsoft.com/en-us/library/ms175156.aspx this allows the ability to join across table valued functions.
This is a subset of the code which is in post 2 of the index analysis and shows how sys.dm_db_index_physical_stats gives an error.
CREATE TABLE dbo.Unused_Indexes([object_id] int NOT NULL,index_id int NOT NULL)
insert into dbo.Unused_Indexes
select object_id,index_id from adventureworks.sys.indexes
now run this code from the user database ( adventureworks in this case ) assumes a dba database called dbalocal
declare @table table(object_id int,ind_name sysname,index_id int)
insert into @table
select top 25 ui.[object_id],si.name,ui.index_id from dbalocal.dbo.Unused_Indexes ui
join sys.indexes si on ui.[object_id] = si.[object_id] and ui.index_id = si.index_id where
objectproperty(ui.[object_id],'IsUserTable') = 1
group by ui.[object_id],ui.index_id,si.name having count(*) =1
order by count(*) desc,ui.[object_id],ui.index_id asc
--
select object_name(ui.[object_id]) as TableName,ui.ind_name as IndexName,
max(fps.index_level)as IndexDepth,sum(fps.page_count) as TotalPages,max(fps.record_count)
as LeafRows, sum(fps.page_count)/128 as Mb
from @table ui
CROSS APPLY master.sys.dm_db_index_physical_stats (DB_ID(),ui.[object_id],ui.index_id,NULL, 'Detailed')
AS fps group by ui.[object_id],ui.ind_name
order by sum(fps.page_count) desc ;
this is the message that is returned
Msg 413, Level 16, State 1, Line 12
Correlated parameters or sub-queries are not supported by the inline function "master.sys.dm_db_index_physical_stats".
Msg 413, Level 16, State 1, Line 12
Correlated parameters or sub-queries are not supported by the inline function "master.sys.dm_db_index_physical_stats".
the entire post / part of my index analysis is at http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/07/22/working-with-indexes-part-2.aspx
Simon Sabin blogged about this before me but I wasn't aware of that at the time. The solution to this error is to place the inline function into a user defined tvf, in fact you could do this with your own inline functions if for some reason you couldn't produce a tvf. Please be careful using sys.dm_db_index_physical_stats in this way on very large tables or across large databases, you may find yourself waiting a long time for a result set.