SQL Server Diagnostic Information Queries Detailed, Day 19

After eighteen days of queries, we have made it through all of the instance-level queries in this set. Now, we move on to the database-specific queries in the set. For these queries, you need to be connected to a particular database that you are concerned with, rather than the master system database.

For Day 19 of this series, we start out with Query #44, which is File Sizes and Space. This query retrieves information from the sys.database_files system catalog view and the sys.data_spaces system catalog view about The sizes and available space for all of your database files. Query #44 is shown in Figure 1.

   1: -- Individual File Sizes and space available for current database  (Query 44) (File Sizes and Space)

   2: SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 

   3: CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],

   4: CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) 

   5: AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name],

   6: f.is_percent_growth, f.growth

   7: FROM sys.database_files AS f WITH (NOLOCK) 

   8: LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 

   9: ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);


  11: -- Look at how large and how full the files are and where they are located

  12: -- Make sure the transaction log is not full!!

Figure 1: Query #44 File Sizes and Space

This query lets you see how large each of your database files are, plus how much space is available in each of your database files. For data files, you can also see what file group each file is in. You can also see exactly where each file is located in the file system. This is all extremely useful information.


Query #45 is IO Stats By File. This query retrieves information from the sys.dm_io_virtual_file_stats dynamic management function and the sys.database_files system catalog view about the cumulative I/O usage by database file. Query #45 is shown in Figure 2.

   1: -- I/O Statistics by file for the current database  (Query 45) (IO Stats By File)

   2: SELECT DB_NAME(DB_ID()) AS [Database Name], df.name AS [Logical Name], vfs.[file_id], df.type_desc,

   3: df.physical_name AS [Physical Name], CAST(vfs.size_on_disk_bytes/1048576.0 AS DECIMAL(10, 2)) AS [Size on Disk (MB)],

   4: vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms,

   5: CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct],

   6: CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],

   7: (vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads], 

   8: CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read], 

   9: CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written],

  10: CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],

  11: CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],

  12: CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct],

  13: CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]

  14: FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs

  15: INNER JOIN sys.database_files AS df WITH (NOLOCK)

  16: ON vfs.[file_id]= df.[file_id] OPTION (RECOMPILE);


  18: -- This helps you characterize your workload better from an I/O perspective for this database

  19: -- It helps you determine whether you has an OLTP or DW/DSS type of workload

Figure 2: Query #45 IO Stats By File

This query lets you see all of the cumulative file activity for each of the files in the current database, since SQL Server was last started. This includes your normal workload activity, plus any other activity that touches your data and log files. This would include things like database backups, index maintenance, DBCC CHECKDB activity, and HA-related activity from things like transactional replication, database mirroring, and AlwaysOn AG-related activity.

Looking at the results of this query helps you understand what kind of I/O workload activity you are seeing on each of your database files. This helps you do a better job when it comes to designing and configuring your storage subsystem.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.