First run of Stored Procedure 2

[sheila11]

Hi All,

I have a Stored Procedure that takes 15 seconds when I run it the first time in Management Studio. Then onwards it takes only a fraction of a second each time it is called.

What could be the reason for this?

Is there a way to find out where the time was spent by the Stored Proc during execution?

TIA,
Sheila

 

[sheila11]

Sorry, I should have mentioned, I am using SQL Server 2005.

 

[gmmastros]

There are a couple reasons for this. The execution plan for the stored procedure is not loaded in to memory the first time (this is a very minor this, usually). And the table data is not loaded in to memory either (this is usually the cause).

You can use:

[tt][blue]
dbcc freeproccache
dbcc dropcleanbuffers
[/blue][/tt]

This will remove the procedure cache from memory and also the table data that is cached to memory. [!]Do not run these two commands on a production server.[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Check

http://www.mssqltips.com/tip.asp?tip=1035

and

http://blogs.microsoft.co.il/blogs/adlaim/archive/2009/04/13/sqlserver-express-2008-profiler.aspx

 

[sheila11]

Thanks George.

If I understand you correctly, the 15 seconds time includes execution plan time and data load time.

So, if this proc is to be called repeatedly from a Web-Application that makes each call in different web-session, the database should be able to use its cached plan and data for the procedure. Is that correct?

How long does the database retain the plan and loaded data?

Thanks again,
Sheila

 

[gmmastros]

SQL Server is a bit greedy in terms of memory. It will try to use all the memory you have in your server. It keeps track of things used and not used. The procedure cache is usually pretty small, so that probably stays in memory forever (until you re-boot the computer or restart the sql service). The data cache is different. If the memory in the server becomes all used up, sql server will dump (from memory) in-frequently used data so that other data can fit in to memory.

If you want to show the query, there may be ways to reduce the 15 seconds to something a bit more reasonable. But... if it's not really a problem, then I wouldn't worry about it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom