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.<\/p>\n
For Day 19 of this series, we start out with Query #44<\/strong>, which is File Sizes and Space. This query retrieves information from the sys.database_files<\/a> system catalog view and the sys.data_spaces<\/a> system catalog view about The sizes and available space for all of your database files. Query #44 is shown in Figure 1.<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/div>\n<\/div>\n Figure 1: Query #44 File Sizes and Space<\/strong><\/p>\n 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.<\/p>\n <\/p>\n Query #45<\/strong> is IO Stats By File. This query retrieves information from the sys.dm_io_virtual_file_stats<\/a> dynamic management function and the sys.database_files<\/a> system catalog view about the cumulative I\/O usage by database file. Query #45 is shown in Figure 2.<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/div>\n<\/div>\n Figure 2: Query #45 IO Stats By File<\/strong><\/p>\n 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.<\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[272],"class_list":["post-1047","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-dmv-diagnostic-queries-detailed"],"yoast_head":"\n 1:<\/span> -- Individual File Sizes and space available for current database (Query 44) (File Sizes and Space)<\/span><\/pre>\n 2:<\/span> SELECT<\/span> f.name AS<\/span> [File<\/span> Name] , f.physical_name AS<\/span> [Physical Name], <\/pre>\n 3:<\/span> CAST<\/span>((f.size<\/span>\/128.0) AS<\/span> DECIMAL<\/span>(15,2)) AS<\/span> [Total Size<\/span> in<\/span> MB],<\/pre>\n 4:<\/span> CAST<\/span>(f.size<\/span>\/128.0 - CAST<\/span>(FILEPROPERTY(f.name, 'SpaceUsed'<\/span>) AS<\/span> int<\/span>)\/128.0 AS<\/span> DECIMAL<\/span>(15,2)) <\/pre>\n 5:<\/span> AS<\/span> [Available Space<\/span> In<\/span> MB], [file_id], fg.name AS<\/span> [Filegroup Name],<\/pre>\n 6:<\/span> f.is_percent_growth, f.growth<\/pre>\n 7:<\/span> FROM<\/span> sys.database_files AS<\/span> f WITH<\/span> (NOLOCK) <\/pre>\n 8:<\/span> LEFT<\/span> OUTER<\/span> JOIN<\/span> sys.data_spaces AS<\/span> fg WITH<\/span> (NOLOCK) <\/pre>\n 9:<\/span> ON<\/span> f.data_space_id = fg.data_space_id OPTION<\/span> (RECOMPILE);<\/pre>\n 10:<\/span> <\/pre>\n 11:<\/span> -- Look at how large and how full the files are and where they are located<\/span><\/pre>\n 12:<\/span> -- Make sure the transaction<\/span> log is<\/span> not<\/span> full<\/span>!!<\/pre>\n 1:<\/span> -- I\/O Statistics by file for the current database (Query 45) (IO Stats By File)<\/span><\/pre>\n 2:<\/span> SELECT<\/span> DB_NAME(DB_ID()) AS<\/span> [Database<\/span> Name], df.name AS<\/span> [Logical Name], vfs.[file_id], df.type_desc,<\/pre>\n 3:<\/span> df.physical_name AS<\/span> [Physical Name], CAST<\/span>(vfs.size_on_disk_bytes\/1048576.0 AS<\/span> DECIMAL<\/span>(10, 2)) AS<\/span> [Size<\/span> on<\/span> Disk<\/span> (MB)],<\/pre>\n 4:<\/span> vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms,<\/pre>\n 5:<\/span> CAST<\/span>(100. * vfs.io_stall_read_ms\/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS<\/span> DECIMAL<\/span>(10,1)) AS<\/span> [IO Stall Reads<\/span> Pct],<\/pre>\n 6:<\/span> CAST<\/span>(100. * vfs.io_stall_write_ms\/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS<\/span> DECIMAL<\/span>(10,1)) AS<\/span> [IO Stall Writes Pct],<\/pre>\n 7:<\/span> (vfs.num_of_reads + vfs.num_of_writes) AS<\/span> [Writes + Reads<\/span>], <\/pre>\n 8:<\/span> CAST<\/span>(vfs.num_of_bytes_read\/1048576.0 AS<\/span> DECIMAL<\/span>(10, 2)) AS<\/span> [MB Read<\/span>], <\/pre>\n 9:<\/span> CAST<\/span>(vfs.num_of_bytes_written\/1048576.0 AS<\/span> DECIMAL<\/span>(10, 2)) AS<\/span> [MB Written],<\/pre>\n 10:<\/span> CAST<\/span>(100. * vfs.num_of_reads\/(vfs.num_of_reads + vfs.num_of_writes) AS<\/span> DECIMAL<\/span>(10,1)) AS<\/span> [# Reads<\/span> Pct],<\/pre>\n 11:<\/span> CAST<\/span>(100. * vfs.num_of_writes\/(vfs.num_of_reads + vfs.num_of_writes) AS<\/span> DECIMAL<\/span>(10,1)) AS<\/span> [# Write<\/span> Pct],<\/pre>\n 12:<\/span> CAST<\/span>(100. * vfs.num_of_bytes_read\/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS<\/span> DECIMAL<\/span>(10,1)) AS<\/span> [Read<\/span> Bytes Pct],<\/pre>\n 13:<\/span> CAST<\/span>(100. * vfs.num_of_bytes_written\/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS<\/span> DECIMAL<\/span>(10,1)) AS<\/span> [Written Bytes Pct]<\/pre>\n 14:<\/span> FROM<\/span> sys.dm_io_virtual_file_stats(DB_ID(), NULL<\/span>) AS<\/span> vfs<\/pre>\n 15:<\/span> INNER<\/span> JOIN<\/span> sys.database_files AS<\/span> df WITH<\/span> (NOLOCK)<\/pre>\n 16:<\/span> ON<\/span> vfs.[file_id]= df.[file_id] OPTION<\/span> (RECOMPILE);<\/pre>\n 17:<\/span> <\/pre>\n 18:<\/span> -- This helps you characterize your workload better from an I\/O perspective for this database<\/span><\/pre>\n 19:<\/span> -- It helps you determine whether you has an OLTP or<\/span> DW\/DSS type of<\/span> workload<\/pre>\n