search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.9k stars 751 forks source link

Re-indexing a databases table by size #46

Open ChrisAVWood opened 6 years ago

ChrisAVWood commented 6 years ago

Please look at re-indexing a databases tables/indexes based on the size of the index. If you re-index smaller tables/indexes first then you reduce the chance of the database expanding because you don't have enough free space for larger tables.

BrentOzar commented 6 years ago

Boy, am I confused. Say you have:

How does it matter what order you do the rebuilds in? When the 40GB object gets rebuilt...

ChrisAVWood commented 6 years ago

Brent,

Most databases don't come that way in my experience but if they did they problem comes if the 40Gb is called A40 and the other tables are called B10, C10, D10 and E10. The way I understand how Ola's utility works is it would re-index the A40 table first needing, based on the 125% figure, another 50Gb when you only have 10Gb free. So your database is now 140Gb. If we started with a 10Gb table then it would need 12.5Gb so we expand to say 105Gb. Lets say we reduce the space needed by 25% from each rebuild.

So if A40 goes first we expand to 140Gb with now 60Gb free. If we start with B10 we grow to 105Gb with now 17.5Gb free. We don't need to expand for C10, D10 and E10 and now we have 25Gb free. Now we touch A40 and we expand to 125Gb and finish with 45Gb free. If my calculations are correct I don't need to have another 15Gb of free disk space.

Do you agree?

Chris

From: Brent Ozar notifications@github.com Sent: April 26, 2018 9:46:48 AM To: olahallengren/sql-server-maintenance-solution Cc: ChrisAVWood; Author Subject: Re: [olahallengren/sql-server-maintenance-solution] Re-indexing a databases table by size (#46)

Boy, am I confused. Say you have:

How does it matter what order you do the rebuilds in? When the 40GB object gets rebuilt...

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://nam03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Folahallengren%2Fsql-server-maintenance-solution%2Fissues%2F46%23issuecomment-384690861&data=02%7C01%7C%7C7fbbfa7c6b50455cd7c208d5ab8ced5c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636603544123427239&sdata=szjtST3t96zt4zOaig36ytvas2uSZ9%2Fg13crFA%2BrpqU%3D&reserved=0, or mute the threadhttps://nam03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAgjvadCcph_eZfUQFMmGeMcabgKZqpe9ks5tsevngaJpZM4Tlfso&data=02%7C01%7C%7C7fbbfa7c6b50455cd7c208d5ab8ced5c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636603544123583490&sdata=XpA%2BuiL2Jxlxig1ChAGWMuizoDAE8tcLvuVZmwiPdW0%3D&reserved=0.

BrentOzar commented 6 years ago

Chris - no, I don't agree, heh, but I'll make popcorn while you talk with Ola about it. 😀

ChrisAVWood commented 6 years ago

Brent,

Have I interpreted how re-indexing works?

Where I work the data sizing is not an exact enough science so we have to be careful with disk.

Chris

From: Brent Ozar notifications@github.com Sent: April 26, 2018 10:05:53 AM To: olahallengren/sql-server-maintenance-solution Cc: ChrisAVWood; Author Subject: Re: [olahallengren/sql-server-maintenance-solution] Re-indexing a databases table by size (#46)

Chris - no, I don't agree, heh, but I'll make popcorn while you talk with Ola about it. 😀

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Folahallengren%2Fsql-server-maintenance-solution%2Fissues%2F46%23issuecomment-384697255&data=02%7C01%7C%7Cad57dbb5c7654126313b08d5ab8f99eb%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636603555600023919&sdata=SsjwMdhwHqEvK3L6vnv2Ya%2FC2J5n0hjJNYTYg%2FFwDFw%3D&reserved=0, or mute the threadhttps://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAgjvaVn5uAfFVQWV-Qae5T8HHekQK7p_ks5tsfBhgaJpZM4Tlfso&data=02%7C01%7C%7Cad57dbb5c7654126313b08d5ab8f99eb%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636603555600023919&sdata=SrH75Jbr2%2BKxNr4gLuFQzn%2FQl9dLGKCxQAlWrPBbhjI%3D&reserved=0.

drstonephd commented 6 years ago

Perhaps expand the discussion to include sort in tempdb effects and recovery mode? Do you want to include the effects on the database and tempdb log file sizes? I say popcorn, pizza, and beer...with popcorn optional.

olahallengren commented 6 years ago

There is also the avg_page_space_used_in_percent and the fillfactor to consider. The size of an index could change when you rebuild it.

SlavaSQL commented 4 years ago

That is very good request to sort performing indexes by size within the database.

@ChrisAVWood I'd rather start from the biggest index first, because there will be more probability to fit smaller indexes in free space after the biggest one is done.

ChrisAVWood commented 4 years ago

Slava, The method we used before we discovered Ola would be smallest first to gain some space without expanding the database. Chris

Get Outlook for Androidhttps://aka.ms/ghei36

From: SlavaSQL notifications@github.com Sent: Thursday, February 6, 2020 12:58:55 PM To: olahallengren/sql-server-maintenance-solution sql-server-maintenance-solution@noreply.github.com Cc: ChrisAVWood chrisavwood@outlook.com; Mention mention@noreply.github.com Subject: Re: [olahallengren/sql-server-maintenance-solution] Re-indexing a databases table by size (#46)

That is very good request to sort performing indexes by size within the database.

@ChrisAVWoodhttps://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FChrisAVWood&data=02%7C01%7C%7Cb9415cad0cdc4ae889a908d7ab3effd4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637166159369110255&sdata=ZD2yrFZgdYt6amz3LmrYZVdUeAmJN80GzRL3IllYCEI%3D&reserved=0 I'd rather start from the biggest index first, because there will be more probability to fit smaller indexes in free space after the biggest one is done.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Folahallengren%2Fsql-server-maintenance-solution%2Fissues%2F46%3Femail_source%3Dnotifications%26email_token%3DAIEO62I5RMXBUACGNMLJ7UTRBRTX7A5CNFSM4E4V7MUKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOELASHWQ%23issuecomment-583082970&data=02%7C01%7C%7Cb9415cad0cdc4ae889a908d7ab3effd4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637166159369110255&sdata=4nsNu14xpaZqYQM5VgtqNJ7jIZ9Yo%2F0f6cF%2FOMPkgEE%3D&reserved=0, or unsubscribehttps://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAIEO62PC3HT6JJD3ZFX5STLRBRTX7ANCNFSM4E4V7MUA&data=02%7C01%7C%7Cb9415cad0cdc4ae889a908d7ab3effd4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637166159369120260&sdata=Q1SToUQuKYHAzQq%2Beaf7rcK3IUNxMxhIHR9iLvbZZDs%3D&reserved=0.