Comparing SQL queries to determine work required


I am trying to compare different queries queries in SQL server to see how much work it takes to process. So in other words the effort to get the results. So is there some sort of metric I can look at. I am guessing something I can compare in the execution plans.


Who is Participating?
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You can turn on SET STATISTICS before running each query and then compare the results.
ste5anConnect With a Mentor Senior DeveloperCommented:
1) Use the actual execution plan. Thus execute both queries in the same window. You can also add a SET STATISTICS IO ON to get thre scans and reads in the message pane, And an SET STATISTICS TIME ON for the exact computation time.
2) Use sys.dm_exec_query_stats. Here you need to tag your queries. E.g. start them with --COMPAREME. Then you can compare the results:

SELECT RANK() OVER ( ORDER BY total_logical_reads + total_logical_writes DESC, sql_handle, statement_start_offset ) AS row_no ,
        creation_time ,
        last_execution_time ,
        total_worker_time / 1000 AS total_worker_time ,
        total_logical_reads ,
        total_logical_writes ,
        execution_count ,
        total_logical_reads + total_logical_writes AS total_logical ,
        CASE WHEN sql_handle IS NULL THEN ' '
             ELSE ( SUBSTRING(st.text, ( qs.statement_start_offset + 2 ) / 2,
                              ( CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
                                     ELSE qs.statement_end_offset
                                END - qs.statement_start_offset ) / 2) )
        END AS query_text ,
        st.text AS sql_text ,
        DB_NAME(st.dbid) AS database_name ,
        st.objectid AS object_id ,
        qp.query_plan ,
        cp.cacheobjtype ,
        cp.objtype ,
FROM    sys.dm_exec_query_stats qs
        INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = qs.plan_handle
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical DESC;

Open in new window

This approach takes a look at the entire history of queries. So you may need to clear the plan cache before testing. This can have an effect in production, cause recompiles are enforced.
Vitor MontalvãoMSSQL Senior EngineerCommented:
whorsfall, it's your question answered?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.