How to View the Cached Query Plans (SQL 2000) - All SQL Server Versions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Scripts, Code Samples and SSMS Custom Reports » All SQL Server Versions » How to View the Cached Query Plans (SQL 2000)

How to View the Cached Query Plans (SQL 2000)

Options

Previous Topic · Next Topic

Scott Whigham

Posted: Sunday, October 01, 2006 8:27:46 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

I was rummaging through my scripts tonight and found this oldie:

Code:

SELECT cast(C.sql as Char(100)) as Query, cacheobjtype, usecounts
FROM Master.dbo.syscacheobjects C
JOIN Master.dbo.sysdatabases D
ON C.dbid = C.dbid
WHERE D.Name = DB_Name()
AND ObjType = 'Adhoc'
ORDER BY Query

This is really more for SQL Server 2000 but might be helpful for anyone, I suppose. It shows you the cached plans and how many times they've been used.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.