Caching stored procedures

I've recently been told by developers that some of our
SQL 2000 stored procedures an application is using take
longer to run the first time they are ran in a while. If
they run them again, they seem to be quicker. Does
anyone have any suggestions as to where to start looking
to resolve this? Does this have to do with the execution
plan not staying in the cache? Is there a way to fix
this?Stored procedures are compiled when the execution plan is not in cache
but the performance hit is usually not significant for occasional
compiles. The more likely cause is that data is retrieved from disk
when the proc is first run and remains in cache for subsequent access.
You can see if this is the case by running the following test:
USE MyDatabase
GO
CHECKPOINT
DBCC DROPCLEANBUFFERS
GO
SELECT GETDATE()
EXECUTE MyProcedure
SELECT GETDATE()
EXECUTE MyProcedure
SELECT GETDATE()
GO
If the second execution is noticeably faster, you might take a look at
the execution plan to see if you can optimize the queries and/or add
indexes. This can reduce both physical and logical i/o.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bill" <bill4390@.hotmaillcom> wrote in message
news:286f01c3673b$06480360$7d02280a@.phx.gbl...
> I've recently been told by developers that some of our
> SQL 2000 stored procedures an application is using take
> longer to run the first time they are ran in a while. If
> they run them again, they seem to be quicker. Does
> anyone have any suggestions as to where to start looking
> to resolve this? Does this have to do with the execution
> plan not staying in the cache? Is there a way to fix
> this?
>