Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Stored Procedures and Caching Expand / Collapse
Author
Message
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
Posted Monday, April 15, 2002 11:52 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, September 15, 2014 8:57 AM
Points: 6,634, Visits: 1,872
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
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
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
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
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: 2 days ago @ 7:25 AM
Points: 8,369, Visits: 740
Big applause. Great article. Nice and tight delievery.


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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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
Posted Friday, November 19, 2004 11:22 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, September 15, 2014 8:57 AM
Points: 6,634, Visits: 1,872
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
Posted Saturday, November 20, 2004 1:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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
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 @ 1:45 PM
Points: 37,104, Visits: 31,659

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #147403
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse