• Fraggle-805517 (10/19/2012)


    ...

    Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.

    ...

    SQL Server's query plan cache assigns an 8 byte hash value that identifies queries with similar SQL text but disregarding parameter values. It sounds like perhaps the goal of your project is to aggregate performance metrics like CPU or disk reads by common query type. If so, then you can achieve this by simply querying sys.dm_exec_query_stats instead of using SQL Profiler traces.

    http://msdn.microsoft.com/en-us/library/ms189741.aspx

    For example:

    SELECT TOP 5 query_stats.query_hash,

    SUM(execution_count) Sum_Execution_Count,

    SUM(query_stats.total_worker_time)

    / SUM(query_stats.execution_count) AS Avg_CPU_Time,

    max(total_logical_reads) Max_Reads,

    MIN(query_stats.statement_text) AS Statement_Text

    FROM

    (SELECT QS.*,

    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

    ((CASE statement_end_offset

    WHEN -1 THEN DATALENGTH(ST.text)

    ELSE QS.statement_end_offset END

    - QS.statement_start_offset)/2) + 1) AS statement_text

    FROM sys.dm_exec_query_stats AS QS

    CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats

    GROUP BY query_stats.query_hash

    ORDER BY Avg_CPU_Time desc;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho