Wednesday, 18 November 2009

Measuring query performance

To measure query performance you can use the SQL Profiler to log to a table then run the following queries.

Most frequent queries

SELECT
    DISTINCT
        CAST(textdata AS varchar(5000)) AS textdata,
        COUNT(duration) AS Occurences,
        AVG(duration) AS AvgDuration,
        SUM(duration) AS TotalDuration
FROM    [trace table name here]
WHERE   LoginName = 'login name here'
        AND textdata NOT LIKE '--%'
        AND CAST(textdata AS VARCHAR(5000)) <> 'exec sp_reset_connection'
GROUP BY CAST(textdata AS VARCHAR(5000))
ORDER BY COUNT(duration) DESC

Most inefficient queries

SELECT DISTINCT
        CAST(textdata AS VARCHAR(5000)) AS textdata,
        COUNT(duration) AS Occurences,
        AVG(duration) AS AvgDuration,
        SUM(duration) AS TotalDuration
FROM    [trace table name here]
WHERE   LoginName = 'login name here'
        AND textdata NOT LIKE '--%'
        AND CAST(textdata AS VARCHAR(5000)) <> 'exec sp_reset_connection'
GROUP BY CAST(textdata AS VARCHAR(5000))
ORDER BY AVG(duration) DESC