SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQL LogScout, SQLDiag or PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.
MIT License
348
stars
99
forks
source link
Perf: Investigate and optimize performance of Post-import Best Practices rules query execution #213
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 distinct stmt.stmt_details.value ('@Database', 'varchar(max)') 'Database' , stmt.stmt_details.value ('@Schema', 'varchar(max)') 'Schema' ,
stmt.stmt_details.value ('@Table', 'varchar(max)') 'table' --into tblObjectsUsedByTopPlans
from
( select cast(FileContent as xml) sqlplan from tblTopSqlPlan) as p cross apply sqlplan.nodes('//sp:Object') as stmt (stmt_details)
Also found some rules to take a long time usp_IOAnalysis due to sub-optimal logic.
Also the update CounterData set CounterDateTime = REPLACE(CounterDateTime, char(0), '') which attempted to fix #150 was not efficient. Made it more focused.
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.