|
Home > Archive > microsoft.public.sqlserver.server > December 2002 > procedure cache and sp_prepare
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
procedure cache and sp_prepare
|
|
| JL Morrison 2002-12-17, 1:24 pm |
| In an attempt to identify some performance problems....
I am trying to determine why some applications use so much procedure cache
as reported by the sysprocesses table. After running profiler, i noticed
that some recursive processes such as a batch bill print process will
generate a temporary sp for every statement using sp_prepare. These sps
will never be reused since each bill is different. It apparently does not
use bind variables. These processes will gradually consume as much as
300-500MB of procedure cache. Once the processes finishes, this memory is
released. My questions are:
1) Are my assumptions correct? Are the temp sps eating up the proc cache?
2) Can I make a server config change to make the server just execute the
sql? Or flush the temp sp from cache before the process finishes? Or is
there a change on the client side?
BOL has not been very helpful. Its says that if a user does not have rights
to sp_prepare, then it will just execute the sql. (this didnt work)
| |
| SQL Server Development Team [MSFT] 2002-12-17, 1:24 pm |
| Are the temporary stored procedures being created with RECOMPILE?
HTH,
Michael Ray
Technical Writer
Microsoft Corporation
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"JL Morrison" <jl_morrison@hotmail.com> wrote in message
news:#p$sz0fpCHA.1636@TK2MSFTNGP10...
> In an attempt to identify some performance problems....
> I am trying to determine why some applications use so much procedure cache
> as reported by the sysprocesses table. After running profiler, i noticed
> that some recursive processes such as a batch bill print process will
> generate a temporary sp for every statement using sp_prepare. These sps
> will never be reused since each bill is different. It apparently does not
> use bind variables. These processes will gradually consume as much as
> 300-500MB of procedure cache. Once the processes finishes, this memory is
> released. My questions are:
>
> 1) Are my assumptions correct? Are the temp sps eating up the proc cache?
>
> 2) Can I make a server config change to make the server just execute the
> sql? Or flush the temp sp from cache before the process finishes? Or is
> there a change on the client side?
>
> BOL has not been very helpful. Its says that if a user does not have
rights
> to sp_prepare, then it will just execute the sql. (this didnt work)
>
>
| |
| JL Morrison 2002-12-17, 2:23 pm |
| I think I was unclear. The application is only passing simple sql such as
"select * from object where id = 100000". The server takes this sql then
uses sp_prepare to compile into a stored proc. The way I have understood
this is that this is how sql server handles all ODBC traffic (non dblib). I
dont know that for sure...just seems that way. The benefit here is that
the app may send the same exact code then there is a sp already made. In
this case, however, the next statement is "select * from object where id =
100001" so the sp_prepare does another compile because the code is
different. Somehow, sql can use bind parameters where sql server is smart
enough to recognize that this code has one parameter @id and create a temp
stored proc that has that 1 parameter.
So to answer the question...No. I am not explicitly creating any stored
procs.
"SQL Server Development Team [MSFT]" <sqldev@microsoft.com> wrote in message
news:u7eWv$fpCHA.1640@TK2MSFTNGP09...
> Are the temporary stored procedures being created with RECOMPILE?
>
> HTH,
>
> Michael Ray
> Technical Writer
> Microsoft Corporation
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
> "JL Morrison" <jl_morrison@hotmail.com> wrote in message
> news:#p$sz0fpCHA.1636@TK2MSFTNGP10...
> > In an attempt to identify some performance problems....
> > I am trying to determine why some applications use so much procedure
cache
> > as reported by the sysprocesses table. After running profiler, i
noticed
> > that some recursive processes such as a batch bill print process will
> > generate a temporary sp for every statement using sp_prepare. These sps
> > will never be reused since each bill is different. It apparently does
not
> > use bind variables. These processes will gradually consume as much as
> > 300-500MB of procedure cache. Once the processes finishes, this memory i
s
> > released. My questions are:
> >
> > 1) Are my assumptions correct? Are the temp sps eating up the proc
cache?
> >
> > 2) Can I make a server config change to make the server just execute the
> > sql? Or flush the temp sp from cache before the process finishes? Or
is
> > there a change on the client side?
> >
> > BOL has not been very helpful. Its says that if a user does not have
> rights
> > to sp_prepare, then it will just execute the sql. (this didnt work)
> >
> >
>
>
| |
| Dan Guzman 2002-12-17, 11:24 pm |
| The creation of these temporary stored procedures is the combination of
the application code, API and driver settings. Some ODBC drivers can
generate temp procs from prepared statements if the option is turned on
(Control Panel/ODBC). I don't know of a way to turn this off on the
server but you should be able to turn off the option at the client via
the ODBC applet.
--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"JL Morrison" <jl_morrison@hotmail.com> wrote in message
news:#p$sz0fpCHA.1636@TK2MSFTNGP10...
> In an attempt to identify some performance problems....
> I am trying to determine why some applications use so much procedure
cache
> as reported by the sysprocesses table. After running profiler, i
noticed
> that some recursive processes such as a batch bill print process will
> generate a temporary sp for every statement using sp_prepare. These
sps
> will never be reused since each bill is different. It apparently does
not
> use bind variables. These processes will gradually consume as much
as
> 300-500MB of procedure cache. Once the processes finishes, this memory
is
> released. My questions are:
>
> 1) Are my assumptions correct? Are the temp sps eating up the proc
cache?
>
> 2) Can I make a server config change to make the server just execute
the
> sql? Or flush the temp sp from cache before the process finishes? Or
is
> there a change on the client side?
>
> BOL has not been very helpful. Its says that if a user does not have
rights
> to sp_prepare, then it will just execute the sql. (this didnt work)
>
>
| |
| Alan Brewer [MSFT] 2002-12-18, 3:24 pm |
| The server does nothing to control this, it is all controlled in the
application or the ODBC data source. The calls to sp_prepare are generated
by the ODBC driver if you have the "Create stored procedures for prepared
statements" turned on. The option also has sub options on whether you want
the temporary stored procedures dropped only when you disconnect, or more
frequently.
If you are connecting through an ODBC data source, you should be able to use
the Data Source (ODBC) applet to turn the option off. Otherwise it would be
turning the option off in the application.
The issue is dicussed in the SQL Server 2000 Books Online in:
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_sa_1a43.asp?frame=true
http://msdn.microsoft.com/library/d...-us/odbcsql/od_
6_015_8nce.asp?frame=true
--
Alan Brewer [MSFT]
Lead Technical Writer
SQL Server Documentation Team
| |
| Hal Berenson 2002-12-18, 8:23 pm |
| There is some barking up the wrong tree going on here.
First, you don't mention what version of SQL Server you are using. In 7.0
and 2000 temporary stored procedures are not created by ODBC for
Prepare/Execute (although plans certainly are created in the procedure
cache). In SQL Server 6.5 temporary stored procedures are created, based on
the setting of a client-side option. But that seems to ignore the more
fundamental question about why you are using Prepare/Execute at all. ODBC
(and other APIs) offer two different models for executing SQL statements.
One is the Prepare/Execute model, the other is a direct command execution
model. The Prepare/Execute model is intended for cases where you know
you'll be repeatedly executing the same SQL statement and want to save the
overhead of re-compiling the statement each time. That's why the plans sit
around in the procedure cache, because you explicitly requested that they do
so! If you have SQL statements that are not being reused, then you
shouldn't be using Prepare/Execute. In ODBC that means you should be using
the SQLExecDirect call instead of the SQLPrepare/SQLExecute combination.
This is all discussed at
http://msdn.microsoft.com/library/e..._6_015_9rce.asp and
http://msdn.microsoft.com/library/d...-us/odbcsql/od_
6_015_8nce.asp
Note: Deallocating the statement handle in ODBC will also cause the plan and
(where applicable) temporary stored procedure to be released. But, this
should only apply where you actually have a good reason to be using
Prepare/Execute in the first place.
--
Hal Berenson
True Mountain Consulting
"JL Morrison" <jl_morrison@hotmail.com> wrote in message
news:unt$wdgpCHA.2360@TK2MSFTNGP09...
> I think I was unclear. The application is only passing simple sql such as
> "select * from object where id = 100000". The server takes this sql then
> uses sp_prepare to compile into a stored proc. The way I have understood
> this is that this is how sql server handles all ODBC traffic (non dblib).
I
> dont know that for sure...just seems that way. The benefit here is that
> the app may send the same exact code then there is a sp already made. In
> this case, however, the next statement is "select * from object where id =
> 100001" so the sp_prepare does another compile because the code is
> different. Somehow, sql can use bind parameters where sql server is smart
> enough to recognize that this code has one parameter @id and create a temp
> stored proc that has that 1 parameter.
>
> So to answer the question...No. I am not explicitly creating any stored
> procs.
>
>
> "SQL Server Development Team [MSFT]" <sqldev@microsoft.com> wrote in
message
> news:u7eWv$fpCHA.1640@TK2MSFTNGP09...
> > Are the temporary stored procedures being created with RECOMPILE?
> >
> > HTH,
> >
> > Michael Ray
> > Technical Writer
> > Microsoft Corporation
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.
> > "JL Morrison" <jl_morrison@hotmail.com> wrote in message
> > news:#p$sz0fpCHA.1636@TK2MSFTNGP10...
> > > In an attempt to identify some performance problems....
> > > I am trying to determine why some applications use so much procedure
> cache
> > > as reported by the sysprocesses table. After running profiler, i
> noticed
> > > that some recursive processes such as a batch bill print process will
> > > generate a temporary sp for every statement using sp_prepare. These
sps[c
olor=darkred]
> > > will never be reused since each bill is different. It apparently does
> not
> > > use bind variables. These processes will gradually consume as much[/color]
as[co
lor=darkred]
> > > 300-500MB of procedure cache. Once the processes finishes, this memory[/color]
i
> s
> > > released. My questions are:
> > >
> > > 1) Are my assumptions correct? Are the temp sps eating up the proc
> cache?
> > >
> > > 2) Can I make a server config change to make the server just execute
the[c
olor=darkred]
> > > sql? Or flush the temp sp from cache before the process finishes? Or
> is
> > > there a change on the client side?
> > >
> > > BOL has not been very helpful. Its says that if a user does not have
> > rights
> > > to sp_prepare, then it will just execute the sql. (this didnt work)
> > >
> > >
> >
> >
>
>[/color]
| |
| JL Morrison 2002-12-20, 3:23 pm |
| Woof! We are using SQL 7.0 Std SP3. Unfortunately, we dont have the source
code for this program. The company that wrote it is practically out of
business and wants us to buy their latest version.....asking them to modify
code is last resort.
I read in one of your links to KB that SQL 2000 doesnt create temp stored
procs in tempdb....so there is no overhead. But does this mean that
proccache is still used? Maybe I finally I have the ammunition that I need
to get management to upgrade to SQL 2000.
"Hal Berenson" < haroldb@truemountainconsulting
.com> wrote in message
news:e$s37DwpCHA.1988@TK2MSFTNGP12...
> There is some barking up the wrong tree going on here.
>
> First, you don't mention what version of SQL Server you are using. In 7.0
> and 2000 temporary stored procedures are not created by ODBC for
> Prepare/Execute (although plans certainly are created in the procedure
> cache). In SQL Server 6.5 temporary stored procedures are created, based
on
> the setting of a client-side option. But that seems to ignore the more
> fundamental question about why you are using Prepare/Execute at all. ODBC
> (and other APIs) offer two different models for executing SQL statements.
> One is the Prepare/Execute model, the other is a direct command execution
> model. The Prepare/Execute model is intended for cases where you know
> you'll be repeatedly executing the same SQL statement and want to save the
> overhead of re-compiling the statement each time. That's why the plans
sit
> around in the procedure cache, because you explicitly requested that they
do
> so! If you have SQL statements that are not being reused, then you
> shouldn't be using Prepare/Execute. In ODBC that means you should be
using
> the SQLExecDirect call instead of the SQLPrepare/SQLExecute combination.
> This is all discussed at
> http://msdn.microsoft.com/library/e..._6_015_9rce.asp and
>
http://msdn.microsoft.com/library/d...-us/odbcsql/od_
> 6_015_8nce.asp
>
> Note: Deallocating the statement handle in ODBC will also cause the plan
and
> (where applicable) temporary stored procedure to be released. But, this
> should only apply where you actually have a good reason to be using
> Prepare/Execute in the first place.
>
> --
> Hal Berenson
> True Mountain Consulting
>
>
> "JL Morrison" <jl_morrison@hotmail.com> wrote in message
> news:unt$wdgpCHA.2360@TK2MSFTNGP09...
> > I think I was unclear. The application is only passing simple sql such
as
> > "select * from object where id = 100000". The server takes this sql
then
> > uses sp_prepare to compile into a stored proc. The way I have
understood
> > this is that this is how sql server handles all ODBC traffic (non
dblib).
> I
> > dont know that for sure...just seems that way. The benefit here is
that
> > the app may send the same exact code then there is a sp already made.
In
> > this case, however, the next statement is "select * from object where id
=
> > 100001" so the sp_prepare does another compile because the code is
> > different. Somehow, sql can use bind parameters where sql server is
smart
> > enough to recognize that this code has one parameter @id and create a
temp
> > stored proc that has that 1 parameter.
> >
> > So to answer the question...No. I am not explicitly creating any stored
> > procs.
> >
> >
> > "SQL Server Development Team [MSFT]" <sqldev@microsoft.com> wrote in
> message
> > news:u7eWv$fpCHA.1640@TK2MSFTNGP09...
> > > Are the temporary stored procedures being created with RECOMPILE?
> > >
> > > HTH,
> > >
> > > Michael Ray
> > > Technical Writer
> > > Microsoft Corporation
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > Use of included script samples are subject to the terms specified at
> > > http://www.microsoft.com/info/cpyright.htm.
> > > "JL Morrison" <jl_morrison@hotmail.com> wrote in message
> > > news:#p$sz0fpCHA.1636@TK2MSFTNGP10...
> > > > In an attempt to identify some performance problems....
> > > > I am trying to determine why some applications use so much procedure
> > cache
> > > > as reported by the sysprocesses table. After running profiler, i
> > noticed
> > > > that some recursive processes such as a batch bill print process
will[
color=darkred]
> > > > generate a temporary sp for every statement using sp_prepare. These
> sps
> > > > will never be reused since each bill is different. It apparently[/color]
does
> > not
> > > > use bind variables. These processes will gradually consume as
much
> as
> > > > 300-500MB of procedure cache. Once the processes finishes, this
memory
> i
> > s
> > > > released. My questions are:
> > > >
> > > > 1) Are my assumptions correct? Are the temp sps eating up the proc
> > cache?
> > > >
> > > > 2) Can I make a server config change to make the server just execute
> the
> > > > sql? Or flush the temp sp from cache before the process finishes?
Or
> > is
> > > > there a change on the client side?
> > > >
> > > > BOL has not been very helpful. Its says that if a user does not
have[
color=darkred]
> > > rights
> > > > to sp_prepare, then it will just execute the sql. (this didnt[/color]
work)
> > > >
> > > >
> > >
> > >
> >
> >
>
>
|
|
|
|
|