CODEWITHSUNDEEP
sql-server-2005
Aheho
Aheho

Reputation: 12821

Sql 2005 Express edition slow connections

I'm running SqlServer 2005 express edition on my laptop for development purposes. It seems that when I open a connection to the database, the setup time is REALLY slow. It can take up to 10 seconds to get a connection. I usually have multiple connections open at the same time (Profiler, Development environment, Query Analyser, etc.) I have a hunch that the slow times are related to the fact that I have multiple connections open.

Is there a governor in Express edition that throttles connection times when multiple connections are made to an instance?

Update: My workstation is not on active directory, and SQL is running mixed mode security. I will try the login with sql authentication. I am not using user instances.

Update2: I setup a trace to try and figure out what is going on. When the connection to the database is opened the follow command is executed:

master.dbo.sp_MShasdbaccess

This command takes 6 seconds to execute.

Upvotes: 1

Views: 1487

Answers (3)

Aheho
Aheho

Reputation: 12821

I figured it out. The problem was I had multiple databases with AutoClose set to true. I shut it off in all my databases and the problem went away.

see this article for more info.

Upvotes: 2

Duncan Smart
Duncan Smart

Reputation: 32058

AFAIK there's no governer anymore in SQL Express.

Now, are you on a Windows Active Directory Domain? If so, there might be an issue with your DNS or something that means the connection to the domain controller to validate your logon to the server instance is taking the time. I suggest you experiment switching the server over to use SQL Security, give the SA account a password, and try logging in as SA and see if that makes a difference.

Upvotes: 0

MusiGenesis
MusiGenesis

Reputation: 75296

Are you sure the connection is the bottleneck? Is it your conn.Open() line that is taking 10 seconds?

Upvotes: 0

Related Questions