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

Question

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

谢谢！

Answer 1

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 [e-mail]

Answer 2

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 [e-mail]

谢谢啊，用了你这个语句也有重复的。我看了一下主要是SYS.dm_exec_procedure_stats里面有重复的，不知道这里面为啥有重复的。

Answer 3

你可以把你的查询结果展示出来嘛

---

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 [e-mail]

Answer 4

你可以把你的查询结果展示出来嘛

---

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 [e-mail]

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

Answer 5

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 [e-mail]

Answer 6

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 [e-mail]

您的意思是同一个链接调用的同一个存储过程会记录到一条记录中？

Answer 7

经过测试是这样的

---

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 [e-mail]

Answer 8

经过测试是这样的

---

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 [e-mail]

谢谢。还想继续请教一个问题，我想每周或每两周收集一下这些数据，是不是每次收集完应该清空缓存？

Answer 9

清空缓存，只需要重启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 [e-mail]

Answer 10

sys.dm_exec_procedure_stats 里面的一条记录，是一个缓存的存储过程执行计划，如果一个存储过程有多个缓存执行计划，那么就会有多条。即使是同一个连接，如果调用参数不一样，也可能是两个执行计划。

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

---

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

Answer 11

我的测试结果是不同连接的调用同一存储过程，即使是相同的执行计划，也是两条记录。

---

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 [e-mail]

Answer 12

清空缓存，只需要重启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 [e-mail]

清空缓存是不是用DBCC FREEPROCCACHE   就可以了？不重启SQLserver服务也可以吧。

Answer 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 [e-mail]

Answer 14

这是可以的

---

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 [e-mail]

我看清除缓存的命令有好几条，请问哪个用于清除动态管理视图的内容最好？谢谢！

Answer 15

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 [e-mail]

Answer 16

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 [e-mail]

发现DBCC FREEPROCCACHE只对sys.dm_exec_procedure_stats起作用，清除sys.dm_db_index_usage_stats内容的话，用哪个命令呢？

Answer 17

我没有找到清除缓存索引信息的命令，下面这个链接中按照不同的需求提供了不同的命令供你参考：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 [e-mail]

Answer 18

我没有找到清除缓存索引信息的命令，下面这个链接中按照不同的需求提供了不同的命令供你参考：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 [e-mail]

谢谢，这个链接有点没看明白，请问，我调整了索引或改写TSQL语句，我想看优化的效果，是在TSQL语句用DBCC FREEPROCCACHE还是DBCC DROPCLEANBUFFERS 还是两者都要用？

Answer 19

Use DBCC FREEPROCCACHE to force sql generates new exec plan.