By giantLincecum


2019-05-15 15:51:33 8 Comments

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 comments

@giantLincecum 2019-05-16 14:57:53

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'

@G.Smith 2019-05-15 16:28:40

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.

@giantLincecum 2019-05-16 07:33:42

Seems to be working, how can I get the plas for an in line table function instead of a procedure

@G.Smith 2019-05-16 14:51:24

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.

@giantLincecum 2019-05-16 14:56:25

Hello, I was able to create query that works perfectly, see in answers below.

@G.Smith 2019-05-16 20:03:36

Nicely done! I was thinking along those lines but lacked the familiarity and didn't want to point you in the wrong direction.

Related Questions

Sponsored Content

1 Answered Questions

2 Answered Questions

[SOLVED] Determine the server that sent a query

  • 2015-03-24 07:59:00
  • retif
  • 75 View
  • 5 Score
  • 2 Answer
  • Tags:   sql-server

1 Answered Questions

1 Answered Questions

[SOLVED] Failed allocate pages: FAIL_PAGE_ALLOCATION 1

1 Answered Questions

[SOLVED] Oracle GoldenGate add trandata errors

2 Answered Questions

[SOLVED] Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent

3 Answered Questions

[SOLVED] Why is `SELECT @@IDENTITY` returning a decimal?

Sponsored Content