Slow connection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Slow connection

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-10-04 : 12:44:43

In my program, I connected to my data using 2 connections to 2 different databases, e.g.:

rstLedger.Open ("Select * from Ledger", conFinance)
rstList.Open ("Select * from ClientList", conClients)

Now, in order to save on connections, I changed the program to have only one connection and I do the following:

rstLedger.Open ("Select * from Ledger", conFinance)
rstList.Open ("Select * from MyFinanceDB.DBO.ClientList", conFinance)

Although this second methodology works, it seems to result in extreme slowness with only a few of our end-clients where the program is running, each of who have an independent computer running its own SQL Server. The vast majority of our end-clients have not experienced any change in performance.

Note that we are still using SQLServer 2005. Would SQL2008 have the same behavior? We're grasping at straws here...

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-04 : 13:03:09

Why do you need to "save on connections"?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-10-04 : 13:10:25

Tara,

Opening a connection, then doing what you need to do, and then closing the connection is time-consuming and affects performance. So you keep the connection open and only close it when closing the program. However, I have always read that connections are "expensive" in terms of resources. So I figured that keeping only one connection open and accessing the data from different databases through that single connection would be a great way to go. And it works great with the vast majority of our clients. Only a few are reporting debilitating slowness which has been determined to be directly related to this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-04 : 13:27:39

Connections really aren't that expensive. We have systems with thousands of connections and no issues.

To diagnose this issue, you'll need to capture the execution plans when the issue occurs. Compare that to a system where the issue isn't happening. Let us know what you find.

It could be as simple as running update stats.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-05 : 01:37:42

WindChaser, this sounds like a situation to use connection pooling . Connection pooling will have a number of connections open to the SQL Server - e.g 10 and the applications will take one of those. The app should be dealing opening \ closing connections.
Assuming you're using ODBC check the confirguration

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-10-09 : 16:03:25

I took some time to execute the 2 following queries and review their execution plans:

rstList.Open ("Select * from ClientList", conClients)
rstList.Open ("Select * from MyFinanceDB.DBO.ClientList", conFinance)
where the conClients is a connection to the Clients database and conFinance is a connection to the Finance database.

Although the first query runs fast and second very, very slow, the execution plans for both are absolutely identical. I also checked and both databases have the Auto Updates Statistics = TRUE and Cross-Database Ownership Chaining Enabled = TRUE . As I stated earlier, I have a computer where the problem occurs and another where it does not. I checked the options of the databases and the SQL Server 2005 instances on both computers and they are all completely identical. Note that the computer where everything runs fast is an older XP station whereas the other computer where the problem occurs is a Win7-64bits.

Any thoughts would be appreciated. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-09 : 16:33:38

It sounds like the problem is client-side then. Can you replicate this behavior from within SSMS?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-10-09 : 16:48:03

Tara, sorry I forgot to mention that all my tests were done within SSMS.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-09 : 17:47:20

In SSMS, add this to the top of your test:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Your Query Goes Here

Run both tests and show us the output of the statistics.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-09 : 17:48:15

Oh and to ensure you are comparing apples to apples, add this too:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

So your script should look like this:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
SET STATISTICS TIME ON

Your Query Goes Here

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-10 : 01:28:51

Beyond the SSMS based tests , have you checked Event Viewer or Perfmon to identify any clues?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-11-01 : 15:20:30

Hi folks, I'm sorry if I neglected this topic but it's been crazy here.

So, I did a few tests. I put the databases on 2 SQL Server 2005 instances on 2 different computers, both PCs being of similar power. The instance of SQL Server 2005 on PC1 is an Express version and the instance on PC2 is a Developper version.

My test is as follows:

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SET SHOWPLAN_ALL ON;
GO
Select * From FinancialSQL.DBO.Transactions where AccountPaying = 3929
Order by TransactionDate DESC, TransactionID DESC
GO
SET SHOWPLAN_ALL OFF;
GO
SET STATISTICS TIME OFF
GO
SET STATISTICS IO OFF
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO

I ran 4 tests using SSMS :
1. Ran the test query on PC1 on its local SQL Server
2. Ran the test query on PC2 on its local SQL Server
3. Ran the test query on PC1 connecting to the SQL Server of PC2
4. Ran the test query on PC2 connecting to the SQL Server of PC1

For all tests, the results were identical, absolutely identical, so they're not yielding any differential information. I also ran the basic query "Select * From FinancialSQL.DBO.Transactions where AccountPaying = 3929 Order by TransactionDate DESC, TransactionID DESC" with stats on and observed the amount of time required to generate the resultset which was as follows:

1. Ran the query on PC1 on its local SQL Server
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 47 ms, elapsed time = 1005 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(13 row(s) affected)
Table 'Transactions'. Scan count 1, logical reads 15134, physical reads 534,
read-ahead reads 618, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 3228 ms.

2. Ran the query on PC2 on its local SQL Server
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 43 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(13 row(s) affected)
Table 'Transactions'. Scan count 1, logical reads 15134, physical reads 0,
read-ahead reads 1121, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 357 ms.

3. Ran the query on PC1 connecting to the SQL Server of PC2
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 37 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(13 row(s) affected)
Table 'Transactions'. Scan count 1, logical reads 15134, physical reads 0,
read-ahead reads 1121, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 308 ms.

4. Ran the query on PC2 connecting to the SQL Server of PC1
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 1028 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(13 row(s) affected)
Table 'Transactions'. Scan count 1, logical reads 15134, physical reads 534,
read-ahead reads 618, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 3158 ms.

So the differences between the test results show that whenever accessing the SQL Server 2005 Express instance, there are more physical reads and more read-ahead reads, independently whether the query is done on the local computer or on a remote computer. This said, I don't know how to interpret the results and derive a solution to the issue. However, I do know that the cause is not inherent to the Express nature of the instance on PC1 because all our clients use the Express version and only a handful seem to be experiencing the observed issue.

As usual, thanks for your time reading this post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources