SQLServerCentral / Discuss Content Posted by Brian Kelley / Article Discussions / Article Discussions by Author / Stored Procedures and Caching / Latest Posts

RE: Stored Procedures and Caching

Praveen Kumar Pasula — Wed, 20 Jul 2005 23:57:00 GMT

Brian, i was unable to reproduce the CacheMiss when i executed a SP without specifying owner name. I followed the steps which u mentioned like creating a non-dbo account, creating a dbo owned SP etc., for testing this scenario. But i always got the ExecContextHit event instead of CacheMiss from the second execution.

Could you please give me some more details on how to reproduce this.

Thanks for such a great article.

RE: Stored Procedures and Caching

Alon Biran — Fri, 10 Dec 2004 17:14:00 GMT

Great article. As a former mainframe DB2 DBA, I was used to having more control over buffering and caching than what SqlServer allows - for better and for worse. and it's good to know a little bit more of the internal behaviour, even if just at a glance, to avoid some future pitfalls. Your article enlightened me. Anyway, I think there may be a way around the recompilation problem of procedures with interleaved DML and DDL using nested stored procedures, but I may be wrong... I should try it some time.

RE: Stored Procedures and Caching

Jeff Moden — Sun, 21 Nov 2004 20:33:00 GMT

Brian,

Really nice job on the "Stored Procedures and Caching" article. Looks like a few other folks share my sentiment. Definitely above the norm of what I've come to expect from "authors", professional or not. Thanks for the great read.

RE: Stored Procedures and Caching

Frank Kalis — Sat, 20 Nov 2004 13:39:00 GMT

Hey, Brian, why should you complain anyway? I like the article, too.

And I'd rather read a good article twice or more than a not so good article once.

RE: Stored Procedures and Caching

K. Brian Kelley — Fri, 19 Nov 2004 23:22:00 GMT

I would agree, Frank, but I had nothing to do with it, honest!!! Every so often they re-schedule some of the older articles and I guess it was my time. I'm not complaining!

RE: Stored Procedures and Caching

Frank Kalis — Fri, 19 Nov 2004 06:13:00 GMT

Hm, I would call it a bit of article recycling.

Without compromising the quality, Brian.

RE: Stored Procedures and Caching

Antares686 — Fri, 19 Nov 2004 06:06:00 GMT

Big applause. Great article. Nice and tight delievery.

RE: Stored Procedures and Caching

NJJohn — Mon, 15 Apr 2002 15:40:00 GMT

I also realized that since the proc has been compiled with ANSI_NULL set to ON it should always be running under the ANSI_NULLS ON. I ran the proc from a QA window with the Connection properties set to have ANSI_NULLS OFF and it did not cause the proc cache to get flushed or recompile of the proc, because the proc had been compiled with the ANSI_NULLS ON.

RE: Stored Procedures and Caching

NJJohn — Mon, 15 Apr 2002 15:40:00 GMT

RE: Stored Procedures and Caching

NJJohn — Mon, 15 Apr 2002 12:01:00 GMT

I shall try and do this. My only confusion then is how the Internet app by itself behaves like ANSI_NULLS are set to ON. What I mean is the first time you run it, it takes a while to create the plan but thereafter it pulls the plan from cache. But the minute you run the proc from Query Analyzer(QA) it again reloads the plan taking a long time and then it caches it for all queries run from within QA. And then you revert back to the app and you again see the rebuild of the query plan. Almost like QA steps on the internet app and vice versa. But I shall try and reset the ANSI NULLS through the app and get back to you on the findings.Thanks for the quick responseJohn

RE: Stored Procedures and Caching

K. Brian Kelley — Mon, 15 Apr 2002 11:52:00 GMT

You may want to explicitly set the ANSI_NULLs on with respect to the Internet app. Perhaps they are being set to OFF and that's causing the recompiles?I'll be honest in the web apps we have running where I work don't have similar recompile problems, so I'm reaching for possibilities.K. Brian Kelleybkelley@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/bkelley/

RE: Stored Procedures and Caching

NJJohn — Mon, 15 Apr 2002 11:22:00 GMT

Actually found that the problem was because I was compiling the proc with ANSI_NULLS OFF, the minute I changed it back to ON the recompiling stopped once the plan was available in cache.Now that that part of the problem was taken care of I started finding some other peculiar behavior. When I run the proc from Query Analyzer from different client PCs it uses the cached plan. The minute I run the same proc from the Internet application for which it was designed, it seems to recompile again and again from the different client PCs if you run the app it uses the cache plan. Going back to Query Analyzer again flushes the cache.It appears like running the proc from two different apps results in flushing the cache. Have you seen this kind of behavior and what is the reason for it? Is there a way around it at all?Thanks for any help,John

RE: Stored Procedures and Caching

NJJohn — Wed, 10 Apr 2002 18:07:00 GMT

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 suggestionsJohn

RE: Stored Procedures and Caching

K. Brian Kelley — Fri, 22 Mar 2002 14:05:00 GMT

From the Q article (Q243586):

quote:
Recompilations Due to Certain SET Statements Executed in Stored ProcedureThe following five SET options are set to ON by default: * ANSI_DEFAULTS * ANSI_NULLS * ANSI_PADDING * ANSI_WARNINGS * CONCAT_NULL_YIELDS_NULLIf 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 Kelleybkelley@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/bkelley/

RE: Stored Procedures and Caching

rohita77 — Fri, 22 Mar 2002 13:50:00 GMT

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 observationThis is with respect to SET ANSI_WARNINGS ONHere is the part of the profiler trace when a SP contains this statementEvent Duration SP:CacheHit 0 execute sp_checkavailSP:CacheMiss 0 sp_checkavailSP:CacheMiss 0 sp_checkavailSP:CacheMiss 0 sp_checkavailSP:CacheRemove 0 sp_checkavailSP:CacheInsert 0 sp_checkavailSP:Starting 0 sp_checkavailSP:StmtStarting 0 -- sp_checkavail SET NOCOUNT ONSP:StmtCompleted 0 -- sp_checkavail SET NOCOUNT ONSP:StmtStarting 0 -- sp_checkavail SET ANSI_WARNINGS OFFSP:StmtCompleted 0 -- sp_checkavail SET ANSI_WARNINGS OFFSP:StmtStarting 0 -- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S' SP:Recompile 0 sp_checkavailSP:CacheMiss 0 sp_checkavailSP:CacheMiss 0 sp_checkavailSP:CacheInsert 0 sp_checkavailSP: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 OFFthe trace looks likeEvent Duration SP:CacheHit 0 execute sp_checkavailSP:CacheMiss 0 sp_checkavailSP:ExecContextHit 0 sp_checkavailSP:Starting 0 sp_checkavailSP:StmtStarting 0 -- sp_checkavail SET NOCOUNT ONSP:StmtCompleted 0 -- sp_checkavail SET NOCOUNT ONSP:StmtStarting 0 -- sp_checkavail SET ANSI_WARNINGS OFFSP:StmtCompleted 0 -- sp_checkavail SET ANSI_WARNINGS OFFSP:StmtStarting 0 -- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S' SP:StmtCompleted 0 -- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S' Any Comments

RE: Stored Procedures and Caching

K. Brian Kelley — Sun, 03 Feb 2002 18:27:00 GMT

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 Kelleybkelley@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/bkelley/

RE: Stored Procedures and Caching

Andy Warren — Sat, 02 Feb 2002 17:53:00 GMT

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

RE: Stored Procedures and Caching

K. Brian Kelley — Mon, 28 Jan 2002 13:40:00 GMT

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 Kelleybkelley@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/bkelley/

RE: Stored Procedures and Caching

Steve Jones - SSC Editor — Mon, 28 Jan 2002 13:30:00 GMT

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 = nullasif @id1 is null select * from MyTable where id2=@id2else select * from MyTable where id1=@id1returnDoes this cause a recompile if different parameters are used?Steve Jonessteve@dkranch.net

RE: Stored Procedures and Caching

K. Brian Kelley — Mon, 28 Jan 2002 13:17:00 GMT

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 Kelleybkelley@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/bkelley/

RE: Stored Procedures and Caching

Robert W Marda — Mon, 28 Jan 2002 12:01:00 GMT

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

Stored Procedures and Caching

K. Brian Kelley — Sun, 27 Jan 2002 00:00:00 GMT

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