SQL Statements um einen SQLServer-Trace aus einer Tabelle zu analysieren

Generellen Analysen:

SELECT
   Duration as DurationInMicroSeconds,  
   DATALENGTH(TextData) as len,
   CPU,
   Reads,
   Writes,
   TextData  
FROM
   trc_table01
WHERE
   TextData IS NOT NULL
order by duration desc;


SELECT
   AVG(Duration) as AVGDuration,
   AVG(READS) AS AVGReads,
   AVG(CPU) AS AVGCPU,
   COUNT(*) AS TotalStatements
   
FROM
   trc_table01   
WHERE
   TextData IS NOT NULL;


SELECT COUNT(*) AS NO_SQLSTATEMENTS_CPU_MORE_THAN_ZERO, AVG(CPU) AS AVG_CPU
FROM
   trc_table01
WHERE CPU>0
AND TextData IS NOT NULL;

Nach einem speziellen Teil suchen

SELECT
COUNT(CAST(TextData AS nvarchar(MAX))),
CAST(TextData AS nvarchar(MAX)) AS x
FROM
TRACETABLE
WHERE
TextData LIKE '%SUCHBEGRIFF%'
GROUP BY
CAST(TextData AS nvarchar(MAX))
ORDER BY
1 DESC