How to Identify Inlineable Scalar UDFs in SQL Server 2019 and later

Summary: Use sys.sql_modules to identify which Scalar User-Defined Functions (UDFs) are eligible for inlining to boost query performance in SQL Server 2019.

Check if Your SQL Scalar Functions are Inlineable

When you're working with SQL Server 2019 (and later), understanding the properties of your functions can be critical for performance optimization. Scalar UDF Inlining is a game-changing feature that automatically transforms scalar functions into relational expressions.

Why Inlining Matters

This query provides insight into whether a scalar UDF can be inlined. If a function is inlineable, SQL Server essentially expands the function's logic directly into the calling query. This avoids the massive performance overhead of row-by-row function calls and context switching.

-- Identify scalar functions and their inlining status
SELECT 
    OBJECT_NAME(m.object_id) AS [Function Name],
    m.is_inlineable,
    m.inline_type,
    efs.total_worker_time,
    efs.execution_count
FROM sys.sql_modules AS m WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_exec_function_stats AS efs WITH (NOLOCK) 
    ON m.object_id = efs.object_id
WHERE efs.type_desc = N'SQL_SCALAR_FUNCTION'
   OR m.object_id IN (SELECT object_id FROM sys.objects WHERE type = 'FN')
OPTION (RECOMPILE);
        

Understanding the Results

• is_inlineable: A value of 1 means the function meets the requirements for inlining.
• inline_type: Indicates whether inlining is currently turned on (1) or off (0) for that specific module.

If your function shows is_inlineable = 0, it might be due to the use of certain non-deterministic functions (like GETDATE()) or specific T-SQL constructs that prevent the optimizer from inlining the code.

---

For more detailed information on requirements and system views, refer to the official Microsoft documentation:

• Scalar UDF Inlining Deep Dive
• sys.sql_modules Reference

Want to speed up your queries? Identifying non-inlineable functions is the first step toward significant performance gains in SQL Server 2019!

How to Analyze SQL Server Execution Plans from Saved XML Files via T-SQL

Summary: Learn how to load saved SQL execution plan (.xml) files into SQL Server, query historical plan handles, and use XQuery to extract performance metrics like CPU and IO costs.

How to Analyze SQL Server Execution Plans from a Saved XML File

If you have an execution plan saved as an XML file (often with a .sqlplan extension), you aren't limited to just viewing it graphically. You can load it into SQL Server and query the XML directly to find specific performance bottlenecks. Here is how to read and analyze it using T-SQL.

1. Load the Execution Plan File

The OPENROWSET function is the most efficient way to pull an external .xml file into a SQL variable for processing.

DECLARE @xml XML;

-- Load file content into XML variable
SELECT @xml = CAST(FileData.BulkColumn AS XML)
FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) AS FileData;

-- Verify the content
SELECT @xml AS LoadedPlan;
        

2. Retrieve Historical Queries via DMVs

If you don't have the file but need to find a plan for a recently executed query, use the Dynamic Management Views (DMVs). This snippet searches for a specific text pattern in your plan cache.

SELECT TOP 100 
    execquery.last_execution_time AS [Date_Time],
    execsql.TEXT AS [Script],
    execquery.plan_handle
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
WHERE execsql.TEXT LIKE '%WITH Temp_Accounts (%'
ORDER BY execquery.last_execution_time DESC;
        

3. Deep-Dive Analysis using XQuery

Once your plan is in an XML variable, use XQuery and namespaces to extract specific attributes like PhysicalOp, EstimateCPU, and EstimatedTotalSubtreeCost for every operator in the plan.

-- Define the namespace for SQL Server Showplans
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
    c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
    c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'float') AS SubtreeCost,
    c.value('.[1]/@EstimateRows', 'float') AS EstimatedRows,
    c.value('.[1]/@EstimateCPU', 'float') AS EstimateCPU,
    c.query('.') AS NodeXml
FROM @xml.nodes('//RelOp') AS T(c)
ORDER BY SubtreeCost DESC;
        

💡 Final Thoughts

By leveraging SQL Server's XQuery capabilities and DMVs, you can gain deep insights into query performance behavior directly from saved files. This method is essential for automated performance auditing or comparing plans across different server environments.

Found this optimization guide helpful? Share it with your SQL community or subscribe for more advanced performance tuning tips!

Track SQL Server CPU Usage History for the Last 256 Minutes

Summary: Use this T-SQL script to extract historical CPU utilization from SQL Server ring buffers, providing a minute-by-minute breakdown of SQL usage vs. system idle time.

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 without needing external monitoring tools.

The CPU History 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 CPU %],
    SystemIdle AS [System Idle %],
    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU %],
    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 targeting the RING_BUFFER_SCHEDULER_MONITOR. This internal ring buffer captures system health events, including CPU utilization, directly from the SQLOS scheduler.

By parsing the XML output, we extract:

• SQLProcessUtilization: Percentage of CPU used by the SQL Server process.
• SystemIdle: Percentage of CPU that is currently unused.
• Other Process Utilization: Calculated as the remaining CPU used by Windows or other background applications.

For more in-depth information about how SQL Server manages internal tracking, check out the community resource: Inside sys.dm_os_ring_buffers.

Did this script help you find a bottleneck? Let us know in the comments below or check out our other performance tuning guides!

How to Find Tables Without a Primary Key in SQL Server

Summary: Use this T-SQL script to audit your database schema and identify any tables lacking a primary key, a critical step for ensuring data integrity and performance.

Identify Tables Without a Primary Key

In SQL Server, primary keys play a crucial role in maintaining data integrity and optimizing query performance. However, during development or when working with legacy databases, you may come across tables that lack a primary key—either by design or oversight. Identifying these tables is an essential step in ensuring your database is well-structured and reliable.

📌 SQL Query to Find Missing Primary Keys

The query below leverages sys.tables and sys.key_constraints to retrieve a list of all user tables in your current database that do not have a primary key defined:

-- Find tables without a Primary Key
SELECT 
    s.[name] + N'.' + t.[name] AS [Table Name],
    t.create_date AS [Created Date]
FROM sys.tables AS t WITH (NOLOCK)
INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS (
    SELECT 1
    FROM sys.key_constraints AS kc
    WHERE kc.[type] = N'PK'
      AND kc.parent_object_id = t.[object_id]
)
ORDER BY [Table Name]
OPTION (RECOMPILE);
        

🧩 Why This Matters

Tables without primary keys (often referred to as Heaps if they also lack a clustered index) can lead to significant architectural issues:

• Data Duplication: Without a PK, there is no physical way for the engine to enforce row uniqueness at the schema level.
• Performance Degradation: Heaps can lead to "RID Lookups" and inefficient data retrieval patterns.
• Replication Failure: Many features, like Transactional Replication and certain ETL tools, require a primary key to function.
• Join Complications: It becomes difficult to maintain reliable relationships between tables.

---

Optimizing your schema? Once you've identified these tables, consider adding an IDENTITY column or a natural key to improve your database's health!

How to Check SQL Server Disk Space and Volume Stats via T-SQL

Summary: Use sys.dm_os_volume_stats to audit physical drive space, mount points, and file system attributes directly from SQL Server for all database files.

Monitoring Database Volume Stats and Disk Space

Ever wonder about the nitty-gritty details of where your SQL Server databases and files live? There's a powerful dynamic management function (DMF) that spills the beans on the operating system volume where your data resides. It's incredibly useful for auditing physical disk attributes or getting a live view of available free space without leaving SQL Server Management Studio.

Volume Stats and Free Space Query

This script uses a CROSS APPLY to join your master files with their respective volume statistics, providing a consolidated view of every drive currently hosting a SQL Server data or log file.

-- Detailed breakdown of volume stats for all database files
SELECT DISTINCT 
    vs.volume_mount_point AS [Mount Point],
    vs.file_system_type AS [File System],
    vs.logical_volume_name AS [Volume Name],
    CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)],
    CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)],
    CONVERT(DECIMAL(18, 2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %],
    vs.supports_compression,
    vs.is_compressed,
    vs.supports_sparse_files,
    vs.supports_alternate_streams
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY [Mount Point]
OPTION (RECOMPILE);
        

Why This Matters

Traditional methods like xp_fixeddrives only show drive letters. However, sys.dm_os_volume_stats is superior because:

• Mount Point Support: It correctly identifies volumes mounted as folders (Mount Points), which are common in enterprise SAN environments.
• Proactive Alerting: You can use the Space Free % logic to build automated alerts before a disk fills up and takes your database offline.
• Feature Awareness: It identifies if a volume supports modern features like Sparse Files (used by Database Snapshots and CheckDB) or Compression.

For more in-depth documentation on this function, refer to the official Microsoft documentation.

---

Is your disk space running low? Identifying the exact volume and its percentage of free space is the first step in effective capacity planning!

Monitoring In-Memory OLTP Index Performance with sys.dm_db_xtp_index_stats

Summary: Audit Hekaton index efficiency using the sys.dm_db_xtp_index_stats DMV to track scans, retries, and row-level operations for In-Memory SQL Server tables.

Analyzing In-Memory OLTP Index Statistics

Understanding how your SQL Server indexes are performing is crucial for database optimization, especially when using In-Memory OLTP (Hekaton). Because memory-optimized tables utilize different data structures (like Hash and Range indexes), standard index DMVs often won't provide the full picture.

The following query leverages sys.dm_db_xtp_index_stats to provide deep insights into your in-memory index usage and health.

In-Memory Index Usage Query

-- Monitor In-Memory OLTP index activity
SELECT 
    OBJECT_NAME(i.[object_id]) AS [Object Name],
    i.index_id,
    i.[name] AS [Index Name],
    i.[type_desc] AS [Index Type],
    xis.scans_started,
    xis.scans_retries,
    xis.rows_touched,
    xis.rows_returned
FROM sys.dm_db_xtp_index_stats AS xis WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) 
    ON i.[object_id] = xis.[object_id]
    AND i.index_id = xis.index_id
ORDER BY [Object Name], i.index_id
OPTION (RECOMPILE);
        

Key Metrics Explained

• scans_retries: A high number here can indicate excessive transaction collisions or "interference" in the lock-free data structures, suggesting a need for better bucket counts in Hash indexes.
• rows_touched vs. rows_returned: If rows_touched is significantly higher than rows_returned, your index may be scanning through many irrelevant rows (common in poorly tuned Hash indexes with high collision rates).
• scans_started: Reflects the actual utilization frequency of the index by the query optimizer.

For further technical details, refer to the official Microsoft documentation for sys.dm_db_xtp_index_stats.

---

Related Performance Resources

• Retrieving SQL Server Memory Dumps
• Understanding Memory Clerk Usage
• Detecting Server Memory Pressure
• Analyzing Memory Grants Pending
• Resource Governor & Pool Configuration

Optimizing In-Memory performance? Ensure your Hash Index bucket counts are roughly 1x to 2x the number of unique values in your indexed column for maximum efficiency!

How to Check SQL Server Memory Pressure and Usage using DMVs

Summary: Use sys.dm_os_process_memory and sys.dm_os_sys_memory to monitor SQL Server memory health, detect low memory conditions, and audit physical vs. virtual memory usage.

Monitoring SQL Server Memory Health and Performance

When working with SQL Server, monitoring memory is crucial for optimal performance. These queries provide valuable insights into your system's memory state, helping you identify and address potential memory pressure issues before they impact your users.

---

1. Process Memory Low Conditions

The following query helps determine if your SQL Server process is experiencing low physical or virtual memory. Ideally, both values should be 0.

-- Check for low memory flags (0 = Normal, 1 = Low)
SELECT 
    m.process_physical_memory_low,
    m.process_virtual_memory_low
FROM sys.dm_os_process_memory m;
        

2. Current Instance Memory Usage

This query displays the amount of physical memory (RAM) currently consumed by your SQL Server instance.

-- Total memory currently used by the SQL process
SELECT 
    m.physical_memory_in_use_kb / 1024 AS [Mem Used in MB]
FROM sys.dm_os_process_memory m;
        

3. Overall System Memory Status

Gain a comprehensive overview of the host's memory health. The system_memory_state_desc is the most important field for a quick health check.

-- System-level memory overview
SELECT 
    m.total_physical_memory_kb / 1024 AS [Total Mem MB],
    m.available_physical_memory_kb / 1024 AS [Available Mem MB],
    m.system_memory_state_desc AS [Memory State]
FROM sys.dm_os_sys_memory m;
        

4. OS Memory & Page File Details

For deep troubleshooting, monitor the page file and system cache. For optimal performance, the state should be "Available physical memory is high."

-- Detailed OS memory audit
SELECT 
    total_physical_memory_kb/1024 AS [Total Memory (MB)],
    available_physical_memory_kb/1024 AS [Available Memory (MB)],
    total_page_file_kb/1024 AS [Total Page File (MB)],
    available_page_file_kb/1024 AS [Available Page File (MB)],
    system_cache_kb/1024 AS [System Cache (MB)],
    system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) 
OPTION (RECOMPILE);
        

Understanding Memory State Descriptions:

• Available physical memory is high: System health is excellent.
• Physical memory usage is steady: Standard operational state.
• Available physical memory is low: The OS is under pressure; check for other processes hogging RAM.
• Available physical memory is running low: Critical state; SQL Server may start trimming its working set.

---

Further Reading and Resources:

• Official Docs: sys.dm_os_sys_memory
• How to Get SQL Server Memory Dumps
• Analyzing Memory Clerk Usage
• Understanding Memory Grants Pending

Experiencing high Page Life Expectancy (PLE) drops? Use these scripts to verify if the OS is forcing SQL Server to release memory back to the system!

How to Audit SQL Server Columnstore Index Physical Stats and Fragmentation

Summary: Use sys.dm_db_column_store_row_group_physical_stats to audit the health of your Columnstore indexes, monitor delta store growth, and calculate row-group fragmentation.

Analyzing Columnstore Index Physical Health

Ever wondered how to peek behind the curtains of your SQL Server's Columnstore indexes? Unlike traditional B-Tree indexes, Columnstore indexes are composed of row groups that can be in various states (Open, Closed, or Compressed).

This handy query gives you a detailed look at their physical stats, helping you understand their internal health and identify when it's time for an index reorganize or rebuild.

Columnstore Row Group Physical Stats Query

-- Audit Columnstore row group health and fragmentation
SELECT 
    OBJECT_NAME(ps.object_id) AS [TableName],
    i.[name] AS [IndexName],
    ps.index_id,
    ps.partition_number,
    ps.delta_store_hobt_id,
    ps.state_desc,
    ps.total_rows,
    ps.size_in_bytes,
    ps.trim_reason_desc,
    ps.generation,
    ps.transition_to_compressed_state_desc,
    ps.has_vertipaq_optimization,
    ps.deleted_rows,
    CAST(100.0 * (ISNULL(ps.deleted_rows, 0)) / NULLIF(ps.total_rows, 0) AS DECIMAL(5,2)) AS [Fragmentation %]
FROM sys.dm_db_column_store_row_group_physical_stats AS ps WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) 
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
ORDER BY [TableName], ps.partition_number, ps.row_group_id
OPTION (RECOMPILE);
        

Key Metrics to Monitor

• state_desc: Look for OPEN row groups. If you have too many small open row groups, it can indicate "trickle inserts" which hurt compression and performance.
• Fragmentation %: In Columnstore, fragmentation is caused by deleted_rows. Since rows aren't physically removed from compressed segments immediately, high fragmentation means you are reading "dead" data into memory.
• trim_reason_desc: This tells you why a row group was closed (e.g., BULKLOAD or MEMORY_LIMITATION). If you see MEMORY_LIMITATION frequently, your server may need more RAM to build optimal segments.

This SQL script dives into sys.dm_db_column_store_row_group_physical_stats to provide deep insights. It's an essential tool for any DBA managing large-scale data warehouses or real-time operational analytics.

---

Is your Fragmentation above 20%? Run ALTER INDEX ... REORGANIZE to physically remove deleted rows and merge smaller row groups for better compression!

Comprehensive SQL Server Database Audit: Logs, Recovery Models, and Settings

Summary: Use this advanced T-SQL query to audit all databases on your instance. Track Transaction Log usage, Recovery Models, Compatibility Levels, and TDE Encryption status in a single view.

Deep Dive: Auditing SQL Server Database Health and Configuration

Monitoring the health of your databases requires more than just checking if they are "online." A proactive DBA needs to understand the Transaction Log usage, Recovery Models, and critical configuration flags that impact performance and disaster recovery.

The following query provides a powerful, single-pane-of-glass view into every database on your instance, gathering crucial insights from across multiple system views and performance counters.

SQL Database Audit Script

-- Comprehensive Database Property and Log Usage Audit
SELECT 
    db.[name] AS [Database Name],
    SUSER_SNAME(db.owner_sid) AS [Database Owner],
    db.recovery_model_desc AS [Recovery Model],
    db.state_desc AS [State],
    db.log_reuse_wait_desc AS [Log Reuse Wait],
    CONVERT(DECIMAL(18, 2), ls.cntr_value / 1024.0) AS [Log Size (MB)],
    CONVERT(DECIMAL(18, 2), lu.cntr_value / 1024.0) AS [Log Used (MB)],
    CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log Used %],
    db.[compatibility_level] AS [Compatibility],
    db.page_verify_option_desc AS [Page Verify],
    db.is_auto_create_stats_on AS [Auto-Create Stats],
    db.is_auto_update_stats_on AS [Auto-Update Stats],
    db.is_read_committed_snapshot_on AS [RCSI Enabled],
    db.is_auto_close_on AS [Auto-Close],
    db.is_auto_shrink_on AS [Auto-Shrink],
    db.is_query_store_on AS [Query Store],
    db.is_encrypted AS [Is Encrypted],
    de.encryption_state AS [TDE State],
    db.is_accelerated_database_recovery_on AS [ADR Enabled]
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) 
    ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) 
    ON db.name = ls.instance_name
LEFT OUTER JOIN sys.dm_database_encryption_keys AS de WITH (NOLOCK) 
    ON db.database_id = de.database_id
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
    AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
    AND ls.cntr_value > 0
ORDER BY db.[name]
OPTION (RECOMPILE);
        

Why This Matters for Performance

• Log Reuse Wait: If your log is full, this column tells you why. Common reasons include LOG_BACKUP (needs a transaction log backup) or ACTIVE_TRANSACTION (an uncommitted transaction is pinning the log).
• Auto-Close/Auto-Shrink: Red Flag! In production, these should almost always be 0. Auto-close causes performance overhead on every connection, and Auto-shrink causes massive fragmentation.
• Compatibility Level: Ensure your databases are taking advantage of the latest Query Optimizer features by aligning this with your current SQL Server version.
• RCSI Enabled: Read Committed Snapshot Isolation can significantly reduce locking and blocking in highly concurrent environments.

---

This query leverages two key Dynamic Management Views (DMVs) for its data:

• sys.dm_os_performance_counters: Provides real-time log usage data.
• sys.dm_database_encryption_keys: Essential for tracking the progress of Transparent Data Encryption (TDE) operations.

Is your Transaction Log out of control? Identifying the Log Reuse Wait Description is the first step toward fixing log growth issues and avoiding "Disk Full" errors!

SQL Server I/O Analysis: Identify Top Databases by Read/Write Activity

Summary: Optimize SQL Server performance by analyzing I/O statistics. Use this T-SQL script to rank databases by their total, read, and write MB usage using the sys.dm_io_virtual_file_stats DMV.

Analyzing Database I/O Statistics in SQL Server

Understanding and monitoring database I/O is crucial for maintaining a healthy and performant SQL Server. High I/O can often indicate hardware bottlenecks, poorly indexed queries, or insufficient memory.

The following query provides a clear breakdown of I/O activity across your entire instance, ranking databases by their total disk throughput.

---

I/O Performance Script

This script calculates MB and percentage of total activity to help you visualize the I/O distribution on your server.

WITH IOStat
AS (
 SELECT DB_NAME(database_id) AS [Database Name]
  ,CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS [TotalMB]
  ,CAST(SUM(num_of_bytes_read) / 1048576 AS DECIMAL(12, 2)) AS [ReadMB]
  ,CAST(SUM(num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS [WriteMB]
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
 GROUP BY database_id
 )
SELECT ROW_NUMBER() OVER (
  ORDER BY TotalMB DESC
  ) AS [I/O Rank]
 ,[Database Name]
 ,TotalMB AS [I/O Total (MB)]
 ,CAST(TotalMB / SUM(TotalMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [I/O Total %]
 ,ReadMB AS [I/O Read (MB)]
 ,CAST(ReadMB / SUM(ReadMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [I/O Read %]
 ,WriteMB AS [I/O Write (MB)]
 ,CAST(WriteMB / SUM(WriteMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [I/O Write %]
FROM IOStat
ORDER BY [I/O Rank]
OPTION (RECOMPILE);
        

How it Works

This query leverages the sys.dm_io_virtual_file_stats Dynamic Management View (DMV). Unlike many other views, this one provides cumulative data since the SQL Server service was last started.

• Database Name: Uses DB_NAME() to resolve IDs into readable names.
• I/O Total %: Helps you identify which specific database is "hogging" the disk bandwidth.
• Read vs Write: Distinguishing between high reads and high writes can help you decide whether to focus on index tuning (reads) or storage subsystem write-latency (writes).

---

Official Reference:

For a deeper dive into the specific columns available in this DMV, visit the Microsoft Docs: sys.dm_io_virtual_file_stats .

Monitoring Tip: If you see a database with a very high I/O Read %, check for missing indexes or large table scans that are forcing data to be repeatedly pulled from disk into the Buffer Pool!