SQL Server Tools

SQL Server Tools

At work our Point Of Sale system runs on SQL Server 2012 express. So I don’t have all the goodies of the full version of SQL Server 2012, but it’s not like our server handles dozens of stores or anything like that. But I like to keep our system backups up and maintained as best as possible. So I figured I’d post some of the stuff I use for the server here.

Server Backup

For our server I use a program called SQL Backup Master. It can be found at https://www.sqlbackupmaster.com/It’s a great program as far as I’m concerned. It’s not my only backup. I am one for redundancy. However what I like about this program is it’s dead simple. Just install it, set up a backup schedule and it’s off and running. It also emails you letting you know that it’s done with a backup, or if one has failed. Best part is it’s free, at least for the amount of use I use it for. There are paid versions if you need more than one scheduled backup. But for me I just need the one.

 

Maintenance

Again our SQL Server is small but from time to time it does need some maintenance. Really it’s mainly in the form of reindexing and rebuilding the indexes, or defragging. I’m sure there’s more I can do to it, but honestly I’m kind new to SQL, sure I can read info, I understand bits of it, but I consider myself still in the training wheels mode. So I found these two scripts that I’m using. I got them from This article: How to automate SQL Server defragmentation using policies. I’ll post this code I use here in case that page ever disappears from the web. But I highly suggest you look at the article. Especially if you’re able to automate these with the full version of SQL Server.

Check Fragmentation of DB

--Script 1: Detecting index fragmentation

SELECT dbschemas.[name] AS 'Schema',
dbtables.[name] AS 'Table',
dbindexes.[name] AS 'Index',
indexstats.avg_fragmentation_in_percent AS 'Frag (%)',
indexstats.page_count AS 'Page count'

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent DESC

Defragment Indexes to the recommended levels




-- Script 4: Automatically analyze and defragment indexes

-- Set variables
-- *********************************************************************************************
SET NOCOUNT ON

DECLARE @reorg_frag_thresh   float	SET @reorg_frag_thresh	= 10.0
DECLARE @rebuild_frag_thresh float	SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint	SET @fill_factor = 0
DECLARE @report_only         bit	SET @report_only = 0
DECLARE @page_count_thresh   smallint	SET @page_count_thresh = 1
-- *********************************************************************************************
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 @page_count     int
DECLARE @command        nvarchar(4000)
DECLARE @intentions     nvarchar(4000)
DECLARE @table_var      TABLE(
                          objectid     int,
                          indexid      int,
                          partitionnum int,
                          frag         float,
		    page_count   int
                        )
INSERT INTO
    @table_var
SELECT
    [object_id]                    AS objectid,
    [index_id]                     AS indexid,
    [partition_number]             AS partitionnum,
    [avg_fragmentation_in_percent] AS frag,
    [page_count]		  AS page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
    [avg_fragmentation_in_percent] > @reorg_frag_thresh 
	AND
	page_count > @page_count_thresh
	AND
    index_id > 0	
 DECLARE partitions CURSOR FOR
    SELECT * FROM @table_var 
OPEN partitions 
WHILE (1=1) BEGIN
    FETCH NEXT
        FROM partitions
        INTO @objectid, @indexid, @partitionnum, @frag, @page_count 
    IF @@FETCH_STATUS < 0 BREAK
    SELECT
        @objectname = QUOTENAME(o.[name]),
        @schemaname = QUOTENAME(s.[name])
    FROM
        sys.objects AS o WITH (NOLOCK)
        JOIN sys.schemas AS s WITH (NOLOCK)
        ON s.[schema_id] = o.[schema_id]
    WHERE
        o.[object_id] = @objectid 
    SELECT
        @indexname = QUOTENAME([name])
    FROM
        sys.indexes WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid 
    SELECT
        @partitioncount = count (*)
    FROM
        sys.partitions WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid		
    SET @intentions =
        @schemaname + N'.' +
        @objectname + N'.' +
        @indexname + N':' + CHAR(13) + CHAR(10)
    SET @intentions =
        REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
        @intentions
    SET @intentions = @intentions +
        N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
        N' PAGE COUNT: '    + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10) 
    IF @frag < @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' + @objectname +
            N' REORGANIZE; ' + 
            N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname + 
            N' ' + @indexname + ';'
    END
    IF @frag >= @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' +     @objectname +
            N' REBUILD'
    END
    IF @partitioncount > 1 BEGIN
        SET @intentions = @intentions +
            N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
    END
    IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
        SET @intentions = @intentions +
            N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
    END
    IF @report_only = 0 BEGIN
        SET @intentions = @intentions + N' EXECUTING: ' + @command
        PRINT @intentions	    
        EXEC (@command)
    END ELSE BEGIN
        PRINT @intentions
    END
        PRINT @command
END
CLOSE partitions
DEALLOCATE partitions
GO


 

Leave a Reply

Your email address will not be published. Required fields are marked *