Iterate and reorganize subset of database tables

Question

Iterate and reorganize subset of database tables

Viewing 4 posts - 16 through 18 (of 18 total)

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

ktflash Default port Points: 1400 More actions · Answer 1

ScottPletcher wrote:

Why are using this setting: SORT_IN_TEMPDB = OFF?
That's generally a very bad idea overall, esp. for larger indexes.

Not sure, im just using the following which was suggested earlier unless i made a mistake, which resulted in this execution:

EXECUTE [MASTER].dbo.IndexOptimize
@Databases = 'prodmd01', -- or just 1 db
@Indexes = @CURRENT_DB_SCHEMA_Table, 
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_OFFLINE', 
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE', 
@FragmentationLevel1 = 30, -- change to a better value
@FragmentationLevel2 = 50, -- change to a better value
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

ktflash Default port Points: 1400 More actions · Answer 2

Duplicate Post i made because i couldnt see my own post/page 2 option.

Please ignore

This reply was modified 2 years, 5 months ago by ktflash.

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

forgot to add that option to the list.

to sort it out add

@SortInTempdb = 'Y'

to that list of options

with regards to that long run - get a copy of sp_whoisactive and if it happens again use it to see what is locking it.

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

ktflash wrote:

Or how does the REBUILD over there interact with action thats happening on the server? from what ive been told its a very active table even at night when the job is running (select, delete, update, insert), could it have been deadlocked somehow?

Rebuild an index offline will lock the table. If the table is very high usage and is large, it would be better to let it fragment and rebuild it after you've done a couple of things...

Figure out WHY the index is fragmenting and try to fix it. On that note, did you watch the flick I gave you a link to earlier? That might help explain.
Are you actually having performance issues due to the "logical" fragmentation". Like Brent Ozar would say, if your not then "What problem are you trying to solve" by de-fragmenting the index?
What Fill Factor does the index have? If the index hax a 0 Fill Factor and you're rebuilding to get rid of the fragmentation, then you're just perpetuating the fragmentation AND you're likely making it much worse because a rebuild at Fill Factor 0 will remove ALL free space just like Reorganize does.

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