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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EvilPostItCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.