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.
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.
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.