Monitoring Microsoft SQL Server

The Microsoft SQL Server sensor is automatically deployed and installed after you install the Instana agent.

Supported versions

Instana supports the following versions of Microsft SQL Server:

  • SQL Server 2016
  • SQL Server 2017
  • SQL Server 2019
  • SQL Server 2022

Configuration

Install the Instana agent on the same machine in which Microsoft SQL is installed. To install the Instana agent on Windows, see the Installing the host agent on Windows topic. To install the Instana agent on Linux, see the Installing the host agent on Linux topic.

When you enable the configuration-section as follows and provide username and password (clear-text), the agent will connect to the SQL Server using SQL Server authentication. If you leave the configuration-section commented out or leave username and password empty, the agent will try to connect using Windows Authentication.

When Windows Authentication is used, the agent authenticates with the credentials that it is running under. Ensure that this account has appropriate permissions as described in the Required Server/Database Permissions section.

If you are running the Microsoft SQL database and the Instana agent on a Windows machine, ensure that the TCP/IP protocol is enabled and the SQL Server Browser service is running.

com.instana.plugin.mssql:
  user: ''
  password: ''

In any case, when running multiple SQL Server instances on the monitored host, make sure the credentials are valid for all of them.

If you want to specify a custom port the sensor should use to monitor the instance (other than the default port 1433), specify the port field in the sensor configuration:

com.instana.plugin.mssql:
  port: 2529    # custom Microsoft SQL TCP port

Required Server/Database Permissions

Instana queries dynamic management views (DMV) on SQL Server by using the user credentials specified in the agent configuration file. Ensure that the SQL user specified in the agent configuration file has the VIEW SERVER STATE and VIEW DATABASE STATE permissions. You can find more information on these permissions here: https://msdn.microsoft.com/en-us/library/ms188754.aspx.

Metrics collection

To view the metrics, select Infrastructure in the sidebar of the Instana User interface, click a specific monitored host, and then you can see a host dashboard with all the collected metrics and monitored processes.

Configuration data

  • Instance Name
  • Version
  • Process ID
  • Start Time

Performance metrics

Metric Description Granularity
User Connections The number of user connections 1 second
Maximum Connection The maximum number of concurrent user connections. By default, the value is 0, which means that an unlimited number of users can connect. To change this value, see Configure the user connections Server Configuration Option. 1 second
Wait-Times on server Page IO-Latch EX, Page IO-Latch SH, Async Network IO, CX-Packet, Writelog 1 second
Reads and Writes Virtual file reads and writes in bytes 1 second
Transactions The number of write transactions 1 second
Errors User, DB Offline and Kill Connection errors 1 second
Locks The number of Lock requests and Deadlocks 1 second
Db Memory Db memory used and capacity in MB 1 second
Virtual Memory Virtual memory in MB 1 second
Response Time Response Time of Transaction 1 second
DB Cache Hit Rate DB Cache Hit Rate in % 1 second

Database

Metric Description Granularity
User Connections The number of user connections 1 second
Reads and Writes Virtual file reads and writes in bytes 1 second
Transactions The number of write transactions 1 second

Top Queries

You will find a section "Top Queries" on the dashboard of your SQL Server. This table lists the top 50 queries (based on the time consumed by these queries) being issued against the monitored instance. Beside the processed SQL Statement you will also find indicators for:

Metric Description Granularity
Execution Time Total and Last Execution Time 60 seconds
Logical Reads Last Logical Reads 60 seconds
Logical Writes Last Logical Writes 60 seconds