MSSQL Server maintenance guidelines for optimal performance with Clarity
Article ID: 24271
Updated On:
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
Feedback
Yes
No
Powered by
