Metrics

Microsoft SQL Server is a relational database management system known for its performance and security. Monitoring its metrics is crucial to ensure performance, stability, and reliability. The following is a list of essential SQL Server metrics in PDS. Understanding these metrics will help administrators optimize performance, troubleshoot issues, and ensure the SQL Server runs smoothly.

note

For Microsoft SQL Server deployment, the data service metrics are accessible on port 9399.

Access metrics

Below is a step-by-step guide on how to access Microsoft SQL Server metrics for PDS deployments:

1. Identify the Microsoft SQL Server pod running in your namespace:

   kubectl get pods -n <your-namespace>

   Look for the pod name that corresponds to your Microsoft SQL Server instance or its sidecar exporter.

2. Port-forward from your local machine’s port 9399 to the pod’s port 9399:

   kubectl port-forward -n <your-namespace> <mssql-pod-name> 9399:9399

3. Open a browser or use curl to go to http://localhost:9399/metrics.

   You should see a text-based Prometheus metrics output specific to Microsoft SQL Server.

4. Check for the service exposing the Microsoft SQL Server exporter. for example, <release-name>-mssql-exporter:

   kubectl get svc -n <your-namespace>

5. Access the metrics:

   • If NodePort, note <nodeport>:

     http://<node-ip>:<nodeport>/metrics

   • If LoadBalancer, note <loadbalancer-ip>:

     http://<loadbalancer-ip>:9399/metrics

6. Verify metrics:

   • Using curl:

     curl http://<host>:9399/metrics

     Replace <host> with either localhost (if using port-forward), <node-ip> (NodePort), or <loadbalancer-ip> (LoadBalancer).

   • Prometheus UI:

     In Prometheus, navigate to the Expression browser and search for metrics beginning with mssql_ or similar Microsoft SQL Server-related prefixes to confirm they are being scraped.

   • Grafana or other dashboards:

     If you have Grafana connected to Prometheus, open your dashboard. Check that Microsoft SQL Server metrics (those starting with mssql_) are being ingested and displayed.

note

• Ensure that any NetworkPolicies or firewall rules allow inbound traffic on port 9399 if you plan to expose it externally.
• Metrics naming conventions can vary depending on the Microsoft SQL Server exporter version. Generally, look for prefixes like mssql_.

Microsoft SQL Server metrics

Name	Description
mssql_batch_requests	Number of command batches received.
mssql_buffer_cache_hit_ratio	Ratio of requests that hit the buffer cache
mssql_checkpoint_pages_sec	Checkpoint Pages Per Second
mssql_connections	Number of active connections.
mssql_deadlocks	Number of lock requests that resulted in a deadlock.
mssql_io_stall_seconds	Stall time in seconds per database and I/O operation.
mssql_io_stall_total_seconds	Total stall time in seconds per database.
mssql_kill_connection_errors	Number of severe errors that caused SQL Server to kill the connection.
mssql_local_time_seconds	Local time in seconds since epoch (Unix time).
mssql_log_growths	Number of times the transaction log has been expanded, per database.
mssql_memory_utilization_percentage	The percentage of committed memory that is in the working set.
mssql_os_memory	OS physical memory, used and available.
mssql_os_page_file	OS page file, used and available.
mssql_page_fault_count	The number of page faults that were incurred by the SQL Server process.
mssql_page_life_expectancy_seconds	The minimum number of seconds a page will stay in the buffer pool on this node without references.
mssql_resident_memory_bytes	SQL Server resident memory size (AKA working set).
mssql_user_errors	Number of user errors.
mssql_virtual_memory_bytes	SQL Server committed virtual memory size.