SQL Server - quite big data flow - Disk became the bottleneck ... what now?

Question

After a lot of searching & follow up of the system we are getting out of ideas to effienctly solve our issue ... hence this question. We suspect (are rather sure) our storage became the bottleneck of our system, but we have no experience in how to solve this.

Situation/Context => We have a single sql-server (standard edition) instance with:

• 2 x "Intel Xeon Gold 6230" CPU's => with a combined core count of 40 (80 logical)
• 192GB MEM
• 2 X 500GB SSD RAID 1 (OS)
• 3 X 2TB SSD RAID5 (SQL)
• 1 x 2TB NON-RAID

The data we are processing is a continuous flow of inserts through the day. We process pushed files on our server and 20 files are processed simultaneously, generating batched inserts. This data needs to be queried as soon as it enters the database by other processes too. To give an idea of the data flow (of the troublesome database): the "SQL Server Activity Monitor -> Data File I/O" shows now (early morning and a calm period on the system) 12MB/sec read and 150MB/sec write.

It is also important to mention that we have some indexes (including a clustered identity field) that get fragmented fast. Those indexes need to be rebuild now and then slowing the whole system down and during such periods we are getting behind on the input flow. If we don't rebuild them, the queries just slow down other systems/processes too much. These indexes (except the clustered) simply must remain and are as minimal as possible. No foreign keys are used on this part of the database. OLTP system thus.

This continuous inserted data gets divided across 4 tables in the troublesome database. Each week the tables are renamed and we keep 4-5 tables before the last one gets truncated and renamed to become the active table with most recent entries. Each of those 4 tables (and the renamed variants) are about 100GB each. Once renamed only read (still a lot per second) actions are done on these "less-recent" tables.

Things we did (all active now and above numbers are from this config):

• We isolated the troublesome database (datafile) to the RAID5 config. Other user databases & all logs are (temporary moved) to our unsafe NON-RAID config.
• The systems databases (except tempdb data + log) remained on RAID5, but don't seem to cause any mentionable I/O.
• Delayed durability is set to forced to disable transaction log delays. Data loss is never good, but in our case it isn't the end of the world and thus we preferred the gained speed.
• MAXDOP is set to 16 to avoid parallelism related latching
• A clustered index was created on an identity column and we suspect index rebuilds are faster now. Previously we worked with a heap table. We should test again without this index, but as you can imagine it takes a while to add the clustered index and thus we prefer to test all the possibilities before dropping it again.

The main issue we see in the Activity Monitor now is a big Buffer I/O fluctuation (2000-17000 ms/sec), but it is always above 1200 ms/sec once we can't process the input fast enough.

We also monitored some disk performance counters. Disk response times are fine, except during burst periods (almost continuously now) we have latencies going up to 45-60ms. Another performance counter "Avg Disk Queue Lenght" has an dramatic avg of 235 (min 0, max 27 583). We also searched for heavy queries using the DMV found in another post (https://stackoverflow.com/questions/48425245/what-can-cause-high-buffer-i-o-on-my-clients-server), but most activity comes from the inserts (most logical reads and most logical writes and most physical reads too).

After all this info, what can we do next? Could be general advice too on how to handle such heavy data flows properly, good books, courses, ... Any advice is welcome as we expect even greater data flows in the future.

Thanks in advance

Edit 2020-06-21 (additional info based on comment rois):

Hereby the wait stats. Although they will be based on activity before we did some tweaks too ... no? We do see latches now and then, but since the MaxDop change they don't have a big impact on the system as a whole (at least based on activity monitor and off course my second screenshot show latchs wait now ... :)). Further I added a screenshot of the current I/O visible in Activity Monitor. Off course during the mornings there is less input, but still a 90 mb/s of writes to the actual data file. Regarding tempdb comment: tempdb was moved to the non-raid disk (also visible on the screenshot).

Answer 1

I must say I'm not sure what is the problem you are facing. I can only give some ideas what could be wrong.

I speculated it could be tempdb. But in this case IO_COMPLETION waits would be much higher. Still I find the IO on tempdb and database puzzling.

First the CXPACKET waits. As you note you changed maxdop. If you haven't restarted the instance in the meantime or reset wait stats, it is probably so high because maxdop was 0. So generally I would ignore it. (Though you could try setting maxdop to 8 and increase cost threshold for parallelism if you haven't)

I see quite a lot of waiting for schema stability locks (LCK_M_SCH_S). It seems were some ddl changes, which blocked other processes. You mentioned you converted heap table to clustered one, so my guess is those waits are results of this conversion.

Then there is PAGEIOLATCH_EX. Which points to inserts you mentioned. In this case the first culprit would be the indexes. I would urge you to review them once more, if they are all really used and needed. Maybe some can be combined or made filtered.

I would consider compressing tables/indexes. Compressed pages take less space in memory, so maybe it would allow you to "gain" more memory. Use sp_estimate_data_compression_savings to check, if there are any savings to be had.

You don't mention any triggers. If you have any, it would be good idea to double check that they don't make anything funky.

Or you simply hit the limits of your hardware.

Answer 2

First of all thanks to @rois and @piotr for their help. The solution (in our case) was a combination of things and thanks to their help we were able to look in the right direction.

These config settings/changes provide us a lot more throughput than before. Just an important note upfront: Since we can bare a little data loss (till a backup point), we use the "Simple recovery model". This justifies some settings we did.

• RAID5 config only holds data files, intensive i/o data files will move to dedicated disk (we need to physically add them first). This will give us another performance boost as while testing we had an ever bigger throughput. During these teste we had a single DB data file on the RAID volume.
• TempDB (data file) & all transaction logs were moved to a separate disk
• TempDB holds multiple data files to improve parallel execution
• All databases have "Delayed Durability" set to Forced
• MAXDOP lowered to 8
• Heavy I/O tables now have a Clustered Columnstore Index combined with some (but less) Non-Clustered indexes
• The remaining non-clustered indexes are rebuild only once at night and we changed the fill factor to a value of 70. This gives us a fragmentation level of 60-70% after one day of input.
• A different index strategy is used. We only created indexes that were key to our throughput (for heavy i/o tables), despite some execution plans suggest indexes. Sometimes a query could run more efficiently, but when the index causes too much insert delays we won't create it.

While testing we also noticed that a fragmented index (>99% fragmentation) still gave us better performance. Probably as queries can still use the index (when covering) instead of accessing the i/o intensive data file/buffer.

Further our CPU usage is increased quite bit (announced by @piotr), but in our case the system can handle it.

Some of these individual steps can be found as guidelines all over the internet and some are really a no-brainer. But it was the combination that helped us to handle the data flow.

Our next steps are further query & index analysis and provide dedicated disks for heavy i/o databases (and maybe even tables). In memory tables (and nightly I/O) is something we will investigate on our next system as we were testing on a production system.

While solved (for now) tips remain welcome!