November 10, 2011 at 5:12 am
in sql server 2005 i can query the sys.dm_exec_requests table and cross join it to the sys.DM_Exec_query_plan() function to get a list of all query plans.
Is there any way i can get a list of the query plans in a sql server 2000 database? ( basically the database is installed on a sql 2005 box, but is in compatability of 80. I am not allowed to change the compatability level).
November 10, 2011 at 5:19 am
A database in compat mode 80 on a SQL 2005 server is a SQL 2005 database. You can get the query plans in exactly the way you showed, you just have to run that query from a DB that's in compat mode 90 (like master)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2011 at 5:28 am
my query is this:
use db
select *
from sys.dm_exec_requests r
cross apply sys.dm_exec_query_plan(r.plan_handle) as qp
where r.plan_handle is not null
if i run that for master, wont it just get query plans for master?
November 10, 2011 at 5:30 am
nevermind, i see what you mean now. thanks!
November 10, 2011 at 6:01 am
The exec DMVs are server-wide.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply