Microsoft SQL Server monitoring

Learn how to monitor a Microsoft SQL Server instance using the ActiveGate extension for Microsoft SQL Server.

Prerequisites

  • Environment ActiveGate
  • Host and port of the Microsoft SQL Server instance
  • Database user with SQL read access to sys.dm_os_performance_counters and sys.dm_os_workers

Extension installation

To install the extension

  1. Get the ZIP install file from your Dynatrace ONE Product Specialist via live chat. Don't rename the file.

  2. Unzip the ZIP file to the plugin_deployment directory of your ActiveGate host.

  3. In Dynatrace, go to Settings > Monitoring > Monitored technologies and select Add new technology monitoring.

  4. Select Add ActiveGate extension.

  5. Select Upload extension and upload the ZIP file.

  6. After you upload the extension, go to Settings > Monitoring > Monitored technologies and switch to the Custom extensions tab.

  7. Find the extension and select it to open it to display Add new endpoint.

  8. Enter the required endpoint information.

    Setting Details

    Endpoint name

    A meaningful endpoint name.

    Microsoft SQL Server address

    The address of the Microsoft SQL Server.

    Microsoft SQL Server user name and Microsoft SQL Server user password

    A user name and password to access the SQL server. The user requires read access to sys.dm_os_performance_counters and sys.dm_os_workers.

    Choose ActiveGate

    The ActiveGate that will run the extension.

Troubleshoot ActiveGate extensions

See Troubleshoot ActiveGate extensions.

Metrics

The following metrics are available.

Latch metrics

The SQLServer:Latches object in Microsoft SQL Server provides counters to monitor internal SQL Server resource locks called latches. Monitoring the latches to determine user activity and resource usage can help you to identify performance bottlenecks.

Metric name Metric description

Average Latch Wait Time (ms)

Average latch wait time (in milliseconds) for latch requests that had to wait.

Latch Waits/sec

Number of latch requests that could not be granted immediately.

Total Latch Wait Time (ms)

Total latch wait time (in milliseconds) for latch requests in the last second.

Wait statistics

The SQLServer:Wait Statistics performance object contains performance counters that report information about wait status.

Metric name Metric description

Average Wait Time (ms)

Average time for the selected type of wait.

General statistics

The SQLServer:General Statistics object in SQL Server provides counters to monitor general server-wide activity, such as the number of current connections and the number of users connecting and disconnecting per second from computers running an instance of SQL Server. This can be useful when you are working on large online transaction processing (OLTP) type systems where there are many clients connecting and disconnecting from an instance of SQL Server.

Metric name Metric description

User Connections

Counts the number of users currently connected to SQL Server.

Logins/sec

Total number of logins started per second. This does not include pooled connections.

Processes blocked

Number of currently blocked processes.

Database service availability

Availability of the database host.

Lock statistics

The SQLServer:Locks object in Microsoft SQL Server provides information about SQL Server locks on individual resource types. Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released. Minimizing locks increases concurrency, which can improve performance. Multiple instances of the Locks object can be monitored at the same time, with each instance representing a lock on a resource type.

Metric name Metric description

Lock Timeouts/sec

Number of lock requests per second that timed out, including requests for NOWAIT locks.

Lock Wait Time (ms)

Total wait time (in milliseconds) for locks in the last second.

Lock Waits/sec

Number of lock requests per second that required the caller to wait.

Total Lock Wait Time (sec)

The total time for which database is in locked state.

Number of Deadlocks/sec

Number of lock requests per second that resulted in a deadlock.

SQL statistics

The SQLServer:SQL Statistics object in SQL Server provides counters to monitor compilation and the type of requests sent to an instance of SQL Server. Monitoring the number of query compilations and recompilations and the number of batches received by an instance of SQL Server gives you an indication of how quickly SQL Server is processing user queries and how effectively the query optimizer is processing the queries.

Compilation is a significant part of a query's turnaround time. In order to save the compilation cost, the database engine saves the compiled query plan in a query cache. The objective of the cache is to reduce compilation by storing compiled queries for later reuse, therefore ending the requirement to recompile queries when later executed. However, each unique query must be compiled at least one time. Query recompilations can be caused by the following factors:

  • Schema changes, including base schema changes such as adding columns or indexes to a table, or statistics schema changes such as inserting or deleting a significant number of rows from a table.
  • Environment (SET statement) changes. Changes in session settings such as ANSI_PADDING or ANSI_NULLS can cause a query to be recompiled.
Metric name Metric description

Batch Requests/sec

Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High batch requests mean good throughput.

SQL Compilations/sec

Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles caused by statement-level recompilations in SQL Server. After SQL Server user activity is stable, this value reaches a steady state.

SQL Re-Compilations/sec

Number of statement recompiles per second. Counts the number of times statement recompiles are triggered. Generally, you want the recompiles to be low.

Memory statistics

Monitoring server and process memory usage to gauge user activity and resource usage can help you to identify performance bottlenecks. Monitoring the memory used by an instance of SQL Server can help determine:

  • If bottlenecks exist from inadequate physical memory for storing frequently accessed data in cache. If memory is inadequate, SQL Server must retrieve the data from disk.
  • If query performance can be improved by adding more memory or by making more memory available to the data cache or SQL Server internal structures.
Metric name Metric description

Target Server Memory (KB)

Indicates the ideal amount of memory the server can consume.

Total Server Memory (KB)

Specifies the amount of memory the server has committed using the memory manager.

Connection Memory (KB)

Specifies the total amount of dynamic memory the server is using for maintaining connections.

Memory Grants Outstanding

Specifies the total number of processes that have successfully acquired a workspace memory grant.

Memory Grants Pending

Specifies the total number of processes waiting for a workspace memory grant.

Page faults per second

Indicates the number of page faults that are incurred by the SQL Server process per second.

Database statistics

Monitor transactions and the transaction log to determine how much user activity is occurring in the database and how full the transaction log is becoming. The amount of user activity can determine the performance of the database and affect log size, locking, and replication. Monitoring low-level log activity to gauge user activity and resource usage can help you to identify performance bottlenecks.

Multiple instances of the Databases object, each representing a single database, can be monitored at the same time.

Metric name Metric description

Log File(s) Size (KB)

Cumulative size (in kilobytes) of all the transaction log files in the database.

Log File(s) Used Size (KB)

The cumulative used size of all the log files in the database.

Log Flush Waits/sec

Number of commits per second waiting for the log flush.

Log Growths

Total number of times the transaction log for the database has been expanded.

Log Shrinks

Total number of log shrinks for this database.

Log Truncations

The number of times the transaction log has been truncated (in Simple Recovery Model).

Percent Log Used

Percentage of space in the log that is in use.

Transactions/sec

Number of transactions started for the database per second. Transactions/sec does not count XTP-only transactions (transactions started by a natively compiled stored procedure).

Buffer Manager statistics

The Buffer Manager object provides counters to monitor how SQL Server uses:

  • Memory to store data pages.
  • Counters to monitor the physical I/O as SQL Server reads and writes database pages.
  • Buffer pool extension to extend the buffer cache by using fast non-volatile storage such as solid-state drives (SSD).

Monitoring the memory and the counters used by SQL Server helps you determine:

  • If bottlenecks exist from inadequate physical memory. If it cannot store frequently accessed data in cache, SQL Server must retrieve the data from disk.
  • If query performance can be improved by adding more memory, or by making more memory available to the data cache or SQL Server internal structures.
  • How often SQL Server needs to read data from disk. Compared with other operations, such as memory access, physical I/O consumes a lot of time. Minimizing physical I/O can improve query performance.
Metric name Metric description

Page reads/sec

Indicates the number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.

Page writes/sec

Indicates the number of physical database page writes that are issued per second.

Page life expectancy

Indicates the number of seconds a page will stay in the buffer pool without references.

Free list stalls/sec

Indicates the number of requests per second that had to wait for a free page.

Checkpoint pages/sec

Indicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.

Buffer cache hit ratio

Indicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.

Scheduler statistics

Metric name Metric description

Worker threads allocated

This count includes workers that are not assigned any task.

Worker threads active

Number of workers that are active. An active worker is never preemptive, must have an associated task, and is either running, runnable, or suspended.