Wednesday, 23 September 2009

Querying SQL Profiler data

If you use the SQL Server Query Profiler and log results to a table you can get some basic stats from the results with these queries:
-- Find most frequently used queries 
SELECT DISTINCT 
    cast(textdata as varchar(150)) as textdata, 
    avg(duration) as avg_duration, 
    count(duration) as Occurences 
FROM 
    [ProfilerTableNameHere] 
GROUP BY 
    Cast(textdata as VarChar(150)) 
ORDER BY 
    count(duration)desc 

-- Find most inefficient queries 
SELECT DISTINCT 
    cast(textdata as varchar(150)) as textdata, 
    avg(duration) as avg_duration, 
    count(duration) as Occurences 
FROM 
    [ProfilerTableNameHere] 
GROUP BY 
    Cast(textdata as VarChar(150)) 
ORDER BY 
    Avg(duration)desc