SQL Server 2016 tempdb latency

Question

Hi,

I get the below results for tempdb latency

db_names DISK_Drive Read Latency Write Latency Overall Latency Avg Bytes/Read Avg Bytes/Write Avg Bytes/Transfer
tempdev_data7 E: 25 169 152 64530 16461 22167
tempdev E: 27 176 158 64761 16433 22152
tempdev_data3 E: 25 168 151 64559 16408 22084
tempdev_data4 E: 25 169 152 64596 16427 22120
tempdev_data8 E: 25 169 152 64636 16471 22186
templog E: 3 32 30 66006 61341 61627
tempdev_data6 E: 25 169 152 64632 16498 22229
tempdev_data5 E: 25 168 151 64885 16555 22333

These are bad results. We have around 25ms in the prord machine and below for our heavily used UAT env which are better:

This is for a 20TB database, so most main tables are 50 billions rows on average and with heavy tempdb usage in general. Are there any ease tweaks to bring these times down, any obvious fault? The team also tested SSD and found that performance wasn't great and switched back to SAN which is where these results come from.

Do you have any ideas what might be causing it? Around 150ms will not call it great to say the least!

db_names DISK_Drive Read Latency Write Latency Overall Latency Avg Bytes/Read Avg Bytes/Write Avg Bytes/Transfer
templog E: 4 3 3 364102 61339 61370
tempdev_data2 E: 3 54 48 65374 15580 21348
tempdev_data3 E: 3 56 49 64921 15496 21183
tempdev_data7 E: 3 57 50 64506 16233 22144
tempdev_data5 E: 3 57 50 64792 16393 22324
tempdev_data4 E: 3 57 51 64704 15975 21756
tempdev_data8 E: 3 59 52 64787 15875 21753
tempdev_data6 E: 3 61 54 65122 15996 21867
tempdev E: 3 63 55 65291 16538 22689

Answer 1

You need to clarify (mostly to yourself) what you are really asking:

a: Is the new disk subsystem much worse than the old?

b: Is SQL Server 2016 by itself somehow worse when it comes to I/O for tempdb?

c: Is your load on SQL Server 2016 (compared to 2008) causing more I/O on tempdb so much to that you push the disk subsystem and in the end see the latency figures you see?

The answer for b: is "No".

As you probably realize, we can't answer a: and c:. That is for you to determine.

For a: you take SQL server out of the equation and run a disk performance test on each disk subsystem. Make sure you run it under realistic conditions (your SAN might be under heavy load during daytime, so a fair comparison should be done at daytime, for instance).

For c: you probably want to compare the old and new SQL Server version with the same load and installed on the same machine and see if only the version of SQL server is what is causing the difference. Or perhaps you already have the answer, from question a:.

Answer 2

I agree with Tibor. There are too many unknown in the equation. Is it a matter of queries regressing? Or is the hardware not up to standards?

I think the best is to find a test that you can run on both environments. That test would simply cram as much data you can into tempdb, and see what latency get you in both environments. If they are radically different, you have a case with the IT department. If they are about the same, the tempdb latency may be a false lead.

You said that you have the old cardinality estimator, but you don't say whether you are still on compatibility level 100. Keep in mind that, if you are on compat level 130, you also buy in an all optimizer fixes since the release of SQL 2008. One of them may be backfiring for you.

When it comes to the hardware, the IT may have lured when they say that is all bare metal. Or they have set up synchronous replication for the tempdb disks - which is not really that useful but which can slow things down.