Sql 2005 Express edition slow connections

Question

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.

Answer 1

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

Answer 2

Are you using user instances?

Answer 3

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.

Answer 4

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.