IOStats monitoring

Basé sur l'article "xxxx", ce dernier présente les différents éléments clés pour faire du monitoring des IOStats de Sql Server.
A l'instar des "Wait Stats, les différentes informations seront stockées dans des table qu'il sera possible de consulter par la suite.
L'inconvéniant dans ce cas, c'est qu'il est obligatoire de faire fonctionner le script de collecte directement depuis le serveur de production.
En effet, la fonction sys.dm_io_virtual_file_stats(null,null) retourne un type "table-valued" et il n'est pas autorisé d'appeler ce type de fonction depuis un serveur distant.

Création de la DB

Créer une DB nommée IOStatsMonitor

Création des tables

CREATE TABLE dbo.ioStatsCapture (
   SessionID int not null, 
  database_id tinyint not null, -- Database name retreivied with DB_NAME(database_id)
  file_id tinyint not null,
  io_stall_read_ms BigInt not null,
  num_of_reads BigInt not null,
  avg_read_stall_ms numeric(10,1) not null,
  io_stall_write_ms bigint not null,
  num_of_writes bigint not null,
  avg_write_stall_ms numeric(10,1) not null,
  io_stalls bigint not null, 
  total_io bigint not null, 
  avg_io_stall_ms numeric(10,1) not null
)
CREATE TABLE dbo.ioStatsSessionMonitor (
  SessionID int identity(1,1) not null,
  CaptureTime datetime not null
)

 
Script de collecte d'information

Tout comme pour les Wait Stats, ce script sera exécuté depuis un Job Sql serveur.
Dans un premier temps, une collecte toutes les 30 à 60 minutes semblent correcte.

--
--  Collect the IO Stats from databases and 
--      Store it into the table ioStatsCapture.
--
declare @ID int
insert into IOStatsMonitor..ioStatsSessionMonitor ( CaptureTime ) 
  values ( getdate() )
select @ID = SCOPE_IDENTITY()

insert IOStatsMonitor..ioStatsCapture 
SELECT @id, 
  database_id, file_id,io_stall_read_ms ,num_of_reads
  ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) -- '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)) -- '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)) -- 'avg_io_stall_ms'
  from sys.dm_io_virtual_file_stats(null,null)
  -- where DB_NAME(database_id) like 'TargetDbToMonitor'