Paging All SQL Monkeys!

[SimonMac]

Anyone got a quick and dirty script they rely on to rebuild all the indexes in a database for SQL 2008 R2?

There is a few in google but would prefer one that comes for someone here

[eek]

Anyone got a quick and dirty script they rely on to rebuild all the indexes in a database for SQL 2008 R2?

There is a few in google but would prefer one that comes for someone here

From the people that actually post in technical....

You are a braver man than me....

[BigRed]

If you really want to rebuild them all, use a maintenance plan, for something doing only those that need it and switching between reorg/rebuild with logic for which edition and online or not use Michelle Uffords (SQLFool) script Index Defrag Script, v4.1 SQL Fool
or one for 2005 but I've lost the acknowlegement:
-- define index defrag rate for this run
DECLARE @fragrate float;
SET @fragrate = 30.0; -- set to do something if defrag rate HIGH

DECLARE @rbrirate float;
SET @rbrirate = 50.0; -- set to rebuild if defrag rate EXTREME

-- define table holding index information
DECLARE @indexinfo TABLE(objectid int, indexid int, partitionnum bigint, frag float)

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);
DECLARE @DBID int;
set @DBID = (select db_id())

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
INSERT INTO @indexinfo
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (@DBID, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @fragrate
AND index_id > 0 AND page_count > 50 AND alloc_unit_type_desc = 'IN_ROW_DATA'; --

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM @indexinfo;
OPEN partitions;

-- Loop through the partitions.
FETCH NEXT FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;

WHILE (@@fetch_status <> -1)
BEGIN;
IF (@@fetch_status <> -2)
BEGIN

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 < @rbrirate
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= @rbrirate
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 N'Executed (Defrag ' + CAST(@frag AS varchar(12)) + '): ' + @command;
END;
-- Get the next record to process
FETCH NEXT FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
END;

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

[d000hg]

If you don't trust random scripts on Google, ask on StackOverflow and rely on the peer view system to spot obvious problems.

[eek]

If you don't trust random scripts on Google, ask on StackOverflow and rely on the peer view system to spot obvious problems.

You have to be careful here and I refer you to

Not quite:

Add question to stackoverflow

Get mate to write your solution down as the solution to the problem.

Then if no one else comments you have both fixed the problem and set up your solution as best practice...

Ironically at previous clientco we discovered that a previous contractor had done exactly that.

As at the moment if I snuck a few drop table ..... commands in the script Simon would probably happily run it.

[DimPrawn]

Anyone got a quick and dirty script they rely on to rebuild all the indexes in a database for SQL 2008 R2?

There is a few in google but would prefer one that comes for someone here

This one looks good.

SQL Server Index and Statistics Maintenance

[BigRed]

This one looks good.

SQL Server Index and Statistics Maintenance

Yes, Ola's solution is what we use at my current site as a full ongoing maintenance solution, it even builds the jobs for you. Bit OTT for the OP though.

[eek]

Yes, Ola's solution is what we use at my current site as a full ongoing maintenance solution, it even builds the jobs for you. Bit OTP for the OP though.

If we could do the indexes without taking the server down we could justify spending weeks on it.

[BigRed]

If we could do the indexes without taking the server down we could justify spending weeks on it.

Sorry, you've completely lost me there

If you are talking about one SQL Instance (and it's DR solution) it really can be worth getting a proper production DBA in to give the system a full healthcheck, set up maintenance, report on deviations from best practices, lock down the security etc. It can be done in a couple of days and save a lot of grief. Development DBAs can not be relied on for this, it isn't what they do. Now where would you find an experienced production DBA

[SimonMac]

Sorry, you've completely lost me there

If you are talking about one SQL Instance (and it's DR solution) it really can be worth getting a proper production DBA in to give the system a full healthcheck, set up maintenance, report on deviations from best practices, lock down the security etc. It can be done in a couple of days and save a lot of grief. Development DBAs can not be relied on for this, it isn't what they do. Now where would you find an experienced production DBA

Read TPD about the Mickey Mouse project Eek and I are on at the moment, then re-read that statement, I'm a Release Manager and Eek is a code monkey, he is playing BA and I am playing DBA