Query to find the most used data files on SQL Server

To find the most used data files on SQL Server, you can use the sys.dm_io_virtual_file_stats dynamic management view. This view contains statistics about the I/O activity of all the data files in the database.

To use this view, you can run the following query:

SELECT DB_NAME(database_id) AS database_name,
       file_id,
       num_of_reads,
       num_of_bytes_read,
       num_of_writes,
       num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY num_of_bytes_written DESC;

This query returns the following columns:

• database_name: The name of the database.
• file_id: The ID of the data file.
• num_of_reads: The number of reads performed on the data file.
• num_of_bytes_read: The total number of bytes read from the data file.
• num_of_writes: The number of writes performed on the data file.
• num_of_bytes_written: The total number of bytes written to the data file.

The query orders the results by the num_of_bytes_written column in descending order, so the data files with the highest number of bytes written will be at the top of the results. These are the data files that are being used the most in the database.

You can use this information to identify which data files are the most heavily used in your database, which can help you optimize the performance and scalability of your system. For example, you may want to move these files to faster storage or add more memory to your system to improve their performance.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!