Thursday, December 11, 2008 1:04 PM
The DMV function sys.dm_db_index_physical_stats() is performing like a dog. Currently it is accounting for 96% of the workload according to the execution plan.
I want to evaluate all of the indexes within a given database however this is proviing very difficult. Database has 200+ tables and is 30GB in size.
Is there a more efficient way of structuring this query or is this a lost cause?Code Snippet
WITHcte AS ( SELECT
@DatabaseIDAS database_id, OBJECT_NAME(dt.object_id) AS table_name,
dt.OBJECT_ID AS table_id, --sch.name AS scheme_name,
si.name AS index_name,
dt.index_id AS index_id,
sc.column_name AS index_columns,
sys.dm_db_index_physical_stats(@DatabaseID, NULL, NULL, NULL, @ScanMode) dt
INNER JOIN sys.index_columns ic
ON si.object_id = ic.object_id AND si.index_id = ic.index_id
INNER JOIN information_schema.tables st
ON OBJECT_NAME(si.object_id) = st.table_name
INNER JOIN information_schema.COLUMNS sc
ON ic.column_id = sc.ordinal_position AND sc.table_name = OBJECT_NAME(dt.object_id)WHERE
si.index_id = dt.index_id AND index_level = 0 AND dt.index_id <> 0 --index object is not a heap. AND avg_fragmentation_in_percent >= @FragThreshold AND si.index_id >0 AND si.is_hypothetical = 0 ) SELECT DISTINCT
index_columns= SUBSTRING( ( SELECT ', ' + index_columns FROM cte c1 WHERE c1.table_name = c2.table_name AND c1.index_name = c2.index_name AND c1.type_desc = c2.type_desc FOR XML PATH('') ),3,1000),
avg_page_space_used_in_percentINTO tmp_ResultSet FROM cte c2
Thursday, December 11, 2008 2:32 PMIt is a known issue that sys.dm_db_index_physical_stats can have performance implications based on db size and level of detail, it is stated in BOL. Best bet is to run this in non-peak hours (assuming that exists in your environment) when it can safely churn through without impacting users.
Thursday, December 11, 2008 3:50 PMModerator
In addition to Whitney's comment, you may consider using the LIMITED scanning mode, that is the fastest.
The following article is on query optimization:
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Sunday, January 29, 2012 6:01 PM
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Friday, October 05, 2012 11:38 PM
Friday, December 12, 2008 2:44 PMHi,
Thanks for your comments guys. So are we saying that there is nothing more I can do to optimize this query?
John Sansom (MCITP) | Please mark answers that solve your problem