Prev: How do I Return Result of Case as Decimal
Next: Want some information from you
From: dbuchanan on 16 Jul 2008 00:57
Running a particula stored procedure against a SQL2000 database within a
SSMS2005 query window using identical parameter values it will sometimes
run in 17 seconds and at other times runs in 47 seconds. What should I look
into for the cause.

What I notice is that if I have not run it in some time - for example a
day - it will run slow the first run (47 sec.) but then fast on the next run
(17 sec.)

Another stored procedure in the same environment - runing with identical
parameter values between runs with run fast at 3 seconds and slow at 30
seconds with a similar pattern described above.

I understand that the first run after altering a stored procedure that SQL
server has the task of storing the execution plan, however after the first
run it should pretty much the same speed if the server has little other
demands on it. Other than seeing if there are other processes running what
could be other causes of this kind of behavior?

Thanks,
Doug


From: Jes�s L�pez on 16 Jul 2008 01:05
It sounds like the first execution does physical reads, and the second time
reads from cache. You can use SET STATISTICS IO ON and see if there are
physical reads or reads ahead

Regards.

Jes�s L�pez


"dbuchanan" <dbuchanan(a)newsgroup.nospam> escribi� en el mensaje
news:eL3Oa$v5IHA.3952(a)TK2MSFTNGP06.phx.gbl...
> Running a particula stored procedure against a SQL2000 database within a
> SSMS2005 query window using identical parameter values it will sometimes
> run in 17 seconds and at other times runs in 47 seconds. What should I
> look into for the cause.
>
> What I notice is that if I have not run it in some time - for example a
> day - it will run slow the first run (47 sec.) but then fast on the next
> run (17 sec.)
>
> Another stored procedure in the same environment - runing with identical
> parameter values between runs with run fast at 3 seconds and slow at 30
> seconds with a similar pattern described above.
>
> I understand that the first run after altering a stored procedure that SQL
> server has the task of storing the execution plan, however after the first
> run it should pretty much the same speed if the server has little other
> demands on it. Other than seeing if there are other processes running what
> could be other causes of this kind of behavior?
>
> Thanks,
> Doug
>


From: Uri Dimant on 16 Jul 2008 02:46
Hi
Run DBCC FREEPROCCACHE and then run a stored procedure. You will see that
the first time it will read from the disk and add the execution plan to the
chache , so the second run will be faster






"dbuchanan" <dbuchanan(a)newsgroup.nospam> wrote in message
news:eL3Oa$v5IHA.3952(a)TK2MSFTNGP06.phx.gbl...
> Running a particula stored procedure against a SQL2000 database within a
> SSMS2005 query window using identical parameter values it will sometimes
> run in 17 seconds and at other times runs in 47 seconds. What should I
> look into for the cause.
>
> What I notice is that if I have not run it in some time - for example a
> day - it will run slow the first run (47 sec.) but then fast on the next
> run (17 sec.)
>
> Another stored procedure in the same environment - runing with identical
> parameter values between runs with run fast at 3 seconds and slow at 30
> seconds with a similar pattern described above.
>
> I understand that the first run after altering a stored procedure that SQL
> server has the task of storing the execution plan, however after the first
> run it should pretty much the same speed if the server has little other
> demands on it. Other than seeing if there are other processes running what
> could be other causes of this kind of behavior?
>
> Thanks,
> Doug
>


From: Eric Russell on 16 Jul 2008 09:53
Here is a nice article that explains how to query the current amount of
memory allocated to SQL Server's memory buffer and also how to group these
totals by object.
http://www.sqlteam.com/article/what-data-is-in-sql-server-memory
Examine this information both before and after running your procedure for
the first time. Notice how your procedure will run faster when data pages
from the tables it references are cached.
You can also clear the buffer cache with the following command:
DBCC DROPCLEANBUFFERS


"dbuchanan" wrote:

> Running a particula stored procedure against a SQL2000 database within a
> SSMS2005 query window using identical parameter values it will sometimes
> run in 17 seconds and at other times runs in 47 seconds. What should I look
> into for the cause.
>
> What I notice is that if I have not run it in some time - for example a
> day - it will run slow the first run (47 sec.) but then fast on the next run
> (17 sec.)
>
> Another stored procedure in the same environment - runing with identical
> parameter values between runs with run fast at 3 seconds and slow at 30
> seconds with a similar pattern described above.
>
> I understand that the first run after altering a stored procedure that SQL
> server has the task of storing the execution plan, however after the first
> run it should pretty much the same speed if the server has little other
> demands on it. Other than seeing if there are other processes running what
> could be other causes of this kind of behavior?
>
> Thanks,
> Doug
>
>
>
 | 
Pages: 1
Prev: How do I Return Result of Case as Decimal
Next: Want some information from you