SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedures and Caching


Stored Procedures and Caching

Author
Message
NJJohn
NJJohn
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1
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



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10142 Visits: 1917
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 Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@‌kbriankelley
NJJohn
NJJohn
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1
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 response
John



NJJohn
NJJohn
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1
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.



NJJohn
NJJohn
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1
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.



Antares686
Antares686
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11378 Visits: 780
Big applause. Great article. Nice and tight delievery.



Frank Kalis
Frank Kalis
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7975 Visits: 289

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

Without compromising the quality, Brian.



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10142 Visits: 1917
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!

K. Brian Kelley
@‌kbriankelley
Frank Kalis
Frank Kalis
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7975 Visits: 289

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.



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84449 Visits: 41061

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.



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search