Bouke Bruinsma (4/21/2014)
This question is about a dedicated SQL Server instance with the main reason being performance issues related to tempdb.Our company sells a range of products all using SQL Server. The database is crucial as it contains all data plus settings. The workload of the products is OLTP plus small datawarehouse load being small reports run in snapshot isolation.
Our requirements state a dedicated database server or a dedicated instance. Of course dedicated hardware is the best option leave aside discussions on CPU, disks etc. There have been issues in the past with Tempdb that have lead to the requirement of a dedicated instance. Unfortunately the details are not longer known but it’s summarized with ‘locking issues on tempdb’. An actual example where the discussion gets hot is a customer that purchases a second of our products. The existing database server is dedicated for our software. Adding an extra instance on this server seem pointless to me. I use the metaphore of creating an extra frontdoor to create moor room in your house. I have looked in detail at the existing server and there seems to be room for more workload so I would add the database to the existing instance.
Only recently we apply or advise best practices for tempdb (dedicated drives, multiple datafiles, tuned growthrates and initial sizes).
Would there be any reason to have a second instance on the same server to prevent performance issues related to tempdb? I’m thinking of issues like Bitmap Allocation Page contention, row versioning load, query spills, sorts/hashes, CHECKDB, rebuild indexes with sort in tempdb etc. I know all these can be monitored in detail (and I will) but I have done a bit of research and have never come across any suggestion or evidence that an extra instance on the same server would help for tempdb performance issues.
To be honest, if the products you're selling have that much of a problem with TempDB, then something is VERY wrong with the code. Using a dedicated server is, at best, a temporary patch for a problem that will only get worse.
My recommendation is that the company should spend some time and money finding the code that causes such contention and fix it because if someone told me that a product needs a dedicated server, I probably wouldn't buy the product even if it were the only show in town.
Fixing such things isn't as big a bullet to bite as you would think IF you hire the right person for these problems. Someone like Kevin Boles could probably solve most of your issues in a week or two.
--Jeff Moden
Change is inevitable... Change for the better is not.