2

Environment:

SQL Server 2019 on Windows Server 2019, on KVM backed by TrueNAS, 16 cores, 32 GB RAM. Application runs 50 parallel threads all inserting into the same massive table. This combination appears to work against the SQL Server architecture

Additional details

  • the problem table is both deep and wide - 20,000,000 rows with over 300 columns and 40-50 indexes
  • The application uses JDBC Batch API's. This particular table, due to row size, is inserting in batches of 1,000 rows.
  • Tables with more reasonable row sizes are inserting in batches of 10,000 rows
  • I can't share the actual DDL, but it's pretty mundane apart from the row simply being massive (a surrogate key BIGINT ID column, two natural key VARCHAR columns, 300 or so cargo columns, 0 BLOB/CLOB columns, then 40-50 indexes)
  • The primary key index DDL is "create unique index mytable_pk on dbo.mytable (keycolumn);"
  • The only other unique index DDL is "create unique index mytable_ndx1 on dbo.mytable (division, itemnum)";
  • The product that owns the database is used by hundreds of fortune 2000 customers, so changing hte data model is not an option for me or the product vendor.

Restrictions

  • Since the database is ultimately a third party's, any changes I make to it must be in-place. Once the data is inserted into it, I no longer have any access to it.
  • The database is owned by a third party off-the-shelf application.
  • the primary key is a sequential integer

Observations and metrics

Early in the process, we were bottlenecked on CPU resources.

Once we hit about 1,000,000 rows, we were single threading on latches, sometimes spending over two seconds in a latch, and rarely spending less than 500ms in a latch. Latching and IO buffer waits were both excessive. CPU dropped to about 12% usage.

In a second test, I dropped all of the indexes and re-ran the job. The job completed 8 times as quickly, showing zero load on the SQL server and bottlenecking on CPU on the application which is very good from the SQL Server perspective.

After reading Microsoft's literature, I came to the conclusion that the data model is working against SQL Server's indexing architecture for tuning for massive inserts.

I will not always have the option of dropping and recreating the indexes. Is there a way to tune the table to distribute the I/O

** Now to the real question **

Is there a way to tune SQL Server, under the covers, to distribute the IO so sequential numbers in an index not in the same buffer when doing massive inserts of sequential data?

4
  • 1
    Use session wait stats to assess the relative importance of latches and IO waits (remembering that PAGEIOLATCH_xx is an IO wait not a latch wait). learn.microsoft.com/en-us/sql/relational-databases/… Large tables with lots of indexes drive random IO on insert, so as Paul White notes, and because you are batching inserts, last-page insert latch contention seems unlikely. Ideally post your wait stats and the insert rates you are achieving. Feb 17, 2023 at 14:46
  • The table is a core business table in a database belonging to an off the shelf product that has over 30 years of history and hundreds of fortune 2000 users behind it. My team is not in a position to change the data model, and the product owner is constrained by the installed user base. Paul White's answer below addresses the 7 available options, of which 3 are potentially viable in this environment, and will be tested.
    – pojo-guy
    Feb 17, 2023 at 15:59
  • Those 7 are for latch contention, which we doubt is your problem. Feb 19, 2023 at 1:11
  • 1
    Empirical testing has established that two of the three solutions in that article that are possible in this environment have a positive impact on duration. While the root cause is that design of that table sucks badly, latch contention on the primary key index is in fact the visible proximal cause, and between that and the additional details from said testing, there are other solutions we are also researching and testing. I will edit and post final results if and when I can.
    – pojo-guy
    Feb 20, 2023 at 16:09

4 Answers 4

8

There are several well-known approaches to addressing last page insert contention in SQL Server.

Many of these are covered in the documentation at Resolve last-page insert PAGELATCH_EX contention in SQL Server. Summarising the options from that link:

  1. Use OPTIMIZE_FOR_SEQUENTIAL_KEY (details)
  2. Move primary key off identity column
  3. Make the leading key a non-sequential column
  4. Add a non-sequential value as a leading key
  5. Use a GUID as a leading key
  6. Use table partitioning and a computed column with a hash value
  7. Switch to In-Memory OLTP

Method 7 can also be implemented as an in-memory OLTP table to handle a high rate of ingestion with regular batch moves to the final destination table. For the very highest concurrency, use natively compiled code with the in-memory table as much as possible (including for the inserts). The frequency and size of moves is dictated by your requirements.

As mentioned in another answer, delayed durability can also improve insert performance in many cases.

Related Q & A: Solving periodic high PAGELATCH_EX Waits. Last page contention?

All that said, you haven't shown evidence of a last-page contention issue at all. More likely, you're encountering problems related to updating all those secondary indexes and a lack of memory on the instance meaning index maintenance often has to wait for pages to be brought in from storage for modification. You don't mention the type of latch you see waits on, but I imagine they'd be PAGEIOLATCH_*.

The primary solution would be to dramatically increase the memory available to SQL Server for its buffer pool so fewer IOs are necessary. Failing that, a faster storage subsystem would be required.

2

Have you tried using Delayed Durability?

When to use delayed transaction durability

Some of the cases in which you could benefit from using delayed transaction durability are:

You can tolerate some data loss.

If you can tolerate some data loss, for example, where individual records are not critical as long as you have most of the data, then delayed durability may be worth considering. If you cannot tolerate any data loss, do not use delayed transaction durability.

You are experiencing a bottleneck on transaction log writes.

If your performance issues are due to latency in transaction log writes, your application will likely benefit from using delayed transaction durability.

Your workloads have a high contention rate.

If your system has workloads with a high contention level much time is lost waiting for locks to be released. Delayed transaction durability reduces commit time and thus releases locks faster, which results in higher throughput.

1
  • Unfortunately delayed durability is not an option here. Data integrity takes priority over raw throughput. But this is very useful to know for the future. Thank you!
    – pojo-guy
    Feb 17, 2023 at 13:28
0

The short answer to your "real question" is no because contiguous keys of a disk-based b-tree index must be stored in the same page.

-1

I've never used SQL server, but your problem isn't specific to one database, so maybe this can still help.

When inserting a large number of rows per second the bottlenecks are either going to be parsing overhead (which can be parallelized), index updates (which may be parallelizable or not), primary key sequence generation, or other stuff like postgres' large object support, but that depends on your column types and database quirks. Then at some point any transactional database must generate sequential transaction log entries which is also a concurrency bottleneck.

First thing you should do is check if the inserts are grouped into transactions (not one insert per transaction). Then make sure the IO is fast, look for bottlenecks there, iowait, etc.

In a second test, I dropped all of the indexes and re-ran the job. The job completed 8 times as quickly, showing zero load on the SQL server

So that eliminates some of the candidates and hints that the problem is indices.

For example if 50 threads each insert a row at the same time, and...

  • You have a high cardinality index with each row hitting a different page in the index, then these can be parallelized
  • You have a low cardinality index, most of the inserted rows have the same value in the same column, and all these threads are fighting for control of the same index page.

This can compound with index/table page splits if your fillfactor is too high, in this case all the threads will want to insert in the same index page, and it's already full, so one thread is splitting the page while all others are waiting.

Unfortunately you didn't post the table info in the question, which you should really do. But you probably know if your indices are low cardinality or high. The first thing you could do is run the same tests again, adding the indices one by one, try to see which one causes trouble.

You can also lower fillfactor so there is less chance the inserts end up in a page that is already full.

If you find a problematic low cardinality index then you should first wonder if it's actually useful for queries, maybe you can drop it. If you want to keep it, you can hack it into a high cardinality index by adding a dummy column at the end. For example if you have an index on (category) which has few different values and causes problems for inserts, you can turn it into (category,other_column) which will work just as well for selecting based on category and might provide some extra features like sorting on other_column while selecting on category. However other_column should not be the PK or date or any other column that will have have values that end up in the same page in all your concurrent inserts, because that would be back to square one.

Next, you can try single-threading, or a low number of threads. Back to this:

In a second test, I dropped all of the indexes and re-ran the job. The job completed 8 times as quickly, showing zero load on the SQL server and bottlenecking on CPU on the application which is very good from the SQL Server perspective.

This may look nice at first glance but there's a problem here. Basically your application is doing the easy things (processing rows) and delegating the hard things (ie, concurrency) to the database. That's fine until it exceeds the database's capabilities, then it breaks down. Databases are excellent at handling concurrency correctly, but doing it fast is a very hard problem: coordinating several cores on a lock has a hard performance limit, caused by latency of communication between the cores, which is the speed of information propagation, in other words the speed of light, which cannot be negotiated with.

Locks are just memory held as cache lines in CPU caches. So a side effect of the way multicore systems work is, it's much faster for the same core to reacquire a lock it just released, because the line is still in its cache, so there is no slow inter-core communication involved. Likewise, several cores attempting to modify different parts of the same index page will result in cache line exchanges between them and lots of communication to determine what core owns what byte in that page. And that is surprisingly slow, it can take microseconds instead of nanoseconds.

In addition you have 50 client threads, so 50 server threads, and only 16 cores, so on the database server the OS will multitask the 50 threads between the 16 cores. This means the OS will end up putting one thread to sleep while it's holding a lock, and when that happens, performance is destroyed.

So the next test you can do is to compare insertion time with all your indices between these two scenarii:

  • Your current one with 50 threads

Then stop it, copy the inserted data from your main table into a temp table, truncate the main table, and insert the exact same data again with:

  • INSERT INTO yourtable SELECT * FROM temptable

In the second case you're inserting the same data. For the test to be valid it should be in the same order, so you might want to add an ORDER BY primary key while copying the rows into the temp table, so they come out in the proper order. I don't know if the tables are clustered, but you'll find a way to get the order correct.

You can also try various orders, one of the indices may be faster if data is inserted in an order that it likes.

If the second insert is much faster than the mutli-threaded one, then that will give you a clue of what you need to do. In this case that's probably a funnel, ie a process that gathers rows generated by the many threads and inserts them using a low number of threads, maybe just one.

This can simply be all the threads inserting into a non-indexed table, and a separate task flushing this table into the main one every X milliseconds.

1
  • Thank you for this detailed analysis. We have already taken these steps and delved more deeply into the database engine before asking the question. I'm not sure why this was downvoted, because it provides the necessary steps to get to the point where this question can be asked. Future viewers of the question are not necessarily starting with this baseline knowledge.
    – pojo-guy
    Feb 17, 2023 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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