Need help with Index Rebuild and Reorganize job set up.

Question

Need help with Index Rebuild and Reorganize job set up.

Dan121

SSCommitted

Points: 1616
More actions
October 26, 2014 at 9:04 am

#312483

Hello,
I tried to find online the script I need but unable to do so. I even looked Ola Hallengren's scripts but so complicated to make my own.
Please some one post the script here, I would be grateful.
Requirements: (this is on A database with all tables)
1. Organize <30% fragmented
2. Rebuild > 30% fragmented
3. pages > 3000
4. Sort on TempDB 'Yes'
5. Rebuild index Online = 'yes'
Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

dinsab11 (10/26/2014)
Hello,
I tried to find online the script I need but unable to do so. I even looked Ola Hallengren's scripts but so complicated to make my own.
Please some one post the script here, I would be grateful.
Requirements: (this is on A database with all tables)
1. Organize <30% fragmented
2. Rebuild > 30% fragmented
3. pages > 3000
4. Sort on TempDB 'Yes'
5. Rebuild index Online = 'yes'
Thank you.

Have you looked up any information on sys.dm_db_index_physical_stats so that you actually know what it is that you're using? Have you looked at Ola's code to see what you could pull out of it? If you want to make your own, why are you asking for someone to post a script?

Google sys.dm_db_index_physical_stats and look at example "D".

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

dinsab11 (10/26/2014)
I even looked Ola Hallengren's scripts but so complicated to make my own.
Please some one post the script here, I would be grateful.
Requirements: (this is on A database with all tables)
1. Organize <30% fragmented
2. Rebuild > 30% fragmented
3. pages > 3000
4. Sort on TempDB 'Yes'
5. Rebuild index Online = 'yes'

If Ola's script looks too complicated to you, I suggest that you don't even venture creating your own.

To fulfill your requirements you just need to call Ola's script with these parameters:

EXEC IndexOptimize

@databases = 'ALL_DATABASES',

@PageCountLevel = 3000,

@SortInTempdb = 'Y'

The rest is provided by the default values for the parameters.

Hope this helps

-- Gianluca Sartori

Dan121 SSCommitted Points: 1616 More actions · Answer 3

Dan121

SSCommitted

Points: 1616

October 27, 2014 at 8:07 am

#1754940

Thank you guys.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

spaghettidba (10/27/2014)
dinsab11 (10/26/2014)
I even looked Ola Hallengren's scripts but so complicated to make my own.
Please some one post the script here, I would be grateful.
Requirements: (this is on A database with all tables)
1. Organize <30% fragmented
2. Rebuild > 30% fragmented
3. pages > 3000
4. Sort on TempDB 'Yes'
5. Rebuild index Online = 'yes'
If Ola's script looks too complicated to you, I suggest that you don't even venture creating your own.
To fulfill your requirements you just need to call Ola's script with these parameters:
EXEC IndexOptimize
@databases = 'ALL_DATABASES',
@PageCountLevel = 3000,
@SortInTempdb = 'Y'
The rest is provided by the default values for the parameters.
Hope this helps

The reason why I suggest that everyone learn how to write their own is so that they have a better understanding of the system as a DBA. It's kind of like a Jedi Knight making his own light saber. 😉 If all one knows how to do as a DBA is use canned scripts, then you stand a strong chance of getting canned. 😀

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Good point, Jeff.

However, building a sophisticated script like Ola's is not something I would recommend to a novice. I would start from something easier.

-- Gianluca Sartori