2

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.

| improve this question | |
  • You moved tempdb on same drive where data and log files of other databases reside this could still could cause I/O contention. Why do you require 8 tempdb files did you read the blog:sqlskills.com/blogs/paul/… – Shanky Jul 16 '14 at 9:54
  • Thanks, I don't know why there are 8 tempdb files. I had seen that blog post and am planning to try the analysis in it when I get a chance. – Dan Jul 16 '14 at 10:03
1

Looks like the disk subsystem on Server B is performing worse than on Server A but, I used to see disk issues not entirely related to disk specs. You could collect some other performance counters such as physical disk --> avg disk sec write (> 25 ms very slow), memory --> page file usage (> 70% bad), cpu --> processor queue length (> 12 very bad), memory --> pages/sec(> 600 slow, > 2500 very slow disk subsystem), sql server: buffer manager --> page life expectancy (< 300 memory issue).

You can also limit the growth of 6 tempdb data files to the size they have now. See what happens.

You should check the waitstats as well on both servers. If you see a lot of PAGELATCH_XX then you will know where to dig more. Jonathan Kehayias has a good article on this theme. And Paul Randal has also a lot of analysis which could help.

| improve this answer | |
  • This > 600 and < 300 stuff are numbers picked out of hats or based on very specific (old) machines and particular workloads. PLE < 300 isn't an issue unless you know that PLE is always much greater with the same workload. – Aaron Bertrand♦ Jul 16 '14 at 13:27
  • Agree with Aron especially with PLE it was a benchmark with SQL Server 2000 when average memory in system would be 4-6 G – Shanky Jul 16 '14 at 22:45
  • Any system that causes a PLE less than 300 for me it would say there is a memory pressure. On nowadays systems, PLE can be as high as 12000 but the question came from someone that didn't give specific hardware configuration. Instead of giving no answer, I would rather give some guidance. – yrushka Jul 18 '14 at 9:41
  • Thanks, I understand that these numbers may not be accurate for different hardware, but they are useful as a rough expectation. As it turns out the system is just about within these parameters, although disk write time is touch and go. – Dan Jul 21 '14 at 8:23
1

So in this case it does seem to have been the 8 tempDB files causing the biggest issue.

I ran the analysis suggested here http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/ and found no PAGELATCH issues and a very high proportion of PAGEIOLATCH waits (I don't remember exactly, but I think it was over 70% of the waits).

Reducing the tempDB back to a single file has reduced the tempDB write latency to just over 1s which is "good enough" for now.

Re-running the analysis above with a single TempDB file still shows 35% of waits being on PAGEIOLATCH and nothing (so less than 5%) for PAGELATCH.

I think our case is the prime example of when multiple tempDB files is a bad idea - mostly single threaded DB access doing some very large queries.

| improve this answer | |
  • We had seen this issue in our environment, but we reduced the tempdb files to half. In our case from 8 to 4 and that helped alleviate tempdb contention. – Kin Shah Jul 22 '14 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.