{"id":1034,"date":"2016-01-11T07:29:12","date_gmt":"2016-01-11T15:29:12","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1034"},"modified":"2017-04-13T12:28:45","modified_gmt":"2017-04-13T19:28:45","slug":"sql-server-diagnostic-information-queries-detailed-day-11","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 11"},"content":{"rendered":"

Today, we will go through four queries that are related to storage. We start out with Query #23<\/strong>, which is Volume Info.  This query retrieves information from the sys.master_files<\/a> catalog view and the sys.dm_os_volume_stats<\/a> dynamic management function about any operating system volumes or mount points on your server where you have any SQL Server database files. Query #23 is shown in Figure 1. <\/p>\n

\n
\n
   1:<\/span> -- Volume info for all LUNS that have database files on the current instance (Query 23) (Volume Info)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> DISTINCT<\/span> vs.volume_mount_point, vs.file_system_type, <\/pre>\n
<\/p>\n
   3:<\/span> vs.logical_volume_name, CONVERT<\/span>(DECIMAL<\/span>(18,2),vs.total_bytes\/1073741824.0) AS<\/span> [Total Size<\/span> (GB)],<\/pre>\n
<\/p>\n
   4:<\/span> CONVERT<\/span>(DECIMAL<\/span>(18,2),vs.available_bytes\/1073741824.0) AS<\/span> [Available Size<\/span> (GB)],  <\/pre>\n
<\/p>\n
   5:<\/span> CAST<\/span>(CAST<\/span>(vs.available_bytes AS<\/span> FLOAT<\/span>)\/ CAST<\/span>(vs.total_bytes AS<\/span> FLOAT<\/span>) AS<\/span> DECIMAL<\/span>(18,2)) * 100 AS<\/span> [Space<\/span> Free<\/span> %] <\/pre>\n
<\/p>\n
   6:<\/span> FROM<\/span> sys.master_files AS<\/span> f WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   7:<\/span> CROSS<\/span> APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS<\/span> vs <\/pre>\n
<\/p>\n
   8:<\/span> ORDER<\/span> BY<\/span> vs.volume_mount_point OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   9:<\/span>  <\/pre>\n
<\/p>\n
  10:<\/span> -- Shows you the total and free space on the LUNs where you have database files<\/span><\/pre>\n
<\/p>\n
  11:<\/span> -- Being low on<\/span> free<\/span> space<\/span> can negatively affect performance<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #23 Volume Info<\/strong><\/p>\n
This query shows you how large each operating system volume or mount point is, and how much space is available. You don\u2019t ever<\/u><\/strong> want to run out of space on a volume where SQL Server database files are located (for obvious reasons), but many people are not aware that being low on space can affect the performance of the volume, with both magnetic storage and with flash storage.<\/p>\n
With magnetic storage, being low on disk space means that you may be no longer getting the increased disk performance possible from short-stroking<\/a>. This can affect your disk performance with magnetic drives because the arm that moves the drive heads back and forth to read the data has to move further as an individual disk becomes more and more full, plus the disk platters are moving slower on the inside portion compared to the outside portion of the disk. <\/p>\n
The reason why I say that this only \u201cmay\u201d be affecting your disk performance is because with many SANs, whatever space has been allocated for a volume may be spread across all of the available drives in the SAN, so the fact that your volume is low on space may have very little to do with what is happening with the overall SAN.<\/p>\n
With flash storage, as the drive gets lower on space, it becomes more difficult for garbage collection and TRIM operations to complete without affecting performance. Generally speaking, your write performance will tend to decrease and become more inconsistent as the drive is closer to being full of data.<\/p>\n
 <\/p>\n
Query #24<\/strong> is Drive Level Latency. This query retrieves information from the sys.master_files<\/a> catalog view and the sys.dm_io_virtual_file_stats<\/a> dynamic management function about I\/O statistics for SQL Server data and log files. Query #24 is shown in Figure 2. <\/p>\n
\n
\n
   1:<\/span> -- Drive level latency information (Query 24) (Drive Level Latency)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> -- Based on code from Jimmy May<\/span><\/pre>\n
<\/p>\n
   3:<\/span> SELECT<\/span> tab.[Drive], tab.volume_mount_point AS<\/span> [Volume Mount Point], <\/pre>\n
<\/p>\n
   4:<\/span>     CASE<\/span> <\/pre>\n
<\/p>\n
   5:<\/span>         WHEN<\/span> num_of_reads = 0 THEN<\/span> 0 <\/pre>\n
<\/p>\n
   6:<\/span>         ELSE<\/span> (io_stall_read_ms\/num_of_reads) <\/pre>\n
<\/p>\n
   7:<\/span>     END<\/span> AS<\/span> [Read<\/span> Latency],<\/pre>\n
<\/p>\n
   8:<\/span>     CASE<\/span> <\/pre>\n
<\/p>\n
   9:<\/span>         WHEN<\/span> io_stall_write_ms = 0 THEN<\/span> 0 <\/pre>\n
<\/p>\n
  10:<\/span>         ELSE<\/span> (io_stall_write_ms\/num_of_writes) <\/pre>\n
<\/p>\n
  11:<\/span>     END<\/span> AS<\/span> [Write<\/span> Latency],<\/pre>\n
<\/p>\n
  12:<\/span>     CASE<\/span> <\/pre>\n
<\/p>\n
  13:<\/span>         WHEN<\/span> (num_of_reads = 0 AND<\/span> num_of_writes = 0) THEN<\/span> 0 <\/pre>\n
<\/p>\n
  14:<\/span>         ELSE<\/span> (io_stall\/(num_of_reads + num_of_writes)) <\/pre>\n
<\/p>\n
  15:<\/span>     END<\/span> AS<\/span> [Overall Latency],<\/pre>\n
<\/p>\n
  16:<\/span>     CASE<\/span> <\/pre>\n
<\/p>\n
  17:<\/span>         WHEN<\/span> num_of_reads = 0 THEN<\/span> 0 <\/pre>\n
<\/p>\n
  18:<\/span>         ELSE<\/span> (num_of_bytes_read\/num_of_reads) <\/pre>\n
<\/p>\n
  19:<\/span>     END<\/span> AS<\/span> [Avg<\/span> Bytes\/Read<\/span>],<\/pre>\n
<\/p>\n
  20:<\/span>     CASE<\/span> <\/pre>\n
<\/p>\n
  21:<\/span>         WHEN<\/span> io_stall_write_ms = 0 THEN<\/span> 0 <\/pre>\n
<\/p>\n
  22:<\/span>         ELSE<\/span> (num_of_bytes_written\/num_of_writes) <\/pre>\n
<\/p>\n
  23:<\/span>     END<\/span> AS<\/span> [Avg<\/span> Bytes\/Write<\/span>],<\/pre>\n
<\/p>\n
  24:<\/span>     CASE<\/span> <\/pre>\n
<\/p>\n
  25:<\/span>         WHEN<\/span> (num_of_reads = 0 AND<\/span> num_of_writes = 0) THEN<\/span> 0 <\/pre>\n
<\/p>\n
  26:<\/span>         ELSE<\/span> ((num_of_bytes_read + num_of_bytes_written)\/(num_of_reads + num_of_writes)) <\/pre>\n
<\/p>\n
  27:<\/span>     END<\/span> AS<\/span> [Avg<\/span> Bytes\/Transfer]<\/pre>\n
<\/p>\n
  28:<\/span> FROM<\/span> (SELECT<\/span> LEFT<\/span>(UPPER<\/span>(mf.physical_name), 2) AS<\/span> Drive, SUM<\/span>(num_of_reads) AS<\/span> num_of_reads,<\/pre>\n
<\/p>\n
  29:<\/span>              SUM<\/span>(io_stall_read_ms) AS<\/span> io_stall_read_ms, SUM<\/span>(num_of_writes) AS<\/span> num_of_writes,<\/pre>\n
<\/p>\n
  30:<\/span>              SUM<\/span>(io_stall_write_ms) AS<\/span> io_stall_write_ms, SUM<\/span>(num_of_bytes_read) AS<\/span> num_of_bytes_read,<\/pre>\n
<\/p>\n
  31:<\/span>              SUM<\/span>(num_of_bytes_written) AS<\/span> num_of_bytes_written, SUM<\/span>(io_stall) AS<\/span> io_stall, vs.volume_mount_point <\/pre>\n
<\/p>\n
  32:<\/span>       FROM<\/span> sys.dm_io_virtual_file_stats(NULL<\/span>, NULL<\/span>) AS<\/span> vfs<\/pre>\n
<\/p>\n
  33:<\/span>       INNER<\/span> JOIN<\/span> sys.master_files AS<\/span> mf WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  34:<\/span>       ON<\/span> vfs.database_id = mf.database_id AND<\/span> vfs.file_id = mf.file_id<\/pre>\n
<\/p>\n
  35:<\/span>       CROSS<\/span> APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS<\/span> vs <\/pre>\n
<\/p>\n
  36:<\/span>       GROUP<\/span> BY<\/span> LEFT<\/span>(UPPER<\/span>(mf.physical_name), 2), vs.volume_mount_point) AS<\/span> tab<\/pre>\n
<\/p>\n
  37:<\/span> ORDER<\/span> BY<\/span> [Overall Latency] OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  38:<\/span>  <\/pre>\n
<\/p>\n
  39:<\/span> -- Shows you the drive-level latency for reads and writes, in milliseconds<\/span><\/pre>\n
<\/p>\n
  40:<\/span> -- Latency above 20-25ms is<\/span> usually a problem<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #24 Drive Level Latency<\/strong><\/p>\n
This particular query aggregates all of the file activity for your database data and log files up to the volume level. This activity includes your normal workload, plus anything else (such as index maintenance, database backups, log reader activity for things like replication, database mirroring, and AlwaysOn AGs) that touches your database data and log files. <\/p>\n
These numbers are cumulative since SQL Server was last started. This means that the latency numbers you see here will usually tend to be a little higher than what you might see in Performance Monitor, and also higher than what shows up at the SAN-level. This query helps you understand your disk performance for SQL Server at the volume level. Once you understand this, then you are ready to drill down to the individual database file level with the next query.<\/p>\n
 <\/p>\n
Query #25<\/strong> is IO Stalls by File. This query retrieves information from the sys.master_files<\/a> catalog view and the sys.dm_io_virtual_file_stats<\/a> dynamic management function about I\/O statistics for SQL Server data and log files. Query #25 is shown in Figure 3. <\/p>\n
\n
\n
   1:<\/span> -- Calculates average stalls per read, per write, and per total input\/output for each database file  (Query 25) (IO Stalls by File)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> DB_NAME(fs.database_id) AS<\/span> [Database<\/span> Name], CAST<\/span>(fs.io_stall_read_ms\/(1.0 + fs.num_of_reads) AS<\/span> NUMERIC<\/span>(10,1)) AS<\/span> [avg_read_stall_ms],<\/pre>\n
<\/p>\n
   3:<\/span> CAST<\/span>(fs.io_stall_write_ms\/(1.0 + fs.num_of_writes) AS<\/span> NUMERIC<\/span>(10,1)) AS<\/span> [avg_write_stall_ms],<\/pre>\n
<\/p>\n
   4:<\/span> CAST<\/span>((fs.io_stall_read_ms + fs.io_stall_write_ms)\/(1.0 + fs.num_of_reads + fs.num_of_writes) AS<\/span> NUMERIC<\/span>(10,1)) AS<\/span> [avg_io_stall_ms],<\/pre>\n
<\/p>\n
   5:<\/span> CONVERT<\/span>(DECIMAL<\/span>(18,2), mf.size<\/span>\/128.0) AS<\/span> [File<\/span> Size<\/span> (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads, <\/pre>\n
<\/p>\n
   6:<\/span> fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS<\/span> [io_stalls], fs.num_of_reads + fs.num_of_writes AS<\/span> [total_io],<\/pre>\n
<\/p>\n
   7:<\/span> io_stall_queued_read_ms AS<\/span> [Resource Governor Total Read<\/span> IO Latency (ms)], io_stall_queued_write_ms AS<\/span> [Resource Governor Total Write<\/span> IO Latency (ms)] <\/pre>\n
<\/p>\n
   8:<\/span> FROM<\/span> sys.dm_io_virtual_file_stats(null<\/span>,null<\/span>) AS<\/span> fs<\/pre>\n
<\/p>\n
   9:<\/span> INNER<\/span> JOIN<\/span> sys.master_files AS<\/span> mf WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  10:<\/span> ON<\/span> fs.database_id = mf.database_id<\/pre>\n
<\/p>\n
  11:<\/span> AND<\/span> fs.[file_id] = mf.[file_id]<\/pre>\n
<\/p>\n
  12:<\/span> ORDER<\/span> BY<\/span> avg_io_stall_ms DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  13:<\/span>  <\/pre>\n
<\/p>\n
  14:<\/span> -- Helps determine which database files on the entire instance have the most I\/O bottlenecks<\/span><\/pre>\n
<\/p>\n
  15:<\/span> -- This can help you decide whether certain LUNs are overloaded and whether you might<\/span><\/pre>\n
<\/p>\n
  16:<\/span> -- want to<\/span> move some<\/span> files to<\/span> a different location or<\/span> perhaps improve your I\/O performance<\/pre>\n
<\/div>\n<\/div>\n
Figure 3: Query #25 IO Stalls by File<\/strong><\/p>\n
This query is similar to the previous query, with all of the same caveats. This query goes down to the individual database file level rather than rolling up the data to the volume level. Once you see high read or write latency for a particular volume, you will want to then determine which database file(s) are seeing the highest latency on that volume. This will help you decide what you might be able to do to improve the situation.<\/p>\n
 <\/p>\n
Query #26<\/strong> is IO Warnings. This query reads the five most recent SQL Server Error logs to look for 15 second I\/O warnings. Query #26 is shown in Figure 4. <\/p>\n
\n
\n
   1:<\/span> -- Look for I\/O requests taking longer than 15 seconds in the five most recent SQL Server Error Logs (Query 26) (IO Warnings)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> CREATE<\/span> TABLE<\/span> #IOWarningResults(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));<\/pre>\n
<\/p>\n
   3:<\/span>  <\/pre>\n
<\/p>\n
   4:<\/span>     INSERT INTO<\/span> #IOWarningResults <\/pre>\n
<\/p>\n
   5:<\/span>     EXEC<\/span> xp_readerrorlog 0, 1, N'taking longer than 15 seconds'<\/span>;<\/pre>\n
<\/p>\n
   6:<\/span>  <\/pre>\n
<\/p>\n
   7:<\/span>     INSERT INTO<\/span> #IOWarningResults <\/pre>\n
<\/p>\n
   8:<\/span>     EXEC<\/span> xp_readerrorlog 1, 1, N'taking longer than 15 seconds'<\/span>;<\/pre>\n
<\/p>\n
   9:<\/span>  <\/pre>\n
<\/p>\n
  10:<\/span>     INSERT INTO<\/span> #IOWarningResults <\/pre>\n
<\/p>\n
  11:<\/span>     EXEC<\/span> xp_readerrorlog 2, 1, N'taking longer than 15 seconds'<\/span>;<\/pre>\n
<\/p>\n
  12:<\/span>  <\/pre>\n
<\/p>\n
  13:<\/span>     INSERT INTO<\/span> #IOWarningResults <\/pre>\n
<\/p>\n
  14:<\/span>     EXEC<\/span> xp_readerrorlog 3, 1, N'taking longer than 15 seconds'<\/span>;<\/pre>\n
<\/p>\n
  15:<\/span>  <\/pre>\n
<\/p>\n
  16:<\/span>     INSERT INTO<\/span> #IOWarningResults <\/pre>\n
<\/p>\n
  17:<\/span>     EXEC<\/span> xp_readerrorlog 4, 1, N'taking longer than 15 seconds'<\/span>;<\/pre>\n
<\/p>\n
  18:<\/span>  <\/pre>\n
<\/p>\n
  19:<\/span> SELECT<\/span> LogDate, ProcessInfo, LogText<\/pre>\n
<\/p>\n
  20:<\/span> FROM<\/span> #IOWarningResults<\/pre>\n
<\/p>\n
  21:<\/span> ORDER<\/span> BY<\/span> LogDate DESC<\/span>;<\/pre>\n
<\/p>\n
  22:<\/span>  <\/pre>\n
<\/p>\n
  23:<\/span> DROP<\/span> TABLE<\/span> #IOWarningResults;  <\/pre>\n
<\/p>\n
  24:<\/span>  <\/pre>\n
<\/p>\n
  25:<\/span> -- Finding 15 second I\/O warnings in the SQL Server Error Log is useful evidence of<\/span><\/pre>\n
<\/p>\n
  26:<\/span> -- poor I\/O performance (which might have many different causes)<\/span><\/pre>\n
<\/p>\n
  27:<\/span>  <\/pre>\n
<\/p>\n
  28:<\/span> -- Diagnostics in SQL Server help detect stalled and stuck I\/O operations<\/span><\/pre>\n
<\/p>\n
  29:<\/span> -- https:\/\/support.microsoft.com\/en-us\/kb\/897284<\/pre>\n
<\/div>\n<\/div>\n
Figure 4: Query #26 IO Warnings<\/strong><\/p>\n
This query may take some time to complete if your SQL Server Error log files are very large. Finding any 15 second I\/O warnings is very strong evidence that you were seeing very poor storage subsystem performance when they were recorded. The warnings go down to the database file level, so you can see which drive and which file was affected. <\/p>\n
What you want to look for is whether there is any sort of recognizable pattern to when you are getting these warnings. For example, perhaps you often see a series of 15 second warnings for the data files for one database at around 3AM each morning. Perhaps this is when you are doing index maintenance, plus running DBCC CHECKDB at the same time, or some other repeating pattern of scheduled disk intensive activity. <\/p>\n
On the other hand, it may be the case that you see many 15 second I\/O warnings at random times, spread across many drive volumes and disk files. This would be a pretty good indicator of generally poor storage subsystem performance!<\/p>\n
The results of these four queries are very useful to have available when you talk to your storage administrator about disk performance. The more evidence and metrics you have available about what SQL Server is seeing from a storage perspective, the stronger your case will be.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, we will go through four queries that are related to storage. We start out with Query #23, which is Volume Info.  This query retrieves information from the sys.master_files catalog view and the sys.dm_os_volume_stats dynamic management function about any operating system volumes or mount points on your server where you have any SQL Server database […]<\/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":[270],"class_list":["post-1034","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-sql-server-diagnostic-information-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 11 - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 11 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"Today, we will go through four queries that are related to storage. We start out with Query #23, which is Volume Info.  This query retrieves information from the sys.master_files catalog view and the sys.dm_os_volume_stats dynamic management function about any operating system volumes or mount points on your server where you have any SQL Server database […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-11T15:29:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:45+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 11 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-11T15:29:12+00:00\",\"dateModified\":\"2017-04-13T19:28:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 11\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 11 - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 11 - Glenn Berry","og_description":"Today, we will go through four queries that are related to storage. We start out with Query #23, which is Volume Info.  This query retrieves information from the sys.master_files catalog view and the sys.dm_os_volume_stats dynamic management function about any operating system volumes or mount points on your server where you have any SQL Server database […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-11T15:29:12+00:00","article_modified_time":"2017-04-13T19:28:45+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 11 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-11T15:29:12+00:00","dateModified":"2017-04-13T19:28:45+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-11\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 11"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1034","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1034"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1034\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1034"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1034"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1034"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}