Execution Plan of Frequent Queries

Bill Galashan, DBA of bet365 sent over the following query that lists the execution plan of the 10 most frequently executed queries.

He writes:

We got into this due to different query plans coming from a VB or a web app than what was seen when running the same query from Management Studio. Eventually tracked this down to a difference in the set options predominatley whether Arithabort was on or off.

Read more to see his query.

Click here to see the execution plan of currently running queries.

SELECT TOP 10 creation_time, last_execution_time,  last_worker_time / 1000 as [Last Worker Time (ms)] ,min_worker_time / 1000 as [Min Worker Time (ms)],
      max_worker_time / 1000 as [Max Worker Time (ms)],
    total_worker_time/execution_count/1000 AS [Avg Worker Time (ms)],
    execution_count,
    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,plan_generation_num ,query_plan, Plan_handle
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
-- where st.text like '%proc name to be searched for%'
ORDER BY execution_count DESC;

--- Determine options used at run time

select  * from sys.syscacheobjects with (nolock) where sql like '%proc to be searched for%' and objtype='proc'

select dbo.fn_setopts(249)

/*
 This contains a bitmap containing the SET options relevant to each cached plan for a proc.
The following function can be used to decipher this bitmask:
*/

create function dbo.fn_setopts(@setopts int)
returns nvarchar(4000)

as
begin

declare @s nvarchar(4000)
select @s='Options: '
if @setopts & 1    > 0 select @s = @s + N'ANSI_PADDING, '
if @setopts & 2    > 0 select @s = @s + N'max degree of parallelism, '
if @setopts & 4    > 0 select @s = @s + N'FORCEPLAN, '
if @setopts & 8    > 0 select @s = @s + N'CONCAT_NULL_YIELDS_NULL, '
if @setopts & 16   > 0 select @s = @s + N'ANSI_WARNINGS, '
if @setopts & 32   > 0 select @s = @s + N'ANSI_NULLS, '
if @setopts & 64   > 0 select @s = @s + N'QUOTED_IDENTIFIER, '
if @setopts & 128  > 0 select @s = @s + N'ANSI_NULL_DFLT_ON, '
if @setopts & 256  > 0 select @s = @s + N'ANSI_NULL_DFLT_OFF, '
if @setopts & 512  > 0 select @s = @s + N'NO_BROWSETABLE, '
if @setopts & 1024 > 0 select @s = @s + N'TriggerOneRow, '
if @setopts & 2048 > 0 select @s = @s + N'ResyncQuery, '
if @setopts & 4096 > 0 select @s = @s + N'ARITHABORT, '
if @setopts & 8192 > 0 select @s = @s + N'NUMERIC_ROUNDABORT, ' 

return @s

end