reindex

Question

reindex

risingflight143

SSC Enthusiast

Points: 189
More actions
March 4, 2016 at 12:27 am

#309164

Hi everyone i want to reindex plz guide me with the command
Object Schema Object Name ObjectId IndexName Avg_Fragmentation_in_percent page_count
monitor PerfSampleMed 1630628582 IX_PerfSampleMed 99.337 147680
Is this right syntax to approx
ALTER INDEX IX_PerfSampleMed ON TABLE PerfSampleMed REBUILD

Viewing 8 posts - 1 through 7 (of 7 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

risingflight143 (3/4/2016)
Hi everyone i want to reindex plz guide me with the command
Object Schema Object Name ObjectId IndexName Avg_Fragmentation_in_percent page_count
monitor PerfSampleMed 1630628582 IX_PerfSampleMed 99.337 147680
Is this right syntax to approx
ALTER INDEX IX_PerfSampleMed ON TABLE PerfSampleMed REBUILD

You needed to provide the schema name and leave out the word TABLE:

ALTER INDEX IX_PerfSampleMed ON monitor.PerfSampleMed REBUILD;

If you are on Enterprise Edition you can consider doing an online rebuild:

ALTER INDEX IX_PerfSampleMed ON monitor.PerfSampleMed REBUILD WITH (ONLINE = ON);

ALTER INDEX (Transact-SQL) - SQL Server 2008 R2

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

If you're on a Standard/BI Edition of SQL Server you'd better use the REORGANIZE operation. It won't interrupt the application's work with the table as the REORGANIZE is an online operation. Don't forget to update the statistics after REORGANIZE because it doesn't update them.

If you're on an Enterprise, you can use REBUILD with the ONLINE option.

Ref: https://msdn.microsoft.com/en-us/library/ms188388(v=sql.100).aspx

Igor Micev,My blog: www.igormicev.com

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Instead of messing with a custom script, I'd just use one that is pre-built. Minion Reindex [/url]is my current favorite.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Grant Fritchey (3/4/2016)
Instead of messing with a custom script, I'd just use one that is pre-built. Minion Reindex [/url]is my current favorite.

Hi Grant,

Can you put here some advantages of the Minion reindex over the Ola's maintenance solution?

Igor Micev,My blog: www.igormicev.com

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Igor Micev (3/4/2016)
Grant Fritchey (3/4/2016)
Instead of messing with a custom script, I'd just use one that is pre-built. Minion Reindex [/url]is my current favorite.
Hi Grant,
Can you put here some advantages of the Minion reindex over the Ola's maintenance solution?

Sorry to jump in...

I think one big disadvantage with Minion (not sure if all his tools require it, but some do) is you have to enable xp_cmdshell, a deal-breaker in a lot of shops including mine.

Ola's does not require xp_cmdshell but does make extensive use of undocumented stored procedures to manage files in the file system. A lesser evil to xp_cmdshell but possibly a concern for some.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 6

Orlando Colamatteo (3/4/2016)
Igor Micev (3/4/2016)
Grant Fritchey (3/4/2016)
Instead of messing with a custom script, I'd just use one that is pre-built. Minion Reindex [/url]is my current favorite.
Hi Grant,
Can you put here some advantages of the Minion reindex over the Ola's maintenance solution?
Sorry to jump in...
I think one big disadvantage with Minion (not sure if all his tools require it, but some do) is you have to enable xp_cmdshell, a deal-breaker in a lot of shops including mine.
Ola's does not require xp_cmdshell but does make extensive use of undocumented stored procedures to manage files in the file system. A lesser evil to xp_cmdshell but possibly a concern for some.

You and everyone is welcome.

I see that solution is becoming mentioned more frequently and want to see what is it that offers more or less ... Thanks.

Igor Micev,My blog: www.igormicev.com

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

Orlando Colamatteo (3/4/2016)
Igor Micev (3/4/2016)
Grant Fritchey (3/4/2016)
Instead of messing with a custom script, I'd just use one that is pre-built. Minion Reindex [/url]is my current favorite.
Hi Grant,
Can you put here some advantages of the Minion reindex over the Ola's maintenance solution?
Sorry to jump in...
I think one big disadvantage with Minion (not sure if all his tools require it, but some do) is you have to enable xp_cmdshell, a deal-breaker in a lot of shops including mine.
Ola's does not require xp_cmdshell but does make extensive use of undocumented stored procedures to manage files in the file system. A lesser evil to xp_cmdshell but possibly a concern for some.

That's true. I'll leave it to the writers of the program to defend it. I did write a review of a previous version here for SSC[/url]. I detailed a lot of what it does.

My short opinion, it offers a lot more control and flexibility than Ola's script. It has a lot more reporting capabilities than Ola's script as well.

I'm not knocking Ola's script. You won't see me post "Don't look at Ola's software" but will instead see "And you should look at Minion".

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning