In general a SQL Server can offer a much better performance for read perations, if it can hold most of the databases in memory. By following the guidelines for CPU and memory recommendations for the Microsoft SQL server holding the Veeam Backup & Replication databases, a stable environment can be provided.
However, there are scenarios where the same SQL Server is used for multiple additional products like the Veeam Enterprise Manager or Veeam ONE, which adds additional load on the system. In such situations it becomes even more crucial to observe performance metrics regularly to be aware of any performance issues that can occur.
The following Windows Performance Monitor metrics can provide a good overview. Most other monitoring solutions can read these values as well.
|LogicalDisk||Avg Disk sec/ Read||below 10ms||For disks holding database and log files.|
|LogicalDisk||Avg Disk sec/Write||below 10ms||For disks holding database and log files.|
|LogicalDisk||Avg Disk sec/Transfer||below 10ms||For disks holding database and log files.|
|Memory||Available Mbytes||close to 100%||A high usage shows that a lot of data can be cached and therefor speed up read request.|
|Processor(Total)||% Processor Time||Avg. below 70%||High spikes can occur when a lot of backup jobs start.|
|SQLInstance: Access Methods||Forwarded Records/sec||Trend to 0||Avoid forwarding of record pointers that cannot be found in cache.|
|SQLInstance: Buffer Manager||Buffer cache hit ratio||Around 100%||Should be constantly high and shows how much requested data can be read from cache.|
|SQLInstance: Buffer Manager||Checkpoint pages/sec||low value||Used to compare against Page life expectancy, the less the better.|
|SQLInstance: Buffer Manager||Page life expectancy||
|Lifetime of pages in the pool without referencing.|
|SQLInstance: Locks (Total)||Average Wait Time (ms)||Trend to 0||Wait time for lock requests to perform writes|
|SQLInstance: Locks (Total)||Lock Waits/sec||Trend to 0||Number of lock requests that could not be handled immediately.|
|SQLInstance: Memory Manager||Memory Grants Pending||Trend to 0||Processes waiting for Memory Grants|
|SQLInstance: Memory Manager||Target Server Memory (KB)||See Information||Memory the SQL Instance is willing to consume. _Total Server Memory / _Target Server Memory** should be around 1.|
|SQLInstance: Memory Manager||Total Server Memory (KB)||See Information||Memory the SQL Instance is currently consuming. _Total Server Memory / _Target Server Memory** should be around 1.|