Stored Procedure runs fast only after execute first in SQL Manager

Question

Stored Procedure runs fast only after execute first in SQL Manager

fmellaerts

SSC Enthusiast

Points: 188
More actions
April 22, 2016 at 12:58 pm

#325871

Hi,
I use SQL Express 2014. I have a table with approx 45.000 rows.
I use a Stored Procedure to select (with filter) from this table.
When I execute the SP from my VB.Net app, it's execution time is approx 13 seconds.
Then I execute the SP from SQL SERVER Management System , and the first time, the execution time is approx. the same. When I run the SP again from my VB.Net app, now the execution time is only like 2 seconds, but when I close the SQL SERVER Management System, it goes up again to 13 seconds.
I thought that Stored Procedures were compiled and saved in the cache, but it seems this is not True.
Please help?
Thanks,
Freddy Mellaerts

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

My initial statement when I read the heading was the typical "it is the time it takes your IO system to load the data into memory, then it is fast from there on because no physical IO access".

Well, you blew that out with the back-and-forth nature of the scenario. Now it is sounding like it could actually be that initial compilation is taking a very long time and subsequent calls are having to be recompiled (various reasons for this). I would set up a profiler trace that includes the text of the call as a filter and add in compile and recompile events in addition to the usual rpc and tsql batch completed. See what is going on with that. You can also capture a variety of cpu times from within SSMS using Query Options.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

I'd check to make sure it doesn't have auto close on.

That could make sense of the sequence.

VB code runs, take a while, session closes, database auto closes.

SSMS session executes stored proc, database starts up, query runs, is slow because nothing is in cache.

With SSMS session still open, database doesn't auto close, so subsequent runs are quick.

SSMS session closes, database auto closes, and slowness on subsequent runs continues.

Just another thing to check.

Cheers!

fmellaerts SSC Enthusiast Points: 188 More actions · Answer 3

Hi,

Indeed, you were right!

It was the autoclose of the database that was ON.

I put it off and now my VB.Net app runs smoothly!

Thanks a lot!

Freddy

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Great thinking for autoclose being a cause of the repeated long initial run times! Actually it is hard to think of a situation where autoclose should be turned on for a database.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

TheSQLGuru (4/22/2016)
Great thinking for autoclose being a cause of the repeated long initial run times! Actually it is hard to think of a situation where autoclose should be turned on for a database.

SQL Express loaded on a desktop/laptop supporting a local application.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Lynn Pettis (4/22/2016)
TheSQLGuru (4/22/2016)
Great thinking for autoclose being a cause of the repeated long initial run times! Actually it is hard to think of a situation where autoclose should be turned on for a database.
SQL Express loaded on a desktop/laptop supporting a local application.

I have clients with tens of thousands of that scenario and not a one has autoclose on. The data is just going to be lifted up off of disk again after each closure. I suppose if the app is only run once per NN time (long amount of time there) and the delay inherent is acceptable.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

TheSQLGuru (4/22/2016)
Lynn Pettis (4/22/2016)
TheSQLGuru (4/22/2016)
Great thinking for autoclose being a cause of the repeated long initial run times! Actually it is hard to think of a situation where autoclose should be turned on for a database.
SQL Express loaded on a desktop/laptop supporting a local application.
I have clients with tens of thousands of that scenario and not a one has autoclose on. The data is just going to be lifted up off of disk again after each closure. I suppose if the app is only run once per NN time (long amount of time there) and the delay inherent is acceptable.

Just giving the only example I could come up with. I see no other reason for using autoclose.