Getting Plan of previously executed Queries

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

"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