SQL Server Rebuild or Reoganize Your Indexes – It depends, but here is a script I use.

So if you are like me, chances are you have that one server where the reindexing or reorganizing is taking a long time to finish. I came up with a solution that has helped me reduce the time it takes to maintain my indexes.

Background

Rebuilding indexes basically recreates an index from scratch (retrieving the data from the table again) whereas reorganizing is a process that relocates the pages of an index.

Rebuilding an Index

Rebuilding an index is typically an offline process, unless you have the Enterprise edition of SQL Server with the online flag set (check BOL for your particular version). While non-clustered indexes are being rebuilt, the table is available. However, rebuilding clustered indexes results in the the table being offline.

Reorganizing an Index

Reorganizing is an online process. It can reduce your fragmentation, but doesn’t always. Remember it is only moving pages around and not actually recreating the pages from data in the table.

Best Practices

Based on what I have read from various experts of SQL Server, rebuilding is recommended for indexes over 30 percent fragmentation. Under 30 percent, the index should be reorganized.

It is also recommended to not do anything to the indexes that are under 100 pages in size as they will not gain anything from a rebuild or reorganize. Of course, sometimes you might need to rebuild a small index, if for example, one of the pages has data corruption.

What the Script Does

Based on the best practices I mention above, it scans the sys.indexes view of every database on your SQL Server instance and then creates (and executes) rebuild or reogranize command for each index that has more than 5% fragmentation and more than 100 pages in size.

It uses the server default fill factor, which you can set in the properties of server instance.It currently DOES rebuild clustered indexes, so if you are running this be sure to run it in your maintenance window.

I encourage you to read through and understand the script as well as test it in your test environment before running it on production to get a feel for its behavior.

Let me know if it helps you out, I know it has helped me get more done in my maintenance window.

DECLARE @rebuildThreshold FLOAT;
SET @rebuildThreshold = 30.0;

CREATE TABLE #indices (
dbname VARCHAR(300),
tablename VARCHAR(300),
indexname VARCHAR(300),
fragmentation FLOAT
)
DECLARE @db SYSNAME;
DECLARE @sql VARCHAR(2000);

DECLARE curs CURSOR
FOR
SELECT  name
FROM    sys.databases
WHERE   name<>’tempdb’  AND state_desc <> ‘OFFLINE’ AND is_read_only = 0

OPEN curs

FETCH NEXT FROM curs INTO @db;
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = ‘SELECT ”’ + @db + ”’,
”[” + SCHEMA_NAME(schema_id) + ”].[” + OBJECT_NAME(i.object_id, DB_ID(”’ + @db + ”’)) + ”]”,
i.name AS indexname,
ips.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(”’ + @db + ”’), NULL, NULL, NULL, NULL) ips
INNER JOIN [‘ + @db + ‘].sys.indexes i ON ips.object_id=i.object_id AND
ips.index_id=i.index_id
INNER JOIN [‘ + @db + ‘].sys.objects o ON i.object_id = o.object_id
WHERE   page_count>=100 AND
avg_fragmentation_in_percent > 5’;

INSERT  INTO #indices
(
dbname,
tablename,
indexname,
fragmentation
)
EXEC (
@sql
);

FETCH NEXT FROM curs INTO @db;
END
CLOSE curs;
DEALLOCATE curs;

DECLARE curs2 CURSOR
FOR
SELECT  CASE WHEN fragmentation<@rebuildThreshold THEN ‘ALTER INDEX ‘ + indexname + ‘ ON [‘ + dbname + ‘].’ + tablename + ‘ REORGANIZE;’
ELSE ‘ALTER INDEX ‘ + indexname + ‘ ON [‘ + dbname + ‘].’ + tablename + ‘ REBUILD;’
END
FROM    #indices
WHERE   indexname IS NOT NULL;

OPEN curs2;

FETCH NEXT FROM curs2 INTO @sql;
WHILE @@FETCH_STATUS=0
BEGIN
EXEC (@sql);
PRINT @sql;

FETCH NEXT FROM curs2 INTO @sql;
END

CLOSE curs2;
DEALLOCATE curs2;

DROP TABLE #indices

Advertisements

MS Access Upsizing to SQL Server Issues & Troubleshooting

Upsizing Access Databases to SQL Server?

If you are like me you have had one or two very small Access databases that have grown and need SQL Server’s power. Naturally you run the upsizing wizard and move over all your tables. This allows you to keep the Access fron-end and have a rip-roaring SQL Server in the back-end. Here are a couple things to look out for:

Careful with the Switchboard

Migrating the switchboard table over to SQL Server is a good idea if you want have many people using the Access database front-end.  If you can figure out the switchboard table, you can modify your switchboard using this table.

If you migrate the switchboard table to SQL Server, you can no longer use the Switchboard Manager

To get around this issue, you have have to modify the table directly.

Timestamps: Problems Updating Data

Be sure that you include a timestamp on your tables, Access needs these if it is going to make some DML changes to your data. I believe although couldn’t find verification that it uses timestamps on the tables as a locking mechanism and reduces contention.

New Problem, Indexes

Primary keys do not come over appropriately. They come over as unique non-clustered indexes, not clustered. This means your data can be stored out of order on disk, which is very inefficient for larger tables. Delete these indexes and create primary keys for them!

Lastly, any changes made to indexes (creating, altering, dropping) will need the Access front end to be updated. Be sure to refresh your linked tables in the linked table manager.

Enjoy!