Rebuild All Indexes in a Database (Tables/Views)

Question

Post reply

Rebuild All Indexes in a Database (Tables/Views)

angelrapallo 90775

SSC Enthusiast

Points: 172
More actions
September 6, 2016 at 4:04 am

#312850

Comments posted to this topic are about the item Rebuild All Indexes in a Database (Tables/Views)

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 1

SQL Guy 1

SSCoach

Points: 15979

September 21, 2016 at 11:12 am

#1902242

But what the reason of rebuilding all indexes?

dsolanki 37114 SSC Rookie Points: 43 More actions · Answer 2

Thanks for sharing query.

can you please provide benefits of rebuild indexes? i mean is there any performance advantage or any other benefits?

webtomte Ten Centuries Points: 1135 More actions · Answer 3

If for any reason a rebuild indexes must take place (Heaven knows why) I suggest an alteration so that indexes containing weird characters also will be shown.

Also specifying that this will not work on a 2008 sql server.

set @message = '- rebuilt [' +

@index_name +

'] index for ' +

@table_type + ' ' +

@table_or_view +

'...done (' +

dbo.fun_time_elapsed(@start_detail,@end_detail) + ')'

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

We've had trouble with a few indexes on occasion where a rebuild fixes it for some odd reason. So we just got into the habit of executing a job on Sunday afternoon that rebuilds all the indexes. The problems went away. Don't know why but it works so we keep doing it.

seboldj Valued Member Points: 60 More actions · Answer 5

I dropped it in and received

Msg 156, Level 15, State 1, Procedure fun_time_elapsed, Line 22 [Batch Start Line 2]

Incorrect syntax near the keyword 'declare'.

Very NEW to MSSQL.

webtomte Ten Centuries Points: 1135 More actions · Answer 6

There are two queries. The firt creates the function and the other runs it.

angelrapallo 90775 SSC Enthusiast Points: 172 More actions · Answer 7

yes you must run the first query to create the function

I rebuild my indexes every month after big loads I guess each one

must have or not have reasons to do this I simply am giving this in the hope it helps someone since it rebuilds both tables and indexed views indexes..

if it does not apply to you well let it go.....sorry...

I was wondering the same Why on Earth would Microsoft include a Menu choice for REBUILD ALL OINDEXES I am telling you these gguys at Microsoft at loosing their minds

in any case I do rebuild indexes and get benefits in speed

seboldj Valued Member Points: 60 More actions · Answer 8

GOT IT!

I worked in IT for 35 years and I always said noting is FOOLPROOF, because some fool will come along and prove you wrong.

Thank-you.

webtomte Ten Centuries Points: 1135 More actions · Answer 9

Rebuild or reorganize just for the cause of it is not something you should do.

Indexes has to monitored over time to see how and when they become a problem.

There are a lot of T-SQL queries around out there that you can run on a regular basis (without changing anything) to better help you understand what is going on.

When you know that, then you also know what indexes to rebuild and/or reorganize and at what frequense.