Microsoft SQL Server Monitoring

Developed by Microsoft, SQL Server is a relational database management system that is used for the primary purpose of storing and retrieving data as requested by other software applications. Note that those applications may run either on the same computer or network as one another, or across another network (and even the Internet).

The protocol layer of SQL Server is what implements the external interface. All operations that can be performed on SQL Server are communicated to the interface via the Tabular Data Stream, or TDS. This is an application layer protocol that is used primarily to transfer information between a database server (in this case, SQL) and a client. All data within SQL Server is stored in a database, which itself is a collection of tables with typed columns containing relevant information. SQL Server supports a large number of different data types, like integers, decimals, character strings, binary, text and others.

All of this brings with it a number of key benefits that organizations would be hard-pressed to find elsewhere. Thanks to the intelligent features and security that are built into Microsoft SQL Server, innovating on-premises has never been faster (or easier). Microsoft SQL Server can even accelerate an enterprise’s move into the cloud by allowing it to easily migrate without changing even a single line of code. Enterprises can gain transformative insights thanks to an AI-driven platform, build more intelligent apps by capitalizing on a rich set of cognitive services and much, much more.

Why use a Telegraf plugin for Microsoft SQL Server monitoring?

Because of the many critical ways in which Microsoft SQL Server is used, it is important to ensure the availability, reachability and performance of these databases as well as understanding the response time of a request or query to avoid poor user experiences.

Thankfully, using the Microsoft SQL Server Telegraf plugin to monitor the state of your instance is easy. It can quickly gather all the key metrics from your instance to provide you with the important insights and you can combine these metrics with metrics from the other parts of your stack to get a comprehensive view of your entire application stack.

How to monitor Microsoft SQL server using the Telegraf plugin

The Microsoft SQL Server Telegraf plugin is a ready-to-use collector agent that collects metrics from your Microsoft SQL Server instance. Collecting metrics with this plugin is easy. Just point the Telegraf plugin to your instance to start collecting all the metrics available from your Microsoft SQL Server instance. The recorded metrics are lightweight and use Dynamic Management Views supplied by SQL Server.

Once you have started collecting your Microsoft SQL Server metrics in InfluxDB, you can build powerful dashboards in InfluxDB, Grafana, or any other visualization to start monitoring and identifying performance optimizations.

Key Microsoft SQL Server metrics to use for monitoring

Some of the important Microsoft SQL Server metrics that you should proactively monitor include:

• Database IO: IO stats from sys.dm_io_virtual_file_stats
• Memory Clerk: Memory clerk breakdown from sys.dm_os_memory_clerks, most clerks have been given a friendly name
• Performance Counters such as: Activity (Transactions/sec/db, Batch requests/sec, blocked processes), Availability Groups (Bytes sent to replica, Bytes received from replica, Log bytes received, Log send queue, transaction delay), Log activity (Log bytes flushed/sec, Log flushes/sec, Log Flush Wait Time)
• Memory: PLE, Page reads/sec, Page writes/sec
• TempDB: Free space, Version store usage, Active temp tables, temp table creation rate
• Resource Governor: CPU Usage, Requests/sec, Queued Requests, and Blocked tasks per workload group + more
• Server properties: Number of databases in all possible states (online, offline, suspect, etc.), cpu count, physical memory, SQL Server service uptime, and SQL Server version. In the case of Azure SQL relevant properties such as Tier, #Vcores, Memory etc.
• Wait stats: Wait time in ms, number of waiting tasks, resource wait time, signal wait time, max wait time in ms, wait type, and wait category. The waits are categorized using the same categories used in Query Store.
• Schedulers
• SqlRequests: This captures a snapshot of dm_exec_requests and dm_exec_sessions that gives you running requests as well as wait types and blocking sessions.

For Azure Managed Instances, you can gather metrics such as:

• cpu_count
• server_memory
• sku
• engine_edition
• hardware_type
• total_storage_mb
• available_storage_mb
• uptime

Project URL   Documentation