SQL2005 IO Bottleneck - Estimation des performances IO depuis SQL server

Dans le cadre de l'optimisation des performances, il est nécessaire d'avoir des disques durs avec un temps d'access le plus bas possible.
En autre, un temps d'access (read IO) continuellement à 15 ms est déja considéré comme un étranglement des performances.

Voici une requête permettant d'extraire depuis le DMV les statistiques de temps d'access au différents fichiers SQL server (io_stall_read_ms).

select DB_NAME(database_id) DB_NAME, file_id,io_stall_read_ms ,num_of_reads
  ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
  ,io_stall_write_ms,num_of_writes
  ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
  ,io_stall_read_ms + io_stall_write_ms as io_stalls
  ,num_of_reads + num_of_writes as total_io
  ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
  from sys.dm_io_virtual_file_stats(null,null)
  -- where DB_NAME(database_id) like 'DBName_toSeeIOStatsForThat_DB' 
  order by avg_io_stall_ms desc

Autre requête a potentiel
Voici une autre requête issue du site SqlBlog.com affichant des informations relatives à la lecture/écriture des informations dans les fichiers SQL.

select db_name(mf.database_id) as databaseName, mf.physical_name, 
       num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes,
       num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes
from sys.dm_io_virtual_file_stats(null,null) as divfs
         join sys.master_files as mf
              on mf.database_id = divfs.database_id
                 and mf.file_id = divfs.file_id 

Quelques définitions

• Io_stall_read_ms – total time user processes waited for IO. Note that this number can be much greater than the sample_ms. If 10 processes are trying to use the file simultaneously, but the disk is only able to server 1, then you might get 9 seconds waiting over a 10 second time period.
• num_of_reads – number of individual read operations that were issued to the file. Note that this is physical reads, not logical reads. Logical reads would not be registered.

Reset des statistiques
La seule façon de faire un reset de ces statistiques sys.dm_io_virtual_file_stats est de redémarrer le serveur.
Pour plus d'info:

• Voir également cet article concernant le "top 15 des problème de performances sur SQL 2005" (db OLTP) performances.
• Voir aussi la section IO BottleNeck de cet autre article de Microsoft "Troubleshooting Performance Problems in SQL Server 2005"
• Les définitions et la seconde requête sont extraits du site SqlBlog.com