Microsoft SQL Server Key Performance Indicators (KPIs) for UIM

Products

• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)
• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Customers often ask what are the most important checkpoints or KPIs they can monitor with the sqlserver probe. Presented below is a list and associated business impact (in general).

Environment

• DX UIM 23.4 or higher
• sqlserver probe

Cause

• Monitoring guidance

Resolution

Monitoring Microsoft SQL Server requires looking past raw infrastructure numbers and focusing on metrics that directly correlate to application stability, user experience, and financial impact. SQL Server performance experts regularly emphasize that technical bottlenecks manifest as business revenue risks.

Below are the Top 8 MS SQL Server Key Performance Indicators (KPIs), detailing their technical meaning and their value from a business impact perspective.

1. Database Availability & Uptime Percentage

• What It Measures: The percentage of time the database engine is accessible and capable of accepting connections and executing queries.

• Business Impact: Direct Revenue & Trust Preservation.

  Unplanned downtime halts online transactions, stops warehouse fulfillment, and freezes internal operations. For e-commerce or SaaS platforms, availability directly maps to the bottom line—every minute of downtime represents lost sales, breached Service Level Agreements (SLAs), potential contractual penalties, and eroded customer trust.

2. Query Latency & Response Time (Execution Time)

• What It Measures: The end-to-end time taken for a SQL query or transaction to execute and return results to the requesting application.

• Business Impact: User Retention & Product Conversion Rates.

  From a business perspective, query response time is user experience. Slow queries manifest as lagging mobile apps, spinning loading wheels, or timed-out checkout pages. Studies repeatedly show that delays of even a few hundred milliseconds drop conversion rates and increase user churn.

3. Concurrent Connections & Active Sessions

• What It Measures: The total number of open user/application connections (sys.dm_exec_connections) and how many are actively executing work at a given moment.

• Business Impact: Scalability & Peak-Demand Readiness.

  Monitoring connection counts prevents connection pool exhaustion. If an influx of users causes active connections to spike beyond capacity, new users get locked out with application error screens. Tracking this KPI ensures your infrastructure can handle peak business events (e.g., Black Friday sales or end-of-quarter reporting) without crashing.

4. Wait Statistics (Resource & Signal Wait Times)

• What It Measures: The duration and type of delays SQL Server threads experience while waiting for resources (like disk I/O, memory, or CPU) versus time spent executing.

• Business Impact: Infrastructure Cost Optimization (ROI).

  Wait stats tell you why your system is slow. Rather than blindly throwing expensive hardware or higher cloud tier resources at a slow database (which wastes capital expenditure), wait stats pinpoint whether you have an indexing issue, bad code, or actual hardware exhaustion—saving significant cloud hosting costs.

5. Blocking Duration & Deadlocks

• What It Measures: Occurrences where one process holds a lock on data, forcing other processes to wait, or where two processes block each other entirely, forcing SQL Server to kill one transaction.

• Business Impact: Data Integrity & Workforce Productivity.

  Excessive blocking leads to application timeouts, while deadlocks result in aborted user actions and uncommitted data. For internal enterprise applications (like ERPs or CRMs), high blocking paralyzes staff efficiency, creates data sync errors, and leads to operational backlog.

6. Page Life Expectancy (PLE) & Buffer Cache Hit Ratio

• What It Measures: PLE measures how long (in seconds) a data page stays in memory (RAM) before being evicted. Cache Hit Ratio measures the percentage of queries satisfied directly from RAM rather than reading from disk.

• Business Impact: Predictable Performance & Cloud Spend.

  Memory is exponentially faster than storage disk I/O. Dropping PLE signals severe memory pressure, forcing the server to read from disk—slowing application throughput to a crawl. Maintaining high memory metrics ensures consistent application performance and avoids expensive storage read/write charges in cloud environments (e.g., Azure SQL, AWS RDS).

7. Batch Requests / sec & Throughput

• What It Measures: The volume of SQL statement batches the database engine receives and processes per second.

• Business Impact: Business Growth Alignment.

  Batch requests quantify overall business activity moving through your database layer. Comparing throughput trends against user growth helps leadership determine capacity planning, forecast infrastructure investments, and spot abnormal application behavior before it causes system outages.

8. SQL Compilations & Re-Compilations Ratio

• What It Measures: How frequently SQL Server compiles new execution plans relative to the total number of batch requests. High ratios mean the engine is constantly recalculating how to run queries rather than reusing cached plans.

• Business Impact: CPU Efficiency & Operational Stability.

  Query compilation is a heavy CPU consumer. Poor plan reuse drains processing power, leading to high CPU usage alerts, server sluggishness, and potential failure during high-traffic periods. Keeping compiles under 10% of batch requests maximizes your hardware investment efficiency.
  
  

KPI Summary Matrix

Metric / KPI	Key Technical Focus	Business Perspective
Availability	System Uptime & Connectivity	SLA compliance, revenue loss prevention
Response Time	End-to-end Latency	End-user experience & customer retention
Concurrent Connections	Active Session Volumes	Scalability during traffic surges
Wait Statistics	Resource Bottlenecks	Targeted hardware/cloud optimization
Blocking & Deadlocks	Lock Contention & Aborted Work	Operational efficiency & data integrity
Page Life Expectancy (PLE)	Memory Saturation	Disk I/O cost reduction & smooth throughput
Batch Requests/sec	Transaction Throughput	Capacity planning & load benchmarking
Compilations Ratio	Plan Reuse Efficiency	Maximize CPU efficiency & hardware ROI

Additional Information

Queries used by the sqlserver Probe in UIM