SQL-Performance recommendations

SQL recommendations

When the amount of data in a table exceeds the available RAM, consider the following:

• • Latency is related to disk I/O speeds. A slow database means slower query performance as data loads from disk.

  • More RAM allows for faster queries. Consider installing the maximum amount of RAM.

  • More disk I/O allows for faster queries.

  • Better disks, such as SSD, also improve or eliminate latency problems.

  • Lower latency on tempDBs and log files means better overall performance.

  • Well tuned indexes and statistics allow for faster CRUD.

Large amounts of data in a single table can also slow maintenance because many SQL indexes may be needed to improve search and sorting performance. Inserts, deletes, and updates all become slower when there are many indexes. You must balance the number indexes against database maintenance performance, and apply indexes judiciously. 

Disks

SQL data should live on the fastest available storage, with the tempDB data and Log files on SSDs and at least 400 MB/s concurrent throughput each on the Data, Log, and FT catalog volumes. This means that the server should be able to sustain a cumulative of 1600 MB/sec write. High-performance sequential write speed (cache optimized) may be desirable for backups, but it generally doesn't impact Relativity performance.

 For example, in the Fast Track requirements, Microsoft recommends 200-400 MB/s throughput per processor core for big data stored in data warehouses.

Optimize for ad hoc workloads option

Optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches.

We recommend enabling the optimize for ad hoc workloads option on each SQL instance. Execute the following statement to enable this option:

SP_CONFIGURE 'show advanced options',1

RECONFIGURE

GO

SP_CONFIGURE 'optimize for ad hoc workloads',1

RECONFIGURE

GO

Max degree of parallelism option

We recommend the following MaxDOP settings:

• • Server with single NUMA node : If the total number of logical processor cores is greater than or equal to 16, set the MaxDOP to a value of 8.

  • Server with single NUMA node : If the total number of logical processor cores is less than 16, set the MaxDOP to a value equal to half the number of available cores.

  • Server with multiple NUMA nodes : If the total number of logical processors cores is 12 or more, set MaxDOP to 6, a value half of the number of available cores per NUMA.

  • Server with multiple NUMA nodes: If the total number of logical processor cores is 8 or more, set MaxDOP to 8, we recommend starting off with 8 when the number of total logical cores is more than or equal to 16

https://help.relativity.com/9.3/Content/System_Guides/Environment_Optimization_Guide/Environment_optimization_guide.htm

https://help.relativity.com/9.3/Content/System_Guides/Environment_Optimization_Guide/Configuring_SQL_Server.htm#Optimize