2

I have several vendor databases that have most (99%) of the tables clustered on GUIDs. Currently we are rebuilding the tables every weekend. Most tables are 80% or more fragmented by this time.

Instead of this endless cycle, would we be better off just not rebuilding?

Would it be more beneficial to update statics more often instead of rebuilding the indexes?

I have read Stop Worrying About SQL Server Fragmentation by Brent Ozar, but it doesn't really give an answer to my question. I was hoping for a more complete article and possibly one that was updated more recently.

I am trying not to do this as I know it is not necessary. If it helps to understand my situation better please see the beginning part of this article again from Brent's blog. I would like to have a formal plan with articles and use cases for this to propose to management and go through the proper documentation before making the change.

I am working to design a new maintenance plan for our production databases. Our only maintenance is the weekly rebuild. There are several (30+) databases that are all about 700 GB-1.2 TB. The tables range from 100-300 GB in size for the largest.

I would like to have a better maintenance plan that provides better performance in the end.

3
  • I can sympathize. I would first get a baseline of acceptable performance. In addition, catalog the work that is being done during your weekly rebuilds, and how that impacts server performance. Then institute the stats updates and show how not only is it less impactful to server performance, it also provides the same (if not better) query performance, depending how often you do it. Jun 22, 2016 at 20:29
  • Hi there. I was just wondering if you had any feedback for either my answer or @jyao's answer. If so, please comment on the answer(s) so that we can update if need be. Thanks :) Nov 1, 2016 at 17:04
  • What's your top wait type, and how many hours of that wait type do you have per hour on the clock?
    – Brent Ozar
    Nov 2, 2016 at 0:09

3 Answers 3

1
+50

Statistics are used by the Query Optimizer to help estimate row counts for operations so that it can determine the most efficient means of accomplishing that operation.

Since you are using GUIDs as the Primary Key in these tables, it is highly doubtful that you are doing any range operations / multi-row operations. Since GUIDs have no inherent order, you are really only ever doing single-row operations. And since these are Primary Keys, they are "unique" and any value only exists once, or not at all, hence your operations are either going to affect 1 row when filtering on the PK, or all rows if not filtering on anything (I am ignoring other filters for the moment since those would use statistics for another index, or an auto-created statistic on a column, neither of which are impacted by this particular issue). In this sense, merely updating statistics isn't going to help much (or at least shouldn't).

At the same time, since operations against these PKs are singleton operations (i.e. seeks), those are not greatly impacted by fragmentation. In this sense, rebuilding the Clustered PKs is not going to have a terribly noticeable affect on those operations. HOWEVER, rebuilding (at least occassionally) still has merit.

So my advice is this:

  1. The easier but less overall effective path:

    Since your system is currently suffering from a rampant page-split debacle, you need to free up that wasted / unused space as there is a lot of wasted space across many data pages that are not only filling up the Buffer Pool, but its also making backup/restore operations take longer. So, try doing index REORGANIZE operations for a few weeks and then a REBUILD. I'm not sure what Edition of SQL Server you are using, but if not using Enterprise then REBUILD operations are offline, yet REORGANIZE operations are online.

    Doing online operations not only reduces the need for the maintenance window, but they can be done more often throughout the week. You can even stagger which indexes get rebuilt when and perhaps pick the top N index, based on fragmentation level, and REORGANIZE a certain number of them each night.

  2. The harder but much more effective path:

    Even if the REORGANIZE and occasional REBUILD plan helps, you are still really just using a bucket to scoop water out of a boat that has a hole in it. But your system is growing and so that "hole" is getting bigger. All of this REBUILD / REORG stuff is merely an effort to live with a bad situation. It might work for a while, it might even work forever, but there could easily come a point when it does not work. The absolutely better approach is to actually fix the bad situation, not just sweep it under the rug.

    That absolutely better approach is to remodel the tables to use INT / BIGINT columns for the Clustered PKs. If the app code uses the GUIDs, or if the GUIDs are known by external systems and hence referenced by external systems, then you can still do this major modification: just keep the GUID values in one table, create a NonClustered Index on the column, and then do a lookup on those values at the beginning of operations to translate them to the internal INT / BIGINT values. This will shrink the size of all but a few tables (especially when considering that NonClustered Indexes have the Clustered Index keys duplicated in them, hence most of your NonClustered Indexes are 16 bytes larger than the sum of the index keys, when they could be just 4 or 8 bytes larger if the Clustered Key was INT or BIGINT, respectively). Smaller tables take up less space in the Buffer Pool, hence pages stay cached for longer and queries are more efficient. Backup and restore operations are also faster. Index REORG and REBUILD operations are also faster.

I have suggested approach #2 before to you in this answer: Best fill factor for GUID clustering key. So, while approach #1 might help, I don't think your situation will drastically improve until moving forward with approach #2. And I do fully understand that it is much easier said than done since I don't have to deal with time / resource constraints and other complicating factors. BUT, I will say that if management is not keen on pursuing such a large project, they need to consider the value-proposition of having a faster system, spending less money on disk space (data files, log files, and backups), reduced down-time / maintenance window, reduced recurring support time due to you needing to "fix" this ever-worsening situation at least once per year, etc, etc, etc. In the long-term, this approach is actually cheaper. This is your best "maintenance plan" that provides for the best performance :-). And it can be accomplished in phases, so it doesn't have to be done in one massive release.

3
  • Been there done that, I have to say approach 2 is most likely un-doable because those dbs are from 3rd party vendors, which means, strictly speaking, you are not even allowed to add an index, let alone changing the table structure. IMHO, readjusting fill factor and with pad_index on is the right approach to minimize the page split for this GUID column PK.
    – jyao
    Oct 28, 2016 at 16:32
  • @jyao I had not understood that the DBs were 3rd party, though now that I look again, I guess that is what the O.P. meant by "vendor" databases? In which case I agree that option 2 is not feasible. However, adjusting FILLFACTOR, depending on the INSERT / UPDATE patterns, is likely of little benefit, or at least of very temporary benefit. But given how large the tables are, it is likely a setback given it will reserve space on all pages, many of which are not being touched, which is a very high price to pay for delaying inevitable page splits. Hence option #1. Oct 28, 2016 at 16:39
  • For large tables in OLTP, usually it means there are lots of inserts to cause the table to the current size. With this assumption (of course we do not need to argue about exception cases), it is absolutely beneficial to the system as a whole to avoid page splitting, which is not only a big cost to INSERT but also expensive for SELECT. With proper FILL FACTOR set, you may not even need to do frequent maintenance as long as page splits does not occur too much.
    – jyao
    Oct 28, 2016 at 16:47
0

When a table has a GUID column as PK, usually it means the SELECT is not based on this GUID column, this PK is mainly for FK purpose to join with other tables. I'd recommend the following:

  1. Set up proper monitoring like (XEvents or trace) to check page splits, and if there are many page splits, adjust your fill factor / pad_index parameter. Otherwise, no change is needed.
  2. If possible, change the GUID column from PK to just a Unique Index, and promote another integer or similar column to PK, this is at least transparent to the application and thus no function impact.
  3. If possible, at least change your GUID column with a default of newsequentialid() if currently not so.
  4. Same to #2, if possible, leave the GUID as PK, but do not create a clustering index on it instead choose a clustering index on other possible column(s). This change is also transparent to the application and thus should not cause any functional difference.
0
  • As this is a vendor database, you don't have the option of implementing a change to use NEWSEQUENTIALID or a INT/BIGINT
  • The side effect of not re-indexing will be the database size being larger than it might otherwise be
  • Performance of key lookups on the GUID column is unlikely to be adversely affected by fragmentation
  • What problem is the index rebuild causing you? Why do you want to stop doing that maintenance. Is it the log traffic being sent to an availability group replica. An index reorganize would help this scenario
  • If you do want to stop the rebuild and simply update statistics. Use the Ola Hallengren maintenance solution, particularly the example D. Update modified statistics on all user databases:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

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.