Regular SQL Server Maintenance
Update Statistics for EcoSys Schema Objects
Analyze statistics during off-peak hours on a weekly basis or after a bulk insert or delete. Start the SQL Server Agent, and then create a task to update statistics for the FMUSER objects.
TempDB Optimization
The tempdb database is used as a scratch area for hashes and sorts. Reducing I/O latency
for such operations may be beneficial.
The number of tempdb files should match the number of processor cores on the database
server.
Configure the file system for database log and data files as RAID 10. Latency should be < 10ms.
Memory/CPU
Set MAXDOP to the number of physical cores. Memory allocated to SQL Server should be set to 80% of the memory allocated to the server. The default setting for both parameters is unlimited, which may cause deadlocking.
Index Fragmentation
Run the following maintenance script to identify fragmented indexes. The generated output will need to be executed to rebuild indexes.
SELECT
'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
CASE
WHEN ps.avg_fragmentation_in_percent > 40
THEN 'REBUILD'
ELSE
'REORGANIZE'
END +
CASE
WHEN pc.partition_count > 1
THEN ' PARTITION = ' + CAST(ps.partition_number AS NVARCHAR(MAX))
ELSE
''
END
FROM
sys.indexes AS ix
INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON s.schema_id = t.schema_id
INNER JOIN
(
SELECT
object_id
, index_id
, avg_fragmentation_in_percent
, partition_number
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
)
AS ps
ON
t.object_id = ps.object_id
AND ix.index_id = ps.index_id
INNER JOIN
(
SELECT
object_id
, index_id
, COUNT(DISTINCT partition_number) AS partition_count
FROM
sys.partitions
GROUP BY
object_id
, index_id
)
AS pc
ON
t.object_id = pc.object_id
AND ix.index_id = pc.index_id
WHERE
ps.avg_fragmentation_in_percent > 10
order by ps.avg_fragmentation_in_percent desc