Performance Metrics for SQL Servers

Gain deep understanding of various memory and buffer manager details for optimal capacity planning. Drill down to performance details such as SQL compilations and longest transaction time of SQL instances, database attributes and more. The SQL monitor uses the Site24x7 Windows agent for monitoring. Install the Windows agent and get your SQL servers auto-discovered

Once the SQL server monitor is successfully added to your Site24x7 account,

Interpret SQL Performance Metrics

  • Operations: Drill down to performance details such as errors, replications, SQL compilations, and longest transaction time of SQL instances.
  • Capacity Planning: Gain deep understanding of various memory and buffer manager details for optimal capacity planning.
  • DevOps: Focus on more intensive portions of SQL servers like job, lock, and latch details.
  • Database: Capture database attributes like data file size used, log file size, log cache hit ratio.

Operations

Parameters Description
Microsoft SQL Server Details Lists down the host name, IP address, version name, status of the SQL agent, and browser services 
Active Connections  The user connection counter identifies the number of users connected to the SQL server
Errors  Number of errors per second. This includes DB Offline, Info, Kill Connection, and User errors.*
Batch Requests Number of SQL batch requests received by server
Transaction Time  The running time of any transaction in seconds
Backup Throughput Read/write throughput for a backup device
Compilations Number of SQL compilations
Replication The number of conflicts per second occurring during the merge process

*The Errors metric include the following errors:

  • DB offline errors that includes severe errors that causes the SQL server to take the current database offline.
  • Information errors that provide insight on error messages to users.
  • Kill connection errors that provide severe errors that cause the SQL server to kill the current connection.
  • User errors

For more details, enable Applogs.

You will be able to view the Error chart only if you use the Windows agent version 20.4.5 and above.

Capacity Planning

Parameters Description
Memory Manager Details:
Target Server Memory (GB) Total amount of dynamic memory the server is willing to consume
Total Server Memory (GB) Total amount of dynamic memory the server is currently consuming
SQL Cache Memory (GB) Total amount of dynamic memory the server is using for the dynamic SQL cache
Optimizer Memory (GB) Total amount of dynamic memory the server is using for query optimization
Connection Memory (GB) Total amount of dynamic memory the server is using for maintaining connections
Lock Memory (GB) Total amount of dynamic memory the server is using for locks
Granted Workspace Memory Total amount of memory granted to executing processes
Memory Grants Pending Current number of processes waiting for a workspace memory grant
Buffer Manager Details:
Buffer Cache Hit Ratio (%) Percentage of pages that were found in the buffer pool without having to incur a read from disk
Checkpoint Pages/sec Number of pages flushed by checkpoint or other operations that require all dirty pages to be flushed
Lazy Writes/sec Number of buffers written by buffer manager's lazy writer
Page Life Expectancy (sec) Number of seconds a page will stay in the buffer pool without references
Page Reads/sec Number of physical database page reads issued
Page Writes/sec Number of physical database page writes issued
Total Pages Number of pages in the buffer pool
Database Pages Number of pages in the buffer pool with database content
Free Pages Total number of pages on all free lists

DevOps

Parameters Description
Plan and Job Details:
Plan Cache Hit Ratio (%) Ratio between cache hits and lookups
Cache Pages Number of 8k pages used by cache objects
Cache Objects Number of cache objects in the cache
Active Jobs Number of running jobs
Queued Jobs Number of jobs queued
Failed Jobs Number of failed jobs
Job Success Rate (%) Percentage of successful jobs from the total number of executed jobs
Lock and Latch Details:
Lock Requests/sec Number of new locks and lock conversions requested from the lock manager
Lock Timeouts/sec Number of lock requests that timed out
Lock Waits/sec Total wait time (milliseconds) for locks in the last second
Deadlocks/sec  Number of lock requests that resulted in a deadlock
Average Lock Wait Time The average amount of wait time (milliseconds) for each lock request that resulted in a wait
Latch Waits/sec Number of latch requests that could not be granted immediately and had to wait before being granted
Average Latch Wait Time  Average latch wait time (milliseconds) for latch requests that had to wait 

Database

Click on Discover Databases to discover and add databases for monitoring. You can also choose to Auto-discover and add new database(s) once they are added in your environment.  

Parameters Description
Top Data Space Utilization Graphical representation showing the data file size of the top ten databases 
Top Log Space Utilization A graph showing the log space of the top ten databases 
Database Details: 
Data File Size (GB) The cumulative size of all the data files in the database
Log Cache Hit Ratio (%) Percentage of log cache reads that were satisfied from the log cache
Log File Size (GB) The cumulative size of all the log files in the database
Log File Used Size (GB) The cumulative used size of all the log files in the database
Log Growths Total number of log growths for this database
Log Shrinks Total number of log shrinks for this database
Transaction/sec Number of transactions started for the database
Active Transactions Number of active update transactions for the database
Actions Click on the pencil icon to set thresholds for that specific database. Click on Threshold Configuration to perform this action in bulk. 

Performance Reports for SQL

Log in to Site24x7 and go to Reports > Microsoft SQL Server. The following reports are available for SQL monitoring:

  • Availability Summary Report
  • Busy Hours Report
  • Health Trend Report
  • Performance Report
  • MSSQL Database Report
  • Top N Microsoft SQL servers by 
    • Batch Requests
    • Target Server Memory
    • Average Lock Wait Time
    • Average Latch Wait Time
    • SQL Compilation
    • Buffer Cache Hit Ratio
    • Longest Transaction Time
    • Database Transactions
    • Page Splits
    • Dead Locks

Learn more.

Related Articles

Was this document helpful?
Thanks for taking the time to share your feedback. We’ll use your feedback to improve our online help resources.