Clarity with MSSQL Database Performance: MSSQL Server Maintenance Guidelines

Document ID : KB000024271
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Below are the SQLServer weekly and daily recommendations for database maintenance to insure more optimal performance with the Clarity product line for our SQLServer customers.

Solution:

Guidelines for SQLServer maintenance (valid for MSSQL 2005, MSSQL 2008 and MSSQL 2012)

Weekly run the below script. Please note the database name in the script on Line 10.

----------------------------------------To reindex tables
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
 
SET @fillfactor = 80
 
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases 
WHERE name IN ('CLARITY')
ORDER BY 1
 
OPEN DatabaseCursor
 
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
 
 SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName 
 FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' 
 -- create table cursor
 EXEC (@cmd) 
 OPEN TableCursor
 
 FETCH NEXT FROM TableCursor INTO @Table
 WHILE @@FETCH_STATUS = 0
 BEGIN
 
 SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
 EXEC (@cmd)
 
 FETCH NEXT FROM TableCursor INTO @Table
 END
 
 CLOSE TableCursor
 DEALLOCATE TableCursor
 
 FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
 
------------------------------------------------To update statistics
USE CLARITY
SET QUOTED_IDENTIFIER OFF
DECLARE @UserName varchar(255)
SET @UserName = 'niku'
DECLARE @TableName varchar(255)
DECLARE @t varchar(255)
DECLARE TableCursor CURSOR FOR
select o.name
from sysindexes i, sysobjects o
where i.id = o.id and indid = 1 and o.type = 'U'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
set @t = @UserName+'.'+@TableName
exec ('update statistics ' + @t + ' WITH FULLSCAN, ALL')
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
SET QUOTED_IDENTIFIER ON
 
---------------------------------Then these 2 for views. Note the database name on line 1.
  
 
-- For reindexing views
USE CLARITY
SET QUOTED_IDENTIFIER OFF
DECLARE @UserName varchar(255)
SET @UserName = 'niku'
DECLARE @ViewName varchar(255)
DECLARE @t varchar(255)
DECLARE ViewCursor CURSOR FOR
select o.name
from sysindexes i, sysobjects o
where i.id = o.id and indid = 1 and o.type = 'V'
OPEN ViewCursor
FETCH NEXT FROM ViewCursor INTO @ViewName
WHILE @@FETCH_STATUS = 0
BEGIN
set @t = @UserName+'.'+@ViewName
exec ('ALTER INDEX ALL ON  ' + @t + ' REBUILD WITH (FILLFACTOR = 80)')
 FETCH NEXT FROM ViewCursor INTO @ViewName
END
CLOSE ViewCursor
DEALLOCATE ViewCursor
SET QUOTED_IDENTIFIER ON
 
-- Script For Updating View Stats 
USE CLARITY
SET QUOTED_IDENTIFIER OFF
DECLARE @UserName varchar(255)
SET @UserName = 'niku'
DECLARE @ViewName varchar(255)
DECLARE @t varchar(255)
DECLARE ViewCursor CURSOR FOR
select o.name
from sysindexes i, sysobjects o
where i.id = o.id and indid = 1 and o.type = 'V'
OPEN ViewCursor
FETCH NEXT FROM ViewCursor INTO @ViewName
WHILE @@FETCH_STATUS = 0
BEGIN 
set @t = @UserName+'.'+@ViewName
exec ('update statistics ' + @t + ' WITH FULLSCAN, ALL')
FETCH NEXT FROM ViewCursor INTO @ViewName
END
CLOSE ViewCursor
DEALLOCATE ViewCursor
SET QUOTED_IDENTIFIER ON
 
---------------------------------------------------

Daily maintenance depends on usage. For example if you run a bulk data import you may wish to reindex the tables involved in that import. Or if you have nightly XOGs that update OBSs or Resource Rights you'll want to run the view scripts above daily, because our indexed views are used heavily in security checks in Clarity.

Here is link to a good MSDN article on index fragmentation. http://msdn.microsoft.com/en-us/library/ms188917.aspx

We recommend running the below script daily. It does an index rebuild if fragmentation is over 30% and an index defrag if fragmentation is between 10 and 30%.

USE CLARITY
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.
  
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 > 10.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;
 
         -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
  
 IF @frag < 30.0
 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
 IF @frag >= 30.0
 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
 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;
GO
 
---------------------------------------------------

Also it would be good to run DBCC SHRINKDATABASE and DBCC SHRINKFILE with 20% free space / 80% fill factor on a weekly basis.
Below are more details on them from MSDN.

http://msdn.microsoft.com/en-us/library/ms190488.aspx
http://msdn.microsoft.com/en-us/library/ms189493.aspx