Microsoft SQL Server's integration query level monitoring

Preview

We're still working on this feature, but we'd love for you to try it out!

This feature is currently provided as part of a preview pursuant to our pre-release policies.

You can view query-level analysis to monitor your SQL queries within your MSSQL database to assess their performance and impact. This feature provides insights into execution times, resource consumption, and potential bottlenecks, enabling you to optimize database operations. For more information, refer Query-Level Analysis.

Prerequisites:

Microsoft SQL Server supports 2017, 2019, or 2022.
DB user should be a member of the sysadmin fixed server role or have the VIEW SERVER STATE permission.

To enable the query level monitoring feature, follow these steps:

To Enable SQL Server and Windows Authentication mode

Enable the login on the SQL Server and Windows Authentication mode. For more information, refer Microsoft change authentication mode documentation.

To Enable TCP

Ensure TCP is enabled for remote access on your instance using the documentation
Check firewall settings:

Ensure that your firewall allows traffic on port 1433. This is the listening port for Microsoft SQL Server:
- Open Windows Defender Firewall with Advanced Security.
- In the left menu, click Inbound Rules.
- Find any rule for SQL Server (typically named SQL Server (TCP-In) or SQL Server TCP 1433).
- If a rule doesn't exist, create a new rule to allow TCP traffic on port 1433.

To Enable the query store

Run the following command

ALTER DATABASE DATABASE_NAME SET QUERY_STORE = ON ( QUERY_CAPTURE_MODE = ALL, DATA_FLUSH_INTERVAL_SECONDS = 900 )

Run the below query to confirm if the query store is enabled

If the output of the query is 1 then we can assume the query store is enabled.

SELECT is_query_store_on FROM sys.databases where physical_database_name = DATABASE_NAME

To Integrate MSSQL agent

Integrate MSSQL agent using this guide.

To Configure the MSSQL integration to enable query performance monitoring

To change the directory to the integration configuration folder, run:
bash
```
$cd "C:\Program Files\New Relic\newrelic-infra\integrations.d\"
```
Edit the mssql-config.yml file with the following configuration:

ENABLE_QUERY_MONITORING - Enable query performance monitoring - Defaults to false.
QUERY_MONITORING_RESPONSE_TIME_THRESHOLD - Set the threshold for query response time in ms to retrieve individual query performance metrics. The Defaults is set to 500 ms.

QUERY_MONITORING_COUNT_THRESHOLD - The number of records for each query performance metrics. The Defaults is set to 20.

integrations:
  - name: nri-mssql
  env:
    HOSTNAME: localhost
    PORT: 1433
    USERNAME: USERNAME
    PASSWORD: PASSWORD
    ENABLE_SSL: true
    TRUST_SERVER_CERTIFICATE: true
    # Enable collection of detailed query performance metrics.
    ENABLE_QUERY_MONITORING: true
    # Threshold in milliseconds for query response time. If response time exceeds this threshold, the query will be considered slow.
    QUERY_MONITORING_RESPONSE_TIME_THRESHOLD: 500
    # Maximum number of queries returned in query analysis results.
    QUERY_MONITORING_COUNT_THRESHOLD: 20
    # Interval in seconds for fetching grouped slow queries; Should always be same as mysql-config interval.
    QUERY_MONITORING_FETCH_INTERVAL: 15
  interval: 15s
  labels:
    environment: production
  inventory_source: config/mssql

Find your data

You can use our prebuilt dashboard template to monitor your MSSQL metrics. Follow these steps to use our prebuilt dashboard template:

From one.newrelic.com, go to the + Integrations & Agents page.
Click Dashboards
In the search bar, type MSSQL On-Host Integration
The MSSQL dashboard appears. Click it to install.

Metrics collected by Integration

Metric	Description
`blocking_spid`	The ID of the blocking session.
`blocking_status`	The status of the blocking session.
`blocked_spid`	The ID of the blocked session.
`blocked_status`	The status of the blocked session.
`wait_type`	The type of wait experienced by the blocked session.
`wait_time_in_seconds`	The time the session has been waiting in seconds.
`command_type`	The type of command being performed.
`database_name`	The name of the database where the blocking is occurring.
`blocking_query_text`	The text of the query causing the block.
`blocked_query_text`	The text of the query being blocked.
`blocked_query_start_time`	The start time of the blocked query.

Metric	Description
`sql_text`	The SQL statement text.
`query_id`	The unique identifier for the query.
`query_plan_id`	The unique identifier for the query plan.
`NodeId`	The ID of the node in the execution plan.
`PhysicalOp`	The physical operation performed by this node.
`LogicalOp`	The logical operation represented by this node.
`EstimateRows`	The estimated number of rows this operation will produce.
`EstimateIO`	The estimated I/O cost of this operation.
`EstimateCPU`	The estimated CPU cost of this operation.
`AvgRowSize`	The average size of the rows processed by this node.
`TotalSubtreeCost`	The estimated total cost for running this subtree.
`EstimatedOperatorCost`	The cost estimate of the node's operation.
`EstimatedExecutionMode`	The estimated execution mode of this operation.
`GrantedMemoryKb`	The amount of memory granted for this query in kilobytes.
`SpillOccurred`	A boolean indicator of whether a spill occurred.
`NoJoinPredicate`	A boolean indicator of whether there is a join predicate.
`total_worker_time`	The total worker time in milliseconds.
`total_elapsed_time`	The total elapsed time in milliseconds.
`total_logical_reads`	The total number of logical reads performed.
`total_logical_writes`	The total number of logical writes performed.
`execution_count`	The number of times the query was executed.
`plan_handle`	The plan handle used for retrieving the plan.
`avg_elapsed_time_ms`	The average elapsed time in milliseconds.

Metric	Description
query_id	The unique identifier for the query.
database_name	The name of the database.
query_text	The text of the query.
wait_category	The category of wait experienced by the query.
total_wait_time_ms	The total amount of wait time in milliseconds.
avg_wait_time_ms	The average amount of wait time in milliseconds.
wait_event_count	The count of wait events that occurred.
last_execution_time	The time of the last execution of the query.
collection_timestamp	The timestamp of when the data was collected.

Metrics	Description
query_id	The unique identifier for the query.
query_text	The text of the query.
database_name	The name of the database.
schema_name	The name of the schema.
last_execution_timestamp	The timestamp of the last execution of the query.
execution_count	The number of times the query was executed.
avg_cpu_time_ms	The average CPU time in milliseconds.
avg_elapsed_time_ms	The average elapsed time in milliseconds.
avg_disk_reads	The average number of disk reads.
avg_disk_writes	The average number of disk writes.
statement_type	The type of SQL statement.
collection_timestamp	The timestamp of when the data was collected.

Microsoft SQL Server's integration query level monitoring

Preview

Prerequisites:

To Enable SQL Server and Windows Authentication mode

To Enable TCP

To Enable the query store

Run the below query to confirm if the query store is enabled

To Integrate MSSQL agent

To Configure the MSSQL integration to enable query performance monitoring

Find your data

Metrics collected by Integration

Blocking session metrics

Query Execution Plan Metrics

Wait Event Metrics

Slow Query Metrics

Microsoft SQL Server's integration query level monitoring

Preview

Prerequisites:.css-21sua1{background:none;border:none;width:0;padding:0;}

To Enable SQL Server and Windows Authentication mode

To Enable TCP

To Enable the query store

Run the below query to confirm if the query store is enabled

To Integrate MSSQL agent

To Configure the MSSQL integration to enable query performance monitoring

Find your data

Metrics collected by Integration

Query Execution Plan Metrics

Wait Event Metrics

Slow Query Metrics

Prerequisites: