1

Currently I use the below two queries to get some properties from the execution plan in stored procedures, the problem arises when I try to run it in a SQL Server 2008 version. Do you know if there is something similar to get these atributes for a SQL inline table-valued function in SQL Server 2008?

First query

SELECT
    ExecutionPlan                       = QueryPlans.query_plan ,
    CachedDateTime                      = ProcedureStats.cached_time ,
    LastExecutionDateTime               = ProcedureStats.last_execution_time ,
    ExecutionCount                      = ProcedureStats.execution_count ,
    AverageElapsedTime_Microseconds     = CAST ((CAST (ProcedureStats.total_elapsed_time AS DECIMAL(19,2)) / CAST (ProcedureStats.execution_count AS DECIMAL(19,2))) AS DECIMAL(19,2)) ,
    SetOptions                          = PlanAttributes.value
FROM
    sys.dm_exec_procedure_stats AS ProcedureStats
CROSS APPLY
    sys.dm_exec_query_plan (ProcedureStats.plan_handle) AS QueryPlans
CROSS APPLY
    sys.dm_exec_plan_attributes (ProcedureStats.plan_handle) AS PlanAttributes
WHERE
    database_id = DB_ID (N'db')
AND
    object_id = OBJECT_ID (N'procedure')
AND
    PlanAttributes.attribute = 'set_options';
GO

Second query

SELECT
    ExecutionPlan                       = QueryPlans.query_plan ,
    CachedDateTime                      = ProcedureStats.cached_time ,
    LastExecutionDateTime               = ProcedureStats.last_execution_time ,
    ExecutionCount                      = ProcedureStats.execution_count ,
    AverageElapsedTime_Microseconds     = CAST ((CAST (ProcedureStats.total_elapsed_time AS DECIMAL(19,2)) / CAST (ProcedureStats.execution_count AS DECIMAL(19,2))) AS DECIMAL(19,2)) ,
    ANSI_NULLS_Value                    = IIF (CAST (PlanAttributes.value AS INT) & 32 = 32 , N'True' , N'False') ,
    ANSI_PADDING_Value                  = IIF (CAST (PlanAttributes.value AS INT) & 1 = 1 , N'True' , N'False') ,
    ANSI_WARNINGS_Value                 = IIF (CAST (PlanAttributes.value AS INT) & 16 = 16 , N'True' , N'False') ,
    ARITHABORT_Value                    = IIF (CAST (PlanAttributes.value AS INT) & 4096 = 4096 , N'True' , N'False') ,
    CONCAT_NULL_YIELDS_NULL_Value       = IIF (CAST (PlanAttributes.value AS INT) & 8 = 8 , N'True' , N'False') ,
    NUMERIC_ROUNDABORT_Value            = IIF (CAST (PlanAttributes.value AS INT) & 8192 = 8192 , N'True' , N'False') ,
    QUOTED_IDENTIFIER_Value             = IIF (CAST (PlanAttributes.value AS INT) & 64 = 64 , N'True' , N'False')
FROM
    sys.dm_exec_procedure_stats AS ProcedureStats
CROSS APPLY
    sys.dm_exec_query_plan (ProcedureStats.plan_handle) AS QueryPlans
CROSS APPLY
    sys.dm_exec_plan_attributes (ProcedureStats.plan_handle) AS PlanAttributes
WHERE
    database_id = DB_ID (N'db')
AND
    object_id = OBJECT_ID (N'procedure')
AND
    PlanAttributes.attribute = 'set_options';
GO
2

I converted the IIF to CASE statements and cleaned it up a bit.

Combining them into one query, I came up with this:

DECLARE @dbname NVARCHAR(128) = N'dbname'
DECLARE @procname NVARCHAR(128) = N'procname'


SELECT
    ExecutionPlan                       = QueryPlans.query_plan ,
    CachedDateTime                      = ProcedureStats.cached_time ,
    LastExecutionDateTime               = ProcedureStats.last_execution_time ,
    ExecutionCount                      = ProcedureStats.execution_count ,
    AverageElapsedTime_Microseconds     = CAST ((CAST (ProcedureStats.total_elapsed_time AS DECIMAL(19,2)) / CAST (ProcedureStats.execution_count AS DECIMAL(19,2))) AS DECIMAL(19,2)) ,
    SetOptions                          = PlanAttributes.value,
    ANSI_NULLS_Value                    = CASE WHEN CAST (PlanAttributes.value AS INT) & 32 = 32 THEN N'True' ELSE N'False' END ,
    ANSI_PADDING_Value                  = CASE WHEN CAST (PlanAttributes.value AS INT) & 1 = 1 THEN N'True' ELSE N'False' END ,
    ANSI_WARNINGS_Value                 = CASE WHEN CAST (PlanAttributes.value AS INT) & 16 = 16 THEN N'True' ELSE N'False' END ,
    ARITHABORT_Value                    = CASE WHEN CAST (PlanAttributes.value AS INT) & 4096 = 4096 THEN N'True' ELSE N'False' END ,
    CONCAT_NULL_YIELDS_NULL_Value       = CASE WHEN CAST (PlanAttributes.value AS INT) & 8 = 8 THEN N'True' ELSE N'False' END ,
    NUMERIC_ROUNDABORT_Value            = CASE WHEN CAST (PlanAttributes.value AS INT) & 8192 = 8192 THEN N'True' ELSE N'False' END ,
    QUOTED_IDENTIFIER_Value             = CASE WHEN CAST (PlanAttributes.value AS INT) & 64 = 64 THEN N'True' ELSE N'False' END
FROM
    sys.dm_exec_procedure_stats AS ProcedureStats
CROSS APPLY
    sys.dm_exec_query_plan (ProcedureStats.plan_handle) AS QueryPlans
CROSS APPLY
    sys.dm_exec_plan_attributes (ProcedureStats.plan_handle) AS PlanAttributes
WHERE
    database_id = DB_ID (@dbname)
AND
    object_id = OBJECT_ID (@procname)
AND
    PlanAttributes.attribute = 'set_options';

IIF statements started in SQL Server 2012 (https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017)

After converting those, everything else worked on SQL2008R2.

  • Seems to be working, how can I get the plas for an in line table function instead of a procedure – giantLincecum May 16 at 7:33
  • For that I'm guessing you're talking about the lack of sys.dm_exec_function_stats in SQL2008... per MS page it looks like that was added in 2016 ( docs.microsoft.com/en-us/sql/relational-databases/… ). I'm not sure I can help you there. – G.Smith May 16 at 14:51
  • Hello, I was able to create query that works perfectly, see in answers below. – giantLincecum May 16 at 14:56
  • Nicely done! I was thinking along those lines but lacked the familiarity and didn't want to point you in the wrong direction. – G.Smith May 16 at 20:03
1

I was able to build another query that works really good.

SELECT t.objectid,p.plan_handle/*,qp.query_plan*/, p.usecounts, p.size_in_bytes, 
  /*set_options = MAX*/(a.value),
    ARITHABORT_Value                    = CASE WHEN CAST   (a.value AS INT) & 4096 = 4096 THEN N'True' ELSE N'False' END ,
      ANSI_NULLS_Value                  = CASE WHEN CAST (a.value AS INT) & 32 = 32 THEN N'True' ELSE N'False' END ,
    ANSI_PADDING_Value                  = CASE WHEN CAST   (a.value AS INT) & 1 = 1 THEN N'True' ELSE N'False' END ,
    ANSI_WARNINGS_Value                 = CASE WHEN CAST   (a.value AS INT) & 16 = 16 THEN N'True' ELSE N'False' END ,

    CONCAT_NULL_YIELDS_NULL_Value       = CASE WHEN CAST   (a.value AS INT) & 8 = 8 THEN N'True' ELSE N'False' END ,
    NUMERIC_ROUNDABORT_Value            = CASE WHEN CAST   (a.value AS INT) & 8192 = 8192 THEN N'True' ELSE N'False' END ,
    QUOTED_IDENTIFIER_Value             = CASE WHEN CAST   (a.value AS INT) & 64 = 64 THEN N'True' ELSE N'False' END
FROM sys.dm_exec_cached_plans AS p
--CROSS APPLY sys.dm_exec_query_plan (p.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
WHERE t.objectid = OBJECT_ID(N'functionname')
AND a.attribute = N'set_options'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.