|
Home > Archive > Microsoft SQL Server forum > May 2005 > Getting Plan of previously executed Queries
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Getting Plan of previously executed Queries
|
|
| Thiru 2005-05-27, 11:23 am |
| Hi,
I am newbie to SQL Server. I am using SQL Server 2000.
I know SQL server compiles the SQL stmt or SP and stores the plan for
later use(I know its not always done. But should be done to reduce
execution time) in memory somewere. I want to capute all the execution
plan which are kept in memory. In oracle v$sql_plan can be used to
access the plan in oracle. I want to know how to do that in SQL Server.
I can use profiler, but it contributes some CPU utilization.
Thanks,
Thiru.
WantedToBeDBA
WantedToBeDBA {at} gmail {dot} com
| |
| Simon Hayes 2005-05-27, 1:23 pm |
|
"Thiru" <WantedToBeDBA@gmail.com> wrote in message
news:1117211283.969054.121710@f14g2000cwb.googlegroups.com...
> Hi,
> I am newbie to SQL Server. I am using SQL Server 2000.
>
> I know SQL server compiles the SQL stmt or SP and stores the plan for
> later use(I know its not always done. But should be done to reduce
> execution time) in memory somewere. I want to capute all the execution
> plan which are kept in memory. In oracle v$sql_plan can be used to
> access the plan in oracle. I want to know how to do that in SQL Server.
> I can use profiler, but it contributes some CPU utilization.
>
> Thanks,
> Thiru.
> WantedToBeDBA
> WantedToBeDBA {at} gmail {dot} com
>
See syscacheobjects in Books Online. But this only gives you some general
information about what's in the cache; as far as I know, there's no way to
actually view the cached plans.
Profiler is probably the only way to capture this information, and unless
the server is under very heavy load, it shouldn't have much of an impact.
You can use a server-side trace (see sp_trace_create) if you don't want to
leave Profiler running on a client PC.
Simon
|
|
|
|
|