SelectEtoile : Base de connaissance SGBD

Import Données Perfmon dans une table avec Powershell

1

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

Ajouter un Commentaire


Code de sécurité
Rafraîchir