查看: 202|回复: 0

[SQLServer] SQL Server如何定位自定义标量函数被那个SQL调用次数最多浅析 2017-09-08 12:04 by 潇湘隐者, ... 阅读, ... 评论,

发表于 6 天前

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

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

clip_image001

那么第一个问题来了。 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,我们创建一个自定义标量函数,然后我们

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

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

clip_image002

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

clip_image003

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

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

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

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

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

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

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

image

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

clip_image001[4]


回复

使用道具 举报