MSSQL Server maintenance guidelines for optimal performance with Clarity

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Below are the SQL Server weekly and daily recommendations for database maintenance to ensure more optimal performance with the Clarity product line for our SQL Server customers

Environment

Release: All supported Clarity releases with SQL server

Resolution

Guidelines for SQL Server maintenance (valid for all supported SQL Server releases)

Weekly Maintenance

Run the below script. Please note the database name in the script is set to CLARITY - you must replace this if it's different on your environment

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

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
 
---------------------------------

Next, run these 2 for views. Note the database name on line 1.

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

Update 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

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 a 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

Additional Information

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