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,
- View performance metrics for SQL monitors. Log in to Site24x7 and go to Server > Microsoft SQL Server.
- Add a Threshold and Availability profile to declare a specific resource as critical or down.
- Analyze trends and identify performance issues with exclusive performance reports.
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.
Note
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