CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2014
Sam Bin Ham
Sam Bin Ham

Reputation: 449

SQL database re-Indexing script

What is happening when running below script against SQL Server databases.

My script:

set arithabort on
set quoted_identifier on    

use TestDB

declare objcur cursor for
    select name
    from sysobjects
    where type = 'u'
    order by name    

declare @obj sysname    

open objcur   
fetch next from objcur into @obj    

while (@@fetch_status = 0)
begin
    dbcc dbreindex( @obj ) WITH NO_INFOMSGS   

    fetch next from objcur into @obj
end   

deallocate objcur

Upvotes: 2

Views: 4087

Answers (2)

Rajesh Bhat
Rajesh Bhat

Reputation: 811

You can use below statement for rebuilding all indexes.

Exec sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'

Upvotes: 2

S3S
S3S

Reputation: 25152

You are

  • Setting ARITHABORT on, which terminates a query when an overflow occurs. By default, this is ON so is likely redundant
  • Setting QUOTED_IDENTIFIER on, which tells SQL Server to follow ISO standards about quotation marks. i.e. allows you to use double quotes to delimit identifiers and forcing you to use single quotes for literal strings. This must be on when you are changing indexes on certain types of columns, like computed columns, indexed views, etc. On is the default.
  • Creating a cursor (loop) for all tables
  • Looping over each table and rebuilding each index, for each table

This isn't a great idea because you have zero thresholds for starters. This means you will rebuild every index regardless if it's fragmented or not, and regardless of the number of pages for this index. So, if you created an fresh index on a fresh table and ran this command, it'd rebuild it. Also, this is an off line operation. So, a shared lock is held for non-clustered indexes for the entire duration of the rebuild which prevents any modifications to the table...

I'd look into using Ola's scripts. They are far superior. Then i'd read up on the myths and misconceptions of fragmentation from Brent Ozar and again in this post.

Upvotes: 2

Related Questions