Google App Script - Ajouter des scripts dans une feuille de calcul GoogleDoc

Google Doc est déja connu pour son gestionnaire de documents (traitement texte, feuille de calcul, présentation, etc) en ligne.
Un projet interne est actuellement en cours d'évaluation. Ce dernier permet d'ajouter des scripts (macros et fonctions) aux documents Google Doc.

La vidéo ci-dessous est une excellente introduction démontrant clairement les capacités de scripting dans les documents.

Les livres lus (jusqu'en 2009)

Un merci tout spécial pour ma tendre épouse qui a passé de longues heures à encoder cette longue liste.
Merci mon amour

 

Livres lus :

• Millénium 2 - La fille qui rêvait d'un bidon d'essence et d'une allumette de Stieg Larsson du 04 octobre au 13 décembre 2008
• Georges et le Secret de l'Univers de Stephen & Lucy Hawking du 24 septembre au 04 octobre 2008
• La tectonique des sentiments de Eric Emmanuel Schmitt du 20 septembre au 23 septembre 2008
• Millénium 1 - Les hommes qui n'aiment pas les femmes de Stieg Larsson du 19 septembre au 18 octobre 2008
• La princesse des glaces de Camilla Läckborg du 30 Aout au 18 septembre 2008
• Toutes ces choses qu'on ne s'est pas dites de Marc Levy du 21 Août au 29 Aout 2008
• Je reviens te chercher de Guillaume Musso du 13 aout au 21 aout 2008
• Le Rasoir d'Ockam de Henri Loevenbruck du 08 aout au 19 aout 2008
• Chaque femme est un roman de Alexandre Jardin du 28 juillet au 07 Aout 2008
• Théorie Gaia de Maxime Chattam du 19 juillet au 27 juillet 2008
• Chroniques Martiennes de Ray Bradbury du 30 juin au 19 juillet 2008
• Le serment des Limbes de Jean-Christophe Grangé du 03 juin au 29 juin 2008
• L'anneau monde (vol 3) de Larry Niven du 27 avril au 3 juin 2008
• L'hygiène de l'assassin de Amélie Nothomb du 20 avril au 26 avril 2008
• Farenheit 451 de Ray Bradbury du 12 avril au 20 avril 2008
• L'alerte Ambler de Robert Ludlum du 27 mars au 12 avril 2008
• L'élégance du Hérisson de Muriel Bradbery du 06 mars au 26 mars 2008
• Next de Michael Crichton du 23 février au 06 mars 2008
• Stupeur et tremblement de Amélie Nothomb du 20 février au 23 février 2008
• L'anneau monde (vol 2) de Larry Niven du 20 janvier au 19 février2008
• Mort aux cons de Carl Aderhold du 05 janvier au 19 janvier 2008
• L'anneau monde (vol 1) de Larry Niven du 02 décembre 2007 au 5 janvier 2008
• Un peu de science pour tout le monde (2e partie) de Claude Allegre du 15 novembre au 01 décembre 2007
• Ni d'Eve, ni d'Adam de Amélie Nothomb du 10 novembre au 14 novembre 2007
• Un peu de science pour tout le monde (2e partie) de Claude Allegre du 27 octobre au 09 novembre 2007
• Histoire du Futur (vol 3) de Heinlein du 28 septembre au 14 octobre 2007
• Histoire du Futur (vol 2) de Heinlein du 18 septembre au 27 septembre 2007
• Sir Conan Doyle des maitres du policier du 17 aout au 18 septembre 2007
  
• Vous revoir de Marc Levy du 30 juillet au 15 aout 2007
• Prédateurs de Maxime Chatam du 23 juin au 29 juillet 2007
• Narnia (2e partie) de CS Lewis du 17 juin au 23 juin 2007
  
• Les archanes du Choas de Maxime Chatam du 21 mai au 17 juin 2007
• Histoire du Futur (vol 1) de Heinlein du 06 mai au 21 mai 2007
• Forteresse Digitale de Dan Brown du 05 avril au 06 mai 2007
• Narnia (1e partie) de CS Lewis du 16 décembre 2006 au 05 avril 2007
• Deception point de Dan Brown du 15 novembre au 16 décembre 2006
• Les Coloriés de Alexandre Jardin octobre 2006
• Moi Asimov de Asimov septembre 2006
• La règle de quatre de I Calwell Aout 2006
• Le roman des Jardin de Alexandre Jardin juin 2006
• I Robot  de Asimov mai 2006
• Le petit Prince de Antoine de Staint Exupéry mars 2006
• La conjuration des imbéciles de JK Toole Janvier 2006   Livre abandonné
• L'île des Gauchers de Alexandre Jardin septembre 2005
• 7 jours pour une éternité de Marc Levy septembre 2005
• Et si c'était vrai de Marc Levy Aout 2005
• Le Zèbre de Alexandre Jardin juin 205
• Anges et Démons - tous les secrets de P Darwin   ???
• L'empire des anges de Weber  ????  Livre Abandonné
• Anges et Démon de Dan Brown mai 2005
• Da Vinci code de Dan Brown mars 2005
  
• Cycle de Fondation (5 volumes) de Asimov de octobre 2004 à décembre 2004
  
• Baton rouge de P Cornwell Février 2004
• La mort n'est pas une fin de Agatha Christie mars 2003
  
• Une mémoire d'éléphant de Agatha Christie janvier 2003
• Meurtre en Mésopotamie de Agatha Christie janvier 2003
• Tout Simenon vol 4 de Simenon en 2002
• Net force de Tom Glancy en 2000
• Deus Machine P Quellette en 1999
• Les indiscrétions de poireau de Agatha Christie en mars 1999
• Les 5 petits cochons de Agatha Christie en mars 1999
• Mr Quinn en voyage de Agatha Christie en mars 1999   
• Les sept cadrans de Agatha Christie en mars 1999
  
• Témoin indésirable de Agatha Christie en mars 1999 
• Dragon d'un crépuscule d'automne de Weis & Hickman  décembre 1997  
  
• Dragon d'une nuit d'hivers de Weis & Hickman  décembre 1997 
  
• Dragon d'une aube de printemps de Weis & Hickman  décembre 1997
  
• Le temps des jumeaux de Weis & Hickman  décembre 1997 
  
• La guerre des jumeaux de Weis & Hickman  décembre 1997
  
• L'épreuve des jumeaux de Weis & Hickman  décembre 1997
• Dragon d'une flamme d'été - nouvelles diverses - 17 volumes de Weis & Hickman  octobre 1997
  
• Philadelphia de Michael Crichton  en 1996
• Brave Haert de Michael Crichton  en 1996
• Le monde Perdu - Jurrasic parc de Michael Crichton  en 1996
• Prince du chaos de Roger Zelazni  en 1995
• Chevalier des ombres de Roger Zelazni  en 1995
• Le signe du chaos de Roger Zelazni  en 1995
• Le sang d'Ambre de Roger Zelazni  en 1995
• Les atouts de la vengeance de Roger Zelazni  en 1995
• Les cours du chaos de Roger Zelazni  en 1995
• La main d'Oberon de Roger Zelazni  en 1995
• Le signe de la licorne de Roger Zelazni  en 1995
• Les fusils d'Avalon de Roger Zelazni  en 1995
• Les étrangers de Dean Koantz en 1995
• Les 9 princes d'Ambre de Roger Zelazni  en  déc 1995
• Douce nuit de Mary Higgins Clark en 1995
• ABC contre poireau de Agatha Christie en 1994 
  
• Mort dans les nuages de Agatha Christie en 1994
• Crime de l'Orient Express de Agatha Christie en 1994
• Souviens toi de Mary Higgins Clark en 1994
  
• Jurrasic parc de Michael Crichton  en déc 1993
• Drame en 3 actes de Agatha Christie en déc 1992
  
• Je ne suis pas coupable de Agatha Christie en déc 1992
• Bazar de Stephen King en nov 1992
• Minuit 4 de Stephen King en déc 1991
• Minuit 2 de Stephen King en oct 1991
• Ca de Stephen King en avril 1990
• Marche ou crève de Stephen King en déc 1989
• Carrie de Stephen King en déc 1989
• Shining de Stephen King en déc 1989
• Tomycknockers de Stephen King en déc 1989
• Simetierre de Stephen King en déc 1989
• Talisman (avec Peter Stranb) de Stephen King en déc 1989
• Différentes saisons de Stephen King en nov 1989
• Le Fléau de Stephen King en juin 1989  (le bien & le mal genre "virus" qui tue tout le monde) 
• Cujo de Stephen King en juin 1989
• Brume la faucheusse de Stephen King en mai 1989

Défragmentation des tables

L'article "Understanding SQL Server's DBCC SHOWCONTIG" est très intéressant pour comprendre la fragmentation des tables.

DBCC ShowContig
L'instruction DBCC ShowContig( ObjectID ) permet d'obtenir des informations sur la fragmentation.
L'article décrit correctement comment interpréter ces valeurs.

DBCC DBReindex
Permet de faire un "defrag" de la table tel que le ferait un logiciel de défragmentation du disque dur.

Attention à l'espace disque!

Un index defrag nécessite énormement de place dans le data file.
A titre d'exemple, j'ai vu mon primary data file glonfler de 4Go à 23 Go lorsque j'ai defragmenté ma table d'audit de 11 Go)

DBCC DBREindex( 'tablename' )

Script utile
Le script suivant permet de générer un script analysant les 10 plus grosses tables de la DB.

SELECT TOP 10
'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')'
+ CHAR(10) +
'PRINT '' ''' + CHAR(10)
FROM
sysindexes
WHERE
indid = 1 or
indid = 0
ORDER BY rows DESC 

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' 

SQL Server Monitoring Performance - Wait Stats

Une question pertinente

Dans le cadre de mon travail, nous avons nos DB de productions stockées sur un iSCSI vault.
La question que je me suis déjà posé: C'est qu'arrive t'il si la DB n'est pas accessible (délai, latence, etc)?
Même plus précisément, qu'arrivera t'il si Sql Server s'il n'est pas capable d'écrire facilement dans son log file (voir pas du tout)?
Personnellement , je soupçonne même que la DB de devenir non opérationelle (time-out lors que l'insertion d'enregistrement). Cependant, en informatiques, les soupçons ne me suffisant pas! Je vais donc m'efforcer de prouver les propos. 


Pour tester ce cas de figure, je vais utiliser trois outils:

1. Un disque dur externe USB .
   
2. IOMeter, un outil OpenSource d'Intel. A l'aide de ce dernier il sera possible de stresser les access disques et de voir comment SQL server se comportera.
3. Les WaitStats stored procedures (décrites plus loin).
   

WaitStats procedures 
Voici deux stored procedures issus de l'article SQL Server Wait Events repris dans les réferences.
Ces dernières permettent d'analyser les waits times SQL depuis "dynamic management views".
La première procédure Begin_WaitStats prend une première image de référence des statistiques. Cette opération précède généralement l'évènement à monitorer.
La seconde procédure End_WaitStats prend une seconde image des statisitiques (après l'événement à monitorer) et effectuer une analyse comparative des informations.
La lecture de l'article permettra de faire une analyse correcte des résultats produits par End_WaitStats.
Ressources:

• Begin_WaitStats.sql
• End_WaitStats.sql
  

IOMeter
IOMeter est un outil open source d'intel. Ce dernier permet de tester les capacités de périphériques comme des disques durs ou des cartes réseaux.
Dans notre cas, nous allons utiliser IOMeter pour charger les access sur le disque dur externe.

 

La configuration utilisée est disponible ici (fichier icf)

Configuration du test

Dans l'exemple qui me concerne, je vais placer mon Transaction Log sur un lecteur USB. Ce qui me permettra de stresser l'accès au transaction Log tout en maintenant un débit normal sur le disque dur local.
Le moteur SQL pourra ainsi accéder librement à ses données.
Le lecteur USB sera chargé en utilisant IOMeter. Et durant cette période de stress, le script suivant sera utilisé pour monitorer les WaitStats lors de la solicitation du Transaction Log.
Script Ajoutant des données dans le transaction log

The script that is used to modify the data (insert 25000 audit trail records).

set nocount ON
EXECUTE begin_waitstats
go

 BEGIN TRANSACTION
 DECLARE @rows INT
 DECLARE @row INT
 DECLARE @count INT

 SELECT @rows = 2500
 SELECT @row = 0
 SELECT @count = 0
 WHILE @row < @rows
 BEGIN
        INSERT INTO tblSSAudit
                (
                ID_ObjectType,
                ID_ObjectInstance,
                ID_Person,
                Audit_Date,
                FieldsValue,
                Audit_Comment
                )
                VALUES
                        (
                        -1,
                        @count,
                        -999,
                        getDate(),
                        'StressTest',
                        REPLICATE('a',100)
                        )
        SELECT @row = @row + 1
        IF @count > 100
     BEGIN
         COMMIT WORK
         BEGIN TRANSACTION
         SELECT @count=0
     END
     SELECT @count=@count+1
 END
 COMMIT WORK
go
EXECUTE end_waitstats
go

Exécution du test
Test 1 - Sans stresser l'access au Transaction Log (stocké sur le drive USB)
The transaction log est accessible librement sur le lecteur USB.
Aucun processus externe ne vient entraver l'access au transaction log.

wait_type                                                    waits                wait_time            signal_wait_time     elapsed_time

------------------------------------------------------------ -------------------- -------------------- -------------------- -----------------------
PAGEIOLATCH_SH                                                                  4                   79                    0 1900-01-01 00:00:03.483
PAGEIOLATCH_UP                                                                  2                   32                    0 1900-01-01 00:00:03.483
PAGEIOLATCH_EX                                                                330                 2657                    0 1900-01-01 00:00:03.483
SLEEP_BPOOL_FLUSH                                                             230                 1437                    0 1900-01-01 00:00:03.483
WRITELOG                                                                       10                   47                   15 1900-01-01 00:00:03.483
session_id cpu_time             tot_sched_time elapsed_time PIO                  writes               LIO
---------- -------------------- -------------- ------------ -------------------- -------------------- --------------------
        51                  735            782         3565                  335                  457                    0

Le test s'est ici executé en 3.5 secondes.
L'écriture dans le log (WRITELOG) à créé un delai d'attente de 47 ms.
La plupart du temps étant perdu dans les accès disques (PAGEIO).
Plusieurs tests consécutifs on démontrés que le temps d'exécution du script (elapsed_time) variait entre 2.1 et 6 secondes.

Test 2 - Test stressant le libre accès au transaction log (stocké sur le Drive USB)
Utiliser le logiciel IOMeter (fichier de config ici) pour stresser les access disques du lecteur USB (le lecteur stockant le transaction log).
Pour les conditions du test, le lecteur USB est chargé avec1700 accès à la seconds (et une charge ridicule de 1 à 16 Mb/S car le débit n'est pas le facteur bloquant).

wait_type                                                    waits                wait_time            signal_wait_time     elapsed_time
------------------------------------------------------------ -------------------- -------------------- -------------------- -----------------------
LATCH_EX                                                                        3               843532                    0 1900-01-01 00:19:06.707
PAGEIOLATCH_SH                                                                  1                  218                    0 1900-01-01 00:19:06.707
PAGEIOLATCH_UP                                                                  3                92172                    0 1900-01-01 00:19:06.707
PAGEIOLATCH_EX                                                                  2                   16                    0 1900-01-01 00:19:06.707
IO_COMPLETION                                                                   9               358938                    0 1900-01-01 00:19:06.707
SLEEP_BPOOL_FLUSH                                                            1800                 9766                    0 1900-01-01 00:19:06.707
SOS_SCHEDULER_YIELD                                                            44                   16                   16 1900-01-01 00:19:06.707
WRITELOG                                                                        6                73625                   16 1900-01-01 00:19:06.707
LOGBUFFER                                                                       5               505203                    0 1900-01-01 00:19:06.707
LOGMGR_RESERVE_APPEND                                                           2                 2000                    0 1900-01-01 00:19:06.707
session_id cpu_time             tot_sched_time elapsed_time PIO                  writes               LIO
---------- -------------------- -------------- ------------ -------------------- -------------------- --------------------
        51                  172         404720      1146889                    7                  133                    0

Dans le de ce test, le temps d'execution est de 1146889 ms (soit 19 minutes).
La plupart du temps est perdu dans des LATCH_EX (14 min), IO_COMPLETION (14 min) et LOGBUFFER (8 min 30 sec).

ces tests démontrent bien l'importance des temps d'accès et de la disponibilité du disque dur stockant le transaction log. Toute opérations entravant l'accès au Transaction Log a une répercusion importante sur le temps de réponse SQL.

Note 1: Blocage en cascade et blocage de "select"
A une exception général près, toutes les opérations de lecture dans les tables de la DB ne sont pas affectées par ce "blocage" du transaction log.
L'exception apparaissant comme suit: si l'opération de lecture (select count(*) from myUsers) couvre une mise-à-jour en attente dans le transaction log (update myUser set Active=0 where idUser=125), dans ce, l'opération de lecture sera elle aussi mise "en attente". Cette "attente" sera résolue lorsque l'operation "update" sera enregistrée dans le transaction log.

Mise en place d'un monitoring des WaitStats
L'exemple précédent démontre que les Wait Time (Wait Statistics) sont des informations de premier plan pour déterminer la cause de problèmes.
Lorsque la situation critique peut être causée sur demande, l'utisation de scripts ou des stored procedure ne cause évidemment aucun problème.
Cependant, si le problème apparait de manière sporadique et sans signe annociateurs, il convient alors de collecter régulièrement ces statistiques de façon automatique et de les stocker de façon permanente (dans une DB).

Utilisation d'une machine de monitoring séparée

Dans les précédents exemple, il a été démontré que certains problèmes particulier (entre autre le libre accès aux ressources/disques) peut représenter une entrave majeure au bon fonctionnement de SQL serveur.
Dans de tel cas de figure, il ne serait pas opportun d'utiliser une DB sur le même serveur pour monitorer et stoquer les WaitStats.
Dans ce cas de figure l'utilisation d'un second serveur SQL avec un Profiler et le service SQLAgent est tout a fait approprié.

Créer la DB "MonitorWaits"

Créer la DB "MonitorWaits" sur la machine de monitoring.
Cette DB ne collectera par énorment d'information (en terme de quantité), cette DB n'a donc pas besoin d'être très grosse (quelques MB suffisent amplement).

--- Wait Monitor Toolbox -----------------------------------------
--
-- This SQL will create the needed table to store the monitoring
--   records
--
use MonitorWaits
go
create table WaitSessionMonitor ( SessionID int identity, CaptureTime DateTime not null )
go
Create table WaitCapture( SessionID int not null, Wait_Type varchar(60), Waiting_tasks_count bigint not null, Wait_time_ms bigint not null, max_wait_time_ms bigint not null, signal_wait_time_ms bigint not null)
go


Créer les tables nécessaires
use MonitorWaits
go
create table WaitSessionMonitor ( SessionID int identity, CaptureTime DateTime not null )
go 
Create table WaitCapture( SessionID int not null, Wait_Type varchar(60), Waiting_tasks_count bigint not null, Wait_time_ms bigint not null, max_wait_time_ms bigint not null, signal_wait_time_ms bigint not null)
go

Lier le server de monitoring au server de production:

Cela permettra au script (ci-dessous) d'effectuer les requêtes SQL dans la table Sys.dm_os_wait_stats du serveur de production.

A l'aide du management console, créer un "Linked Server" vers le serveur de production.

 

Préparer et configurer le script de capture tel que celui-ci:

declare @ID int
insert into monitorwaits..WaitSessionMonitor ( CaptureTime ) 
  values ( getdate() )
select @ID = SCOPE_IDENTITY()

insert WaitCapture 
SELECT @id, wait_type, waiting_tasks_count, wait_time_ms, 
  max_wait_time_ms, signal_wait_time_ms 
FROM DBPRODUCTION.master.Sys.dm_os_wait_stats 
where wait_type like 'LogBuffer' or 
  Wait_Type like 'LOGMGR%' or 
  wait_type like 'WRITE%' or 
  wait_type like 'latch_%' or 
  wait_Type like 'IO_COMPLETION' or 
  Wait_type like 'SOS_SCH%' or
  wait_type like 'PAGEIOLATCH%'

Ce script sera éxécuté par un Job Sql depuis la machine de monitoring toutes les minutes.
Afin d'éviter les surprises, une date de fin sera programmée sur le job (afin que celui-ci ne fonctionne pas des années durant s'il est oublié). 

Analyse des informations

Le script WaitMonitorCompute.sql suivant permet de d'extraire des information pertinentes depuis les statistiques collectées.
Ce script manipulera les statistiques et stockera les résultats dans la table ComputedWaits. 
Les variables  @SessionStart et @SessionEnd permettent de restreindre le champs de l'analyse des statistiques collectées. Ces variables recoivent les valeurs de SessionID qui peuvent être retrouvée dans la table WaitSessionMonitor.

--- Wait Monitor Toolbox -----------------------------------------
--
-- This SQL will compute the content of the WaitCapture table and
--   produce the results into the table ComputedWaits
--
use MonitorWaits
go

DECLARE @SessionStart int
DECLARE @SessionEnd int

SELECT @SessionStart = -1 -- Use -1 to start to very first entry
SELECT @SessionEnd = -1 -- use -1 to end at very last entry

if not exists (select 1 
               from sys.objects 
               where object_id = object_id ( N'[dbo].[waitSessionMonitor]') and 
               OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
begin
  raiserror ('Missing [waitSessionMonitor] table. Execute script WaitMonitorCreateTables.sql!',16,1) with nowait    
end

if not exists (select 1 
               from sys.objects 
               where object_id = object_id ( N'[dbo].[WaitCapture]') and 
               OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
begin
  raiserror ('Missing [WaitCapture] Table. Execute script WaitMonitorCreateTables.sql!',16,1) with nowait    
end

if exists (select 1 
           from sys.objects 
           where object_id = object_id ( N'[dbo].[ComputedWait]') and 
           OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
  EXEC( 'drop table [dbo].[ComputedWait]' )

CREATE TABLE [dbo].[ComputedWait] (
  SessionID bigint not null,
  CaptureTime datetime not null,
  Wait_Type varchar(60) not null,
  Waits bigint not null,
  Wait_time_ms bigint not null,
  TimePerWait_ms decimal(7,3) default 0
)

if @SessionStart < 0 
  select @SessionStart = min( SessionID ) from WaitSessionMonitor
if @SessionEnd < 0
select @SessionEnd = max( SessionID ) from WaitSessionMonitor

declare @CurrentID int
select @CurrentID = @SessionStart
while ( @CurrentId < @SessionEnd ) -- Cannot process the last entry 
begin
  print 'Processing SessionID '+ cast( @CurrentID as Varchar(15) )
  insert [dbo].[ComputedWait] ( SessionID, CaptureTime, Wait_Type, Waits, Wait_time_ms )
  select 
    v1.SessionID, 
    sess.CaptureTime, 
    v1.Wait_Type, 
    v2.Waiting_Tasks_count - v1.Waiting_Tasks_Count as Waits,
    v2.Wait_Time_ms - v1.Wait_Time_ms as WaitTime
  from WaitCapture v2
    left join WaitCapture v1 on v1.SessionID=@CurrentID and v1.Wait_type = v2.Wait_Type
    left join WaitSessionMonitor sess on sess.SessionID = @CurrentID
  where V2.SessionID = @CurrentId+1

  select @CurrentID = @CurrentID + 1
end

update [dbo].[ComputedWait]
set TimePerWait_ms = Wait_time_ms / Waits 
where waits > 0

--
-- Display informations about the computed range
--
select SessionID as SessionID_Range, min(CaptureTime) as CaptureTime_Range
from [dbo].[ComputedWait] 
where SessionID in (
  select min(SessionID) from [dbo].[ComputedWait] 
  UNION
  select max(SessionID) from [dbo].[ComputedWait]
)
group by SessionID

-- 
--  Display statisitical information about the computed range
--
select Wait_Type, count(*) as NumberOfCaptures, sum( Waits ) as TotalOfWaits, sum(Wait_time_ms) / 1000 as TotalWaitTimeInSec  
from ComputedWait
where TimePerWait_ms > 0 
group by Wait_Type 

Exemple de résultat

Voici un exemple de résultat produit par le script WaitMonitorCompute.sql, le tout extrait d'un récent monitoring.

Dans ce dernier exemple, il apparait que SQL server à attendu un total de 21 secondes pour des PAGEIOLATCH_SH. Il semblerait donc évident qu'il existe, dans ce cas, un problème d'accès disque.

Par la suite, une requête sur la table ComputedWait permet de mettre en lumière (dans le temps) le phénomène de congestion d'accès disques.

SELECT * FROM ComputedWait 
where Wait_Type like 'PAGEIOLATCH%'

Qui produira le résultat suivant:

SessionID CaptureTime              WaitType       Waits Wait_Time_ms TimePerWait_ms 
4193    2009-05-11 16:57:02.810    PAGEIOLATCH_NL    0         0     0.000
4193    2009-05-11 16:57:02.810    PAGEIOLATCH_KP    0         0     0.000
4193    2009-05-11 16:57:02.810    PAGEIOLATCH_SH    275    3844     13.000
4193    2009-05-11 16:57:02.810    PAGEIOLATCH_UP    0         0     0.000
4193    2009-05-11 16:57:02.810    PAGEIOLATCH_EX    0         0     0.000
4193    2009-05-11 16:57:02.810    PAGEIOLATCH_DT    0         0     0.000
4194    2009-05-11 16:58:01.780    PAGEIOLATCH_NL    0         0     0.000
4194    2009-05-11 16:58:01.780    PAGEIOLATCH_KP    0         0     0.000
4194    2009-05-11 16:58:01.780    PAGEIOLATCH_SH    297    5359     18.000
4194    2009-05-11 16:58:01.780    PAGEIOLATCH_UP    0         0     0.000
4194    2009-05-11 16:58:01.780    PAGEIOLATCH_EX    37      234     6.000
4194    2009-05-11 16:58:01.780    PAGEIOLATCH_DT    0         0     0.000
4195    2009-05-11 16:59:01.780    PAGEIOLATCH_NL    0         0     0.000
4195    2009-05-11 16:59:01.780    PAGEIOLATCH_KP    0         0     0.000
4195    2009-05-11 16:59:01.780    PAGEIOLATCH_SH    234   12235     52.000
4195    2009-05-11 16:59:01.780    PAGEIOLATCH_UP    0         0     0.000
4195    2009-05-11 16:59:01.780    PAGEIOLATCH_EX    34      219     6.000
4195    2009-05-11 16:59:01.780    PAGEIOLATCH_DT    0         0     0.000

Reset des WaitStats
Le contenu de ce point n'a pas encore été vérifié.
Certaines Wait statistiques ne sont pas forcement resetée (par exemple, si elle font parties d'une queue de traitement).
Executer la commande suivante puis attendre 5 secondes.

DBCC sqlperf ('sys.dm_os_wait_stats', clear)

Ressources et Références

• Les différents script de capture décrits dans cet article sont accessibles dans l'archive DomeuWaitMonitor.zip.
• Les scripts relatifs à la session de test du blocage du fichier Transaction Log sont disponibles ici: Begin_WaitStats.sql et End_WaitStats.sql

• Article "SQL Server Wait Events: Taking the Guesswork out of Performance Profiling" (copie ici).
• IOMeter est un outils OpenSource d'Intel permettant de mesurer et stresser les périhpériques.
• Article de l'équipe SQLOS de Microsoft relatif à l'extraction des WaitStats par sessions (SQL 2008).
• Article de Microsoft: Trouble Shooting Performances in SQL2005.