SQL Server 2008 中的內聯函式計劃屬性

目前我使用以下兩個查詢從儲存過程中的執行計劃中獲取一些屬性，當我嘗試在 SQL Server 2008 版本中執行它時會出現問題。你知道是否有類似的東西可以在 SQL Server 2008 中為 SQL 內聯表值函式獲取這些屬性？

第一次查詢

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

第二次查詢

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

我將 IIF 轉換為 CASE 語句並對其進行了一些清理。

將它們組合成一個查詢，我想出了這個：

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 語句始於 SQL Server 2012 ( https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017 )

轉換這些之後，其他一切都在 SQL2008R2 上執行。

我能夠建構另一個執行良好的查詢。

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'

引用自：https://dba.stackexchange.com/questions/238239