Post reply

MS SQL options to handle a large table, from the options listed below

  • September 8, 2024 at 9:37 am

    #4455554

    Hi,

    I have a SQL Server database with a table that contains 50 million rows, I expect the row count to growth to 700 million rows by the end of my processing cycle.  The current table performs well, and it’s been tuned with the appropriate indexes.

    In order to scale the database, and for performance, I am considering the implementation partition tables, however, I keep reading about downsides.  I wanted about your experiences and preferred approach between the options listed below, in the context of implementation ease, efficiency and subsequent maintenance:

    • Partitioning table
    • Index views
    • Sharding
    • Filtered Indexes
    • Partitioned Views
    • In-Memory OLTP

  • September 9, 2024 at 10:10 am

    #4455750

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • September 9, 2024 at 1:50 pm

    #4455828

    If it is truly indexed appropriately (and filtered indexes may be a part of that), then none of the other approaches may be needed to maintain performance at that level.

    What is your transaction isolation level?

    Making sure your I/O (network & disk speed) are up to the increased data flow will be important factors.

    Indexed views may help with specific queries. Partitioned views, having less restrictions than partitioning, might help with specific queries.

    Sharding? Another even more complicated form of partitioning not directly supported by SQL Server. If you have billions of rows, and very isolatable subsets of data (multi-tenant, geographical divisions where application servers might reduce latency if near the data), then maybe it could help performance. But SQL Server probably isn't the platform for that.

    Partitioning can be valuable with archive/deletion performance. It is usually not going to have a good impact on query performance.

    You'll probably find few use cases and few advocates for in-memory OLTP.

     

  • September 12, 2024 at 3:29 pm

    #4457014

    Thanks for the reply Ratback.  I did index the table (primarily using non-clustered index) which help tremendously, with inserts and updates, which are the primary transactions on the table.  The problem that I am facing is that the table in question now grown to 98,000,000 rows, and performance of updates and inserts has degraded.  Usually I find that the performance issue is resolved by rebuilding the highly fragmented indexes, however, I noticed that some of the indexes will not rebuild at all, that is, during index rebuild attempts the system just hangs for hours and the rebuild never occurs...:(  I end up using sp_loopup and sp_whoz to identify the pid and kill the process.

    Regarding your earlier question about my current transaction isolation level is set to Read Committed.

    I think that I might need to breakup the large table into smaller tables to gain back the performance and the ability to perform maintenance (index rebuilds) as needed.  But, I am hesitating on this approach, any thoughts on this regards will be greatly appreciated.

     

  • September 12, 2024 at 8:32 pm

    #4457084

    I just wanted to add to ratbak's post recommending indexed views - these are less useful on SQL Standard. SQL Standard doesn't automatically use an index on a view, you must explicitly state you want to use it. Plus, all objects in the view will get a performance hit for CRUD operations as SQL will need to try to add them to the index on the view.

    Mind you, indexes in general slow down CRUD operations.

    My thoughts on index rebuilds hanging is that something is wrong with the SQL instance. How often do you run CHECKDB? If it is run frequently, is it coming back nicely or is it giving you errors? I have done index rebuilds on some of our large tables (not 100 million records, but 73 million records) and it completes in a few minutes. I've never seen an index rebuild hang for hours like that. Mind you, when I do my index rebuilds, I have downtime. Nobody is using the system while my maintenance is happening. I would test things on a test system and see what you can do to improve the index. I would restore the backup onto a test system and do some analytics on it. Try creating a duplicate index and see how long it takes as that shouldn't take longer than rebuilding the existing one. If it does, it may make sense to drop the index and create it...

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • September 15, 2024 at 12:28 am

    #4457611

    Hi Mr. Brian,

    Thanks for your reply.

    Since this is a non-production/non-critical database, it's in a development sandbox, I been running CHECKDB once a monthly or as needed.  Would you recommend a weekly frequency?  Before the table grew to 98 million rows, index rebuilding took a few minutes, similar to your findings.

    I will run CHECKDB and will gladly report any reported errors.

  • September 16, 2024 at 3:14 pm

    #4458757

    I ru ln CHECKDB nightly on my systems. I want to find out about works early. But it really depends on your risk tolerance.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • September 17, 2024 at 4:56 am

    #4458934

    rafter wrote:

    Hi Mr. Brian,

    Thanks for your reply.

    Since this is a non-production/non-critical database, it's in a development sandbox, I been running CHECKDB once a monthly or as needed.  Would you recommend a weekly frequency?  Before the table grew to 98 million rows, index rebuilding took a few minutes, similar to your findings.

    I will run CHECKDB and will gladly report any reported errors.

    Although it's not a substitute for CHECKDB, periodically checking the msdb.dbo.suspect_pages table can give you a super early warning that something is wrong.

    https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server

    https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/suspect-pages-transact-sql

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 17, 2024 at 5:32 am

    #4458943

    1. How many Megabytes does your current 50 million row table have for the Clustered Index?
    2. How many rows per batch will typically be inserted into the table?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply