Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Brian Kelley » Stored Procedures and Caching
Add to briefcase
22 posts, Page 2 of 3
««123»»

Stored Procedures and Caching Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
NJJohn
Posted Monday, April 15, 2002 11:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 17, 2006 9:20 AM
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


Post #27005
K. Brian Kelley
Posted Monday, April 15, 2002 11:52 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Today @ 7:47 AM
Points: 6,584, Visits: 1,796
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, 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 #27006
NJJohn
Posted Monday, April 15, 2002 12:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 17, 2006 9:20 AM
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


Post #27007
NJJohn
Posted Monday, April 15, 2002 3:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 17, 2006 9:20 AM
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.


Post #27008
NJJohn
Posted Monday, April 15, 2002 3:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 17, 2006 9:20 AM
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.


Post #27009
Antares686
Posted Friday, November 19, 2004 6:06 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Yesterday @ 10:09 AM
Points: 8,357, Visits: 685
Big applause. Great article. Nice and tight delievery.


Post #147166
Frank Kalis
Posted Friday, November 19, 2004 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:48 PM
Points: 5,955, Visits: 277

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/
Post #147170
K. Brian Kelley
Posted Friday, November 19, 2004 11:22 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Today @ 7:47 AM
Points: 6,584, Visits: 1,796
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, 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 #147339
Frank Kalis
Posted Saturday, November 20, 2004 1:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:48 PM
Points: 5,955, Visits: 277

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/
Post #147357
Jeff Moden
Posted Sunday, November 21, 2004 8:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #147403
« Prev Topic | Next Topic »

Add to briefcase
22 posts, Page 2 of 3
««123»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.