My Thoughts & Views

Archive for December 13th, 2007

How To: Re index Entire database

Applies to : SQL Server 2000/ 2005

Recently i moved some database to new server and wanted to reindex entire Sql Server database, as people were complaining about slow response for our .NET WinForms application.

I thought it might save some body’s hours of time who is searching for the solution.
Problem :
You want to increase the performance of your database so you move Sql Server from one machine to another machine which has higher capacity than the older one. But after migration users start complaining that their application response is slow.

Soultion :
This is what i done to solve the above problem. I read somewhere that re indexing and reorganizing will help to improve some performance. So here is how to do it.

System Stored Provedure : sp_MSforeachtable

This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set.

EXEC [sp_MSforeachtable] @command1=”DBCC DBREINDEX(‘?’)”

To see progress messages while it is running, try this version:

EXEC [sp_MSforeachtable] @command1=”RAISERROR(‘DBCC DBREINDEX(”?”) …’,10,1) WITH NOWAIT DBCC DBREINDEX(‘?’)”.

You can Re index the Live Databases as well.Each table will be locked up for the duration of the reindexing. Users will be locked out if they are hitting that table.

If you know any other solution to such problems then please share it here so that it helps people to solve such problems.

Blog Stats

  • 71,043 hits
December 2007

Top Clicks

  • None