SQL Server 2000：如何判断存储过程缓存了多少计划？

Sometimes when diagnosing issues with our SQL Server 2000 database it might be helpful to know that a stored procedure is using a bad plan or is having trouble coming up with a good plan at the time I'm running into problems. I'm wondering if there is a query or command I can run to tell me how many execution plans are cached currently for a particular stored procedure.

有时,在诊断SQL Server 2000数据库的问题时,了解存储过程使用错误的计划或在遇到问题时遇到问题时遇到问题可能会有所帮助。我想知道是否有一个查询或命令我可以运行告诉我当前为特定存储过程缓存了多少执行计划。

1 个解决方案

#1

You can query the cache in a number of different ways, either looking at its contents, or looking at some related statistics.

您可以通过多种不同的方式查询缓存,查看其内容或查看一些相关的统计信息。

A couple of commands to help you along your way:

一些命令可以帮助您:

SELECT * FROM syscacheobjects -- shows the contents of the procedure 
    -- cache for all databases
DBCC PROCCACHE -- shows some general cache statistics
DBCC CACHESTATS -- shows  the usage statistics for the cache, things like hit ratio

If you need to clear the cache for just one database, you can use:

如果只需要清除一个数据库的缓存,可以使用:

DBCC FLUSHPROCINDB (@dbid) -- that's an int, not the name of it. 
           -- The int you'd get from sysdatabases or the dbid() function

Edit: above the line is for 2000, which is what the question asked. However, for anyone visiting who's using SQL Server 2005, it's a slightly different arrangement to the above:

编辑:上面的行是2000年,这是问题。但是,对于访问使用SQL Server 2005的人来说,它与上述的安排略有不同:

select * from sys.dm_exec_cached_plans -- shows the basic cache stuff

A useful query for showing plans in 2005:

2005年展示计划的有用查询:

SELECT  cacheobjtype, objtype, usecounts, refcounts, text
from sys.dm_exec_cached_plans p
join  sys.dm_exec_query_stats s on p.plan_handle = s.plan_handle
cross apply sys.dm_exec_sql_text(s.sql_handle)

#1