SQL reindex and fragmentation

Hi, I have the following script that i run on my SQL database
            DECLARE @TableName varchar(255)
            DECLARE TableCursor CURSOR FOR
            SELECT table_name FROM information_schema.tables
            WHERE table_type = 'base table'

            OPEN TableCursor
            FETCH NEXT FROM TableCursor INTO @TableName
            WHILE @@FETCH_STATUS = 0
            BEGIN
            DBCC DBREINDEX(@TableName,' ',90)
            FETCH NEXT FROM TableCursor INTO @TableName
            END

            CLOSE TableCursor
            DEALLOCATE TableCursor

To try and imporove performance, would i need to rebuild each index on every table?
I have a stored proc which executes the above code. Does the code above rebuild/reorganize each index in each table as well?
for example
GO
ALTER INDEX [I_CustomerID] ON [dbo].[tblabc] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
I am using SQL express 2008.
thanks
LVL 4
CraigLazarAsked:
Who is Participating?
 
EvilPostItConnect With a Mentor Commented:
You should do both rebuild and reorganise based upon how fragmented they are.

Although unless you are using SQL Server 2000 you should not be using the DBCC commands to rebuild indexes.

In SQL 2005 and later use ALTER INDEX

To find out how fragmented the indexes are use the following DMV.

SELECT * FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL)

The options that i have initialy filled in all NULL are explained in the below link.

http://msdn.microsoft.com/en-us/library/ms188917.aspx

General rule of thumb is to reorganise anything under 20% fragmented and rebuild anything over. But this is just a general guideline and you should use what you feel is appropriate.

Hope this helps.
0
 
kamindaCommented:
Instead of relying on a SP you can automate this process by scheduling a SQL Server job. Use Maintainance Plan Wizard to configure a job to reorganise and rebuild the indexes. Anyway we should note that Index reorganize is not supproted to use Parrellel process but Rebuilding does. So if your database is not that big and few indexes and you have a multiprocessors server (these days every server do) it is sometimes efficient to do a rebuild rather than a reorganize.
0
 
CraigLazarAuthor Commented:
hi EvilPostIt:
is there a specific reason not to use DBCC?
if i use alter index, will this automatically defragment the table?

thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
EvilPostItCommented:
Those dbcc command are depricated now. ALTER INDEX is the new way of defragmenting indexes.

If you have a look at the below link, it notes at the top that it will stop being used in future versions. So probably best to get to grips with the new way.

http://msdn.microsoft.com/en-us/library/ms181671.aspx

@kaminda i generally find it better to use an sp rather than a maintenance plan as they are quite locked down on how configurable they are.
0
 
CraigLazarAuthor Commented:
@kaminda
i am using Sql Express which does ot come with teh maintenance plan offerngs that are in fuull sql management studio.

thanks
0
 
Anthony PerkinsCommented:
For a more sophisticated solution that takes in account your fragmentation/SQL Server Edition in order to do a rebuild vs an online reorganization, see here:
Index Optimization
http://ola.hallengren.com/Documentation.html
0
 
CraigLazarAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.