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.

Performace montior

HI..
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
.. "
also i check that i am administrator and i login as administrator
pls help
-TP
Hi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/default...812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:

> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preformace
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collected.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>

Performace montior

HI..
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
. "
also i check that i am administrator and i login as administrator
pls help
-TPHi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/defaul...;812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:

> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preforma
ce
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collecte
d.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>

Performace montior

HI..
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
. "
also i check that i am administrator and i login as administrator
pls help
-TPHi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:
> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preformace
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collected.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>