none
我想查询平均用时最多的存储过程,结果下面的语句查询结果有重复的存储过程,请问怎么避免查询结果有重复的存储过程呢? RRS feed

  • 问题

  • SELECT TOP 50 DB_NAME(ISNULL(a.database_id,'')) 数据库名称,
        OBJECT_NAME(object_id, database_id) 存储过程名称 ,
        a.execution_count 执行次数,
        a.last_execution_time 上次执行的时间,
        CAST(a.last_worker_time*1.0/1000000 AS decimal(10,4)) [上次执行用时(秒)],
        CAST(a.total_elapsed_time*1.0/a.execution_count/1000000 AS decimal(10,4)) [执行平均用时(秒)],
        CAST(a.max_elapsed_time*1.0/1000000 AS decimal(10,4))  [执行最长用时(秒)],
        CASE WHEN a.last_worker_time*1.0/1000000>5 AND a.total_elapsed_time*1.0/a.execution_count/1000000>5 THEN 'Y' ELSE 'N' END AS 告警
      FROM    sys.dm_exec_procedure_stats AS a
        CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
        CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c
       ORDER BY a.total_elapsed_time/a.execution_count DESC

    谢谢!


    • 已编辑 Tim-2009 2019年4月25日 6:45 写错了
    2019年4月25日 2:31

答案

  • Hi Tim,

    我想这是因为不同连接调用同一存储过程时生成了不同的执行计划,存储在缓存中,因此这个DMV里面记录了多条一样的存储过程。

    经过测试,确实是因为不同连接调用同一个存储过程的原因。

    Hope this could help you .

    Best regards,

    Dedmon Dai



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    2019年4月26日 7:52
  • Use DBCC FREEPROCCACHE to force sql generates new exec plan.
    • 已标记为答案 Tim-2009 2019年5月6日 6:26
    2019年5月5日 14:00

全部回复

  • Hi Tim,

     

    你可以尝试使用下面的query

    SELECT TOP 100 db_name(d.database_id) as DBName,s.name as 存储名称,s.type_desc as 存储类型,d.cached_time as SP添加到缓存的时间,

    d.last_execution_time as 上次执行SP的时间,d.last_elapsed_time as [上次执行SP所用的时间(微妙)],d.total_elapsed_time as [完成此SP的执行所用的总时间(微妙)],

    d.total_elapsed_time/d.execution_count as [平均执行时间(微妙)],d.execution_count as 自上次编译以来所执行的次数

    FROM SYS.procedures S JOIN SYS.dm_exec_procedure_stats D

    ON S.object_id=D.object_id

    WHERE S.NAME='SP_Name'

    ORDER BY D.total_elapsed_time/D.execution_count DESC

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年4月25日 5:31
  • Hi Tim,

     

    你可以尝试使用下面的query

    SELECT TOP 100 db_name(d.database_id) as DBName,s.name as 存储名称,s.type_desc as 存储类型,d.cached_time as SP添加到缓存的时间,

    d.last_execution_time as 上次执行SP的时间,d.last_elapsed_time as [上次执行SP所用的时间(微妙)],d.total_elapsed_time as [完成此SP的执行所用的总时间(微妙)],

    d.total_elapsed_time/d.execution_count as [平均执行时间(微妙)],d.execution_count as 自上次编译以来所执行的次数

    FROM SYS.procedures S JOIN SYS.dm_exec_procedure_stats D

    ON S.object_id=D.object_id

    WHERE S.NAME='SP_Name'

    ORDER BY D.total_elapsed_time/D.execution_count DESC

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    谢谢啊,用了你这个语句也有重复的。我看了一下主要是SYS.dm_exec_procedure_stats里面有重复的,不知道这里面为啥有重复的。
    2019年4月25日 6:44
  • 你可以把你的查询结果展示出来嘛

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年4月25日 8:16
  • 你可以把你的查询结果展示出来嘛

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    下面保留部分名字的存储过程都是重名的

    2019年4月25日 12:37
  • Hi Tim,

    我想这是因为不同连接调用同一存储过程时生成了不同的执行计划,存储在缓存中,因此这个DMV里面记录了多条一样的存储过程。

    经过测试,确实是因为不同连接调用同一个存储过程的原因。

    Hope this could help you .

    Best regards,

    Dedmon Dai



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    2019年4月26日 7:52
  • Hi Tim,

    我想这是因为不同连接调用同一存储过程时生成了不同的执行计划,存储在缓存中,因此这个DMV里面记录了多条一样的存储过程。

    经过测试,确实是因为不同连接调用同一个存储过程的原因。

    Hope this could help you .

    Best regards,

    Dedmon Dai



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    您的意思是同一个链接调用的同一个存储过程会记录到一条记录中?
    2019年4月28日 2:33
  • 经过测试是这样的

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年4月29日 1:10
  • 经过测试是这样的

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    谢谢。还想继续请教一个问题,我想每周或每两周收集一下这些数据,是不是每次收集完应该清空缓存?
    2019年4月29日 1:47
  • 清空缓存,只需要重启sql server服务就行了,但是可能在sql server刚启动时会有一些性能问题,它需要重新加载内容到缓存内存中

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年4月29日 3:03
  • sys.dm_exec_procedure_stats 里面的一条记录,是一个缓存的存储过程执行计划,如果一个存储过程有多个缓存执行计划,那么就会有多条。即使是同一个连接,如果调用参数不一样,也可能是两个执行计划。

    你想要去重的话,group by然后sum一下就好。


    想不想时已是想,不如不想都不想。

    2019年4月29日 3:53
    版主
  • 我的测试结果是不同连接的调用同一存储过程,即使是相同的执行计划,也是两条记录。

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年4月29日 4:17
  • 清空缓存,只需要重启sql server服务就行了,但是可能在sql server刚启动时会有一些性能问题,它需要重新加载内容到缓存内存中

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    清空缓存是不是用DBCC FREEPROCCACHE   就可以了?不重启SQLserver服务也可以吧。
    2019年4月29日 6:13
  • 这是可以的


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年4月29日 6:41
  • 这是可以的


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    我看清除缓存的命令有好几条,请问哪个用于清除动态管理视图的内容最好?谢谢!
    2019年4月29日 7:35
  • Hi Tim,

     

    应该使用 DBCC FREEPROCCACHE,还可以通过参数指定删除特定得计划,请参考:https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017

     

    DBCC FREESESSIONCACHE 是清除分布式查询所使用得分布式查询连接缓存。

     

    DBCC FREESYSTEMCACHE 从所有缓存中释放所有未使用的缓存条目

     

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年4月29日 9:33
  • Hi Tim,

     

    应该使用 DBCC FREEPROCCACHE,还可以通过参数指定删除特定得计划,请参考:https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017

     

    DBCC FREESESSIONCACHE 是清除分布式查询所使用得分布式查询连接缓存。

     

    DBCC FREESYSTEMCACHE 从所有缓存中释放所有未使用的缓存条目

     

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    发现DBCC FREEPROCCACHE只对sys.dm_exec_procedure_stats起作用,清除sys.dm_db_index_usage_stats内容的话,用哪个命令呢?
    2019年4月30日 2:35
  • 我没有找到清除缓存索引信息的命令,下面这个链接中按照不同的需求提供了不同的命令供你参考:https://www.mssqltips.com/sqlservertip/4714/different-ways-to-flush-or-clear-sql-server-cache/希望可以帮到你

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年4月30日 8:13
  • 我没有找到清除缓存索引信息的命令,下面这个链接中按照不同的需求提供了不同的命令供你参考:https://www.mssqltips.com/sqlservertip/4714/different-ways-to-flush-or-clear-sql-server-cache/希望可以帮到你

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    谢谢,这个链接有点没看明白,请问,我调整了索引或改写TSQL语句,我想看优化的效果,是在TSQL语句用DBCC FREEPROCCACHE还是DBCC DROPCLEANBUFFERS 还是两者都要用?
    2019年5月5日 1:12
  • Use DBCC FREEPROCCACHE to force sql generates new exec plan.
    • 已标记为答案 Tim-2009 2019年5月6日 6:26
    2019年5月5日 14:00