SQL Server Connection Timeout

I had a problem recently on play-free-games.com whereby I was hitting connection timeouts while connecting to SQL Server 2008 from my .Net application using ADO.

I would stress the point that these were connection timeouts rather than command timeouts. In many cases the timeout occurred when calling SqlConnection.Open(), without even defining a command to execute.

What surprised me (I'm easily surprised) was that the timeouts were almost instantaneous - within 1-2 seconds. SQL Server connection strings include a parameter called "Connection Timeout", which I had not set, but the default is listed as 15 seconds.

I wasted a lot of time asking network nerds to trace the connection, check for dodgy switches etc, only to find a Microsoft blog post stating that in some circumstances the timeout applied will only be 8% of what is specified if your SQL server is mirrored:

http://msdnrss.thecoderblogs.com/2011/05/ado-net-application-connecting-to-a-mirrored-sql-server-database-may-timeout-long-before-the-actual-connection-timeout-elapses-sometimes-within-milliseconds/

With this in mind I increased the timeout value in my connection string to 200, and the problem disappeared except at peak usage times.

I then added a "Min Pool Size" setting of 30, and the problem disappeared altogether.

Top hole!

Multiple Column Primary Keys on Table Variables

I luuurrrve table variables in SQL Server - in fact they are my third favorite thing after otters and custard.

I actually used them for a couple of years without realizing that I could define a primary key, boosting performance:

DECLARE @Cakes table (CakeId int PRIMARY KEY, CakeName varchar(30), HasFrosting bit)

For some of my recent work on Giant Panda Planet I found I needed my table variable to have a multiple column primary key... was it an impossible dream?

Not really:

DECLARE @PandaFriends table (
PandaId int,
FriendId int,
IsBestFriend bit,
PRIMARY KEY(PandaId, FriendId)
)

Side Pocket!