1

I'm using Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

I have some very large tables (the tables by themselves can be more than 700gb) on a database that's 2.5 terabytes.

I've been using some scripts for index maintenance. Right now I'm doing :

  • Reorg if fragmentation is > 10%
  • Rebuild if fragmentation is > 50%.

It works fine most of the time but for my tables that are 700gb or larger, I think that 10% will be a hard percentage to hit. I was wondering if I should lower that percentage for those big tables or if it's fine to leave it at that threshold.

I have to add that my servers have SSD drivers, but only 128 gb of RAM because it's Standard edition. So there's no way that the tables can fit in RAM, in case of large queries (like reporting), it has to scan/seek the indexes.

I don't want to hit the 665 error file system limitation due to fragmentation. https://learn.microsoft.com/en-ie/troubleshoot/sql/admin/1450-and-665-errors-running-dbcc (I'm starting to add filegroups to the database to avoid that error, as I'm having it once in a while on those large tables that are part of the primary file group.)

So should I do a custom percentage for index maintenance on very large tables? Or it should be fine?

2 Answers 2

1

It really depends on how transactional your tables are. If they are frequently being updated and inserted into, you'll definitely hit the 10%. If you're not hitting the 10% then it's probably not even worth it to lower to 5% because that means your tables are fairly well unfragmented already.

I've personally worked on a very similarly sized database to your example, also SQL Server 2016 standard, on a server that only had 16 GB of RAM. Our database was fairly transactional (adding about 1,000 new records every few minutes) and we were hitting the 10% threshold for reorgs every night. Because the operation was so heavy and took a while, I actually did the opposite and increased the fragmentation threshold for reorgs to 20% for our larger Tables' indexes. This proved a lot better for our business because the different in performance was negligible and it generally allowed for the larger tables to not be reorganized until the weekend.

Worrying about index reorgs, and even more so rebuilds, is on the low end of things to optimize for. Even Brent Ozar says not to worry about it. :)

4
  • Yeah I was following Brent Ozar's advices but I've started to have the error 665 about file fragmentation on an NTFS partition so I started doing more frequent reorg. Have you had this issue before? I'm trying to split my database in order to stop having this error and not having to reorg all the time. Dec 17, 2020 at 15:04
  • 2
    index reorgs and rebuilds only affect the placement of database pages within the database data files. they do not affect file fragmentation within the filesystem.
    – sqL_handLe
    Dec 18, 2020 at 17:48
  • 1
    @DaniellePaquette-Harvey To sqL_handLe's point, I just looked further into the error you mentioned and it sounds rather obscure and unrelated to your index fragmentation question. Are you currently running into error 665 yet or you're trying to be pro-active?
    – J.D.
    Dec 18, 2020 at 21:58
  • @J.D. Yes I am running into error 665. I've been splitting my database into multiple filegroups and activating some maintenance of old data (as management gave the "ok") and it seems to be doing the job for now. I'm not getting the 665 error anymore but not sure if it's gonna come back or not. Dec 21, 2020 at 14:05
1

While I agree with what @BrentOzar says I also agree that this is a particular scenario. The question is pertinent because you don't usually deal with a 700Gb table.

In my opinion you should:

  1. Partition the table because is too big. That is exactly the reason why Microsoft created this feature. Plus you are on SQL Server 2016. Lucky you!
  2. Now that the table is partitioned you should write a logic that check every partition and decide to reorganize if > 10% or rebuild if > 50%
  3. Now that the logic is written you should share such logic with the community and release it open source because you are not the only one facing this particular challenge : Rebuilding partitioned table clustered indexes

That's why I find your question very pertinent. I believe we are going to a World where more and more databases will have, sooner or later, terabytes of tables.

The data are growing faster than our adaptation to it and we don't have the tools to deal with such issue.

1
  • 1
    Yes good point about partitioning the tables, 100% agree, that is something I did in my similar above scenario. It makes the maintenance a lot more manageable. Even so, I still agree with Brent Ozar to the scale of a 700 GB Table (I actually had a 1.3 TB big table in my use case). It made sense for us from an availability standpoint to reorganize more so around 20% fragmentation and there was no measurable performance difference on querying the Table when it was 10% fragmented vs 20%.
    – J.D.
    Dec 18, 2020 at 21:53

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.