June 13, 2011 at 9:34 am
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
June 13, 2011 at 10:36 am
And what is your question (assuming there is one)?
June 13, 2011 at 10:55 am
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?
June 13, 2011 at 12:50 pm
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
June 13, 2011 at 1:06 pm
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)
June 13, 2011 at 1:46 pm
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
June 13, 2011 at 2:45 pm
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!
June 13, 2011 at 2:50 pm
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
June 13, 2011 at 4:53 pm
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