Analyze SQL Server plan Cache

Http://www.cio360.net/Page/1808/InfoID/291578/SourceId/11281/PubDate/2009-02-03/Default.aspx#pager

SQL Server 7.0Before a version is released, the Plan cache is usedSQL ServerThe entire memory can be configured separately in the cache area. Only the part of the stored procedure cached in the cache. For this reason, it is calledProgramCache. in SQL Server 7.0 and later versions, the planned cache is not a separate part of SQL Server Memory. Currently, SQL server uses a very dynamic integrated memory management and cache management mechanism.

The following script is used for SQL Server 2000 and earlier versions. It provides the content and usage frequency of the SQL Server plan cache.

The following is part of the result set of SQL Server 2000 script #1.

Figure 1

For SQL Server 2005 and its updated version, DMV has been introduced to obtain this information. Therefore, to obtain the content and usage statistics of the cache plan, you may need to use the following DMV script.

The following is part of the result set of SQL Server 2005 script #2.SQL Server 7.0The entire memory can be configured separately in the cache area. Only the part of the stored procedure cached in the cache. For this reason, it is called program cache. in SQL Server 7.0 and later versions, the planned cache is not a separate part of SQL Server Memory. Currently, SQL server uses a very dynamic integrated memory management and cache management mechanism.

And the previous version. It will provideSQL ServerPlan the cached content and usage frequency.

Part of the result set in script #1.

And its updated version, DMV has been introduced to obtain this information. Therefore, to obtain the content and usage statistics of the cache plan, you may need to use the following DMV script.

Figure 2

The above scripts and their descriptions are used in the following fields:

The following is part of the result set of SQL Server 2005 script #2.

Is part of the result set of SQL Server 2005 script #2.

With the above script, you can see the objects in SQL Server and their usage frequency. Remember that for SQL Server 2000, you need to use the system table syscacheobjects. for SQL Server 2005 and the updated version, you mainly use DMV SYS. dm_exec_cached_plans.

When testing or solving problems, you may need to clear the plan cache. You can use the following two commands to achieve your goal.

Script #3:Clear DBCC freeproccache for the entire SQL Server plan Cache Go Script #4:ClearsSQL ServerPlan Cache DBCC flushprocindb () Go /* You can get dbid through following command Select dbid from sysdatabases where name = <'dbname'> */

In addition to the above commands, the following operations will also refresh the entire plan cache and the new batch requires a new plan.

Detach any database

InSQL Server 2005Upgrade any database to a compatibility level of 90 or higher in or later versions.

Run the alter database... modify filegroup command for any database.

Run alter database... collate to modify the checking of any database.

Using any of the following commands to change a database will delete all the plans cached in a specific database.

Alter database... modify name

Alter database... Set Online

Alter database... Set offline

Alter database... set emergency

The following operation also deletes a cache plan for a specific database.

Delete A Database

Automatic database Shutdown

This is related to the absence of a way to delete a separate query plan from the cache of SQL 2005 and earlier versions, but it is now possible in SQL 2008.