Monitoring Microsoft SQL Server
The Microsoft SQL Server sensor is automatically deployed and installed after you install the Instana agent.
Supported information
Supported versions
Instana supports the following versions of Microsft SQL Server:
- SQL Server 2016
- SQL Server 2017
- SQL Server 2019
- SQL Server 2022
Supported operating systems
- Windows
- Linux
Supported client-side tracing
For this technology, Instana supports client-side tracing for the following languages and runtimes:
Configuring the MS SQL Server sensor
You must install the Instana host agent on the same system in which Microsoft SQL Server is installed. To install the Instana host agent on Windows, see the Installing the host agent on Windows topic. To install the Instana host agent on Linux, see the Installing the host agent on Linux topic.
After the Instana agent is installed, the MS SQL Server sensor is enabled by default. You can choose how the Instana agent connects to the SQL Server as follows:
-
SQL Server Authentication: If you provide the username and password (clear-text) in the agent configuration file
<instana-agent-dir>/etc/instana/configuration.yaml
, the agent connects to the SQL Server by using the SQL Server authentication.com.instana.plugin.mssql: user: '' password: '' poll_rate: 1 # seconds top_queries_poll_rate: 60 # seconds
-
Windows Authentication: If you leave the configuration section in the agent configuration file commented out or leave the username and password empty, the agent tries to connect by using Windows Authentication. When the Windows Authentication is used, the agent authenticates with the credentials that it is running under.
-
poll_rate
: Specify the poll rate. By default, the value is 1 second. -
top_queries_poll_rate
: Specify the poll rate for top queries. By default, the value is 60 seconds.When the Windows Authentication is used, ensure that your account has appropriate permissions as described in the Required server or database permissions section.
Network settings
If you are running the Microsoft SQL database and the Instana agent on a Windows system, ensure that the TCP/IP protocol is enabled and the SQL Server Browser service is running.
Setting a custom port
If you want to specify a custom port that the sensor must use to monitor the instance (other than the default port 1433), specify the port field in the agent configuration file:
com.instana.plugin.mssql:
port: 2529 # custom Microsoft SQL TCP port
Required server or 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. For more information on these permissions, see System dynamic management views.
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 |