SQL Server query timeout due to load in different DB on same server but different physical disk

Question

We have 2 separate DBs on the same physical server (one for big data, one for runtime data), they use separate set of physical disks, but same SQL Server instance.

Recently we run heavy query in the 'big data' database (analysing gigabytes of data) and a very simple query (from remote .net client on same dedicated LAN with network RT time in order of nanoseconds; running SP verifying a single bit) timed out:

System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=0; handshake=15000;
---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.Open()

Is the only option how to separate resources for those tow databases to separate them to two physical servers? Or is it possible to somehow ensure that the 'Runtime' database has always some small amount of resources available so that simple queries doesn't timeout?

One more option that I realized is usage of Resource Governor. Can anybody using this comment on this - if that's also viable option? That would be more preferble than two instances on same server (as that would block resources even if one instance is idle). — Jan

simon at rcl simon at rcl · Accepted Answer · 2015-04-29T10:09:11

Within a single instance of SQL Server there's not a lot you can do. You can, though, run multiple instances of SQL on a single physical (or virtual) machine. That alone should help - if you have enough memory, I guess. You can set the max memory each instance will use, and if you have multiple cores you can limit each instance to a sub-set of those cores. For example, you can set Instance 1 (Big Data) to use cores 0, 1 and 2 of a 4 core machine, while Instance 2 (small data ) can use cores 2 and 3. This sill allow big data to use 3 cores when it needs to, and also allows small data to always have a least 1 core available.

Setting the maximums involves trial and error, really. Try it and see.

Apart from that, and putting the second instance on a different machine which is simpler, there's not much you can, I think. However, it's always worth it to get hard data when you have a performance problem. PAL at CodePlex has a set of performance counters for many configurations including a SQL Server machine. It then chomps the recorded values and outputs some pretty and useful reports about what might be wrong. (Due to PAL I found that assigning less memory to the SQL instance improved through put on one machine! The OS was being starved of memory and swapped out which slowed a lot of things down.)

SQL Server query timeout due to load in different DB on same server but different physical disk

1 Answers