Stored Proc Benchmarking

  • I'm trying to use DMVs to compare stored procedures against each other to determine which is more efficient. Here's what I've got so far:

    SELECT

    name, MAX(DMExQryStats.Execution_Count) Exec_Count,

    AVG(total_elapsed_time) Avg_Elapsed, AVG(total_worker_time) Avg_Worker, AVG(total_physical_reads) Avg_PReads, AVG(total_logical_reads) Avg_LReads,

    MAX(max_elapsed_time) Max_Elapsed, MAX(max_worker_time)Max_Worker, MAX(max_physical_reads) Max_PReads, MAX(max_logical_reads) Max_LReads

    FROM

    sys.dm_exec_query_stats AS DMExQryStats

    CROSS APPLY fn_get_sql(DMExQryStats.sql_handle)

    LEFT JOIN sys.procedures ON objectid=sys.procedures.object_id

    WHERE name IN ('Proc1','Proc2')

    GROUP BY name

  • And what is your question (assuming there is one)?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • How could I do this better? Are there any useful criteria I've left out? This seems to revolve around disk usage, can you see a way to add cpu benchmarks?

  • I wouldn't mess with all the aggregations. sys.dm_exec_sql_stats is an aggregate of the performance already. You won't get differences. Also, why are you using that function instead of sys.dm_exec_sql_text and the statement offsets? That's a more direct method for getting the query, at least I've always found it so.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I think its an aggregate per plan not per procedure. In any case, I get duplicate entries when I don't use aggregates.

    I'd Like to use dm_exec_sql_text but I don't have an object id to link from. (exec query stats only has the sql_handle)

  • sql_handle is how you connect to sys.dm_exec_query_text.

    If you're concerned more about procedures, in 2008 you can use sys.dm_exec_procedure_stats. Not available in 2005, but it is 2008.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • That makes sense now, I switched to dm_exec_sql_text and I guess our DB is still on 2005. I'll look into sys.dm_exec_procedure_stats as soon as we update.

    Thanks for your help!

  • The goal here is to identify procs that may need a rewrite. I'm trying to think of a way to create benchmarks for procs using a comparison to other procs in the database. (Maybe using standard deviations?) Here's the script as it stands:

    SELECT

    name, MAX(DMExQryStats.Execution_Count) Exec_Count,

    AVG(total_elapsed_time) Avg_Elapsed, AVG(total_worker_time) Avg_Worker, AVG(total_elapsed_time)-AVG(total_worker_time) Avg_Wait, AVG(total_physical_reads) Avg_PReads, AVG(total_logical_reads) Avg_LReads,

    MAX(max_elapsed_time) Max_Elapsed, MAX(max_worker_time) Max_Worker, MAX(max_elapsed_time)-MAX(max_worker_time) Max_Wait, MAX(max_physical_reads) Max_PReads, MAX(max_logical_reads) Max_LReads

    FROM

    sys.dm_exec_query_stats AS DMExQryStats

    CROSS APPLY sys.dm_exec_sql_text (DMExQryStats.sql_handle)

    LEFT JOIN sys.procedures ON objectid=sys.procedures.object_id

    WHERE

    name NOT LIKE 'sp_MS%' --Ignore Replication Procs

    GROUP BY name

    ORDER BY AVG(total_elapsed_time)-AVG(total_worker_time) DESC

  • Yeah, it's a valid approach. Just as long as you know that those procs are cache dependent which means you could lose queries as they age out of the cache, you're fine.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply