Hosting ASP.Net session database on same same instance as the user database?

Question

We have a web farm using SQL Server sessions. We are seeing occasional timeouts connecting to the session DB even though overall utilization is low. The ASPState session database is on the same instance as the application database. I suspect this is not a good idea because the user database queries are closely correlated with the session database queries. What do you think?

If this is a bad idea, would it help to move it to another SQL Server instance on the same machine? Or is it best practice to run the session DB on another machine?

Answer 1

Personally, in addition to having an extra machine for the session DB, I would consider

• moving the session database files onto different disk arrays (not partitions on the same volume)
• adding more RAM in case it is read load, not write load causing the problem

The first option includes moving your user databases and tempdb to different disk arrays too. That is, one file per array basically.

I wouldn't think about either of these because you don't add any new resources:

• another SQL Server instance on the same machine
• using virtualisation for my SQL Servers

Answer 2

You need to figure out why the connections are timing out before you conclude that isolation is the answer. I've used session state on the primary instance for a very heavy used web farm app and never considered putting it on its own instance.

So my recommendation is getting to the bottom of the issue instead of blindly throwing resources at it and hoping that helps. If you have major blocking or certain types of waits, it might not help at all.