Bulk insert and fast ordered search large data 3B+ records

Question

Scenario

We want to study behavior of some similar entities that changes based on time.

Currently I'm using MS SQL Server 2016

Table Structure

CREATE TABLE [dbo].[BehaviorHistory](
    [EntityId] [uniqueidentifier] NOT NULL,
    [BehaveFactor] [bigint] NOT NULL,
    [TimeOffset] [bigint] NOT NULL,
 CONSTRAINT [PK_BehaviorHistory] PRIMARY KEY CLUSTERED 
(
    [EntityId] ASC,
    [BehaveFactor] ASC,
    [TimeOffset] ASC
)

Sample Query

We want to see this similar entities how often repeat same behavior as our test subject

SELECT *
FROM 
    (
        SELECT 
        EntityId,
        BehaveFactor,
        (TmpOffset.PerviousMainTimeOffset - TmpOffset.CurrentMainTimeOffset) MainDeltaTime , 
        (TmpOffset.PerviousSubTimeOffset - TmpOffset.CurrentSubTimeOffset) SubDeltaTime 
        FROM
            (
                SELECT
                LAG(main.TimeOffset) OVER (ORDER BY main.EntityId ,history.TimeOffset,testSub.TimeOffset) As PerviousMainTimeOffset,
                LAG(testSub.TimeOffset) OVER (ORDER BY main.EntityId ,history.TimeOffset,testSub.TimeOffset) As PerviousSubTimeOffset,
                main.EntityId AS EntityId,
                main.BehaveFactor as BehaveFactor,
                main.TimeOffset AS CurrentMainTimeOffset,
                testSub.TimeOffset AS CurrentSubTimeOffset
                FROM BehaviorHistory AS main
                INNER JOIN SampleBehaviorHistory AS testSub ON main.BehaveFactor = testSub.BehaveFactor
            ) as TmpOffset
    )AS MatchTable

Where 
MainDeltaTime = SubDeltaTime --repeated behavior in same time order
AND MainDeltaTime <> 0 --exclude repeated behavior at the same time

Problem

We currently inserting large amount of data into BehaviorHistory table its size is like 100 GB more with more than 3 Billions records in last 8 months or so.

The sample query performance is good (like 300ms) if we have index but when we create non clustered index on BehaveFactor (includes TimeOffset) the insert operation timeout on this table, so currently I have two DB one without index used for inserting data and another that every day get copy of inserted DB and create index on it and used for reports.

Error

With no index its works fine, when we try to insert in a indexed table after a couples of bulk insert (eg 2000 records) we got this error

[fnInsertBehaviors(EntityId : 1705438)]
#     1 : The underlying provider failed on Open.
#     2 : Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=58416; handshake=9; 
#     3 : The wait operation timed out
#

Looking for solution to provide real time reports (not based on yesterday data) and avoid two DB and scheduled backup restore to create indexed table.

~600 records inserted per second in a certain period of time in a day.

With non clustered index, insert query timeout and without it performance is out of question. Insert fails in normal mode with no lock on table, normal insert fails when we have no query running against that table.

Also I need to consider growing database maybe next year its size is 300 GB so I have to find a solution that can handle that much data before it gets too late and system fails.

Answer 1

It looks like this question will be closed soon, but you could consider using a nonclustered columnstore index. The business case that you are describing with this question seems to match Microsoft's vision for it:

Real-time analytics uses an updateable columnstore index on a rowstore table. The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. This minimizes the performance impact of both workloads running at the same time. SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. With this design, it is possible and practical to run analytics in real-time on up-to-date data. This works for both disk-based and memory-optimized tables.

There's a lot of information contained in that link and it links out to a bunch of other blog posts, so it's difficult to summarize it all. You might experience the following benefits:

• CCIs have a typical compression ratio of 10:1, so it might be smaller than the nonclustered index that you want to build. It'll certainly be smaller than a separate copy of the table.
• You can use the compression delay option discussed here to possibly lighten the load on the server when inserting data.
• Converting the table to a CCI will enable batch mode for your query. You may see performance benefits from this, especially because the window functions are eligible for batch mode as of SQL Server 2016.

However, it also might not be helpful for your workload. The only way to know is to test. If you really need to constantly run a query against a 3 billion row table without any filters perhaps a data model change is needed. The insert timeout that you saw also hints at a deeper problem, but I don't have the knowledge or additional context needed to to troubleshoot that.