Stored Procedures and Caching

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Brian Kelley » Stored Procedures and Caching

22 posts, Page 1 of 3

1 2 3 »»»

Stored Procedures and Caching

Rate Topic

Display Mode

Topic Options

Author

Message

K. Brian Kelley

Posted Sunday, January 27, 2002 12:00 AM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:47 AM
Points: 6,584, Visits: 1,796

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #2380

Robert W Marda

Posted Monday, January 28, 2002 12:01 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

I like this article. Even though you say it only touches the surface of the subject it goes into it enough to give me a better understanding of stored procedure caching.

I have a couple questions about portions of your article.

First, when you say that a stored procedure becomes serialized when being compiled and as such can cause bottlenecks, do you mean that while being compiled no one can execute it until the compile is finished or does this just mean that it can't be modified until the compile is complete?

Second, I tried a test of running a stored procedure not in the master that started with sp_ and the same one in the same database that didn't start with sp_. The difference in milliseconds was very small. These were simple stored procedure. Would the benefit in speed be greater with a more complex stored procedure?

Robert Marda

Robert W. Marda
SQL Programmer
Ipreo

Post #26996

K. Brian Kelley

Posted Monday, January 28, 2002 1:17 PM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:47 AM
Points: 6,584, Visits: 1,796

It indeed causes blocking on the stored procedure meaning it's a wait in line type of thing. Here's a quote from the Q article that made my eyes widen with fear:

quote:
If many connections are simultaneously running the same stored procedure, and a compile lock must be acquired for that stored procedure each time it is run, it is possible that system process IDs (SPIDs) may begin to block each other as they each try to acquire an exclusive compile lock on the object.

Until I started doing the research on caching I didn't realize that blocking could take place in this manner.

As for the sp_ prefix, a SPID is having to get the [COMPILE] lock before it can begin the second search. But the [COMPILE] lock is exclusive, meaning it needs the stored procedure all to itself. I haven't tested it, but I assume this means that a SPID waiting on an exclusive [COMPILE] lock is having to wait until the other SPID completes execution, since it needs the stored procedure exclusively. If this is true, then more complex stored procedures would indeed cause some serious delays. I'm going to have to put a WAITFOR DELAY in an sp_ and see what happens to be sure.

K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #26997

Steve Jones - SSC Editor

Posted Monday, January 28, 2002 1:30 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:09 AM
Points: 31,526, Visits: 13,864

Excellent article Brian. It is one of the best written articles I have seen on stored procedure execution on the server.

One issue I was wondering if you'd look at is a stored procedure that runs two different types of selects such as:

create procedure MyProc
@id1 int = null, @id2 int = null
as
if @id1 is null
select * from MyTable where id2=@id2
else
select * from MyTable where id1=@id1
return

Does this cause a recompile if different parameters are used?

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #26998

K. Brian Kelley

Posted Monday, January 28, 2002 1:40 PM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:47 AM
Points: 6,584, Visits: 1,796

I haven't looked into it and it's another area I need to do more research. In my production environment where DBAs were allowed only in an advisory role, developers coded stored procedures using the CRUD methodology. Okay, fine, different stored procedures covering each, right? No, they did it in a single stored procedure. I'm assuming a recompile has to happen based on what operation they perform, but I haven't confirmed it with testing.

K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #26999

Andy Warren

Posted Saturday, February 02, 2002 5:53 PM

SSCertifiable

Group: Moderators
Last Login: Tuesday, June 11, 2013 6:34 AM
Points: 6,463, Visits: 1,388

Good article! How does exec() work compared to sp_executesql? Lately I've been trying to develop the habit of dbo qualifying all my sql...not there yet! And as far as dynamic sql and base table access, I've got a thought and a question or two that I'll post separately!

Andy

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #27000

K. Brian Kelley

Posted Sunday, February 03, 2002 6:27 PM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:47 AM
Points: 6,584, Visits: 1,796

Another thing to investigate! I've seen all the standard "Microsoft prefers you use sp_executesql" but I've not run any specific tests. Since it's a "(server internal)" system stored procedure, I'm thinking it has additional code to assist with caching, but that's just a guess.

K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #27001

rohita77

Posted Friday, March 22, 2002 1:50 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2002 12:00 AM
Points: 2, Visits: 1

This article has allowed me to bring down the execution time for an SP from 3 secs to 0.5 secs. Great Job. I suprised that this information is not present itn books online. Especially since SQL Server recommends the use of Temporary tables over Cursors. However I found that every DML on a temporary table causes the SP to recompile. This can be avoided by embedding all Temp Table DML's within sp_executeSQL. This brought the execution time of my SP from 2.5 to 0.5 secs.
I have one more observation
This is with respect to
SET ANSI_WARNINGS ON
Here is the part of the profiler trace when a SP contains this statement
Event Duration
SP:CacheHit 0 execute sp_checkavail
SP:CacheMiss 0 sp_checkavail
SP:CacheMiss 0 sp_checkavail
SP:CacheMiss 0 sp_checkavail
SP:CacheRemove 0 sp_checkavail
SP:CacheInsert 0 sp_checkavail
SP:Starting 0 sp_checkavail
SP:StmtStarting 0 -- sp_checkavail SET NOCOUNT ON
SP:StmtCompleted 0 -- sp_checkavail SET NOCOUNT ON
SP:StmtStarting 0 -- sp_checkavail SET ANSI_WARNINGS OFF
SP:StmtCompleted 0 -- sp_checkavail SET ANSI_WARNINGS OFF
SP:StmtStarting 0 -- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'
SP:Recompile 0 sp_checkavail
SP:CacheMiss 0 sp_checkavail
SP:CacheMiss 0 sp_checkavail
SP:CacheInsert 0 sp_checkavail
SP:StmtStarting 0 -- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'
SP:StmtCompleted 391 -- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'

When I comment out the SET ANSI_WARNINGS OFF
the trace looks like
Event Duration
SP:CacheHit 0 execute sp_checkavail
SP:CacheMiss 0 sp_checkavail
SP:ExecContextHit 0 sp_checkavail
SP:Starting 0 sp_checkavail
SP:StmtStarting 0 -- sp_checkavail SET NOCOUNT ON
SP:StmtCompleted 0 -- sp_checkavail SET NOCOUNT ON
SP:StmtStarting 0 -- sp_checkavail SET ANSI_WARNINGS OFF
SP:StmtCompleted 0 -- sp_checkavail SET ANSI_WARNINGS OFF
SP:StmtStarting 0 -- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'
SP:StmtCompleted 0 -- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'

Any Comments

Post #27002

K. Brian Kelley

Posted Friday, March 22, 2002 2:05 PM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:47 AM
Points: 6,584, Visits: 1,796

From the Q article (Q243586):

quote:

Recompilations Due to Certain SET Statements Executed in Stored Procedure
The following five SET options are set to ON by default:

* ANSI_DEFAULTS
* ANSI_NULLS
* ANSI_PADDING
* ANSI_WARNINGS
* CONCAT_NULL_YIELDS_NULL

If you execute the SET statement to set any of these options to OFF, the stored procedure will be recompiled every time it runs. The reason for this is that changing these options may affect the query result that triggered the recompilation.

That explains the recompiles with the SET statements.

K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #27003

NJJohn

Posted Wednesday, April 10, 2002 6:07 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 17, 2006 9:20 AM
Points: 11, Visits: 1

Fantastic article Brian.
I noticed with a proc that I'm running, that it recompiles the proc based on the parameters that are being passed to it. So if you run the proc with param A, it does not recompile for params B, C and D. But the minute you run it with param E it initiates a recompile. And the funny thing is that when you exec the proc again with param A it initiates a recompile once more. I analyzed the SP statements in Profiler and noticed that the one that causes the recompile had a couple more temp tables that it was using based on the data.
So the first thing I did was to bring all the DDL script to the top of the proc. This seemed to result in fixing the problem, the speed improved from 90 secs to 50 secs on the first run, and then using the cached query plan to 2-5 secs depending on rows returned and using param E did not result in a recompile. But only for a while. All of a sudden, a couple of hours later, I noticed it went back to its old behaviour. One thing I noticed was that one of the other DBAs had changed the number of processors that was being used by SQL Server from 4 to 3. Resetting this did not change anything. Is there anything that could have been reset at a database level that might have started the problems with this proc again??? Could something have changed with tempdb or any of the dboptions?
Thanks for any suggestions
John

Post #27004

« Prev Topic | Next Topic »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions