Rebuild All Indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Rebuild All Indexes

Author

Topic

poser
Posting Yak Master

124 Posts

Posted - 2007-12-17 : 14:28:47

I am upgrading from SQL2000 to SQL2005.
I have restored my 2000 db to 2005.
I have changed the Compatiblilty level to 90.
Now I need to reindex.
How do I reindex all the tables at once?
Thanks for ALL your help
r/p

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 14:30:27

http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspx

Just make the fragmentation level very small.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

poser
Posting Yak Master

124 Posts

Posted - 2007-12-17 : 15:53:46

Thank you very much...
r/p

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-04 : 21:45:26

I have been looking at this thread and when would you change it to do reorganize or rebuild.

Also is there a way to change the script to print out the command rather than run it i tried to change the

EXEC (@SQL)
and put in PRINT (@SQL) but nothing printed out.

If you have a fill factor of 80 on one table and one of 90 on another will it use this.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-04 : 22:08:21

You can do ALTER INDEX ALL ON <table> REBUILD and generate a script for all tables and execute it. The fillfactors will be maintained.


SELECT 'ALTER INDEX ALL ON ' + [Name] + '  REBUILD ' 
FROM Sysobjects
WHERE Type = 'u'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-05 : 00:41:24

quote:
Originally posted by TRACEYSQL

I have been looking at this thread and when would you change it to do reorganize or rebuild.

Also is there a way to change the script to print out the command rather than run it i tried to change the

EXEC (@SQL)
and put in PRINT (@SQL) but nothing printed out.

If you have a fill factor of 80 on one table and one of 90 on another will it use this.

Thanks

There is no rule for when to do REBUILD vs. REORGANIZE. I always do REBUILD though since it runs so fast on my databases.

Not sure why PRINT @SQL didn't work for you as that's what I use when testing. Perhaps you don't have any fragmented indexes that match the input parameters.

The code uses the original fillfactor, so you don't have to worry about changing it in this script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-05 : 09:52:55

I will try again with the print on another database that i restored.
Must admit i always do the REBUILD too.

This script looks good as it will only do the ones that is required to be rebuilt rather then whole database.

I did not get what the
@statsMode - LIMITED, SAMPLED or DETAILED
was ?

When running the rebuild index using the maintenance plans run for hours if i do rebuild on all indexes table it runs 10 minutes or so on a small database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-06 : 13:47:23

quote:
Originally posted by TRACEYSQL

I did not get what the
@statsMode - LIMITED, SAMPLED or DETAILED
was ?

Those parameters are passed to sys.dm_db_index_physical_stats. Take a look at sys.dm_db_index_physical_stats in BOL for more details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-07 : 13:28:18

SELECT 'ALTER INDEX ALL ON ' + [Name] + ' REBUILD '
FROM Sysobjects
WHERE Type = 'u'
When i run this i got my tables and then i got
ALTER INDEX ALL ON DTPROPERTIES REBUILD (Do i just ignore this one).

--On BOL there a nice script.
But it fails on line 22 on a ) but i cannot see it.
Can you see if it works for you.
i change the EXEC to a print so it does nothing

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ---here it fails.
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
--EXEC (@command);
print @command
PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-07 : 13:47:05

Why would you want to go through sysobjects? And yes you ignore dtproperties, but why are you doing that?

The BOL script sucks. It doesn't have nearly as many features as mine.

Why don't you just try out my code? It's been thoroughly tested and gone through several revisions, mostly based upon comments in my blog. I've
had it running in production for over a year now.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-07 : 15:25:53

Tkizer was following example in thread.
I have your script here on my machine

(Few questions:
@defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)

When to run as REORGANIZE and when to run this as REBUILD).

Put the script in master? then run
exec isp_ALTER_INDEX 'mydatabasename'

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-07 : 15:36:32

As mentioned earlier, there is no rule for when to run REORGANIZE or REBUILD.

The script should be put into some kind of Admin database so that it can be recovered without having to recover master. User objects should not be put into the master database for this reason.

In the comment header block of the stored procedure is an example of how to run it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-07 : 15:46:48

Let me use that script and try it out I have 100 databases to move from SQL 2000 so i be using it daily.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-07 : 15:53:21

You might be interested in this then:
http://weblogs.sqlteam.com/tarad/archive/2007/11/28/60417.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

pootle_flump

1064 Posts

Posted - 2008-01-07 : 16:03:52

Not my rule but....
Kalen Delaney suggests reorganise for 5-20% logical fragmentation and rebuild for greater. Also remember that reorganise will not allocate any new pages to an index so you cannot reduce the average density with reorganise. Reorganise is automatically online.

We use rebuild only but we are not 247 so there is no downside for us.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-07 : 16:14:38

It also helps to just run rebuild only if you have Enterprise edition like we do. Index operations are then performed online where possible (using my script at least).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-08 : 09:56:50

Been running the script all day yesterday and today.
One fails on
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'pk_qrac3' because the index contains column 'failurenote' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

Is there a way to detect this and change the alter index to offline for these indexes.

Thanks

sql_noob
Starting Member

9 Posts

Posted - 2008-01-08 : 11:55:40

i use the script from BOL that i modified. i have an admin db on each server where i run the system view to dump the data into a table and then there are a few extra columns that i update with the date, db name, table name and index name to make it easier to read.

then part 2 is grabbing the latest data to run alter index. we run only rebuild here since then we don't have to run update statistics. some db's we rebuild anything over 20% others anything over 5% or 10%. through past experience i've noticed that we had 30% frag and still had index seeks.

i save the data because it's a nice record of table growth and when i have time i plan to learn RS and AS to view this data in a nice graph

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-08 : 15:54:11

quote:
Originally posted by TRACEYSQL

Been running the script all day yesterday and today.
One fails on
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'pk_qrac3' because the index contains column 'failurenote' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

Is there a way to detect this and change the alter index to offline for these indexes.

Thanks

The BOL script doesn't handle this situation, but mine does.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-09 : 09:11:07

Is there a way to detect in the script to do offline if the condition occurs

I like to start using it...but it not set to offline the script fails.

Thanks so much

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-09 : 20:09:43

Well my code does that. So check it out and grab the code that is needed. It's quite complex what has to happen for all of the different conditions that may occur when running ALTER INDEX. Hence the reason just to use my working script rather than roll your own.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources