Get IO Statistics by database

Understanding and monitoring database I/O is crucial for maintaining a healthy and performant SQL Server. High I/O can often indicate bottlenecks and impact overall system responsiveness. The following SQL query provides a quick and effective way to analyze I/O statistics across your databases, helping you identify which databases are generating the most read and write activity.

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);

This query leverages the **sys.dm_io_virtual_file_stats** Dynamic Management View (DMV), which provides detailed I/O statistics for data and log files. By grouping the results by database, we can easily see which databases are contributing most to the overall I/O workload on your SQL Server instance.

For more in-depth information on the **sys.dm_io_virtual_file_stats** DMV, you can refer to the official Microsoft documentation: sys.dm_io_virtual_file_stats.

Get VLF count for all databases

This post explores how to retrieve information about Virtual Log Files (VLFs) within your SQL Server transaction logs. Understanding VLF distribution is crucial because a large number of VLFs can negatively impact database startup, restore, and recovery times.

---

What are Virtual Log Files (VLFs)?

VLFs are segments within your SQL Server transaction log. While the physical transaction log file appears as one continuous unit, SQL Server internally divides it into these smaller, manageable VLFs. You can learn more about their architecture here.

---

Why VLF Count Matters

An excessive number of VLFs can lead to performance degradation. When SQL Server needs to read or process the transaction log (e.g., during database startup, recovery after a crash, or restoring a backup), it has to process each VLF. More VLFs mean more overhead, extending these critical operations.

---

Querying VLF Information

You can use the sys.dm_db_log_info dynamic management function to inspect VLF details for your databases.

Here's a handy query to identify databases with a high VLF count, ordered from highest to lowest:

SELECT [name] AS [Database Name]
 ,[VLF Count]
FROM sys.databases AS db WITH (NOLOCK)
CROSS APPLY (
 SELECT file_id
 ,COUNT(*) AS [VLF Count]
 FROM sys.dm_db_log_info(db.database_id)
 GROUP BY file_id
 ) AS li
ORDER BY [VLF Count] DESC
OPTION (RECOMPILE);

To see the detailed VLF information for your current database, you can run:

select * from sys.dm_db_log_info(db_id())

---

Further Reading

For more in-depth information about sys.dm_db_log_info, refer to the official Microsoft documentation: dm_db_log_info (Transact-SQL).

SQL Query to Retrieve Alert Information from SQL Server

This SQL query retrieves alert information from the msdb.dbo.sysalerts table, such as alert name, event source, severity, and occurrence details. This can be useful for monitoring SQL Server alerts and troubleshooting purposes.

SELECT name
     ,event_source
     ,message_id
     ,severity
     ,[enabled]
     ,has_notification
     ,delay_between_responses
     ,occurrence_count
     ,last_occurrence_date
     ,last_occurrence_time
FROM msdb.dbo.sysalerts WITH (NOLOCK)
ORDER BY name
OPTION (RECOMPILE);

The query uses the WITH (NOLOCK) hint to avoid blocking and reads uncommitted data. The results are ordered by the name of the alert. The OPTION (RECOMPILE) ensures that SQL Server doesn't cache the query plan, improving performance for dynamic conditions.

Explanation:

• name: The name of the alert.
• event_source: The source that triggered the alert.
• message_id: The ID of the message associated with the alert.
• severity: The severity level of the alert.
• [enabled]: Whether the alert is enabled or not.
• has_notification: Whether the alert has a notification configured.
• delay_between_responses: The delay between consecutive responses.
• occurrence_count: How many times the alert has occurred.
• last_occurrence_date: The date when the alert last occurred.
• last_occurrence_time: The time when the alert last occurred.

This query is useful for monitoring the status and history of SQL Server alerts. You can customize it further by filtering specific conditions or adding additional columns from the sysalerts table.

SQL Server database design: Best practice

Table Design Best Practices

• Avoid special characters and spaces in table names, column names, and other database object names (e.g., views, stored procedures). Stick to alphanumeric characters and underscores (_) for consistency and compatibility.
• Avoid storing binary data, such as files or images, directly in tables. Instead, use file paths or dedicated storage solutions like blob storage for better performance and manageability.
• Always define a primary key on each table. This ensures data integrity and helps with indexing and performance optimization.
• Do not use deprecated data types like NTEXT, TEXT, and IMAGE. Use NVARCHAR(MAX), VARCHAR(MAX), and VARBINARY(MAX) instead.
• Avoid using UNIQUEIDENTIFIER as a primary key unless absolutely necessary. It can lead to performance issues due to its large size and randomness, which affects index efficiency.

Get IO warning from log

If you're managing a SQL Server instance, you know how crucial fast disk I/O is for overall database performance. When I/O operations start to slow down, it can lead to noticeable performance degradation and user frustration. Thankfully, SQL Server logs warnings when I/O operations take longer than 15 seconds, and you can easily access this information to pinpoint potential bottlenecks.

By querying the SQL Server error log, you can identify these long-running I/O warnings. This can be an invaluable first step in diagnosing disk-related performance issues, allowing you to investigate further and optimize your storage subsystem.

Here's a handy SQL script you can use to retrieve these I/O warning messages from your SQL Server error logs:

CREATE TABLE #IOWarning (LogDate datetime
 , ProcessInfo sysname
 , LogText nvarchar(1000));

INSERT INTO #IOWarning EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 5, 1, N'taking longer than 15 seconds';

SELECT LogDate, ProcessInfo, LogText
FROM #IOWarning
ORDER BY LogDate DESC;
  

How the Script Works:

• The script first creates a temporary table named #IOWarning to store the retrieved log entries.
• It then uses xp_readerrorlog to query the SQL Server error logs. The parameters `0, 1, N'taking longer than 15 seconds'` specifically look for the current log (0) and archived logs (1 through 5) for entries containing the phrase "taking longer than 15 seconds," which is the standard warning message for slow I/O.
• Finally, it selects and orders the results by LogDate in descending order, showing you the most recent warnings first.

This script provides a quick way to gain insight into potential I/O performance issues. If you consistently see these warnings, it's a strong indicator that you need to investigate your disk configuration, storage array, or underlying hardware. Happy troubleshooting!