Get a count of SQL connections by IP address

Here's a handy SQL query to get an overview of your active SQL Server connections and sessions. It helps you quickly see who is connected, from where, and how many connections they have open. This can be super useful for monitoring activity or troubleshooting connection issues.

SELECT ec.client_net_address
 ,es.[program_name]
 ,es.[host_name]
 ,es.login_name
 ,COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) ON es.session_id = ec.session_id
GROUP BY ec.client_net_address
 ,es.[program_name]
 ,es.[host_name]
 ,es.login_name
ORDER BY ec.client_net_address
 ,es.[program_name]
OPTION (RECOMPILE);

This query joins two dynamic management views (DMVs):

• sys.dm_exec_connections: Provides information about the physical connections to the SQL Server.
• sys.dm_exec_sessions: Gives details about each active session on the SQL Server.

By grouping and counting, we can easily see the number of connections per unique combination of client IP address, program name, host name, and login name. The OPTION (RECOMPILE) ensures the query plan is recompiled each time it runs, which can be beneficial for DMVs that frequently change data.

Get tempdb version store space usage by database

Looking to understand how much space the version store is using in your SQL Server tempdb? The sys.dm_tran_version_store_space_usage dynamic management view is your go-to. It provides a table showing the total tempdb space consumed by version store records for each database.

This view is super efficient and inexpensive to run because it doesn't dig into individual version store records. Instead, it gives you an aggregated view of the version store space used in tempdb on a per-database basis. That makes it perfect for quick checks and monitoring!

Retrieve Version Store Space Usage

SELECT DB_NAME(database_id) AS [Database Name],
       reserved_page_count AS [Version Store Reserved Page Count],
       reserved_space_kb / 1024 AS [Version Store Reserved Space (MB)]
FROM sys.dm_tran_version_store_space_usage WITH (NOLOCK)
ORDER BY reserved_space_kb / 1024 DESC
OPTION (RECOMPILE);

This query will give you a clear breakdown of the version store space by database, ordered from largest to smallest.

For more SQL Server insights, check out this article on getting your TempDB files count.

You can also find more detailed documentation on sys.dm_tran_version_store_space_usage on the Microsoft Docs website.

CPU utilization trends

Track SQL Server CPU Usage for the Last 256 Minutes

Ever wonder how your SQL Server has been utilizing its CPU over time? This handy T-SQL script allows you to view the CPU utilization history for the last 256 minutes, broken down into one-minute intervals. It's a fantastic way to quickly pinpoint any performance bottlenecks or trends related to CPU usage.

The SQL Script

DECLARE @ts_now BIGINT = (
  SELECT cpu_ticks / (cpu_ticks / ms_ticks)
  FROM sys.dm_os_sys_info WITH (NOLOCK)
  );

SELECT TOP (256) SQLProcessUtilization AS [SQL Server Process CPU Utilization]
 ,SystemIdle AS [System Idle Process]
 ,100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
 ,DATEADD(ms, - 1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
 SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
  ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle]
  ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization]
  ,[timestamp]
 FROM (
  SELECT [timestamp]
    ,CONVERT(XML, record) AS [record]
  FROM sys.dm_os_ring_buffers WITH (NOLOCK)
  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
    AND record LIKE N'%<SystemHealth>%'
  ) AS x
 ) AS y
ORDER BY record_id DESC
OPTION (RECOMPILE);

How It Works

This script queries `sys.dm_os_ring_buffers`, specifically looking at the `RING_BUFFER_SCHEDULER_MONITOR` type. This ring buffer captures system health events, including CPU utilization. By parsing the XML output from the `record` column, we can extract details like `SystemIdle` (how much CPU is idle), `SQLProcessUtilization` (how much CPU SQL Server is using), and then calculate the CPU usage by other processes.

For more in-depth information about `sys.dm_os_ring_buffers` and how it's used, check out this great resource: Inside sys.dm_os_ring_buffers.

Give this script a try and let us know in the comments if you found it useful for your SQL Server monitoring!

Hardware information from SQL Server

This query provides valuable basic hardware information about your database server.

It's important to note that a 'HYPERVISOR' value for 'virtual_machine_type_desc' doesn't automatically confirm SQL Server is running inside a virtual machine. This merely indicates that a hypervisor is present on your host machine.

SELECT cpu_count AS [Logical CPU Count], scheduler_count, 
       (socket_count * cores_per_socket) AS [Physical Core Count], 
       socket_count AS [Socket Count], cores_per_socket, numa_node_count,
       physical_memory_kb/1024 AS [Physical Memory (MB)], 
       max_workers_count AS [Max Workers Count], 
       affinity_type_desc AS [Affinity Type], 
       sqlserver_start_time AS [SQL Server Start Time],
       DATEDIFF(hour, sqlserver_start_time, GETDATE()) AS [SQL Server Up Time (hrs)],
       virtual_machine_type_desc AS [Virtual Machine Type], 
       softnuma_configuration_desc AS [Soft NUMA Configuration], --SQL Server 2016
       sql_memory_model_desc, --Added in SQL Server 2016 SP1
       container_type_desc -- New in SQL Server 2019
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

For more in-depth information, you can explore the following resources:

• Soft-NUMA (SQL Server)
• sys.dm_os_sys_info

Check all drive space on the server

Ever need to quickly check the available space on your SQL Server drives? Here are a couple of handy SQL queries that can help you out. We'll explore using a dynamic management view and a system stored procedure.

Method 1: Using `sys.dm_os_enumerate_fixed_drives`

This dynamic management view provides detailed information about fixed drives, including their path, type, and free space. It's a robust method for obtaining precise data.

SELECT fixed_drive_path
    ,drive_type_desc
    ,CONVERT(DECIMAL(18, 2), free_space_in_bytes / 1073741824.0) AS [Available Space (GB)]
FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK)
OPTION (RECOMPILE);

Method 2: Using `xp_fixeddrives`

For a simpler and quicker overview, the `xp_fixeddrives` extended stored procedure is a great option. It returns a summary of free space on fixed drives.

xp_fixeddrives

You can find more in-depth information about sys.dm_os_enumerate_fixed_drives on the Microsoft documentation.

---

Further Reading on SQL Server Space Management

If you're interested in learning more about managing space in SQL Server, check out these related posts:

• Get TempDB Version Store Space Usage by Database
• Log File Used Space

Resource Governor Resource Pool information

Ever wondered about the current status of your SQL Server resource pools? This quick query provides valuable insights into their performance and memory utilization.

SELECT
    pool_id,
    [Name],
    statistics_start_time,
    min_memory_percent,
    max_memory_percent,
    max_memory_kb / 1024 AS [max_memory_mb],
    used_memory_kb / 1024 AS [used_memory_mb],
    target_memory_kb / 1024 AS [target_memory_mb],
    min_iops_per_volume,
    max_iops_per_volume
FROM
    sys.dm_resource_governor_resource_pools WITH (NOLOCK)
OPTION (RECOMPILE);

This query leverages the sys.dm_resource_governor_resource_pools dynamic management view to give you a clear picture of how your resource governor is managing your SQL Server resources.

Get any memory dumps from SQL Server

Understanding the status of memory dumps in your SQL Server instance is crucial for troubleshooting and performance analysis. This post provides a simple method to quickly get details about any memory dumps, including their location, when they occurred, and their size.

Retrieve SQL Server Memory Dump Information

You can use the following SQL query to access data from the sys.dm_server_memory_dumps dynamic management view. This view provides valuable insights into recent memory dump events.

SELECT
    [filename],
    creation_time,
    size_in_bytes / 1048576.0 AS [Size (MB)]
FROM
    sys.dm_server_memory_dumps WITH (NOLOCK)
ORDER BY
    creation_time DESC
OPTION (RECOMPILE);

This query will return the **filename** of the dump, its **creation time**, and its **size in megabytes**. The results are ordered by creation time, showing the most recent dumps first.

For more in-depth information about the sys.dm_server_memory_dumps view, you can refer to the official Microsoft documentation: sys.dm_server_memory_dumps on Microsoft Docs

Related SQL Server Memory Management Resources

• Get Memory Clerk Usage
• Get In-Memory OLTP Index Usage
• Check Memory Pressure on the Server
• Memory Grants Pending Value
• Resource Governor Resource Pool Information