SQL Server Database - EcoSys - Help - Hexagon PPM

EcoSys Performance Tuning

Language
English
Product
EcoSys
Search by Category
Help
EcoSys Version
8.6

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