Changing defaults

When you lick the terminals of a brand new 9-volt battery the shock you feel is Nature's way of saying "You really shouldn't be doing this."

When you approach a bonfire and feel the heat coming off in waves and singing your eyebrows it is Nature's way of saying "You really shouldn't be doing this."

When you are timing out when running a report in SQL Server Reporting Services it is Nature's way of saying "You really shouldn't be doing this."

We have received a troubling number of requests recently to expand the timeout of SQL Server or the maximum length of a transaction or enlarge the number of connections in the connection pool.  What people are doing is trying to put "fix" their problem by pretending the problem doesn't exist.  Let's blast through a number of items really quickly.

Increasing the connections in the connection pool.  The error message that people receive is "The timeout period elapsed prior to obtaining a connection from the pool."  This is almost always an application issue as the application has most likely forgotten to clean up SQL Connection objects.  Yes, .NET does automatic garbage collection.  No, it does not clean up everything.  Resources that are managed outside of .NET (for example SQL connections) need to be cleaned up by your code as .NET will not do it for you.  As a result, if you ever receive the error message listed above, look in your code first before looking anywhere else.  In our environment a well running application will only have 5 - 10 connections in use at any one time and, by default, the connection pool has 100 connections available to be used.  We've had applications that have had hundreds of concurrent users with no issues. (In an extreme case we had over 1800 concurrent users and we still stayed within the 100 connection limit of the connection pool.)  If you get the error message there is a problem.  FIX the problem.

Increasing the transaction limit above the default.  The default transaction limit is 10 minutes.  If you are trying to exceed that it means that you have a transaction which is trying to run for more than 10 minutes without a single commit or rollback in that time.  This is way too long.  You should not be running 10 minute transactions.  In reality, if your transaction is longer than 10 seconds you have an issue.  Yes, if you are importing a lot of data you can go for longer than 10 seconds, but the longer you run in a single transaction the slower your application will respond.  Keep the transaction short and meaningful.  If you need to raise the limit above 10 minutes, rethink your architecture as there are some fundamental questions that you have not answered correctly.

Increasing the session state timeout.  This one is alterable by the application team but, in all honesty, should never be altered.  If altered it should be moved down not up.  The Microsoft default of 20 minutes, in the words of many companies, is "too long".  If you are maintaining session state and the user is not doing anything to interact with your system for 20 minutes, then you need to re-evaluate your system.  Twenty minutes is a long time.  Light has circled the earth over 10,000 times. In 2011 an average of over 65 million searches were done on Google properties worldwide in a a 20 minute period.  If your user is doing nothing on your page for 20 minutes, consider them lost.  If, for some obscure reason your clients are on a single page for 20 minutes, you need to redevelop that page as it is not in the least bit effective.

In general, if you feel that you need to alter some default setting then you should be experiencing the same feeling as licking that 9-volt battery.  Stop it and step back.