SQL SERVER – Identify Database File with Maximum IO Issues

Every day is a new challenge day for me as a SQL Server Performance Tuning Consultant. The other day, I have received an interesting email from an existing client of Comprehensive Database Performance Health Check. His question was related to files with IO Issues. Let us understand the question and explore its answer.

“Hi Pinal,

As discussed in the last call, we have just got the new drive for our SQL Server box. The drive is the super fast and the latest version of SSD. Now we are ready to move some of our database files to the new drive, would you please help us with that part.

We want to do is that we want to move the files which are facing the maximum issues with the IO. How do we find in our database file which is facing the maximum IO issues? Once we know which file is creating the issue, we will use the detach and attach method to move the database files.”

I think the question is extremely valid. Here is the script which you can use to identify which SQL Server database files has the maximum issues related to IO. Once you know the file which is facing the maximum IO Issues, you can easily move it to the new drive using the detach and attach method.

SELECT DB_NAME (fs.database_id) AS DatabaseName, 
	   mf.type_desc, mf.name [FileName], 
       fs.io_stall, fs.num_of_reads, fs.num_of_writes,
       (cast(mf.size as float)*8)/1024/1024 AS FileSizeinGB,
       mf.physical_name AS FileLocation
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs,
      sys.master_files AS mf
WHERE fs.database_id = mf.database_id AND fs.FILE_ID = mf.FILE_ID
ORDER BY fs.io_stall DESC

When you run the above query, it will give you output similar to the following image. It is quite easy from that point.

Please note that in this blog post, I have taken the base of the column IO Stalls from the DMV. There are many different ways to know which file is trouble maker for you and I will blog about other methods in the future blog post. Once my client moved their file with the maximum IO stall to another drive, their major performance problem was resolved.

Reference: Pinal Dave (https://blog.sqlauthority.com)