0

(Some context first because while I hope the questions are general enough we're always told the solutions depend on context. There's no DBA in this scenario.)

This is a [very small office] database, used 5 days a week, 8 hours per day, by a handful of clients running a program that mostly does listings and small numbers of inserts/updates at each time.

The database is a single file, has around 25 tables, and it currently runs to 20 GB, 19 of which are used by the same table, which is the one with a blob (image) column.

There is a daily backup of the data file, but in all of its more than 10 years there has never been any maintenance, other than moving it from server to server - it started on SQL Server 2005 / Windows SBS 2003 or something, has changed 3 times, and is now on SQL Server 2019 / Windows 2019.

I thought the 20 GB was too much (it's not a problem for backups, the system is fast enough), but when I saw the number of records on the table with the blob I concluded that it's about 300 KB per blob, which seems fair. It would be a great candidate for compression or off-database storing, but I do not control the program that uses the database.

The index for that table is 96% fragmented. The standard advice would be to do a rebuild and update statistics, but I have a number of questions and here they are, finally, I hope helpful to all:

  • Does rebuilding the index not risk inflating the DB file size by a large factor? (even tho it's never been shrunk, and the current size seems to be filled with valid data rather than empty) I'm not keen on bumping the DB up to, say, 25 GB, especially given that data is seldom deleted from this DB, only added. I could tolerate a 1 or 2 GB increase; my issue is that I have no idea what to expect.

  • Given I have loads of idle time to use, is there a disadvantage to just do a reorganize? From the description it sounds like a less intrusive operation and only less recommended because it's an older, slower process during which the DB will be less responsive.

  • Can I combine a reorganize with updating the statistics, or does this question betray I really have no idea how indexes work?

  • Given the usage pattern of the database, is it a risk not having backups of the logs and such?

0

Does rebuilding the index not risk inflating the DB file size by a large factor? (even tho it's never been shrunk, and the current size seems to be filled with valid data rather than empty) I'm not keen on bumping the DB up to, say, 25 GB, especially given that data is seldom deleted from this DB, only added. I could tolerate a 1 or 2 GB increase; my issue is that I have no idea what to expect.

  • The general rule is you will need 100% the size of your index as it is today and an additional 20% for overhead. Also keep in mind rebuilds are fully logged, so your transacton log will inflate also a good bit. Best Practices say to let the data and log file expand as needed making sure that there is plenty space on the drives.

Given I have loads of idle time to use, is there a disadvantage to just do a reorganize?

  • I would perform a reorg at the very least which can be done anytime. At least that would alleviate some of the fragmentation you are seeing until you can schedule a time to rebuild it.

Can I combine a reorganize with updating the statistics, or does this question betray I really have no idea how indexes work? Yes -

  • Rebuilds automatically run statistics on your indexes.
  • Reorgs require statistics run manually (exec sp_updatestats)

From the description it sounds like a less intrusive operation and only less recommended because it's an older, slower process during which the DB will be less responsive.

  • Reorgs defragment the leaf level of clustered and non-clustered indexes by physically reordering the leaf-level pages to match the logical order (Less intrusive and is done online)

  • Rebuilds drops and rebuilds the indexes (Most intrusive and blocking can occur and can be done offline or online depending on your edition)

  • Both remove fragmentation but rebuilds are most efficient

Given the usage pattern of the database, is it a risk not having backups of the logs and such?

  • If the Recovery Model for the database is FULL, you should be taking transaction log backups. I am assuming the recovery model is Simple in which case transaction log backups cannot happen. Also, without transaction log backups you cannot perform a point in time recovery if ever one was needed. This is all directed by the RTO/RPOs dictated by the business.

Rebuild Index RebuildReorgIndex

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.