SQL Server database settings

When creating a new SQL Server database I always use this checklist:

Name: Use as less non-alpha numeric characters in the database name as possible (do not use the ‘-‘, it will mess up your scripts). Use pascal casing for readability (use OrdersDb, do not use Orders-db or Orders_db)

Set Page verify to Checksum

Pre allocate data and log size to a reasonable size, do not rely on autogrowth.
Set a reasonable Autogrow size in MB (not a percentage) for data and log files

Enabling auto create/update statistics, is fine for most situations.

Set the right recovery model.
If your using ‘full’ or ‘bulk recovery’, schedule regular log backups (see this post )

Try to create at least two –equally sized!- device files for the data devices.
Not only reduces this locking issues but also when a database is regular under a heavy load,files can easily be moved to additional physical volumes (see this post )

Create one file for the Log

Use fn_virtualfilestats() to measure I/O load per device file (see this post )

Set the .MDF, .NDF and .LDF files in their own folders with the same name as the database. Don’t store all your db’s in one folder

Reorganize indexes/statistics on a regular basis (see this post )

Run DBCC CHECKDB on a regular basis (see this post )

Do not schedule any shrink operations

Configure instant file initialization (see this post )

Check out my post on auto index management and tools for performance, to setup more databases on the same instance and save costs

Reorganize fragmented indexes on database tables

Over time when data is added, changed or deleted from Microsoft SQL Server database tables the indexes on these tables become fragmented.
It is very important to keep your indexes in a healthy and defrage’d state to keep performance of the database to an optimum. In my experience this is an area that doesn’t get the attention as it should (honest I’ve seen tables fragmented for more then 99%)

Microsoft presents us with a maintenance-plan option to defrag the SQL Server tables, but the downside of their approach is that all tables within the db –including the big and not so defrag’ed ones are reorganized.For larger db’s this could lead to less availability of the db (processed tables can be locked), and unnecessary transaction log growth.

Why I think the code presented here is better:
- It only defrags indexes that suffer from fragmentation
- Small –base- tables (<8kb)>
- Databases that are not on-line, or are being used as fail-over db’s in transaction-log shipping or database mirroring are skipped
- System db’s (master, msdb, distribution) are included
- Not so heavily defrag’ed tables are reorganized instead of rebuild which takes less time to perform and improves uptime of the database
- Statistics are updated when required
- Newly created db’s are automatically included
- Through the usage off globally (##)temp-db tables the code can be stored as stored-procs in one central location per instance, I use a CodeCatalog db for this.

I normally schedule this script in SQL Server Agent once a day as a separate job.

Here’s T-SQL Code (store in CodeCatalog)

-- =========================================================================
-- Author:  SQL Server Advisor
-- Create date: jan/2009
-- Description: Loops through database collection and calls
--              script to de-fragment
-- =========================================================================
CREATE PROCEDURE [dbo].[usp_SetupReorgDatabase] AS

    DECLARE @dbname VARCHAR(100)

    DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
      SELECT name FROM sys.databases
       WHERE name NOT IN ('tempdb','Northwind','model') -- skip unwanted DBs
         AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
         AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
        ORDER BY name

    OPEN dbnames_cursor
    FETCH NEXT FROM dbnames_cursor INTO @dbname

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- actual reorganization goes here...
        EXEC usp_ReorgDatabase @dbname

        FETCH NEXT FROM dbnames_cursor INTO @dbname
    END

    CLOSE dbnames_cursor
    DEALLOCATE dbnames_cursor

-- ==========================================================================
-- Author:   SQL Server Advisor
-- Create date: jan/2009
-- Description: Reorganizes fragmented tables
--       called from usp_SetupReorgDatabase
-- ==========================================================================

CREATE PROCEDURE [dbo].[usp_ReorgDatabase] @DBname AS VARCHAR(100)
AS

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);
DECLARE @DB_ID SMALLINT;

SET @DB_ID = DB_ID(@DBname)

-- drop any tempdb objects that might exist
    IF OBJECT_ID('tempdb..#ReorgCandidates') IS NOT NULL
        DROP TABLE #ReorgCandidates

    IF OBJECT_ID('tempdb..##DBobjects') IS NOT NULL
        DROP TABLE ##DBobjects

    IF OBJECT_ID('tempdb..##DBschemas') IS NOT NULL
        DROP TABLE ##DBschemas

    IF OBJECT_ID('tempdb..##DBindexes') IS NOT NULL
        DROP TABLE ##DBindexes

    IF OBJECT_ID('tempdb..##DBpartitions') IS NOT NULL
        DROP TABLE ##DBpartitions

    -- use temp global ## tables to get objects/schemas/indexes info for the requested db
    SET @command = N'USE ' + @DBname + N' SELECT * INTO ##DBobjects FROM sys.objects'
    EXEC SP_EXECUTESQL @command

    SET @command = N'USE ' + @DBname + N' SELECT * INTO ##DBschemas FROM sys.schemas'
    EXEC SP_EXECUTESQL @command

    SET @command = N'USE ' + @DBname + N' SELECT * INTO ##DBindexes FROM sys.indexes'
    EXEC SP_EXECUTESQL @command

    SET @command = N'USE ' + @DBname + N' SELECT * INTO ##DBpartitions FROM sys.partitions'
    EXEC SP_EXECUTESQL @command

    SELECT  OBJECT_ID AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag
    INTO #ReorgCandidates
    FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL , NULL, 'DETAILED')-- LIMITED DETAILED
    WHERE index_id > 0--Only clustered and nonclustered indexes
      AND (avg_fragmentation_in_percent >= 10 OR avg_page_space_used_in_percent < 60)
      AND page_count > 8 -- We do not want indexes less than 1 extent in size

    -- Declare the cursor for the list of partitions to be processed.
    DECLARE ReIndexCursor CURSOR FAST_FORWARD FOR
        SELECT DISTINCT objectid,indexid,partitionnum – ONLY GET UNIQUE objects
            FROM #ReorgCandidates;

    -- Open the cursor.
    OPEN ReIndexCursor;

    -- Loop through the objects.
    WHILE (1=1)
        BEGIN;
            FETCH NEXT FROM ReIndexCursor
                INTO @objectid, @indexid, @partitionnum;

            IF @@FETCH_STATUS < 0
                BREAK;

            SELECT @frag = MAX(frag)-- get the max fragmentation level of this table
                FROM #ReorgCandidates
                WHERE objectid = @objectid
                  AND indexid = @indexid

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM ##DBobjects AS o
            JOIN ##DBschemas AS s ON s.schema_id = o.schema_id
            WHERE o.OBJECT_ID = @objectid;

            SELECT @indexname = QUOTENAME(name)
            FROM ##DBindexes
            WHERE OBJECT_ID = @objectid AND index_id = @indexid;

            SELECT @partitioncount = COUNT (*)
            FROM ##DBpartitions
            WHERE OBJECT_ID = @objectid AND index_id = @indexid;

                        -- 20 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
            IF @frag < 20.0
            BEGIN
                SET @command = N'USE ' + @DBname + N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname;
                PRINT N'Executed: ' + @command

                EXEC SP_EXECUTESQL @command

                SET @command = N'USE ' + @DBname + N' ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
            END

            IF @frag >= 20.0
            BEGIN
                SET @command = N'USE ' + @DBname + N' ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON)';
            END

            IF @partitioncount > 1
            BEGIN
                SET @command = @command + N'USE ' + @DBname + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));
            END

            EXEC SP_EXECUTESQL @command

            PRINT N'Executed: ' + @objectname + '-' + CAST(@objectid AS NVARCHAR(30)) + '-' + @indexname  + '-' + CAST(@frag AS NVARCHAR(30)) + '/' + @command;
        END;

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

-- Drop the temporary tables.
    IF OBJECT_ID('tempdb..#ReorgCandidates') IS NOT NULL
        DROP TABLE #ReorgCandidates

    IF OBJECT_ID('tempdb..##DBobjects') IS NOT NULL
        DROP TABLE ##DBobjects

    IF OBJECT_ID('tempdb..##DBschemas') IS NOT NULL
        DROP TABLE ##DBschemas

    IF OBJECT_ID('tempdb..##DBindexes') IS NOT NULL
        DROP TABLE ##DBindexes

    IF OBJECT_ID('tempdb..##DBpartitions') IS NOT NULL
        DROP TABLE ##DBpartitions