SQL Server如何定位自定义标量函数被那个SQL调用次数最多浅析-阿里云开发者社区

SQL Server如何定位自定义标量函数被那个SQL调用次数最多浅析

2018-08-03 957

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS SQL Server，独享型 2核4GB

简介： 原文:SQL Server如何定位自定义标量函数被那个SQL调用次数最多浅析前阵子遇到一个很是棘手的问题，监控系统DPA发现某个自定义标量函数被调用的次数非常高，高到一个离谱的程度。然后在Troubleshooting这个问题的时候，确实遇到了一些问题让我很是纠结，下文是解决问题过程的一点思索和尝试，如果你有更好的思路和解决方法，也请多多指教。

原文: SQL Server如何定位自定义标量函数被那个SQL调用次数最多浅析

前阵子遇到一个很是棘手的问题，监控系统DPA发现某个自定义标量函数被调用的次数非常高，高到一个离谱的程度。然后在Troubleshooting这个问题的时候，确实遇到了一些问题让我很是纠结，下文是解决问题过程的一点思索和尝试，如果你有更好的思路和解决方法，也请多多指教。

DPA可以监控到该函数每小时被调用的次数，如下截图所示：

那么第一个问题来了。 DPA如何监控获取这个函数每小时执行多少次呢？其实这个很简单， sys.dm_exec_query_stats视图里面有个字段execution_count 统计SQL的执行次数，但是它记录的是计划自上次编译以来所执行的次数。那么，我整点执行两次结果的差值（execution_count的差值）就是一小时内的执行次数。

execution_count Number of times that the plan has been executed since it was last compiled.(计划自上次编译以来所执行的次数。)

这个我们可以试验一下，如下所示，在AdventureWorks2014，我们创建一个自定义标量函数，然后我们

 
  USE AdventureWorks2014; 
  
  GO 
  
  CREATE FUNCTION Sales.FetchProductOrderNum 
  
  ( 
  
      @ProuctID  INT 
  
  ) RETURNS INT 
  
  BEGIN 
  
      DECLARE @SaleOrderNum INT; 
  
      SELECT @SaleOrderNum=COUNT(SalesOrderID)  FROM Sales.SalesOrderDetail  
  
      WHERE ProductID=@ProuctID 
  
      GROUP BY ProductID; 
  
      RETURN @SaleOrderNum; 
  
  END 
  
  GO 
  
  DBCC FREEPROCCACHE; --删除计划缓存中的所有元素 
  
  GO 
  
  DECLARE @ProductID INT; 
  
  SET @ProductID=870 
  
  SELECT DISTINCT ProductID, Sales.FetchProductOrderNum(ProductID) FROM Sales.SalesOrderDetail 
  
  WHERE ProductID=@ProductID

 
  SELECT qs.plan_handle 
  
    ,  OBJECT_NAME(qt.objectid) 
  
    , qs.execution_count AS [Execution Count] 
  
    , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second] 
  
    , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime] 
  
    , qs.total_worker_time AS [TotalWorkerTime] 
  
    , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime] 
  
    , qs.max_logical_reads 
  
    , qs.max_logical_writes 
  
    , qs.total_physical_reads 
  
    , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache] 
  
  FROM 
  
      sys.dm_exec_query_stats AS qs 
  
  CROSS APPLY  
  
      sys.dm_exec_sql_text(qs.[sql_handle]) AS qt 
  
  WHERE 
  
      qt.[dbid] = DB_ID() 
  
  AND qt.objectid = OBJECT_ID('Sales.FetchProductOrderNum') 
  
  OPTION (RECOMPILE);

DECLARE @ProductID INT;

SET @ProductID=897

SELECT DISTINCT ProductID, Sales.FetchProductOrderNum(ProductID) FROM Sales.SalesOrderDetail

WHERE ProductID=@ProductID

DECLARE @ProductID INT;

SET @ProductID=870

SELECT DISTINCT ProductID, Sales.FetchProductOrderNum(ProductID) FROM Sales.SalesOrderDetail

WHERE ProductID=@ProductID

如上所示，假设一个小时内只执行了上面两个SQL，那么我知道这个自定义函数被调用了 9378 - 4688 =4690次。所以基于这种规则，就可以找出一小时内自定义函数的调用次数，当然其它数据库对象也可以如此计算。

那么接下来的问题，一个数据库中有多脚本都调用了这个自定义标量函数，但是我怎么判别、区分那个SQL脚本调用了自定义函数最多（或者能查出TOP 10 SQL语句），这个问题纠结了好久，查找了好多资料，均无法实现这个功能。

为什么有这样的需求呢？因为，不能定位那个SQL调用的次数最多，我无法定位问题根源，我遇到的案例，发现有接近200个地方调用这个自定义函数，如果一个个去判断分析、这个是个累死人的体力活，而且有些SQL非常不容易确定

会调用多少次，（例如有些是在临时表上调用该函数，有些条件是传入的变量等等）。

 
  SELECT TOP 120 
  
          SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1, 
  
                    ( ( CASE qs.statement_end_offset 
  
                          WHEN -1 THEN DATALENGTH(qt.text) 
  
                          ELSE qs.statement_end_offset 
  
                        END - qs.statement_start_offset ) / 2 ) + 1) , 
  
          qt.text , 
  
          qs.execution_count , 
  
          qs.total_logical_reads , 
  
          qs.last_logical_reads , 
  
          qs.total_logical_writes , 
  
          qs.last_logical_writes , 
  
          qs.total_worker_time , 
  
          qs.last_worker_time , 
  
          qs.total_elapsed_time / 1000000 total_elapsed_time_in_S , 
  
          qs.last_elapsed_time / 1000000 last_elapsed_time_in_S , 
  
          qs.last_execution_time , 
  
          qp.query_plan 
  
  FROM    sys.dm_exec_query_stats qs 
  
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt 
  
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
  
  WHERE   qt.text LIKE '%FunctionName%' 
  
  ORDER BY qs.execution_count DESC;

如果你用这样的SQL语句，也是无法找到那个SQL调用自定义函数次数最多（自定义标量函数的特性，不清楚的可以看看我这篇博客SQL SERVER中用户定义标量函数（scalar user defined function）的性能问题），这里不做过细介绍。后面我发现如果那个SQL调用自定义标量函数过多，它的逻辑读是非常高的。当然这个不是非常精准，而且也需要在一定范围内进行甄别，不过在这个范围内做了一个范围刷选，已经非常难得了，在我这个案例当中，我非常顺速的对几个SQL进行判断、甄别后，就快速找到了调用次数非常多的SQL。这个已经是我能找到最好的方法了。如果其它更好的方法，欢迎指点一二！

 
  SELECT TOP 120 
  
          SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1, 
  
                    ( ( CASE qs.statement_end_offset 
  
                          WHEN -1 THEN DATALENGTH(qt.text) 
  
                          ELSE qs.statement_end_offset 
  
                        END - qs.statement_start_offset ) / 2 ) + 1) , 
  
          qt.text , 
  
          qs.execution_count , 
  
          qs.total_logical_reads , 
  
          qs.last_logical_reads , 
  
          qs.total_logical_writes , 
  
          qs.last_logical_writes , 
  
          qs.total_worker_time , 
  
          qs.last_worker_time , 
  
          qs.total_elapsed_time / 1000000 total_elapsed_time_in_S , 
  
          qs.last_elapsed_time / 1000000 last_elapsed_time_in_S , 
  
          qs.last_execution_time , 
  
          qp.query_plan 
  
  FROM    sys.dm_exec_query_stats qs 
  
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt 
  
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
  
  WHERE   qt.text LIKE '%FunctionName%' 
  
  --ORDER BY qs.execution_count DESC; 
  
  ORDER BY qs.total_logical_reads DESC  
  
  --ORDER BY qs.last_logical_reads  有时按last_logical_reads更精准

我们来验证一下我们上面测试的案例，如下所示，你会看到本身Sales.FetchProductOrderNum这个自定义标量函数的调用次数才是最多的，所以如果按execution_count来统计，这个是完全不行的，例如其中一个SQL执行一次(ProductID=870)，Sales.FetchProductOrderNum标量函数就要被调用好几千次，而按照了逻辑读（total_logical_reads或last_logical_reads）来分析，基本上就能定位到调用标量函数次数最多的SQL了。当然实际环境远远比这个测试案例复杂的多。

SQL Server如何定位自定义标量函数被那个SQL调用次数最多浅析

热门文章

最新文章

相关课程

相关电子书

相关实验场景