We have two servers that were set very similarly, but not identically. These two servers have hugely different performance running the same application.
On server A the application runs successfully (although slowly).
On server B the application runs successfully (and a bit faster) initially, but soon grinds to a halt with SQL timeout issues.
After some investigation I found that the write latency to the temp DB on Server B was 14s (not a typo) (using the query from here: http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/). I thought that this might be down to the fact that the temp DB was on the C: drive on Server B (and the swap file was being used). So I have moved the temp DB to the D: drive (where the other database files live). After restarting SQL server the write latency was intially around 1s - still terrible from what I have read, but the application was running happily in that state so it would be good enough for now. Less than 24 hours later the write latency on the temp DB has climbed to 11.5s and we're seeing timeouts again. The temp DB write latency on server A is under 10ms.
So my question is, what could be causing this rather dramatic difference and change in performance?
My SQL is rather rusty so please don't assume that something is too obvious to be the solution!
The main differences that I know of between the two servers are:
- Server A has two disks in a RAID mirror, the mirror is then split into two partitions, whilst Server B has two disks not in any kind of RAID set up
- Server B has a couple of extra databases on it, although I don't believe that they are used (PerfStats, ReportServer, ReportServerTempDB).
- Server B has eight tempDB files, whilst Server A only has a single file
- Server B is running on a slightly stripped down database that has been reduced to a single big customer's data. Server A is processing this customer and a number of others (although the other customers take up a tiny fraction of the processing power needed for the big customer)
- The database on Server B was stripped down by a SQL specialist who may well have changed other things that I'm not aware of :( (e.g. they are probably responsible for the 8 temp DB files)
Also in case it helps, there is another desktop machine also running the application as a test. This machine appears to have a single drive split into a couple of partitions, half the RAM of server A and server B but a much more modern CPU. It is performing rather faster than either server and has a tempDB write latency of around 0.5s. Again I think this indicates that we don't need great write latency, just not so terrible that it causes timeouts.