MSSQL/PHP: mssql_select_db() takes 2 seconds on certain database

Question

I have just noticed one of my MSSQL databases has slowed down dramatically. The weird thing is, it's the smallest database of them all but it takes the longest to open.

After the database opens using mssql_select_db() it queries fine, but the initial mssql_select_db() takes 2 seconds. What could possibly be causing this?

Database 1 is much larger and has many more tables/rows then Database 2, they are both on the same MSSQL server so it's not a networking issue.

Database 1
Connection/Selecting DB Time: 0.00554800033569
Query Time: 0.035463809967

Database 2 (Slow One)
Connection/Selecting DB Time: 2.40434789658
Query Time: 0.144586801529

Update: When i have "Database 2" open in Server Management Studio. mssql_select_db() seems to run normally, and connects fast see below

Database 1
Connection/Selecting DB Time: 0.00480604171753
Query Time: 0.0346729755402

Database 2 (Slow One)
Connection/Selecting DB Time: 0.00085186958313
Query Time: 0.0340950489044

This is not caused by too many active connections, I have already ruled that out. Any ideas?

Update 2: I'm running PHP Version 5.2.6-1+lenny13 using MSSQL FreeTDS Library. I am directly connecting via IP not using hostnames so it's not a resolv issue.

Are those DBs on the same machine as the webserver? Please give more information about the infrastructure. — Raffael Luthiger, Jul 07 '11 at 08:33
Raffael, Both those DB's are on the same windows machine, and i'm using a Linux server to connect to them with PHP. Database 1 loads so fast thats why i do not think its a network issue because they're both on the same machine/ip. — x9821, Jul 07 '11 at 08:38
Do you use DNS to resolv the host name or do you directly connect to the IP address? — Raffael Luthiger, Jul 07 '11 at 09:41

score 0 · Answer 1 · answered Jul 07 '11 at 07:45

0

Could it be that other processes have open connections to that DB and there are no other available available connections?

answered Jul 07 '11 at 07:45

Tudor Constantin

26,330
7
49
72

Tudor, Thanks for the suggestion but that does not seem to be the problem. I noticed, if i have SQL Management Studio open editing the slow "Database 2", the mssql_select_db() only takes 0.008 seconds. I'm guessing this is because the database is already preloaded? Have you ever heard of anything like this before? – x9821 Jul 07 '11 at 07:49
I have never worked with MSSQL - anyway, this is a conectivity issue - so it doesn't matter how big the DB is. Try looking here http://stackoverflow.com/questions/216007/how-to-determine-total-number-of-open-active-connections-in-ms-sql-server-2005 and check the number of active connections when you have long delays – Tudor Constantin Jul 07 '11 at 08:08
Tudor, there is only 2 active connections so i don't think that is causing the problem. Thanks for all your help anyway. – x9821 Jul 07 '11 at 08:25

score 0 · Accepted Answer · answered Jul 08 '11 at 10:29

0

I have rebuilt the database and re-imported all the data, that has seemed to fix the problem. It may have just have been database fragmentation this whole time.

answered Jul 08 '11 at 10:29

x9821

23
3

MSSQL/PHP: mssql_select_db() takes 2 seconds on certain database

2 Answers2