You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Discovered that this query can be very long if tblTopSqlPlan is populated with a large query plans. The query is processing XML in memory. The tblTopSqlPlan contains only 5 rows based on PSSDIAG collection but again if the plans are large, it is expensive to process the XML. In my test with a data set, it took slightly over 3 minutes to run this query.
set QUOTED_IDENTIFIER on;
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan'AS sp)
select distinctstmt.stmt_details.value ('@Database', 'varchar(max)') 'Database' , stmt.stmt_details.value ('@Schema', 'varchar(max)') 'Schema' ,
stmt.stmt_details.value ('@Table', 'varchar(max)') 'table'--into tblObjectsUsedByTopPlansfrom
( select cast(FileContent as xml) sqlplan from tblTopSqlPlan) as p cross apply sqlplan.nodes('//sp:Object') as stmt (stmt_details)
No description provided.
The text was updated successfully, but these errors were encountered: