Performance improves with Studio Query window open

Bit of a strange one here. We have a SQL Express instance running with one database which is accessed by a VB6 client application.

Performance between the application and the database isn't great, but bizarrely if you open a Query window from the Management Studio (against the database) the performance dramatically improves - to the extent that it is visually noticeable from within the application.

So, I'm thinking that the database connections being made by the application are taking longer because of instance name resolution or the like and with the Query window open this resolution is performed quicker.

Has anyone come across this situation? I can re-create it on several different computers each with their own Express instance and I've not found anything about this on the net.

Here's the connection string used by the application - I have tried various permutations of values to no avail:

Provider=SQLOLEDB.1;Initial Catalog=SampleDB;Data Source=(local)\sqlexpress;Trusted_Connection=yes

Thanks very much

hi, SQLExpress sets by default it's related databases a database propery that could be involved in your observation..

SQLExpress sets the auto close database property to true, so that every database with no active connection is shut down to preserve file integrity.. at next database connection, the database will be re-opened, requiring initial overhead to start it.. you can modify that database option as required...

this "solution" was probably taken out of the box becouse SQLEXpress is expected to execute on "client pcs" and not on "server" hardware that obviously are more "secure" and "stable"...

regards|||

Yes, thank you - switching the Auto Close to False works nicely.

I note that Auto Close is False by default on databases on full SQL Server, so this we only need to watch out for those databases put up on SQL Express.

Thanks again.

Performance improves with Studio Query window open

Bit of a strange one here. We have a SQL Express instance running with one database which is accessed by a VB6 client application.

Performance between the application and the database isn't great, but bizarrely if you open a Query window from the Management Studio (against the database) the performance dramatically improves - to the extent that it is visually noticeable from within the application.

So, I'm thinking that the database connections being made by the application are taking longer because of instance name resolution or the like and with the Query window open this resolution is performed quicker.

Has anyone come across this situation? I can re-create it on several different computers each with their own Express instance and I've not found anything about this on the net.

Here's the connection string used by the application - I have tried various permutations of values to no avail:

Provider=SQLOLEDB.1;Initial Catalog=SampleDB;Data Source=(local)\sqlexpress;Trusted_Connection=yes

Thanks very much

hi, SQLExpress sets by default it's related databases a database propery that could be involved in your observation..

SQLExpress sets the auto close database property to true, so that every database with no active connection is shut down to preserve file integrity.. at next database connection, the database will be re-opened, requiring initial overhead to start it.. you can modify that database option as required...

this "solution" was probably taken out of the box becouse SQLEXpress is expected to execute on "client pcs" and not on "server" hardware that obviously are more "secure" and "stable"...

regards|||

Yes, thank you - switching the Auto Close to False works nicely.

I note that Auto Close is False by default on databases on full SQL Server, so this we only need to watch out for those databases put up on SQL Express.

Thanks again.

Performance and logs

Hi,

I'm using SQL Server Management Studio Express and I have made a website
that uses a database with stored procedures.

When running pages in my webpages it cost around 2 seconds to load each
page.
Thanks too long!! But where is the problem?

I like to see a list of all executed stored procedures with there execute
time (for each page).
In this way I can check if the problem is here.

How can I get a logfile like this?

Thanks!

Performance tuning part of SQL Server does not come with Express, the Tuning Advisor which replaced the Index tuning Wizard, PerfMon and Profiler. The last can tell you stored proc duration and other statistics. Try the link below and download the eval which is good for 180days but it will be a good idea to buy the no deployment developer edition because it cost only $33. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx