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.googlegr oups.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

Getting error while using CREATE GLOBAL CUBE

Hi,

I am getting this following error while trying to create a local cube using CREATE GLOBAL CUBE stmt:

[ 1] Microsoft? OLE DB Provider for Analysis : Data source provider error.
[ 2] Microsoft? OLE DB Provider for Analysis : One or more errors occurred during processing of command.
[ 3] Microsoft? OLE DB Provider for Analysis : Column expression associated with level '_TemplateLevel' is not found

The MDX stmt used is:

CREATE GLOBAL CUBE

---

STORAGE 'D:\Codes\PPlay1.cub'
FROM

---

(
MEASURE

---

.[Org Salary],
MEASURE

---

.[Count],
DIMENSION

---

.[Store]
(
LEVEL [Store Country],
LEVEL [Store State],
LEVEL [Store City],
LEVEL [Store Name],
MEMBER [Store].[Store Country].&[USA]
),
DIMENSION

---

.[Time]
(
LEVEL [Year],
LEVEL [Quarter],
LEVEL [Month]
),
DIMENSION

---

.[Employees]
(
LEVEL [CEO],
LEVEL [Senior Management],
LEVEL [Level],
LEVEL [Level 01],
LEVEL [Level 02],
LEVEL [Level 03],
LEVEL [Level 04]
),
DIMENSION

---

.[Department]
(
LEVEL [Department Description]
),
DIMENSION

---

.[Position]
(
LEVEL [Management Role],
LEVEL [Position Title]
),
DIMENSION

---

.[Store Type]
(
LEVEL [Store Type]
),
DIMENSION

---

.[Pay Type]
(
LEVEL [Pay Type]
)
)

While trying to run this MDX stmt directly in MDX Query appln, i can see an error message saying "Unable to open cellset"

Any pointers to this would be highly appreciated.

Thanks in advance.
Santosh.

On further debugging found following error being thrown:

Errors in the metadata manager. The 'Level 4' source attribute ID of the 'Level 4' level does not correspond to an existing attribute in the dimension.

And when i remove all levels from the Employee dimension, it works:

CREATE GLOBAL CUBE

---

STORAGE 'D:\Codes\PPlay1.cub'
FROM

---

(
MEASURE

---

.[Org Salary],
MEASURE

---

.[Count],
DIMENSION

---

.[Store]
(
LEVEL [Store Country],
LEVEL [Store State],
LEVEL [Store City],
LEVEL [Store Name],
MEMBER [Store].[Store Country].&[USA]
),
DIMENSION

---

.[Time]
(
LEVEL [Year],
LEVEL [Quarter],
LEVEL [Month]
),
DIMENSION

---

.[Employees],
DIMENSION

---

.[Department]
(
LEVEL [Department Description]
),
DIMENSION

---

.[Position]
(
LEVEL [Management Role],
LEVEL [Position Title]
),
DIMENSION

---

.[Store Type]
(
LEVEL [Store Type]
),
DIMENSION

---

.[Pay Type]
(
LEVEL [Pay Type]
)
)

But not sure, why is this happening...Any known issues?

Thanks,
Santosh