SelectEtoile : Base de connaissance SGBD

Posts Tagged 'path'

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