SelectEtoile : Base de connaissance SGBD

Posts Tagged 'mssql'

Lecture des deadlock en mode graphique sous SQLServer

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>
Articles tagged

Comment changer la valeur d'une colonne identity sous SQLServer ?

Voici un exemple de table avec une colonne identity :

CREATE TABLE IDENTITYTABLE(Id int IDENTITY(1,1) NOT NULL,c char(1), i int)
go

 

On insère 10 lignes

insert into IDENTITYTABLE values ('a',1)
go 10
select * from IDENTITYTABLE
go

 

Ce qui donne le résultat attendu :

1    a    1
2    a    1
3    a    1
4    a    1
5    a    1
6    a    1
7    a    1
8    a    1
9    a    1
10    a    1

 

Maintenant, on change la valeur de la prochaine identity :

DBCC CHECKIDENT (IDENTITYTABLE, RESEED, 99)
go

Checking identity information: current identity value '10', current column value '99'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

On insert à nouveau 10 lignes et voici le résultat obtenu :

1    a    1
2    a    1
3    a    1
4    a    1
5    a    1
6    a    1
7    a    1
8    a    1
9    a    1
10    a    1
100    b    2
101    b    2
102    b    2
103    b    2
104    b    2
105    b    2
106    b    2
107    b    2
108    b    2
109    b    2

 

Nous voyons clairement que l'on passe directement de 10 à 100.

 

 

 

Afficher la différence entre n lignes d'une table dans SQLServer

Nous avons une table 'arch_dm_os_performance_counters' avec les lignes suivantes :

DateCollect                         Page lookups/sec
2013-05-21 10:10:00.287    48929368335
2013-05-21 10:15:00.327    48929486443
2013-05-21 10:20:00.367    48929553472
2013-05-21 10:25:00.420    48933159477
2013-05-21 10:30:00.457    48934613177
2013-05-21 10:35:00.607    49051424461
2013-05-21 10:40:00.733    49197499383
2013-05-21 10:45:00.807    49269614483
2013-05-21 10:50:00.857    49361353885
2013-05-21 10:55:00.953    49455296177
2013-05-21 11:00:00.943    49551027790
2013-05-21 11:05:00.117    49623120162
2013-05-21 11:10:00.153    49623180626

 

 

Nous cherchons à afficher ici le différentiel entre chaque ligne par date de collecte pour le résultat suivant :

DateCollect                      Page lookups/sec          DateCollect                 Page lookups/sec    Diff Page lookups/sec
2013-05-21 10:15:00.327    48929486443    2013-05-21 10:10:00.287    48929368335    118108
2013-05-21 10:20:00.367    48929553472    2013-05-21 10:15:00.327    48929486443    67029
2013-05-21 10:25:00.420    48933159477    2013-05-21 10:20:00.367    48929553472    3606005
2013-05-21 10:30:00.457    48934613177    2013-05-21 10:25:00.420    48933159477    1453700
2013-05-21 10:35:00.607    49051424461    2013-05-21 10:30:00.457    48934613177    116811284
2013-05-21 10:40:00.733    49197499383    2013-05-21 10:35:00.607    49051424461    146074922
2013-05-21 10:45:00.807    49269614483    2013-05-21 10:40:00.733    49197499383    72115100
2013-05-21 10:50:00.857    49361353885    2013-05-21 10:45:00.807    49269614483    91739402
2013-05-21 10:55:00.953    49455296177    2013-05-21 10:50:00.857    49361353885    93942292
2013-05-21 11:00:00.943    49551027790    2013-05-21 10:55:00.953    49455296177    95731613
2013-05-21 11:05:00.117    49623120162    2013-05-21 11:00:00.943    49551027790    72092372
2013-05-21 11:10:00.153    49623180626    2013-05-21 11:05:00.117    49623120162    60464

 

 

Voici la requête sql permettant d'obtenir ce résultat :

;WITH A AS
(select ROW_NUMBER()OVER (order by A.datecollect) RowNumber, A.datecollect, A.[Page lookups/sec]
FROM arch_dm_os_performance_counters A
where A.DateCollect > '2013/05/21 10:10' and A.DateCollect < '2013/05/21 11:15')
SELECT A.DateCollect, A.[Page lookups/sec],
    B.DateCollect, B.[Page lookups/sec],
    A.[Page lookups/sec]-B.[Page lookups/sec] as [Diff Page lookups/sec]
FROM A
INNER JOIN
(SELECT ROW_NUMBER()OVER (order by Datecollect) RowNumber, datecollect, [Page lookups/sec]
FROM arch_dm_os_performance_counters
where DateCollect > '2013/05/21 10:10' and DateCollect < '2013/05/21 11:15'
) B ON A.RowNumber = B.rownumber+1

 

 

La requête suivante permet de n'afficher que la Date de collecte et la colonne contenant le différentiel :

SELECT A.DateCollect,
(A.[Page lookups/sec]-B.[Page lookups/sec])/DATEDIFF(SS,B.DateCollect,A.DateCollect) as [Diff Page lookups/sec]
FROM
(select ROW_NUMBER()OVER (order by A.datecollect) RowNumber, A.datecollect, A.[Page lookups/sec]
FROM arch_dm_os_performance_counters A
where A.DateCollect > '2013/05/21 10:10' and A.DateCollect < '2013/05/21 11:15') A
INNER JOIN
(SELECT ROW_NUMBER()OVER (order by Datecollect) RowNumber, datecollect, [Page lookups/sec]
FROM arch_dm_os_performance_counters
where DateCollect > '2013/05/21 10:10' and DateCollect < '2013/05/21 11:15') B
ON A.RowNumber = B.rownumber+1
where DATEDIFF(SS,B.DateCollect,A.DateCollect)>0

 

 

Résultat :

DateCollect                         Diff Page lookups/sec
2013-05-21 10:15:00.327    393
2013-05-21 10:20:00.367    223
2013-05-21 10:25:00.420    12020
2013-05-21 10:30:00.457    4845
2013-05-21 10:35:00.607    389370
2013-05-21 10:40:00.733    486916
2013-05-21 10:45:00.807    240383
2013-05-21 10:50:00.857    305798
2013-05-21 10:55:00.953    313140
2013-05-21 11:00:00.943    319105
2013-05-21 11:05:00.117    240307
2013-05-21 11:10:00.153    201

 

Merci à Olivier pour cet article

Articles tagged

Trouver le compte de service d'une instance MSSQL

DECLARE @ServiceaccountName varchar(250)
DECLARE @CurrentSet varchar(250)
if @@SERVICENAME = 'MSSQLSERVER'
select @CurrentSet = 'SYSTEM\CurrentControlSet\Services\'+@@SERVICENAME
else
select @CurrentSet = 'SYSTEM\CurrentControlSet\Services\MSSQL$'+@@SERVICENAME
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
@CurrentSet,
N'ObjectName',
@ServiceAccountName OUTPUT,
N'no_output'
 
SELECT @@servername + ' - ' + @ServiceaccountName

Propriétés d'un login sous MSSQL

SELECT name,

create_date,

modify_date,

case

      when LOGINPROPERTY(name, 'DaysUntilExpiration') is null then 'CHECK_POLICY or CHECK_EXPIRATION is OFF for a login, or if the operating system does not support the password policy.'

      when LOGINPROPERTY(name, 'DaysUntilExpiration') = 0 then 'the login is expired or if it will expire on the day when queried.'

      when LOGINPROPERTY(name, 'DaysUntilExpiration') = -1 then 'the local security policy in Windows never expires the password.'

end as DaysUntilExpiration,

LOGINPROPERTY(name, 'PasswordLastSetTime') PasswordLastSetTime,

LOGINPROPERTY(name, 'IsExpired') IsExpired,

LOGINPROPERTY(name, 'IsMustChange') IsMustChange,

LOGINPROPERTY(name, 'PasswordHash')

From sys.sql_logins

where name not like '##%'

Liste des traceflags sous SQLServer

Cet article décrit plusieurs traceflags qui peuvent être très pratiques

 

661 : Désactivation du processus interne SQLServer Ghost Cleanup. Ce processus va supprimer physiquement les enregistrements "fantôme" (enregistrement qui a été supprimé logiquement d’une page d’un index d’une table lors d’une opération de suppression à l’aide d’une commande TSQL DELETE par exemple).

Exemple de mise en oeuvre :

  1. le processus ghost cleanup peut s'avérer consommateur en ressource CPU sur certains systèmes très transactionnel ou il peut être utile de le désactiver.
  2. Il m'est arrivé de le désactiver sur des environnements de test sur lesquels on devait passer la base offline. En effet, la commande offline était bloqué par le processus ghost cleanup.

 

 


2371 : Change le  mode de déclenchement de l'auto statistics en fonction de la cardinalité.

Cela peut être pratique pour les tables très volumineuses. En effet, pour une table qui fait 1 milliards de lignes, par défaut, il va falloir attendre 20 millions de modifications avant déclenchement des update statistics. Avec ce traceflag 2371, il ne faudra attendre que 1 millions de mise à jour. Ce qui permet de maintenir des statistiques suffisamment à jour même avec de fortes volumétries.

 

Les règles "classiques" du mécanisme d'auto update statistics sont les suivantes :

  • Pour les tables créées dans la base tempdb avec une cardinalité inférieure à 6, l'auto update statistics se déclenche toutes les 6 modifications.
  • Pour les tables avec une cardinalité comprise entre 6 et 500, l'auto update statistics se déclenche toutes les 500 modifications.
  • Pour les tables avec une cardinalité supérieure à 500, l'auto update statistics se déclenche quand (500 + 20% de la table) modifications sont faites.
  • Pour les variables 'table', pas d'auto update statistics.

 

 

 

1118 : Diminue la contention sur la base tempdb

Grâce à ce traceflag, SQL Server alloue des extents complets à chaque objet tempdb, éliminant ainsi la contention sur la page SGAM.
Cela entraine forcément un peu de déchet au niveau de l'espace disque dans tempdb mas qui largement compensé par le gain de performance.

On peut observer de la contention sur la base tempdb via les DMVs dm_exec_request, dm_os_waiting_tasks. Les colonnes wait_type (PAGELATCH_*) et wait_resource (2:1:1 pour la page PFS ou 2:1:3 pour une page SGAM).

 

 

1200 : Affiche les informations instantanées sur les verrous posés pour la requête lancée.

 

Gestion d'un Cluster via Powershell

Pour commencer, il faut importer les modules relatif au cluster windows :
Import-Module FailoverClusters

 

On peut ensuite lister les commandes disponibles :
Get-Command -Module FailoverClusters

 

Pour rappel, pour avoir la description d'une commande, il suffit de taper get-help devant :
PS P:\> get-help Get-ClusterGroup

NAME
    Get-ClusterGroup

SYNOPSIS
    Get information about one or more clustered services or applications (resource groups) in a failover cluster.


SYNTAX
    Get-ClusterGroup [-InputObject <psobject>] [[-Name] <StringCollection>] [-Cluster <string>] [<CommonParameters>]


DESCRIPTION
    A resource group (a clustered service or application) is the unit of failover. During failover, all resources in th
    e resource group move together.


RELATED LINKS
    Online version: http://go.microsoft.com/fwlink/?LinkId=143785
    Add-ClusterGroup
    Move-ClusterGroup
    Remove-ClusterGroup
    Start-ClusterGroup
    Stop-ClusterGroup

REMARKS
    To see the examples, type: "get-help Get-ClusterGroup -examples".
    For more information, type: "get-help Get-ClusterGroup -detailed".
    For technical information, type: "get-help Get-ClusterGroup -full".

 

Quelques commandes pratiques :

get-cluster : retourne le nom de votre cluster

Get-ClusterNode : retourne l'état des noeuds

 

Informations globales sur le cluster :

PS P:\> Get-ClusterResource "Cluster Name" | Format-List *


Cluster                : DPRRADPRDCLUS01
IsCoreResource         : True
IsNetworkClassResource : False
IsStorageClassResource : False
OwnerNode              : srvparradp22
ResourceType           : Network Name
State                  : Online
OwnerGroup             : Cluster Group
Name                   : Cluster Name
MaintenanceMode        : False
MonitorProcessId       : 6536
Description            :
SeparateMonitor        : False
PersistentState        : 1
LooksAlivePollInterval : 4294967295
IsAlivePollInterval    : 4294967295
RestartAction          : 2
RestartThreshold       : 1
RestartDelay           : 500
RestartPeriod          : 900000
RetryPeriodOnFailure   : 3600000
PendingTimeout         : 180000
DeadlockTimeout        : 300000
ResourceSpecificStatus :
Id                     : 9584290f-7ad9-412d-9006-c6cfdbd37f39

 

Détailler les interfaces réseau :
Get-ClusterNetwork PUBLIC | Format-List *Cluster           : NOM_DU_CLUSTER
State             : Up
Name              : PUBLIC
Ipv6Addresses     : {}
Ipv6PrefixLengths : {}
Ipv4Addresses     : {xxx.xx.x.x}
Ipv4PrefixLengths : {21}
Address           : xxx.xx.x.x
AddressMask       : 255.255.248.0
Description       :
Role              : 3
AutoMetric        : True
Metric            : 10000
Id                : xxxxxxxxx

 

Pour info, le parametre 'Metric' permet d'identifier les réseaux privés et public. En effet, les valeurs '1000 et plus' correspondent aux réseaux privés et '10000 et plus' aux réseaux 'public'.

Par exemple, on peut récupérer le nom de réseau public en lançant la commande :

(Get-ClusterNetwork | where-object {$_.Metric -eq "10000"}).Name

 

Pour plus d'information, cliquer sur le lien (http://technet.microsoft.com/fr-fr/library/ff182335%28v=ws.10%29.aspx)

 

Informations sur les disques cluster :

Import-Module FailoverClusters

$objs = @()

$csvs = Get-ClusterSharedVolume
foreach ( $csv in $csvs )
{
   $csvinfos = $csv | select -Property Name -ExpandProperty SharedVolumeInfo
   foreach ( $csvinfo in $csvinfos )
   {
      $obj = New-Object PSObject -Property @{
         Name        = $csv.Name
         Path        = $csvinfo.FriendlyVolumeName
         Size        = $csvinfo.Partition.Size
         FreeSpace   = $csvinfo.Partition.FreeSpace
         UsedSpace   = $csvinfo.Partition.UsedSpace
         PercentFree = $csvinfo.Partition.PercentFree
      }
      $objs += $obj
   }
}

$objs | ft -auto Name,Path,@{ Label = "Size(GB)" ; Expression = { "{0:N2}" -f ($_.Size/1024/1024/1024) } },@{ Label = "FreeSpace(GB)" ; Expression = { "{0:N2}" -f ($_.FreeSpace/1024/1024/1024) } },@{ Label = "UsedSpace(GB)" ; Expression = { "{0:N2}" -f ($_.UsedSpace/1024/1024/1024) } },@{ Label = "PercentFree" ; Expression = { "{0:N2}" -f ($_.PercentFree

 

 

 

SQL Server et les partitions

Vous trouverez ici quelques scripts sql permettant de visualiser et manipuler les partitions sous SQLServer

Les tables systèmes :

  • sys.tables
  • sys.indexes
  • sys.partitions
  • sys.partition_schemes
  • sys.partition_functions
  • sys.partition_parameters

 

Lister les partitions d'une base :

select object_name(object_id) as tablename,* from sys.partitions



Lister les partitions de toutes les tables

select T.name, T.Object_id, T.Type_Desc,P.Partition_Id
from sys.tables T
inner join sys.partitions P on T.object_id = P.object_id

 

Affiche la fonction et le schéma de partition de toutes les tables :

SELECT
    tbl.name AS [Name],
    tbl.object_id AS [ID],
    ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount],
    CASE WHEN 'FG'=dsidx.type THEN dsidx.name ELSE N'' END AS [FileGroup],
    CAST(CASE WHEN 'PS'=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
    ps.name AS [PartitionScheme],
    pf.name    AS [PartitionFunction]
FROM
    sys.tables AS tbl
    INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
    LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
    LEFT OUTER JOIN sys.partition_schemes AS ps ON dsidx.name = ps.name    
    LEFT OUTER JOIN sys.partition_functions pf on ps.function_id=pf.function_id



Lister les caractéristiques des partitions d'une table

select object_name(object_id) as table_name, index_id, partition_number, rows
from sys.partitions  where object_name(object_id) = 'matable'

 

Afficher le détails des fonctions et schémas de partitions

-- Affiche les schémas et fonctions de partitions

select s.name as scheme_name, f.name as function_name, limit = case f.boundary_value_on_right when 0 then 'LEFT' else 'RIGHT' END
from sys.partition_schemes s
inner join sys.partition_functions f on s.function_id=f.function_id

 

-- Liste des paramètres et des valeurs de la fonction pfDate

select    *
from    sys.partition_functions pf
        inner join sys.partition_parameters pp on pf.function_id=pp.function_id
        where pf.name = 'pfDate'

select    *
from    sys.partition_functions pf
        inner join sys.partition_range_values prv on pf.function_id=prv.function_id
        where pf.name = 'pfDate'

 

--relation scheme fonction valeurs

select    s.name as scheme_name, f.name as function_name,
        limit = case f.boundary_value_on_right when 0 then 'LEFT' else 'RIGHT' END ,
        v.boundary_id, v.value
from sys.partition_schemes s
inner join sys.partition_functions f on s.function_id=f.function_id
inner join sys.partition_range_values v on v.function_id=f.function_id

 

 

 

Autocommit contre Transaction explicite sous SQLServer

Le but de l'article est de démontrer la différence de performance entre une mise à jour faite en commit implicit ou autocommit et une transaction explicite.

C'est un exemple un peu extrême mais ça montre bien la différence de temps.

 

Commençons par se créer une petite table :

create table matable (i int,c varchar(50))
create clustered index ind on matable(i)
set statistics io,time on
go

 

Code de la requête avec AUTOCOMMIT :

-- AUTOCOMMIT ou COMMIT IMPLICIT
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)
declare @i int
select @i=0
WHILE @i < 60000
Begin
INSERT INTO matable values (1,'Name')
select @i=@i+1
End
select * from sys.dm_os_wait_stats s where s.wait_type='WRITELOG'

Temps d'execution : 40 secondes

Résultats :

wait_type      waiting_tasks_count    wait_time_ms    max_wait_time_ms    signal_wait_time_ms
WRITELOG    60045                       34054              260                         671

 

Code de la requête avec TRANSACTION EXPLICITE :

-- EXPLICIT TRANSACTION
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)
declare @i int
select @i=0
BEGIN TRAN
WHILE @i < 60000
Begin
INSERT INTO matable values (1,'Name')
select @i=@i+1
End
COMMIT TRAN
select * from sys.dm_os_wait_stats s where s.wait_type='WRITELOG'

Temps d'execution : 10 secondes

Résultats :

wait_type      waiting_tasks_count    wait_time_ms    max_wait_time_ms    signal_wait_time_ms
WRITELOG    1                              5                     5                             0

 

On va donc 4 fois plus vite avec une transaction explicite qu'en AUTOCOMMIT.

Cela s'explique très bien avec les temps d'attentes sur la Wait Task 'WRITELOG'. En effet, lors de la 1ère requête, on passe 34 secondes sur 40 à écrire dans le log.

'WRITELOG' indique que SQL serveur attend que les opérations IO relatives au transaction log soient terminées.

Il est généralement bon indicateur d'un problème de performance disque.

 

J'espère que ca vous a plu.

 

 

Utilisation de logman pour scripter des traces perfmon

logman est un programme permettant de définir, démarrer, stopper des traces perfmon.

Voici quelques exemples bien pratiques d'utilisation des ce programme

 

Tout d'abord, il faut définir une trace de compteur perfmon. Pour faire plus simple, je me suis créer un fichier '.bat' dans lequel j'ai mis le code suivant :

Logman.exe create counter Perf-Log -f bincirc -v mmddhhmm -max 250 -o "d:\perfmon\logman" -c "\LogicalDisk(*)\*" "\Memory\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\PhysicalDisk(*)\*" "\Process(*)\*" "\Redirector\*" "\Server\*" "\System\*" "\Thread(*)\*" -si 00:00:05


En executant le script, on a créé la trace perfmon nommé 'Perf-Log' avec les compteus de perfs spécifié dans le parametre '-c', avec un echantillonnage à 5 secondes.

Le fichier en sortie (au format .blg) sera écris dans 'd:\perfmon\' et nommé logman*.blg. Ce fichier ne dépassera pas les 250 MB (Option -max) et il sera au format bincirc.

 

Il est possible de spécifier un fichier contenant la conf (ex : PerfLog.cfg) et de lancer la commande suivante :

Logman.exe create counter High-CPU-Perf-Log -f bincirc -v mmddhhmm -max 250 -o "d:\perfmon\perfmon" -cf "d:\perfmon\PerfLog.cfg" -rf 05:00

Ici, l'option '-rf' permet de préciser le temps que va durer la collecte (ici, 5 mins)

Exemple de fichier PerfLog.cfg :

"\Memory\Available MBytes"
"\Memory\Pool Nonpaged Bytes"
"\Memory\Pool Paged Bytes"
"\PhysicalDisk(*)\Current Disk Queue Length"
"\PhysicalDisk(*)\Disk Reads/sec"
"\PhysicalDisk(*)\Disk Reads Bytes/sec"
"\PhysicalDisk(*)\Disk Writes/sec"
"\PhysicalDisk(*)\Disk Writes Bytes/sec"
"\Process(*)\% Processor Time"
"\Process(*)\Private Bytes
"\Process(*)\Virtual Bytes"

 

Un fichier blg est alors créé, vous pouvez double cliquer dessus pour visualiser les graphes sous perfmon.

Il est possible de croiser les données de ce fichier BLG avec une trace profiler par exemple. c'est très pratique pour identifier les problèmes lors de pic CPU, IO par exemple. J'écrirai un article à ce sujet un jour j'espère.

Dans l'outil perfmon, on peut visualiser directement la trace ainsi générée :

Visualisation sous perfmon

 

Une fois la trace créée, il suffit de démarrer la trace en tapant tout simplement 'logman start High-CPU-Perf-Log'

Pour visualiser, il suffit de taper 'logman', pour la stopper 'logman stop High-CPU-Perf-Log'

Utilisation de logman 

 

Doc Microsoft : http://technet.microsoft.com/fr-fr/library/cc753820%28v=ws.10%29.aspx