AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

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

2019-06-01 1280
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
简介： 前阵子遇到一个很是棘手的问题，监控系统DPA发现某个自定义标量函数被调用的次数非常高，高到一个离谱的程度。然后在Troubleshooting这个问题的时候，确实遇到了一些问题让我很是纠结，下文是解决问题过程的一点思索和尝试，如果你有更好的思路和解决方法，也请多多指教。

前阵子遇到一个很是棘手的问题，监控系统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，我们创建一个自定义标量函数，然后我们

 

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);

 

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非常不容易确定

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

 

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更精准

 

 

image

 

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

 

 

clip_image001[4]
文章标签：
云数据库 RDS SQL Server 版
SQL
Go
监控
关键词：
自定义SQL
SQL函数
SQL定位
云数据库RDS SQLServer版sql
SQL server
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
潇湘隐者
目录
相关文章
蓝染-惣右介
|
1月前
|
SQL 缓存 Java
【详细实用のMyBatis教程】获取参数值和结果的各种情况、自定义映射、动态SQL、多级缓存、逆向工程、分页插件
本文详细介绍了MyBatis的各种常见用法MyBatis多级缓存、逆向工程、分页插件 包括获取参数值和结果的各种情况、自定义映射resultMap、动态SQL
蓝染-惣右介
113 1
【详细实用のMyBatis教程】获取参数值和结果的各种情况、自定义映射、动态SQL、多级缓存、逆向工程、分页插件
1689161901914944
|
2月前
|
SQL 数据库 开发者
功能发布-自定义SQL查询
本期主要为大家介绍ClkLog九月上线的新功能-自定义SQL查询。
1689161901914944
38 6
Codelinghu
|
2月前
|
SQL 数据库
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
Codelinghu
341 0
游客j4mujezz7vm7y
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
游客j4mujezz7vm7y
38 0
pbeskoyq7ffm4
|
4月前
|
SQL 运维 监控
SQL Server 运维常用sql语句（二）
SQL Server 运维常用sql语句（二）
pbeskoyq7ffm4
40 3
pbeskoyq7ffm4
|
4月前
|
SQL XML 运维
SQL Server 运维常用sql语句（三）
SQL Server 运维常用sql语句（三）
pbeskoyq7ffm4
29 1
vohelon
|
4月前
|
SQL 关系型数据库 MySQL
SQL数据库和 SQLserver数据库
【8月更文挑战第19天】SQL数据库和 SQLserver数据库
vohelon
69 2
土木林森
|
4月前
|
Java Apache Android开发
Struts 2的秘密武器：揭秘社区中隐藏的学习宝藏，让你从新手到高手的不归路！
【8月更文挑战第31天】Struts 2学习资源丰富，除官方文档外，TutorialsPoint和W3Schools等网站提供详尽教程；《Apache Struts 2实战》等书籍含全面实例。Udemy、Pluralsight及YouTube上视频课程众多，Apache Software Foundation亦有网络研讨会。实践方面，GitHub上的开源项目及个人小项目都是好选择。寻求帮助可访问Apache官方论坛、Stack Overflow等平台。
土木林森
43 0
土木林森
|
4月前
|
Java 应用服务中间件 Maven
从零到英雄：一步步构建你的首个 JSF 应用程序，揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准，用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets，便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用，包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
土木林森
107 0
土木林森
|
4月前
|
前端开发 开发者
Vaadin Grid的秘密武器：打造超凡脱俗的数据展示体验！
【8月更文挑战第31天】赵萌是一位热爱UI设计的前端开发工程师。在公司内部项目中，她面临大量用户数据展示的挑战，并选择了功能强大的Vaadin Grid来解决。她在技术博客上分享了这一过程，介绍了Vaadin Grid的基本概念及其丰富的内置功能。通过自定义列和模板，赵萌展示了如何实现复杂的数据展示。
土木林森
50 0

热门文章

最新文章

  • 1
    多台SQLServer数据实时同步，syncnavigator使用
  • 2
    阿里云服务器怎么搭建sql server（即 MS SQL）？
  • 3
    SQLServer CDC数据通过Kafka connect实时同步至分析型数据库 AnalyticDB For PostgreSQL及OSS
  • 4
    使用批处理文件命令行方式快速启动和停止IIS、SqlServer
  • 5
    [推荐] (SqlServer)批量清理指定数据库中所有数据
  • 6
    数据库SQL Server与C#中数据类型的对应关系
  • 7
    SQL Server 查询性能优化 相关文章
  • 8
    SQLSERVER数据库分离作用及附加方法
  • 9
    第十二章——SQLServer统计信息（2）——非索引键上统计信息的影响
  • 10
    SQL Server: Get table primary key and Foreign Key using sql query
  • 1
    SQL脚本相除
    70
  • 2
    0基础学习SQL注入之万能账号密码（BUUctf例题-[极客大挑战 2019]EasySQL1）
    660
  • 3
    SQL脚本利用带关联子查询Update语句更新数据
    135
  • 4
    10分钟了解Flink SQL使用
    390
  • 5
    SQL脚本把多行SQL数据变成一条多列数据
    118
  • 6
    mybatis动态sql
    44
  • 7
    基于若依的ruoyi-nbcio流程管理系统修改代码生成的sql菜单id修改成递增id（谨慎修改，大并发分布式有弊端）
    112
  • 8
    SQL脚本字符串替换
    75
  • 9
    你写的每条SQL都是全表扫描吗
    183
  • 10
    数据库SQL语言实战（六）
    54

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。