SelectEtoile : Base de connaissance SGBD

Posts Tagged 'perfmon'

Import Données Perfmon dans une table avec Powershell

Ce script fonctionne pour la version SQLServer 2008. Je n'ai pas tester pour d'autre version encore.

Il fonctionne pour des instances nommées. Mais peu de modifications sont necessaire pour l'adapter.

 

 

 

Param (
[string]$ServerName,
[string]$InstanceName
)




##### VARIABLES

$SQLServiceName = 'MSSQL$'+$InstanceName
$FullInstanceName = $ServerName+"\"+$InstanceName

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$ServerNameSQL = New-Object "Microsoft.SqlServer.Management.Smo.Server" $FullInstanceName
$ClusterState=0



##### FUNCTIONS 


function Test_PSSnapin {
$sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin100"}
if($sqlSnapin -eq $null)
{
Add-PSSnapin SqlServerCmdletSnapin100
}
}



function fn_imp-perfmon
{

#Build array of all counter that we want to collect
$counterPath = @()  

# get the version of the current instance
$version = $ServerNameSQL | SELECT Version
#$prefixVersion = $version.version.substring(0,$version.version.IndexOf('.'));
$prefixVersion = 10


if(($prefixVersion -eq 10)) # si SQLserver 2008/2008R2 en instance par défaut en cluster ou standalone
{
$counterType = 0;
}
elseif (($prefixVersion -eq 11) -and ($ClusterState -eq 0))# si SQLserver 2012 en instance par défaut mais pas en cluster
{
$counterType = 0;
}
elseif (($prefixVersion -eq 11) -and ($ClusterState -eq 1))# si SQLserver 2012 en instance par défaut mais  en cluster
{
$counterType = 1;
}
else
{
$counterType = 1;
}



# get the counter prefix on the instance inside PERFMON

if($counterType -eq 0)
{  
$counterPath +="\"+$SQLServiceName+":Cursor Manager by Type(_Total)\Cursor Requests/sec"
$counterPath +="\"+$SQLServiceName+":General Statistics\Processes Blocked"
$counterPath +="\"+$SQLServiceName+":General Statistics\Temp Tables Creation Rate"
$counterPath +="\"+$SQLServiceName+":General Statistics\Active Temp Tables"
$counterPath +="\"+$SQLServiceName+":General Statistics\Temp Tables For Destruction"
$counterPath +="\"+$SQLServiceName+":General Statistics\Logins/sec"
$counterPath +="\"+$SQLServiceName+":General Statistics\Logouts/sec"
$counterPath +="\"+$SQLServiceName+":General Statistics\User Connections"
$counterPath +="\"+$SQLServiceName+":General Statistics\Logical Connections"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Lock waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Log write waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Network IO waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Non-Page latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Page IO latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Page latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Wait for the worker"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Total Server Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Target Server Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Access Methods\Forwarded Records/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\FreeSpace Scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Full Scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Index Searches/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Mixed page allocations/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Page Splits/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Range scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Scan Point Revalidations/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Workfiles Created/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Worktables Created/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Worktables From Cache Ratio"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Buffer cache hit ratio"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Buffer cache hit ratio base"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Checkpoint pages/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Free pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Total pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Target pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Database pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Stolen pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Lazy writes/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page life expectancy"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page lookups/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page reads/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page writes/sec"
$counterPath +="\"+$SQLServiceName+":Latches\Latch Waits/sec"
$counterPath +="\"+$SQLServiceName+":Latches\Total Latch Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Latches\Average Latch Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Latches\Average Latch Wait Time Base"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Timeouts (timeout > 0)/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Requests/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Waits/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Number of Deadlocks/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Average Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Average Wait Time Base"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Memory Grants Pending"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Connection Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Optimizer Memory (KB)"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\Batch Requests/sec"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\SQL Compilations/sec"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\SQL Re-Compilations/sec"
$counterPath +="\"+$SQLServiceName+":Plan Cache(_Total)\Cache Hit Ratio"
$counterPath +="\"+$SQLServiceName+":Plan Cache(_Total)\Cache Hit Ratio Base"
$counterPath +="\"+$SQLServiceName+":Plan Cache(_Total)\Cache Pages"
$counterPath +="\"+$SQLServiceName+":Transactions\Free Space in tempdb (KB)"
$counterPath +="\"+$SQLServiceName+":Databases(_Total)\Transactions/sec"
$counterPath +="\"+$SQLServiceName+":Databases(_Total)\Log Flushes/sec"
$counterPath +="\"+$SQLServiceName+":Databases(tempdb)\Data File(s) Size (KB)"
$counterPath +="\"+$SQLServiceName+":Databases(tempdb)\Log File(s) Size (KB)"
$counterPath +="\"+$SQLServiceName+":Databases(tempdb)\Log File(s) Used Size (KB)"
}
else
{
#bug : inhibit $ inside the counter name
#fixed with Aaron Bertrand's post
#http://sqlblog.com/blogs/aaron_bertrand/archive/2011/05/03/a-little-powershell-syntax-tip-dealing-with-sql-server-named-instances.aspx

$counterPath +="\"+$SQLServiceName+":Cursor Manager by Type(_Total)\Cursor Requests/sec"
$counterPath +="\"+$SQLServiceName+":General Statistics\Processes Blocked"
$counterPath +="\"+$SQLServiceName+":General Statistics\Temp Tables Creation Rate"
$counterPath +="\"+$SQLServiceName+":General Statistics\Temp Tables For Destruction"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Lock waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Log write waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Network IO waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Non-Page latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Page IO latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Page latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Wait for the worker"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Total Server Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Target Server Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Timeouts (timeout > 0)/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Forwarded Records/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\FreeSpace Scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Full Scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Index Searches/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Mixed page allocations/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Page Splits/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Range scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Scan Point Revalidations/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Workfiles Created/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Worktables Created/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Worktables From Cache Ratio"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Buffer cache hit ratio"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Checkpoint pages/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Free pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Lazy writes/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page life expectancy"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page lookups/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page reads/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page writes/sec"
$counterPath +="\"+$SQLServiceName+":Latches\Latch Waits/sec"
$counterPath +="\"+$SQLServiceName+":Latches\Total Latch Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Requests/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Waits/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Number of Deadlocks/sec"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Memory Grants Pending"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\Batch Requests/sec"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\SQL Compilations/sec"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\SQL Re-Compilations/sec"
}


$counterPath +="\Memory\Cache Bytes"
$counterPath +="\Memory\Available MBytes"
$counterPath +="\Memory\Pages/sec"
$counterPath +="\Memory\Pages Input/sec"
$counterPath +="\Memory\System Cache Resident Bytes"
$counterPath +="\System\Processor Queue Length"
$counterPath +="\LogicalDisk(*)\Avg. Disk sec/Read"
$counterPath +="\LogicalDisk(*)\Avg. Disk sec/Write"
$counterPath +="\LogicalDisk(*)\Disk Reads/sec"
$counterPath +="\LogicalDisk(*)\Disk Writes/sec"
$counterPath +="\LogicalDisk(*)\Disk Bytes/sec"
$counterPath +="\Network Interface(*)\Output Queue Length"
$counterPath +="\Process(Idle)\% Processor Time"
$counterPath +="\Process(_Total)\% Processor Time"
$counterPath +="\Process(Idle)\% Privileged Time"
$counterPath +="\Process(_Total)\% Privileged Time"
$counterPath +="\System\Context Switches/sec"


#################
#Bug fixed : how get the process information of my current instance
# CPU Time by process
$instancePID =  get-wmiobject -class win32_service | where-object {($_.Name -like $SQLServiceName) } | SELECT ProcessID
$processName = Get-WmiObject Win32_PerfFormattedData_PerfProc_Process |  where-object{ $_.IDProcess -eq $instancePID.ProcessID} | SELECT name
$pn = $processName.Name
$counterPath +="\Process($pn)\% Processor Time"


# Collect all counters from my list $counterPath
$collectCounters=(Get-Counter  -ComputerName $ServerName -Counter $counterPath  | ForEach-Object { $_.CounterSamples});


# Fetch the result to put in database
ForEach($c in $collectCounters  | SELECT   Path,InstanceName,CookedValue) 
{
$Path =  $c.Path;
$InstanceNamePath =  $c.InstanceName;
$CookedValue =  $c.CookedValue;
$PathTab=$Path.split("\");
$ObjectName=$PathTab[4].replace("mssql`$"+$InstanceName.tolower()+":","");
$CounterName=$PathTab[5];

$verbosLog=(Invoke-Sqlcmd -ServerInstance $FullInstanceName -Database SQLMonitor -Query "INSERT INTO [dbo].[SQL_DISCOVER_PERFORMANCE_COUNTERS] SELECT getdate(), '$InstanceName', '$Path', '$CookedValue', '$InstanceNamePath', '$ObjectName', '$CounterName'");

#Verbos log
echo $verbosLog #| out-file $logFile -encoding "default" -append
}


}



function usage {
"Exemple : getPerfmon.ps1 -ServerName HOSTNAME -InstanceName INSTANCENAME"
exit 1
}




##### MAIN


if(!$ServerName){usage}
if(!$InstanceName){usage}

Test_PSSnapin

fn_imp-perfmon

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