Insertando datos de rendimiento en SQL Server, Parte III

ene

2018

Insertando datos de rendimiento en SQL Server, Parte III

Realmente no tenía pensado escribir este post, después de escribir la Parte I y la Parte II, creía que ya estaba bien así, pero cuando llegó a hora de desplegar la solución a los diferentes servidores SQL Server que necesito monitorizar, me di cuenta que necesitaba algo más para automatizar el proceso y no tener que ir a cada servidor haciendo click por las diferentes ventanas que puede ser bastante aburrido, verdad?

Antecedentes

Como he dicho, a la hora de desplegar la solucion propuesta en mis anteriores posts en multiples servidores, esta puede resultar bastante tediosa, especialmente si tenemos diferentes instancias de SQL Server corriendo en la misma máquina, ya que el nombre de los contadores depende del nombre de la instancia, con lo cuál habría que seleccionar manualmente todos esos contadores y no sería suficiente con tener una sola plantilla para crear el recopilador de datos.

Así que decidí hacer lo que más me gusta y me puse a escribir unas consultas que combinadas con un poco de powershell harán el trabajo.

La lógica es fácil, tenemos tres tablas donde vamos a almacenar los diferentes servidores SQL Server donde queremos crear los recopiladores de datos (en formato Servidor\Instancia), luego tenemos otras dos tablas, una para los contadores de Windows y otra para los contadores de SQL Server.

Para obtener la lista completa de contadores, podéis ejecutar el siguiente comando.

typeperf -q > c:\temp\all_server_counters.txt

Esto creará un archivo con la lista completa de contadores, es necesario ejecutarla en un servidor SQL Server con la instancia por defecto instalada para obtener también los contadores de SQL Server.

Luego debeis revisar la lista y elegir los contadores en los que estáis más interesados.

La consulta generará solamente una fila por máquina pero esta contendrá todos los contadores de Windows más los contadores SQL Server para cada instancia, así tendremos la foto completa en un solo fichero de salida y podremos correlacionar los datos mas fácilmente.

Después de eso, utilizo Logman para crear de forma remota los diferentes recopiladores de datos en cada servidor, así que asegúraos de tener suficientes privilegios administrativos para realizar la tarea.

Import-Module sqlps -DisableNameChecking
 
$cmssrv = "localhost\MSSQL2016";
$templatePath = "c:\temp\templates\"; 
$outputPath = "c:\Perfmon\"
 
$serverlist = invoke-sqlcmd -ServerInstance $cmssrv -Database "master" -query "
IF OBJECT_ID('tempdb..#SQLServers')         IS NOT NULL DROP TABLE #SQLServers
IF OBJECT_ID('tempdb..#ServerCounters')     IS NOT NULL DROP TABLE #ServerCounters
IF OBJECT_ID('tempdb..#SQLServerCounters')  IS NOT NULL DROP TABLE #SQLServerCounters
 
CREATE TABLE #SQLServers        (ID INT NOT NULL IDENTITY, instance_name SYSNAME)
CREATE TABLE #ServerCounters    (ID INT NOT NULL IDENTITY, counter_name SYSNAME)
CREATE TABLE #SQLServerCounters (ID INT NOT NULL IDENTITY, counter_name SYSNAME)
 
INSERT INTO #SQLServers
VALUES ('localhost')
, ('localhost\MSSQL2014')
, ('localhost\MSSQL2016')
, ('localhost\MSSQL2017')
 
INSERT INTO #ServerCounters ( counter_name )
VALUES ('\Memory\Available MBytes')
, ('\NUMA Node Memory(*)\Available MBytes')
, ('\Paging File(*)\% Usage')
, ('\PhysicalDisk(*)\Avg. Disk Queue Length')
, ('\PhysicalDisk(*)\Current Disk Queue Length')
, ('\PhysicalDisk(*)\Disk Reads/sec')
, ('\PhysicalDisk(*)\Disk Writes/sec')
, ('\PhysicalDisk(*)\Avg. Disk sec/Read')
, ('\PhysicalDisk(*)\Avg. Disk sec/Write')
, ('\PhysicalDisk(*)\Avg. Disk Read Queue Length')
, ('\PhysicalDisk(*)\Avg. Disk Write Queue Length')
, ('\System\Processor Queue Length')
, ('\Processor(*)\% Processor Time')
, ('\Network Interface(*)\Bytes Total/sec')
, ('\Network Interface(*)\Packets/sec')
, ('\Network Interface(*)\Packets Received/sec')
, ('\Network Interface(*)\Packets Sent/sec')
 
INSERT INTO #SQLServerCounters ( counter_name )
VALUES ('\SQLServer:Buffer Manager\Page life expectancy')
, ('\SQLServer:Buffer Node(*)\Page life expectancy')
, ('\SQLServer:General Statistics\User Connections')
, ('\SQLServer:Memory Manager\Memory Grants Pending')
, ('\SQLServer:SQL Statistics\Batch Requests/sec')
, ('\SQLServer:SQL Statistics\SQL Compilations/sec')
, ('\SQLServer:SQL Statistics\SQL Re-Compilations/sec')
 
SELECT DISTINCT 
        CASE 
            WHEN CHARINDEX('\', s.instance_name) > 0 
                THEN LEFT(s.instance_name, CHARINDEX('\', s.instance_name) - 1) 
            ELSE s.instance_name 
        END AS server_name
        , STUFF((SELECT CHAR(10) + counter_name FROM #ServerCounters AS c ORDER BY ID FOR XML PATH('')), 1,1,'') 
        + 
        (SELECT CHAR(10) + 
                    CASE WHEN CHARINDEX('\', s1.instance_name) = 0  
                        THEN c.counter_name 
                        ELSE REPLACE(c.counter_name, 'SQLServer', 'MSSQL$' + CONVERT(SYSNAME, RIGHT(s1.instance_name, LEN(s1.instance_name) - CHARINDEX('\', s1.instance_name))))
                    END
             FROM #SQLServerCounters AS c 
             CROSS APPLY #SQLServers AS s1
             ORDER BY s1.instance_name, c.ID 
             FOR XML PATH('')
        ) AS template
    FROM #SQLServers AS s
    ORDER BY server_name"
 
foreach ($servername in $serverlist) {
     
    $OutputExists = Get-Item -Path $templatePath -ErrorAction SilentlyContinue;
 
    if ($OutputExists -eq $null) {
        Write-Output "Creating directory $templatePath";
        New-Item -Path $templatePath -Type Directory;
    }
 
    $templateFile = "$($templatePath)\$($servername.server_name)_DBA_collector.txt";
    $servername.template | Out-File -FilePath $templateFile;
    $serverOutputPath = "$($outputPath)$($servername.server_name)";
 
    $OutputExists = Get-Item -Path $serverOutputPath -ErrorAction SilentlyContinue;
 
    if ($OutputExists -eq $null) {
        Write-Output "Creating directory $serverOutputPath";
        New-Item -Path $serverOutputPath -Type Directory;
    }
     
    try{
        Write-Output "Trying to create counter in $($servername.server_name)";
        logman create counter -s $servername.server_name -cf $templateFile -n "DBA_perfmonCollector" -f "csv" -si 15 -o "$($serverOutputPath)\$($servername.server_name)" -a -cnf "24:00:00" -v "nnnnnn";
        logman start -s $servername.server_name -n "DBA_perfmonCollector";
    }
    catch{
        Write-Output "Failed to create counter in $($servername.server_name)";
    }
}

Después de ejecutar con éxito este script de powershell, tendremos los distintos recopiladores de datos creados en cada servidor.

Yo recomendaría utilizar una carpeta compartida para centralizar todos los ficheros de salida en una misma ubicación y evitar que el disco c:\ se llene, cosa que puede ser un verdadero dolor de muelas.

Conclusión

Como ya he dicho antes, no me esperaba escribir esta publicación, pero sentí como que faltaba algo con solo las dos publicaciones anteriores.

Actualmente estoy trabajando en algunos informes para hacer que los datos sean un poco más útiles, porque la cantidad de información puede ser enorme y realmente puede frustrar el propósito de tenerla.

Espero que os hay gustado y estad atentos!

¡Gracias!