CODEWITHSUNDEEP
sql-serverdatabasesql-server-2016
TChadwick
TChadwick

Reputation: 878

Microsoft SQL Server Scaling for multi-billion row table

Calling all MSSQL server gurus! I'm currently working on a project that involves a table with the following layout:

[DbNodeClosures]

This is a closure table used for storing parent child relationships and currently has the following indexes:

[IX_DbNodeClosures_ParentID] ON [dbo].[DbNodeClosures] ([ParentID])

[IX_DbNodeClosures_ChildID] ON [dbo].[DbNodeClosures] ([ChildID]) INCLUDE ([Depth], [ParentID])

[IX_DbNodeClosures_ParentID-ChildID] ON [dbo].[DbNodeClosures] ([ParentID], [ChildID]) INCLUDE ([Depth])

Queries to this table typically involve querying all children ID's using the parentID or vice versa. Rows in this table are only inserted or removed, never updated. We currently have this working and with 600 million rows, performance is still excellent, however we are about to enter a migration period where that number will exceed 1.2 billion rows, and as time goes on, potentially grow to 30+ billion rows. At 600 million rows, the current table including indexes takes about 30 GB of storage on our SQL server.

So now the real questions:

How well will this scale?

Will query performance degrade as the number of rows increase?

Will insert and delete performance slow significantly as the number of rows increase?

I have been researching options like Table partitioning (available on all versions of MSSQL server 2016+) and ColumnStore Indexes, but don't have any real world experience with them nor do I have real-world experience with scaling a MSSQL table to billions of rows. (And yes we are looking at a wholesale changeover to NOSQL for it's scaling benefits, but that will be prohibitively expensive from a development cost stand-point) I have seen that others (NASDAQ) are able to get Quintillions of rows, so I think it's possible and I'm hoping someone who does have experience in this area can offer some insight as to what they've seen as far as performance and scaling of SQL server at these levels. Thanks for any thoughts!

Upvotes: 2

Views: 1118

Answers (1)

user1443098
user1443098

Reputation: 7645

Looks like a possible application for a SQL Graph Database

FWIW we have much wider tables with billions of rows in one of our production systems and things run pretty well.

Upvotes: 1

Related Questions