Standalone app: keep the database healthy/responsive over time

Question

I have a WinForms C# application using a MS SQL Server Express database. The application is deployed on the PCs of our customers and they don't have computer related knowledge.

The application updates the database regularly and I see a lot of fragmentation on the index files. How do I keep the database healthy/responsive over time?

I was thinking about programming a stored procedure which reorganizes every index, but I lack t-sql skills; can someone lead me in the right direction?

Bas

Answer 1

Use the DBCC REINDEX option if you can afford to take the table offline for a short while, alternatively DBCC INDEXDEFRAG. The IndexDefrag option has been depracted though. You can also use the ALTER INDEX statement in SQL 2005/2008.

Answer 2

Also make sure that your database FILE is less prone to fragmentation. This is admittedly very tough to do, since you don't know what the layout of your customers' drives are, but I suggest starting your .MDB file off at a reasonably large initial size to prevent online rebuilds, which suck time and resources, and often lead to file level fragmentation as well.

Your index design also impacts how fragmented your indexes are. You need to make sure indexes you're inserting to a lot have an appropriately low FILLFACTOR to prevent page splitting. Also get rid of any indexes which are not being used.

To defrag your indexes, use the DBCC DBREINDEX command.

Answer 3

Would be nice to have a reusable stored procedure written that can do everything that I should do on a database programmatically using best DBA maintenance practices.

Like updating statistics, check for page errors, defrag, reindex, shrink?, .....

Like a "Make my DB healthy" stored proc

anyone who have a script like that available?

Answer 4

I now use 2 sql scripts.

SELECT 
    st.object_id AS objectid,
    st.index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag,
    o.name,
    i.name
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') st
join
    sys.objects o on o.object_id = st.object_id
join 
    sys.indexes i on st.object_id = i.object_id and i.index_id=st.index_id

I run this when starting my program and check if my main tables has an avg_fragmentation_in_percent of more then 70. If so I run the following script.

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.

if ( object_id( 'tempdb..#work_to_do' ) is not null )
  DROP TABLE #work_to_do;

-- Alleen indexen die meer dan x% gefragemteerd zijn
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')
WHERE avg_fragmentation_in_percent > 5.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;

        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

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

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

This script defrags all tables with a fragmantation of more then 5%