|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ReCompileReferences using System SP?C++ which I know nothing about) is mentioned. It causes recompilation "prior to the next execution" of any stored procedure on the referenced table. In a production system as part of the maintenance plan we reindex nightly. From what I have read, this forces the recompile and recreation of execution plan of virtually every Stored Procedure upon first execution the following morning, resulting in some long response times for our users. I have been looking for a way to implement something like ReCompileReferences as part of the nightly maintenance. I can't find that as an option in the maintenance plan wizard nor can I find a TSQL equivalent. Does anybody know of a way to generate cached execution plans prior to the first production run in the morning? Even an answer of, "no there isn't any way" from a SQL MVP would save me some time. Thanks for your input.... No need to look any further. Pre-compile isn't really possible without executing the code (which you
can implement yourself, for the stuff that doesn't modify data, at least). This is (partly) because a plan if based on the actual parm values etc you have in the query/procedure. My guess is that BOL is badly worded and this DMO method simply execute sp_recompile against the table (will force recompile at next execution). A quick test with a test DMO app and Profiler trace would show that (which I unfortunately don't have time for right now). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "John Kotuby" <jo***@powerlist.com> wrote in message news:uiFAcG19FHA.2816@tk2msftngp13.phx.gbl... > In SQL Server 2000 Online Help, the method ReCompileReferences (looks like C++ which I know > nothing about) is mentioned. It causes recompilation "prior to the next execution" of any stored > procedure on the referenced table. > > In a production system as part of the maintenance plan we reindex nightly. From what I have read, > this forces the recompile and recreation of execution plan of virtually every Stored Procedure > upon first execution the following morning, resulting in some long response times for our users. > > I have been looking for a way to implement something like ReCompileReferences as part of the > nightly maintenance. I can't find that as an option in the maintenance plan wizard nor can I find > a TSQL equivalent. Does anybody know of a way to generate cached execution plans prior to the > first production run in the morning? > > Even an answer of, "no there isn't any way" from a SQL MVP would save me some time. > > Thanks for your input.... > Thanks very much Tibor,
We will continue with the optimizing technique already in progress. We are currently taking long complicated stored procedures and breaking them down into smaller pieces. When we first started programming for SQL Server we vastly overestimated SQL's ability to handle complex code in a transactional environment with a few million records. Thanks again.... Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:OEBJlV29FHA.2040@TK2MSFTNGP14.phx.gbl... > No need to look any further. Pre-compile isn't really possible without > executing the code (which you can implement yourself, for the stuff that > doesn't modify data, at least). This is (partly) because a plan if based > on the actual parm values etc you have in the query/procedure. > > My guess is that BOL is badly worded and this DMO method simply execute > sp_recompile against the table (will force recompile at next execution). A > quick test with a test DMO app and Profiler trace would show that (which I > unfortunately don't have time for right now). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "John Kotuby" <jo***@powerlist.com> wrote in message > news:uiFAcG19FHA.2816@tk2msftngp13.phx.gbl... >> In SQL Server 2000 Online Help, the method ReCompileReferences (looks >> like C++ which I know nothing about) is mentioned. It causes >> recompilation "prior to the next execution" of any stored procedure on >> the referenced table. >> >> In a production system as part of the maintenance plan we reindex >> nightly. From what I have read, this forces the recompile and recreation >> of execution plan of virtually every Stored Procedure upon first >> execution the following morning, resulting in some long response times >> for our users. >> >> I have been looking for a way to implement something like >> ReCompileReferences as part of the nightly maintenance. I can't find that >> as an option in the maintenance plan wizard nor can I find a TSQL >> equivalent. Does anybody know of a way to generate cached execution plans >> prior to the first production run in the morning? >> >> Even an answer of, "no there isn't any way" from a SQL MVP would save me >> some time. >> >> Thanks for your input.... >> >  |
|||||||||||||||||||||||
Other interesting topics