View a cached query plan?

Hi all.

Is there a way to view the compiled query plan of a stored procedure and/or table-valued function?

The reason that I am asking is that I have spent a good deal of time performance tuning a few different multi-statement table-valued functions by executing the DML statements directly in QA and examining the query plan and IO statistics.

Unfortunately, the functions accept 8 parameters that can greatly affect the execution time - and probably the query plan. For instance, the first two parameters define a date and a date range indicator (e.g. "One Month", "YTD", "Rolling 12 months"). These and other parameters can affect the joins. One combination of parameters may pull nearly all rows from a table while other combinations may only use a small number of rows. I imagine that the optimal execution plan could change from a merge join to a hash or nested loop in the latter case.

Additionally, there is an unknown factor when the code is migrated to production. Our production environment hardware is different from development and staging.

I would like to view the compiled plans to make sure that they matche the plans from QA.

TIA

Keith Payne
Technical Marketing Solutions
www.tms-us.com

Thank you for the ideas. I'm going to have the production DBA run a trace after it migrates and I will learn to use the text query plan.

Keith Payne
Technical Marketing Solutions
www.tms-us.com

Do you have a performance problem? If not, stop worrying!

Tom Pullen
DBA, Oxfam GB

quote:Originally posted by thomas

Do you have a performance problem? If not, stop worrying!

Tom Pullen
DBA, Oxfam GB

Yes, performance is a big issue with these objects. The functions are combined in a stored procedure that can take 25 minutes with the wrong execution plans - or 2.5 minutes with efficient plans.

Keith Payne
Technical Marketing Solutions
www.tms-us.com

Frank,<br /><br />Thank you for the offer of looking through the code. I spent all day yesterday reworking the joins and indexes and finally hit on a configuration where SQL Server consistently chose the query plan that resulted in the shortest execution time. I made sure to execute the procs and functions with a parameter combination that pulls the largest row counts from each table.<br /><br />I've got consistent execution times of under 2 minutes. The only outstanding issue is to make sure that the production DBA executes the procs and functions with the same parameters immediately after they migrate [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br /><b>Keith Payne</b><br />Technical Marketing Solutions<br />www.tms-us.com