Is there a way to tell the SQL Server 2005 to automatically generate
query plan for certain stored procedure (as they run on the production
server)? (I know it's a bad idea, but still).
Thanks.Can you elaborate? A plan *is* created when you execute the procedure. You can't create a plan
without executing the procedure since the plan is dependent on a number of things like environment
settings (like SET) and also parameter values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:OQ$48kjyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is there a way to tell the SQL Server 2005 to automatically generate query plan for certain stored
> procedure (as they run on the production server)? (I know it's a bad idea, but still).
> Thanks.|||Frank,
Do you mean that you want to compile the stored procedure as soon as it
is created/deployed?
In that case, look up SET NOEXEC in BOL. You could set NOEXEC on and
call the SP.
HTH,
Gert-Jan
Frank Rizzo wrote:
> Is there a way to tell the SQL Server 2005 to automatically generate
> query plan for certain stored procedure (as they run on the production
> server)? (I know it's a bad idea, but still).
> Thanks.|||Tibor Karaszi wrote:
> Can you elaborate? A plan *is* created when you execute the procedure.
> You can't create a plan without executing the procedure since the plan
> is dependent on a number of things like environment settings (like SET)
> and also parameter values.
Understood. However, after the proc is ran, I'd love to have SQL Server
save somewhere that execution plan that it ran the proc with.|||On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
> Understood. However, after the proc is ran, I'd love to have SQL Server
> save somewhere that execution plan that it ran the proc with.
SQL 2005 profiler can capture this information. You can also attach plan to
a proc call to have SQL server use your preferred plan if you have tested
over and over and can't get SQL server to pick the plan you want. (I would
submit to http://connect.microsoft.com as they like to hear about those
kind of issues.
-Chuck Lathrope
www.sqlwebpedia.com|||SQL Server 2005 automatically captures this information and the plan is
available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
all, as when you create or exec the proc WITH RECOMPILE). You don't need
profiler.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:c0ajevo59mxn.na0e1n0vrpyc$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
>> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
>> Understood. However, after the proc is ran, I'd love to have SQL Server
>> save somewhere that execution plan that it ran the proc with.
> SQL 2005 profiler can capture this information. You can also attach plan
> to
> a proc call to have SQL server use your preferred plan if you have tested
> over and over and can't get SQL server to pick the plan you want. (I would
> submit to http://connect.microsoft.com as they like to hear about those
> kind of issues.
> -Chuck Lathrope
> www.sqlwebpedia.com|||Yes, as Tibor and Kalen said, the plan is created automatically when you
executed your procedure, it is stored in a cache of SQL Server and you can
query it from sys.dm_exec_cached_plans.
In addition, sometimes you may want to have your query execute with a
specific execution plan. In this case, you can save the execution plan in
XML format and then force your query to use the query plan by using "USE
PLAN".
For more information, please refer to:
How to: Save an Execution Plan in XML Format
http://msdn2.microsoft.com/en-us/library/ms190646.aspx
Forcing Query Plans
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
Hope this helps. Please feel free to let us know if you have any other
questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Kalen Delaney wrote:
> SQL Server 2005 automatically captures this information and the plan is
> available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
> all, as when you create or exec the proc WITH RECOMPILE). You don't need
> profiler.
Thank you. How do I now derive the actual plan from this view?
I'd like to get it into XML format so that I can open the execution plan
in SQL Server Management Studio (for inspection).|||You can a column called "plan_handle". You feed the value from this into the function
sys.dm_exec_query_plan(). You can also use CROSS APPLY:
SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:%23KV37%23xyHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Kalen Delaney wrote:
>> SQL Server 2005 automatically captures this information and the plan is available in
>> sys.dm_exec_cached_plans. (Unless the plan is never cached at all, as when you create or exec the
>> proc WITH RECOMPILE). You don't need profiler.
> Thank you. How do I now derive the actual plan from this view?
> I'd like to get it into XML format so that I can open the execution plan in SQL Server Management
> Studio (for inspection).
>|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let us know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
sql
