February 4, 2002 at 12:16 pm
I have the opposite problem to most people, I have a very large stored procedure which is run via ADO and keeps dropping out of cache as it is only used once every few days. How do i force it to remain in cache, as it takes over 15 minutes to run from cold?
February 4, 2002 at 12:40 pm
Is the issue that you are losing the execution plan or that the data that will be touched by the stored procedure isn't in cache? Both of those can cause the delay.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 4, 2002 at 12:55 pm
Brian,
Thanks for the reply. I wish i knew, i'm pretty sure something is being aged out and have assumed it was the execution plan as the data is not changing at the moment (only test data in there), this could be wrong. I also noticed that running it in query analyzer does not help, it must be run via ado in order to cache it for ado use. Any ideas?
Edited by - hvallian on 02/04/2002 1:10:57 PM
February 4, 2002 at 5:47 pm
Not sure why running in one place or the other would make a difference. I'd bet its the data dropping out of cache that's hurting you rather than the query plan - unless its one whopper of a query! You could look at pintable to try to keep it in cache. Have to ask though - why do you care? Is performance that critical on this particular query? Overall you WANT SQL to age out cache so you can cache stuff you are using now.
Andy
February 4, 2002 at 6:55 pm
Thanks for the reply Andy. Before i go on i must add a disclaimer that this is not my system i have been asked to get it up and running.
The procedure is very very big, over 745 000 lines long and when added to the cache uses up nearly 1 gig of memory (as far as i know its the biggest in the world :)). It appears to be dropping out after a day of non use which is no good because no one will use it at the weekend, the first unfortunate person on Monday will have to wait for up to 30 minutes since the procedure is run nearly constantly when a user logs on. I have written a simple asp page that caches it and i am considering some sort of run once a day but then i'm not sure how to do that either, web and iis stuff is not my strongpoint.
February 4, 2002 at 11:07 pm
Hi
Thats a BIG stored proc, whoever coded it must have no concept of modular programming. Anyhow, cache stored proc plans? didnt know you could "programmaticaly" (ie, force) do it in SS?? pin just pins tables, not plans.
"After an execution plan is generated, it stays in the procedure cache. SQL Server 2000 ages old, unused plans out of the cache only when space is needed. Each query plan and execution context has an associated cost factor that indicates how expensive the structure is to compile. <etc>"
if its not being used regularly, its cached out, simple as that, but once executing you should no issues at all with performance unless there are other perf problems within the stored proc code itself.
In the end, im confused about how you are caching the plans? and overriding the aging of the cache.
Tell the people who are complaining to rack off and live with it. Its not worth the effort!
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
February 5, 2002 at 4:48 am
First I would suggest reevaluating the procedure itself as that sounds entirely too large and cumbersome to troubleshoot in it's current state (which I understand from your statement this may not be an option but I am making it the first suggestion). Also, you need to look at it at the most basic level as to what it is doing when you first run it (get a good shot of the execution plan before and another after shot, this will also show if you are hitting an index that is actually slowing you down). What kind of indexes does this database have (good indexing can be a big bennefit)? Finally when looking at the execution plan take a look at how SQL is handling the indexes and then consider adding index hints (this is like building a predefined plan as you are telling SQL how to traverse the data and may help in first execution).
All in all though the size of the data in the cache may be the ultimate problem if it is as large as you suggest. Once it drops out your next bottleneck will be overall speed of the drives and controller to access that data (I suggest making sure you have the latest and greatest SCSI drives on a RAID 5 or RAID 10 and hopefully you do not make a lot of changes to the data). ANd one last thing, consider if all the records that are being pulled are not needed move them to seperate breakdown tables in seperate file groups and if possible on seperate drive arrays (this should give you better access times). If this cannot be done consider creating additional files within the same file group but on seperate drive arrays (this is a fraction of what the previous will do for you but it can help some).
February 5, 2002 at 2:41 pm
quote:
The procedure is very very big, over 745 000 lines long and when added to the cache uses up nearly 1 gig of memory (as far as i know its the biggest in the world :)).
Wow. That's like having a single function in VB being 745,000 lines long. Just out of curiousity, how long does it take to run once it is in cache?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 5, 2002 at 3:32 pm
Man thats big. I've seen a few that were 800-900 lines with lots of calls to other procs - would take minutes for the query plan to draw in QA. How about just adding one more parameter that would essentially cause it to exit when run, then run it a couple times a day to keep it in cache. Hows that for a hack! (If it works anyway).
Andy
February 6, 2002 at 9:59 am
Just for those who are interested, on a dual processor WIn2000 machine with 2 gig of RAM:
Once in cache it takes only 6-10 seconds (single user) depending on the parameters supplied
To run it via QA before it is cached takes 7.5 minutes
The query plan - i left it running for a day then gave up as it never appeared
Thanks for all your replies - for now i am going to continue running it once a day.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply