Never set “AutoShrink = true”

In my last post, I wrote how you could detect fragmentation and how to fix it.  This post describes a feature that many people in the SQL Server Community hate. This is a single setting under “Database Properties” and might seem to be a pretty good idea for someone who don’t know all the facts, and will sooner or later result in decreased performance. This feature will in worst case totally fragment your database – tables, indexes and even files on the file system level.

You can run the following command to detect which databases that have turned on AutoShrink:

select name, is_auto_shrink_on 
from sys.databases
where is_auto_shrink_on = 1

You can run the following command to turn off AutoShrink for a database:

alter database [MyDB]
set auto_shrink off

The AutoShrink feature fires every 30 minutes by default and it severly fragement the database.  If you use AutoGrowth as well, this can totally fragment the entire database. AutoShrink starts at end of file and brute-force moving the pages as close to the beginning of the file as possible. These pages are physically getting out of order inside the database. If you have a “full rebuild index” the night before and AutoShrink could fragment the database again.

Advertisements

Check SQL Server fragmentation – rebuild/reorganize

If you have performance issues on your database, one of the first things you should check is the fragmentation.  If the fragmentation is high, SQL Server has the possibility to either reorganize or rebuild indexes. You can detect index fragmentation by using the Dynamic Management View (DMV) sys.dm_db_index_physical_stats and check the avg_fragmentation_in_percent column.

Use [MyDB];
SELECT a.index_id, name, avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
   JOIN sys.indexes AS b ON a.object_id = b.object_id 
AND a.index_id = b.index_id
ORDER BY avg_fragmentation_in_percent DESC;

If the value avg_fragmentation_in_percent between 5% and 30% you should perform a reorganize of your indexes, not a total rebuild. A rebuild you only ne invoked if the fragmentation percentage is greater than 30%. No action should be taken if the fragmentation percentage is lower tha 5% which is a normal level of fragmentation.

The following command will rebuild all indexes With default fill factor :

Use [MyDB]; 
EXEC sp_MSforeachtable 
  @command1="print '?'", 
  @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"