How to mantain index in SQL and size of Database

Question

How to mantain index in SQL and size of Database

andre1992

SSC Enthusiast

Points: 183
More actions
December 2, 2020 at 4:58 pm

#3816113

Hi everyone,
I'm trying to figure out how to manage the maintenance of the indexes I just created in some tables of my database.
Once I run the Rebuild, the size of the database increases and by shrink, the indexes are fragmented again.
Is there a way to clean up the indexes without affecting the size of the database?
Thanks!

Viewing 14 posts - 1 through 13 (of 13 total)

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

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Please explain why you are shrinking your database. About the only time you need to shrink your database is when something happens in it to cause file size bloat that you are not going to ever use again, or you are completely out of space on the drive and have to make the file smaller.

andre1992 SSC Enthusiast Points: 183 More actions · Answer 2

Because rebuilding the indexes considerably increases the size of the database.

And over time it could be a problem.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

andre1992 wrote:

Hi everyone,
I'm trying to figure out how to manage the maintenance of the indexes I just created in some tables of my database.
Once I run the Rebuild, the size of the database increases and by shrink, the indexes are fragmented again.
Is there a way to clean up the indexes without affecting the size of the database?
Thanks!

There's a way to fix this particular problem. What is the size of your largest clustered index and what is the size of the whole database?

--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)

ScottPletcher SSC Guru Points: 100947 More actions · Answer 4

Are you using data compression? If not, that should be the first thing to check toward reducing total table size.

If you want to improve overall performance, we'd need to look at all existing indexes, particularly the clustered index, as that is the single most critical factor for best overall table performance.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 5

I use this index maintenance solution - https://ola.hallengren.com/.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Evgeny Garaev wrote:

I use this index maintenance solution - https://ola.hallengren.com/.

Please answer my questions in my previous post so I can help you fix your problem. 😉

--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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Sorry... wrong person...

@ andre1992 ... Please answer the questions from my first post on this thread so that I can help you solve this problem.

--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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

Evgeny Garaev wrote:

I use this index maintenance solution - https://ola.hallengren.com/.

That's nice but it won't help solve the problem the OP posted. Also, while OLA's good scripts are the "gold standard" for index maintenance, the supposed "Best Practice" settings of using REORGANIZE between 5 and 30% logical fragmentation and using REBUILD after 30% is actually a "WORST Practice", but people keep using Ola's code to do that worst practice. Those recommendations were forced from Paul Randal (he actually has an article on the subject) by MS way back when most people's largest indexes were tiny and more than 2 decades ago. Somehow, the world has embraced that recommendation as a "Best Practice" and it's anything but. It even says so in the documentation but no one reads the footnote in the documentation.

--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)

andre1992 SSC Enthusiast Points: 183 More actions · Answer 9

The heaviest clustered index is around 8 Gb while the database weighs 17 Gb.

Thanks Jeff.

Michael L John One Orange Chip Points: 27038 More actions · Answer 10

andre1992 wrote:

Because rebuilding the indexes considerably increases the size of the database.
And over time it could be a problem.

Ok, that's normal. It's what you want. You have a 17 GB database. How big is the drive that you are worried about it over time?

I'm sorry, but 17 GB is nothing. I'm not understanding the concern about something this small.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

andre1992 wrote:

The heaviest clustered index is around 8 Gb while the database weighs 17 Gb.
Thanks Jeff.

I should have asked the same question that Michael asked about total disk size and how much of that is available as "free space".

As you've found out, if an index is over 128 extents (only 8MB) and you REBUILD an index, a new index (which occupies space) will be created and the old index won't be dropped until the new one commits. Of course, that means that you have free space the size of the old index and, in your case, that increases the size of your database by 33%.

As you've also found out, shrinking a file can cause massive fragmentation (due to "index inversion") and you can end up in a pretty vicious cycle that does nothing for you.

One way out of this is to use file groups. Take that largest 8GB Clustered Index and move it to a separate file group and, when you want to do an Index REBUILD, create a new fill group, rebuild to that and then drop the now-empty file group that it was originally in.

Some will tell you to use REORGANIZE, which does it's trick in the space of the original pages of the index, instead of doing REBUILDs. I strongly suggest you don't fall for that because REORGANIZE doesn't work the way that most people claim it does. It's also really tough on the log file. In fact, I've found it to be twice as bad on the log file than REBUILDing indexes in the FULL RECOVERY model.

Here's what I currently do with my largest indexes... and, to be clear, you're still going to need some headroom but that headroom will be shared by all of the databases.

Let's say that your largest Clustered Index is on a table called "Audit". Here's how to setup the cycle of things I spoke of above.

Create a new file group slightly smaller than the current size of the Clustered Index. Call the File Group "Audit1" (naming convention is tablename followed by a single digit number, the first one will be "1").
Do a CREATE INDEX using the "Drop Existing" option to "rebuild" the index onto the new file group. This will also have the same effect as a REBUILD does for defragmenting.
The action in Item 2 above free's up the space it occupied in the PRIMARY file group so, if you need the space, go ahead and shrink the PRIMARY file group at this point.
Of course, shrinking the Primary file group will fragment the dickens out of some of your indexes. Go ahead and REBUILD them along with any other indexes that we're fragmented in the normal course of business. When you REBUILD them, do the rebuilds in order from smallest to largest in an effort to use some of the free space created by tightening up the smaller indexes for the larger indexes.
You may still end up with some free space but it won't be half the size of the database like it was before. I'd not worry about that little bit of freespace because, ostensibly, your data is growing and will eventually use that space.

So, now you have two file groups... the Primary and Audit1 and they need to be treated separately in the future.

To handle rebuilds in the Primary, do like you did before. Rebuild fragmented indexes in order from smallest to largest.
To handle rebuilds of the Clustered Index on the Audit table (which now lives in the Audit1 file group), to the following...
1. Do like you did before... create a new file group/file slightly smaller than the size of the clustered index except call this new file group "Audit2".
2. Instead of using ALTER INDEX REBUILD, do like you did before with CREATE INDEX using the "DROP EXISTING" option to move it to the new "Audit2" file group/file.
3. When that's done, drop the file from "Audit1" and then drop the file group called "Audit1".
4. Next time you need to do a REBUILD, just reverse the numbers in the steps above.

Now, a warning... if you're doing any index maintenance (due to fragmentation) on any index that has the default Fill Factor of "0" (which is the same as "100)) or an assigned Fill Factor of 100, STOP. Fragmentation is caused either by out-of-order inserts or ExpAnsive updates. REBUILDing them to the equivalent of a 100% Fill Factor simply removes all the free space from the index and they will suffer massive fragmentation right after the rebuilds because you've just removed any chance of an insert or expansive update having room to operate without a page split.

Look at the average page density of such an index... if it's below 82% and you have no clue as to why or know but can't prevent the page splits that caused it, then REBUILD those indexes to 82%. That recovers the lost disk space and still provides headroom for out of order inserts and expansive updates which, in turn, can seriously help prevent page splits and the resulting perpetuation of more page splits.

The reason why I say "82%" us that the "2" in the Fill Factor will remind you that you still have something "to do" to fix fragmentation problems for those indexes.

There's a lot more to index maintenance that the supposed "Best Practices", especially if you have ever-increasing indexes (like an IDENTITY column) that suffer from expansive update. Those NEED to be fixed to eliminate the expansive update. If you don't have time to do that, make some time. If they can't be fixed, then rebuild them at 97% (the "7" looks like a "2" but without a foot to stand on) to remind you that you have something "to do" on these indexes to prevent their fragmentation. It won't help at all to rebuild them at "82" because most such indexes suffer expansive updates shortly after the insert and inserts on such indexes always fill the pages to 100%.

And there's still more but the first part of this post will help you with not having a shedload of unwanted freespace in your database after rebuilds so you can avoid the huge mistake of using shrink-file on a regular basis.

--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)

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 12

Jeff Moden wrote:

That's nice but it won't help solve the problem the OP posted. Also, while OLA's good scripts are the "gold standard" for index maintenance, the supposed "Best Practice" settings of using REORGANIZE between 5 and 30% logical fragmentation and using REBUILD after 30% is actually a "WORST Practice", but people keep using Ola's code to do that worst practice. Those recommendations were forced from Paul Randal (he actually has an article on the subject) by MS way back when most people's largest indexes were tiny and more than 2 decades ago. Somehow, the world has embraced that recommendation as a "Best Practice" and it's anything but. It even says so in the documentation but no one reads the footnote in the documentation.

You can configure the fragmentation thresholds and many other things in Ola's solution. It is much better than the Maintenance plans solution from Microsoft.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

@ Evgeny Garaev ,

I totally agree that Ola's good code will take a huge number of different settings. There's no argument there or on the quality of Ola's code.

The problem is with the way people use it. They typically use it to defrag according to supposed "Best Practices".

--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)