Lecture des deadlock en mode graphique sous SQLServer

1

Le script ci-dessous permet de lister les deadlocks survenus sur une instance (2008 et plus) et d'afficher le deadlock choisi en mode graphique.

 

param
(
    [string]$FullInstanceName,
    [string]$sourceXMLFile
)

if (!$FullInstanceName){
    "Usage : "
    "        FullInstanceName     : Mandatory. Full Name of MSSQL Server (ex hostname\mssqlservername)"
    "        sourceXMLFile         : XML Full File Name (ex : c:\temp\Deadlock.xml)"
    ""
    "ex :"
    "        ./readDeadlock.ps1 -FullInstanceName "
    ""
    exit 2
}

## Functions

function chooseDateCollect {
Param ($CreationDateArray)
[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") | out-null

$Form = New-Object System.Windows.Forms.Form
$Form.width = 500
$Form.height = 110
$Form.Text = "Choose the date of a deadlock :"

$DropDown = new-object System.Windows.Forms.ComboBox
$DropDown.Location = new-object System.Drawing.Size(100,10)
$DropDown.Size = new-object System.Drawing.Size(320,30)
ForEach ($Item in $CreationDateArray) {$DropDown.Items.Add($Item) | out-null}

$Form.Controls.Add($DropDown)  | out-null

$Form.KeyPreview = $True
$Form.Add_KeyDown({if ($_.KeyCode -eq "Enter") {$global:DateCollect=$DropDown.SelectedItem.ToString();Write-Host $DateCollect;$Form.Close()}})
$Form.Add_KeyDown({if ($_.KeyCode -eq "Escape") {$Form.Close()}})

$DropDownLabel = new-object System.Windows.Forms.Label
$DropDownLabel.Location = new-object System.Drawing.Size(50,12)
$DropDownLabel.size = new-object System.Drawing.Size(100,20)
$DropDownLabel.Text = "DateCollect"
$Form.Controls.Add($DropDownLabel) | out-null

$Button = new-object System.Windows.Forms.Button
$Button.Location = new-object System.Drawing.Size(225,50)
$Button.Size = new-object System.Drawing.Size(50,20)
$Button.Text = "OK"
$Button.Add_Click({$global:DateCollect = $DropDown.SelectedItem.ToString();$Form.Close() | out-null;Write-Host $DateCollect})
$form.Controls.Add($Button) | out-null

$Form.Add_Shown({$Form.Activate()})
$Form.ShowDialog()

}



# Function to set sql environment
function setSQL()
{
    if ( Get-PSSnapin -Registered | where {$_.name -eq 'SqlServerProviderSnapin100'} )
    {
        if( !(Get-PSSnapin | where {$_.name -eq 'SqlServerProviderSnapin100'}))
        {Add-PSSnapin SqlServerProviderSnapin100 | Out-Null} ;
        if( !(Get-PSSnapin | where {$_.name -eq 'SqlServerCmdletSnapin100'}))
        {Add-PSSnapin SqlServerCmdletSnapin100 | Out-Null}
    }
    else
    {
        if( !(Get-Module | where {$_.name -eq 'sqlps'}))
        {$env:PSModulePath = $env:PSModulePath + ";C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules"
        Import-Module 'sqlps' –DisableNameChecking}
    }
}


## Main

setSQL

#$FullInstanceName="mscsinap02\msparinap02"
$FullInstanceName2=$FullInstanceName -replace "\\", "_"

if (! $sourceXMLFile){
    [string]$sourceXMLFile="c:\temp\DeadLock_"+$FullInstanceName2+".xml"

    $CreationDateArray = @(Invoke-Sqlcmd -Query "select CONVERT(nvarchar(30), XEventData.XEvent.value('@timestamp', 'datetime'), 126) as Creation_Date
    FROM
    (select CAST(target_data as xml) as TargetData
    from sys.dm_xe_session_targets st
    join sys.dm_xe_sessions s on s.address = st.event_session_address
    where name = 'system_health') AS Data
    CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    where XEventData.XEvent.value('@name', 'varchar(max)') = 'xml_deadlock_report'" -Serverinstance $FullInstanceName -outputsqlerrors $true -ErrorAction Stop -QueryTimeout 20000) | select-object -expand Creation_Date

    chooseDateCollect -CreationDateArray $CreationDateArray
    $DateCollect

    (Invoke-Sqlcmd -Query "select T.Creation_Date,T.DeadlockGraph
from (select XEventData.XEvent.value('@timestamp', 'datetime') as Creation_Date,
CONVERT(nvarchar(30), XEventData.XEvent.value('@timestamp', 'datetime'), 126) AS UsingConvertTo_ISO8601,
CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML) as DeadlockGraph
FROM
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report') T
where T.Creation_Date = '$DateCollect'" -Serverinstance $FullInstanceName  -MaxCharLength 6000 -outputsqlerrors $true -ErrorAction Stop -QueryTimeout 20000).DeadlockGraph | out-file $sourceXMLFile
} else {
    "file XML : $sourceXMLFile"
}


# Convert XML File to XDL File (to open it with SSMS)
[Reflection.Assembly]::LoadWithPartialName("System.Xml")
[Reflection.Assembly]::LoadWithPartialName("System.Xml.Xsl")
[Reflection.Assembly]::LoadWithPartialName("System.IO")
[Reflection.Assembly]::LoadWithPartialName("System.IO.Path")
# Load in our transform
$xslTransform = [System.Xml.Xsl.XslCompiledTransform](new-object System.Xml.Xsl.XslCompiledTransform);
$xslTransform.Load("c:\Tools\admin\scripts\transform.xslt");
# Load in the xml produced by the extended event
$dom = [System.Xml.XmlDocument](new-object System.Xml.XmlDocument);
$dom.Load($sourceXMLFile);
# Transform the xml into the profiler format
$results = [System.IO.StringWriter](new-object System.IO.StringWriter);   
$XSLArg = New-Object System.Xml.Xsl.XsltArgumentList
$xslTransform.Transform($dom, $XSLArg, $results)
$xml = $results.ToString();
# write the amended xml out to a new file
$domNew = [System.Xml.XmlDocument](new-object System.Xml.XmlDocument);
$domNew.LoadXml($xml)
$newFilename = [System.IO.Path]::ChangeExtension($sourceXMLFile, "xdl")
$domNew.Save($newFilename);

# Run SSMS with rdl file to show deadlock graph
ssms.exe $newFilename

 

 

Fichier transform.xslt :

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">

  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/">
    <deadlock-list>
      <deadlock>
        <xsl:attribute name="victim">
          <xsl:value-of select="deadlock/victim-list/victimProcess/@id"/>
        </xsl:attribute>
        <xsl:apply-templates select="deadlock/process-list"/>
        <xsl:apply-templates select="deadlock/resource-list"/>
      </deadlock>
    </deadlock-list>
  </xsl:template>

  <xsl:template match="process-list">
    <process-list>
      <xsl:apply-templates select="@* | node()"/>
    </process-list>
  </xsl:template>

  <xsl:template match="resource-list">
    <resource-list>
      <xsl:apply-templates select="@* | node()"/>
    </resource-list>
  </xsl:template>

  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>


</xsl:stylesheet>