Make queries and BCP faster on a very large table

Question

This question is somewhat of a continuation to this: Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that question here for legibility.

I've written an application with a SQL Server backend that collects and stores an extremely large amount of records. I've calculated that, at the peak, the average amount of records is somewhere in the avenue of 3-4 billion per day (20 hours of operation).

My application receives data, chunks it into batches of ~100k records and creates BCP-ready staging files, moves them to the server and SQL Server uses BCP to move the data into a production table. With that solution I've successfully moved 5.2 billion rows into my production table with little to no backlog of BCP files. I have BCP running continually as a SQL Agent job.

My production table has the following schema:

CREATE TABLE [dbo].[SignalValues](
    [Timestamp] [datetime] NOT NULL,
    [SignalId] [uniqueidentifier] NOT NULL,
    [SignalValue] [nvarchar](4000) NULL,
    [SignalValueId] [uniqueidentifier] NOT NULL
)

Because of the volume of records, the table is partitioned on [Timestamp] every half hour from 2016-01-20 21:00:00.000 to 2016-06-15 08:00:00.000. (~11k total partitions).

My problem is twofold:

1. Querying the table in any way takes eons.
2. While queries are running against the table, the BCP processing basically stops. It's like all processing power goes to the query and I get a files backlog.

How do I speed up query performance? Users will mostly query based on [Timestamp] and SignalId. I created a non-clustered index on [Timestamp] but it didn't seem to speed things up that much. Seems to be that the more indexing I apply, the slower the BCP inserts which creates a file backlog. Any index suggestions?

My assumption of partitions is that internally they act as separate tables so if I were to query [Timestamp] for 2016-01-25 22:01:00.000, it should only lock the one partition it needs for the query and leave the other partitions unlocked. Is my assumption incorrect? It would seem so with the behavior seen in 2) above. How do I force SQL Server not to lock during a SELECT query (WITH (READPAST) and WITH (NOLOCK) didn't help)?

I should note that rows in this table will never be updated or deleted. Also, it's possible that data could be inserted from a time in the past, depending on what the applications providing my application with data set as the timestamp for their rows. This unfortunately negates any possibility of partition switching unless I were to do some crazy magic.

The average number of rows per partition is ~70 million.

Based on the feedback from my previous question (above), I was able to beef up my server considerably. The following is my server's system information:

Also, I've configured SQL Server as follows based on feedback:

• All database-related files are connected to a SAN which is connected to the system via fibre channel
• My database's data file is on its own drive alone -- a RAID 10 array of 4 spinning disks
• tempdb consists of 6 files (half the number of logical processors), each sized at 512mb, all on one drive -- a RAID 0 array of 2 spinning disks
• All transaction logs live on their own drive -- a RAID 1 array of 2 spinning disks.

The table does not have a clustered index. I'd had a clustered unique index (the PK) before but the uniqueness constraint caused inserts to take 10 minutes for 100k records in a table with 800m rows. My understanding was that a clustered index creates a uniqueness constraint internally per the heading, "Can you create a clustered index on a column with duplicate values?" from this page:

14 SQL Server Indexing Questions You Were Too Shy To Ask

I could be completely wrong though.

Let me know if I'm missing anything.

Answer 1

This is a long shot but seen as you are running SQL 2014 Enterprise I would have a test with using a Clustered Columnstore Index on this table and see if this improves performance for you.

Especially considering that you are using the table for selects and bulk inserts only - no updates and no deletes.

You will have the added advantage of taking a bit of load off the spinning disks and moving it more towards the CPU / Memory which you seem to have a decent amount of.

Answer 2

Is going to be hard to find something that excels both at data ingress (accepting +50k rows per second) and ad-hoc querying an arbitrary EAV time series (timestmap, signal_id, signal_value). I would give clustered columnstore a try. Clustered columnstore would leverage segment elimination on timestamp and clustered columnstores also have better concurrency characteristics for bulk insert. Make sure to read SQL Server clustered columnstore Tuple Mover and you achieve a sufficiently high BCP rate to create directly compressed segments. If not, staging, bcp+rebuild in staging and then partition switch is a must.

With the layout you have I would have much preferred having clustered index on timestamp. As is now, your best hope is to leverage partition elimination. There are many way this can misfire (Why Doesn’t Partition Elimination Work?) and even when it works it still causes concurrency problems. Specially since most times the user queries concern recent data, this leads to blocking against the BCP inserts.

I would make sure my BPC does not escalate to table X locks. The Data Loading Performance Guide is mandatory reading, but be aware that the whitepaper is rather obsolete when it comes to columnstores so only applies to the current structure. Pay special attention to the 'Bulk Loading a Partitioned Table' chapter.

I would consider a much coarser partitioning granularity, perhaps 1 partition per day. Having too many partitions brings significant runtime cost.

And, as with any performance issues, measure.

I would not be shy in considering radical different approaches. Cassandra for instance can ingress data at very high rate, and has the advantage of a "throw more hardware at the problem" scale-out solution. Queryability is not Cassandra's forte, but once you buy into that ecosystem there is Spark+Hive+ORC/Parquet and these can query data pretty fast.

Answer 3

• Querying the table in any way takes eons.

• While queries are running against the table, the BCP processing basically stops. Its like all processing power goes to the query and I get a files backlog.

• How do I speed up query performance?

• I should note that rows in this table will never be updated or deleted. Also, it's possible that data could be inserted from a time in the past

First, try a few combinations of clustered index - even a non-unique clustered index.

Second, you're dealing with a huge amount and rate of data - if you definitely do not need and will not need nvarchar, change to varchar, and save a lot of space and time.

Third, if your main table data is never updated, and never deleted, and if you don't care if you get an entire file's contents in at a time or not (as your chunking it indicates), then consider moving all queries to READ UNCOMMITTED/WITH (nolock), since you won't have any update statements anyway.

Fourth, consider each BCP file load creating and filling a new staging table; get BCP away from your main table. You can even spread those over different sets of physical disks later to run multiple BCP operations in parallel, then. You'll need to put in an "I'm ready" signal on those staging tables, either by sp_rename after the BCP's done, or some controlling/logging table (started on table X102 from file Uyi, finished table X102, X102 moved to main, X102 dropped, etc.).

ETA: Fifth, it sounds like you've got some resources available - adding RAM was great. Make sure tempdb is on different disks than the main tables, and make sure the staging tables are on their own independent disks as well. Perhaps also put some solid state disks in for both tempdb and those BCP staging tables, which should limit the expense. If you have the budget, all solid state, of course. Note that for at least tempdb, you can - and should - use local SSD, not SAN SSD - it's faster for the same expense, and there's no need to replicate tempdb in most cases.