\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":"\n