{"id":399,"date":"2017-03-02T15:31:50","date_gmt":"2017-03-02T15:31:50","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=399"},"modified":"2017-11-21T20:13:00","modified_gmt":"2017-11-21T20:13:00","slug":"benchmarking-storage-using-diskspd-exe","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2017\/03\/02\/benchmarking-storage-using-diskspd-exe\/","title":{"rendered":"Analizando sistemas de almacenamiento con Diskspd.exe"},"content":{"rendered":"

Una de las cosas m\u00e1s importantes cuando algo se tuerce, es tener una idea de cu\u00e1l es el rendimiento normal que podemos esperar de nuestro entorno. Dej\u00e1dme mostraros como f\u00e1cilmente podemos testar nuestro sistema de almacenamiento y conseguir resultados significativos que podermos guardar para que sirvan de futura referencia La semana pasada estuve probando un nuevo almacenamiento en el trabajo para ver que tal rinde y tuve que usar una herramienta [con suerte] bien conocida proporcionada por Microsoft llamada diskspd.exe<\/a><\/p>\n

Diskspd.exe es ideal para generar actividad de lectura y escritura en un volumen (independientemente de que sea un disco local o proveniente de una SAN como mi caso) usando diferentes par\u00e1metros para manipularlo de varias maneras, tratando de replicar las m\u00faltiples formas en que SQL Server realiza IO operaciones.<\/p>\n

** Descargo de responsabilidad: ** el almacenamiento SAN es m\u00e1s complejo de probar debido a la forma m\u00e1s sofisticada de servir los datos, que pueden incluir el almacenamiento en cach\u00e9 y el almacenamiento en niveles autom\u00e1tico. Por favor, consulte este podcast<\/a> para m\u00e1s detalles<\/span><\/em><\/strong><\/p>\n

En esta publicaci\u00f3n no voy a entrar en muchos detalles sobre la herramienta en s\u00ed, sino sobre c\u00f3mo procesar la salida generada para sentar la base o la evaluar el rendimiento de nuestro subsistema de almacenamiento.<\/p>\n

 
\nAntecedentes<\/strong><\/p>\n

Hay muchas operaciones que requieren que SQL Server interactue con el subsistema de almacenamiento subyacente y tambi\u00e9n un mont\u00f3n de conceptos err\u00f3neos a su alrededor. En cuanto a la mayor\u00eda de las preguntas relacionadas con SQL Server, no existe una verdad absoluta que se aplique ciegamente a cada caso.<\/p>\n

Recomiendo encarecidamente que tom\u00e9is un minuto para leer un art\u00edculo escrito por MCM y MVP Argenis Fernandez (b<\/a>|t<\/a>) llamado What is SQL Server\u2019s IO Block Size?<\/a> para ver lo que acabo de decir, porque eso os puede ayudar a tener una mejor comprensi\u00f3n del tipo de pruebas que se deb\u00e9is realizar para analizar el rendimiento de vuestro almacenamiento.<\/p>\n

El uso de tama\u00f1os de bloques reales de SQL Server puede darnos una buena idea de en qu\u00e9 puede ser \u00fatil nuestro almacenamiento. Por ejemplo, si vemos un buen rendimiento de lectura pero no tan bueno al escribir bloques de 60k, podemos adivinar que los archivos de base de datos con mayor actividad de lectura son buenos candidatos, pero el archivo de registro de transacciones no lo es tanto.<\/p>\n

 
\nUsando diskspd<\/strong><\/p>\n

Hay muchos art\u00edculos y publicaciones que explican c\u00f3mo usar esta herramienta, as\u00ed que prefiero se\u00f1alarlos y solo poner un ejemplo para mostraros c\u00f3mo procesar la salida.<\/p>\n

La documentaci\u00f3n completa est\u00e1 aqu\u00ed<\/a> y algunos art\u00edculos de Jeremiah Peschka<\/a> and Glenn Berry<\/a> te ayudar\u00e1n a comenzar a trabajar.<\/p>\n

En mi ejemplo, estar\u00eda probando la idoneidad de un disco para almacenar mis copias de seguridad. Entonces, lo que ejecutar\u00eda se parece a<\/p>\n

\r\nF:\\Diskspd\\amd64fre>diskspd.exe -b1M -d60 -o32 -h -L -t8 -W -w100 -Rxml F:\\temp\\test.dat > F:\\20170302_Wite_1MB_8th_1file.xml\r\n<\/pre>\n
Vea c\u00f3mo eleg\u00ed el par\u00e1metro para que coincida con lo que creo que ser\u00eda la actividad m\u00e1s similar a SQL Server, en este caso el tama\u00f1o de bloque es de 1 MB, solo escritura, 8 hilos y 1 archivo de destino. Pod\u00e9is ajustarlo a vuestras necesidades, pero como ejemplo, supongo que esto es suficiente.<\/p>\n
 
\nProcesamiento de la salida de diskspd<\/strong><\/p>\n
Otra gran cosa de diskspd es que puedes obtener la salida en diferentes formatos, pero para fines de procesamiento prefiero el formato XML, porque al fin y al cabo, a todos nos encanta escribir consultas para XML, especialmente en SQL \ud83d\ude42<\/p>\n
Y ese es el objetivo de esta publicaci\u00f3n, mostraros c\u00f3mo analizar f\u00e1cilmente el resultado XML de una manera que podamos guardar en una base de datos para futura referencia. As\u00ed que vamos a remangarnos y ponernos a trabajar. Me refiero a m\u00ed, no a vosotros que solo ten\u00e9is que copiar y pegar esto para hacerlo realidad.<\/p>\n
 
\nEl truco<\/strong><\/p>\n
Como antes de DBA he sido desarrollador muchos a\u00f1os, me encanta escribir c\u00f3digo, as\u00ed que esto es lo que surgi\u00f3 despu\u00e9s de aburrirme de mirar algunos archivos de salida de txt. <\/p>\n
\r\nUSE master;\r\nGO\r\nIF OBJECT_ID('dbo.sqlg_ParseDiskskpXml') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE dbo.sqlg_ParseDiskskpXml AS RETURN';\r\nGO\r\nSET ANSI_NULLS ON;\r\nGO\r\nSET QUOTED_IDENTIFIER ON;\r\nGO\r\n-- =============================================\r\n-- Author:\t\tRaul Gonzalez @SQLDoubleG\r\n-- Create date: 26\/02\/2017\r\n-- Description:\tProcess XML output file of diskspd.exe\r\n--\r\n-- Parameters:\r\n--              - @filePath\t\t-> Full path of the file that contains the XML output of diskspd.exe\r\n--              - @simplified   -> will return just one row with general info and IO totals, including IOps, throughput and latency for Reads and Writes.\r\n--\r\n-- Log:\r\n--\t\t\t\t26\/02\/2017\tRAG\t- Created\r\n--\r\n-- Copyright:   (C) 2017 Raul Gonzalez (@SQLDoubleG https:\/\/sqldoubleg.live-website.com)\r\n--\r\n--              THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF \r\n--              ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED \r\n--              TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n--              PARTICULAR PURPOSE.\r\n--\r\n--              THE AUTHOR SHALL NOT BE LIABLE TO YOU OR ANY THIRD PARTY FOR ANY INDIRECT, \r\n--              SPECIAL, INCIDENTAL, PUNITIVE, COVER, OR CONSEQUENTIAL DAMAGES OF ANY KIND\r\n--\r\n--              YOU MAY ALTER THIS CODE FOR YOUR OWN *NON-COMMERCIAL* PURPOSES. YOU MAY\r\n--              REPUBLISH ALTERED CODE AS LONG AS YOU INCLUDE THIS COPYRIGHT AND GIVE DUE CREDIT. \r\n--\r\n-- =============================================\r\nALTER PROCEDURE dbo.sqlg_ParseDiskskpXml\r\n\t@filePath\t\tNVARCHAR(512)\r\n\t, @simplified\tBIT = 1\r\n\r\nAS\r\nBEGIN\r\n\r\nSET NOCOUNT ON;\r\n\r\nDECLARE @xml XML; \r\nDECLARE @sql NVARCHAR(1000) = N'SET @xml = (SELECT CONVERT(XML, BulkColumn) FROM OPENROWSET(BULK ''' + @filePath + ''', SINGLE_BLOB) AS x);';\r\n\r\nEXECUTE sys.sp_executesql @sql, N'@xml XML OUTPUT', @xml = @xml OUTPUT;\r\n\r\nIF OBJECT_ID('tempdb..#SystemInfo')\t\t\tIS NOT NULL DROP TABLE #SystemInfo;\r\nIF OBJECT_ID('tempdb..#RunningParameters')\tIS NOT NULL DROP TABLE #RunningParameters;\r\nIF OBJECT_ID('tempdb..#RunningValues')\t\tIS NOT NULL DROP TABLE #RunningValues;\r\nIF OBJECT_ID('tempdb..#TimeSpan')\t\t\tIS NOT NULL DROP TABLE #TimeSpan;\r\nIF OBJECT_ID('tempdb..#ProcessorUsage')\t\tIS NOT NULL DROP TABLE #ProcessorUsage;\r\n--IF OBJECT_ID('tempdb..#AverageLatency')\t\tIS NOT NULL DROP TABLE #AverageLatency\r\nIF OBJECT_ID('tempdb..#LatencyPercentiles')\tIS NOT NULL DROP TABLE #LatencyPercentiles;\r\nIF OBJECT_ID('tempdb..#ResultsPerThread')\tIS NOT NULL DROP TABLE #ResultsPerThread;\r\n\r\n--===========================================================================================\r\n-- Info contained in <Results><System>\r\n--===========================================================================================\r\nSELECT @xml.value('(\/Results\/System\/ComputerName)[1]', 'VARCHAR(128)') AS computerName\r\n\t\t, @xml.value('(\/Results\/System\/RunTime)[1]', 'VARCHAR(30)') AS RunTime\r\n\t\t, @xml.value('(\/Results\/System\/ProcessorTopology\/Group\/@ActiveProcessors)[1]', 'TINYINT') AS ActiveProcessors\r\nINTO #SystemInfo;\r\n--===========================================================================================\r\n-- Info contained in <Results><Profile><TimeSpans><TimeSpan>\r\n--===========================================================================================\r\nSELECT t.c.value('(.\/TimeSpan\/CompletionRoutines)[1]', 'VARCHAR(5)') AS CompletionRoutines\r\n\t\t, t.c.value('(.\/TimeSpan\/MeasureLatency)[1]', 'VARCHAR(5)') AS MeasureLatency\r\n\t\t, t.c.value('(.\/TimeSpan\/CalculateIopsStdDev)[1]', 'VARCHAR(5)') AS CalculateIopsStdDev\r\n\t\t, t.c.value('(.\/TimeSpan\/DisableAffinity)[1]', 'VARCHAR(5)') AS DisableAffinity\r\n\t\t, t.c.value('(.\/TimeSpan\/Duration)[1]', 'INT') AS Duration\r\n\t\t, t.c.value('(.\/TimeSpan\/Warmup)[1]', 'INT') AS Warmup\r\n\t\t, t.c.value('(.\/TimeSpan\/Cooldown)[1]', 'INT') AS Cooldown\r\n\t\t, t.c.value('(.\/TimeSpan\/ThreadCount)[1]', 'TINYINT') AS ThreadCount\r\n\t\t, t.c.value('(.\/TimeSpan\/IoBucketDuration)[1]', 'INT') AS IoBucketDuration\r\n\t\t, t.c.value('(.\/TimeSpan\/RandSeed)[1]', 'INT') AS RandSeed\r\n\t\t--,  t.c.query('.') AS CompletionRoutines\r\nINTO #RunningParameters\r\nFROM @xml.nodes('\/Results\/Profile\/TimeSpans') AS t(c);\r\n--===========================================================================================\r\n-- Info contained for each <Target> in <Results><Profile><TimeSpans><TimeSpan><Targets>\r\n--===========================================================================================\r\nSELECT t2.d.value('(.\/Path)[1]', 'VARCHAR(512)') AS Path\r\n\t\t, t2.d.value('(.\/BlockSize)[1]', 'VARCHAR(512)') AS BlockSize\r\n\t\t, t2.d.value('(.\/BaseFileOffset)[1]', 'VARCHAR(512)') AS BaseFileOffset\r\n\t\t, t2.d.value('(.\/SequentialScan)[1]', 'VARCHAR(512)') AS SequentialScan\r\n\t\t, t2.d.value('(.\/RandomAccess)[1]', 'VARCHAR(512)') AS RandomAccess\r\n\t\t, t2.d.value('(.\/TemporaryFile)[1]', 'VARCHAR(512)') AS TemporaryFile\r\n\t\t, t2.d.value('(.\/UseLargePages)[1]', 'VARCHAR(512)') AS UseLargePages\r\n\t\t, t2.d.value('(.\/DisableOSCache)[1]', 'VARCHAR(512)') AS DisableOSCache\r\n\t\t, t2.d.value('(.\/WriteThrough)[1]', 'VARCHAR(512)') AS WriteThrough\r\n\t\t--, d.value('(.\/WriteBufferContent>'\r\n\t\t--, d.value('(.\/Pattern>sequential<\/Pattern>'\r\n\t\t--, d.value('(.\/\/WriteBufferContent>'\r\n\t\t, t2.d.value('(.\/ParallelAsyncIO)[1]', 'VARCHAR(512)') AS ParallelAsyncIO\r\n\t\t, t2.d.value('(.\/StrideSize)[1]', 'VARCHAR(512)') AS StrideSize\r\n\t\t, t2.d.value('(.\/InterlockedSequential)[1]', 'VARCHAR(512)') AS InterlockedSequential\r\n\t\t, t2.d.value('(.\/ThreadStride)[1]', 'VARCHAR(512)') AS ThreadStride\r\n\t\t, t2.d.value('(.\/MaxFileSize)[1]', 'VARCHAR(512)') AS MaxFileSize\r\n\t\t, t2.d.value('(.\/RequestCount)[1]', 'VARCHAR(512)') AS RequestCount\r\n\t\t, t2.d.value('(.\/WriteRatio)[1]', 'VARCHAR(512)') AS WriteRatio\r\n\t\t, t2.d.value('(.\/Throughput)[1]', 'VARCHAR(512)') AS Throughput\r\n\t\t, t2.d.value('(.\/ThreadsPerFile)[1]', 'VARCHAR(512)') AS ThreadsPerFile\r\n\t\t, t2.d.value('(.\/IOPriority)[1]', 'VARCHAR(512)') AS IOPriority\r\nINTO #RunningValues\r\nFROM @xml.nodes('\/Results\/Profile\/TimeSpans\/TimeSpan\/Targets\/Target') AS t2(d);\r\n--===========================================================================================\r\n-- Info contained in <Results><TimeSpan>\r\n--===========================================================================================\r\nSELECT t.c.value('(.\/TestTimeSeconds)[1]', 'DECIMAL(10,2)') AS TestTimeSeconds\r\n\t\t, t.c.value('(.\/ThreadCount)[1]', 'SMALLINT') AS ThreadCount\r\n\t\t, t.c.value('(.\/ProcCount)[1]', 'SMALLINT') AS ProcCount\r\nINTO #TimeSpan\r\nFROM @xml.nodes('\/Results\/TimeSpan') AS t(c);\r\n--===========================================================================================\r\n-- Info contained for each <CPU> in <Results><TimeSpan><CpuUtilization>\r\n--===========================================================================================\r\nSELECT t.c.value('(.\/Id)[1]', 'TINYINT') AS Id\r\n\t\t, t.c.value('(.\/UsagePercent)[1]', 'DECIMAL(5,2)') AS UsagePercent\r\n\t\t, t.c.value('(.\/UserPercent)[1]', 'DECIMAL(5,2)') AS UserPercent\r\n\t\t, t.c.value('(.\/KernelPercent)[1]', 'DECIMAL(5,2)') AS KernelPercent\r\n\t\t, t.c.value('(.\/IdlePercent)[1]', 'DECIMAL(5,2)') AS IdlePercent\r\nINTO #ProcessorUsage\r\nFROM @xml.nodes('\/Results\/TimeSpan\/CpuUtilization\/CPU') AS t(c);\r\n--===========================================================================================\r\n-- Info contained in <Results><TimeSpan><Latency>, this can be calculated from the details too, so skip it\r\n--===========================================================================================\r\n--SELECT t.c.value('(.\/AverageReadMilliseconds)[1]', 'DECIMAL(10,2)') AS AverageReadMilliseconds\r\n--\t\t, t.c.value('(.\/ReadLatencyStdev)[1]', 'DECIMAL(10,2)') AS ReadLatencyStdev\r\n--\t\t, t.c.value('(.\/AverageWriteMilliseconds)[1]', 'DECIMAL(10,2)') AS AverageWriteMilliseconds\r\n--\t\t, t.c.value('(.\/WriteLatencyStdev)[1]', 'DECIMAL(10,2)') AS WriteLatencyStdev\r\n--\t\t, t.c.value('(.\/AverageTotalMilliseconds)[1]', 'DECIMAL(10,2)') AS AverageTotalMilliseconds\r\n--\t\t, t.c.value('(.\/LatencyStdev)[1]', 'DECIMAL(10,2)') AS LatencyStdev\r\n--INTO #AverageLatency\r\n--FROM @xml.nodes('\/Results\/TimeSpan\/Latency') AS t(c)\r\n--===========================================================================================\r\n-- Info contained for each <Bucket> in <Results><TimeSpan><Latency>\r\n--===========================================================================================\r\nSELECT t.c.value('(.\/Percentile)[1]', 'DECIMAL(15,7)') AS Percentile\r\n\t\t, t.c.value('(.\/ReadMilliseconds)[1]', 'DECIMAL(10,4)') AS ReadMilliseconds\r\n\t\t, t.c.value('(.\/WriteMilliseconds)[1]', 'DECIMAL(10,4)') AS WriteMilliseconds\r\n\t\t, t.c.value('(.\/TotalMilliseconds)[1]', 'DECIMAL(10,4)') AS TotalMilliseconds\r\nINTO #LatencyPercentiles\r\nFROM @xml.nodes('\/Results\/TimeSpan\/Latency\/Bucket') AS t(c);\r\n--===========================================================================================\r\n-- Info contained for each <Thread> in <Results><TimeSpan><Latency>\r\n--===========================================================================================\r\nSELECT t.c.value('(.\/Id)[1]', 'INT') AS ThreadId\r\n\t\t, t.c.value('(.\/Target\/Path)[1]', 'VARCHAR(512)') AS Path\r\n\t\t, t.c.value('(.\/Target\/BytesCount)[1]', 'BIGINT') AS BytesCount\r\n\t\t, t.c.value('(.\/Target\/FileSize)[1]', 'BIGINT') AS FileSize\r\n\t\t, t.c.value('(.\/Target\/IOCount)[1]', 'BIGINT') AS IOCount\r\n\t\t, t.c.value('(.\/Target\/ReadBytes)[1]', 'BIGINT') AS ReadBytes\r\n\t\t, t.c.value('(.\/Target\/ReadCount)[1]', 'BIGINT') AS ReadCount\r\n\t\t, t.c.value('(.\/Target\/WriteBytes)[1]', 'BIGINT') AS WriteBytes\r\n\t\t, t.c.value('(.\/Target\/WriteCount)[1]', 'BIGINT') AS WriteCount\t\t\r\n\t\t, t.c.value('(.\/Target\/AverageReadLatencyMilliseconds)[1]', 'DECIMAL(10,3)') AS AverageReadLatencyMilliseconds\r\n\t\t, t.c.value('(.\/Target\/ReadLatencyStdev)[1]', 'DECIMAL(10,3)') AS ReadLatencyStdev\r\n\t\t, t.c.value('(.\/Target\/AverageWriteLatencyMilliseconds)[1]', 'DECIMAL(10,3)') AS AverageWriteLatencyMilliseconds\r\n\t\t, t.c.value('(.\/Target\/WriteLatencyStdev)[1]', 'DECIMAL(10,3)') AS WriteLatencyStdev\r\n\t\t, t.c.value('(.\/Target\/AverageLatencyMilliseconds)[1]', 'DECIMAL(10,3)') AS AverageLatencyMilliseconds\r\n\t\t, t.c.value('(.\/Target\/LatencyStdev)[1]', 'DECIMAL(10,3)') AS LatencyStdev\r\nINTO #ResultsPerThread\r\nFROM @xml.nodes('\/Results\/TimeSpan\/Thread') AS t(c);\r\n\r\n\r\n--===========================================================================================\r\n-- Return useful Information\r\n--===========================================================================================\r\nIF @simplified = 1 BEGIN\r\n\r\n\tSELECT  si.computerName\r\n\t\t\t, si.RunTime\r\n\t\t\t, ts.TestTimeSeconds\r\n\t\t\t, ts.ThreadCount\r\n\t\t\t, COUNT(DISTINCT th.Path) AS FileCount\r\n\t\t\t, STUFF(tf.path, 1, 2, '') AS TargetFiles\r\n\t\t\t, rv.ThreadsPerFile\r\n\t\t\t, rv.BlockSize\r\n\t\t\t--th.FileSize \/ 1024 \/ 1024 AS [FileSize MB],\r\n\t\t\t, CONVERT(VARCHAR(3), ( 100 - rv.WriteRatio )) + '\/' + CONVERT(VARCHAR(3), rv.WriteRatio) AS [Read\/Write Ratio]\r\n\t\t\t-- Total Read + Writes\r\n\t\t\t, SUM(th.BytesCount) AS [Total bytes]\r\n\t\t\t, SUM(th.IOCount) AS [Total I\/Os]\r\n\t\t\t, CEILING(SUM(th.IOCount) \/ ts.TestTimeSeconds) AS [Total IOps]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(SUM(th.BytesCount) \/ 1024. \/ 1024 \/ ts.TestTimeSeconds, 2)) AS [Total MB\/s]\r\n\t\t\t-- Reads\r\n\t\t\t, SUM(th.ReadBytes) AS [Read bytes]\r\n\t\t\t, SUM(th.ReadCount) AS [Read I\/Os]\r\n\t\t\t, CEILING(SUM(th.ReadCount) \/ ts.TestTimeSeconds) AS [Read IOps]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(SUM(th.ReadBytes) \/ 1024. \/ 1024 \/ ts.TestTimeSeconds, 2)) AS [Read MB\/s]\r\n\t\t\t-- Writes\r\n\t\t\t, SUM(th.WriteBytes) AS [Write bytes]\r\n\t\t\t, SUM(th.WriteCount) AS [Write I\/Os]\r\n\t\t\t, CEILING(SUM(th.WriteCount) \/ ts.TestTimeSeconds) AS [Write IOps]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(SUM(th.WriteBytes) \/ 1024. \/ 1024 \/ ts.TestTimeSeconds, 2)) AS [Write MB\/s]\r\n\t\t\t-- Latency Total\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.AverageLatencyMilliseconds, 0)), 2)) AS [AverageLatencyMilliseconds]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.LatencyStdev, 0)), 2)) AS [LatencyStdev]\r\n\t\t\t-- Latency Read\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.AverageReadLatencyMilliseconds, 0)), 2)) AS [AverageReadLatencyMilliseconds]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.ReadLatencyStdev, 0)), 2)) AS [ReadLatencyStdev]\r\n\t\t\t-- Latency Write\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.AverageWriteLatencyMilliseconds, 0)), 2)) AS [AverageWriteLatencyMilliseconds]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.WriteLatencyStdev, 0)), 2)) AS [WriteLatencyStdev]\r\n\t\tFROM #ResultsPerThread AS th\r\n\t\t\tCROSS JOIN #TimeSpan AS ts\r\n\t\t\tCROSS JOIN ( SELECT TOP 1 * FROM   #RunningValues) AS rv\r\n\t\t\tCROSS JOIN #SystemInfo AS si\r\n\t\t\tCROSS JOIN ( SELECT DISTINCT ', ' + Path + ' (' + CONVERT(VARCHAR(10), FileSize \/ 1024\/ 1024) + 'MB)' AS [text()]\r\n\t\t\t\t\t\t\tFROM #ResultsPerThread FOR XML PATH('')) AS tf ( [path] )\r\n\t\tGROUP BY si.computerName\r\n\t\t\t, si.RunTime\r\n\t\t\t, ts.TestTimeSeconds\r\n\t\t\t, ts.ThreadCount\r\n\t\t\t, rv.ThreadsPerFile\r\n\t\t\t, rv.BlockSize\r\n\t\t\t, rv.WriteRatio\r\n\t\t\t, th.FileSize\r\n\t\t\t, tf.[path];\r\nEND; ELSE BEGIN\r\n\t--===========================================================================================\r\n\t-- Input parameters\r\n\t--===========================================================================================\t\r\n    SELECT  si.computerName AS [computer name]\r\n\t\t\t, si.RunTime AS [run time]\r\n\t\t\t, p.Duration AS [duration]\r\n\t\t\t, p.Warmup AS [warm up time]\r\n\t\t\t, p.Cooldown AS [cood down time]\r\n\t\t\t, p.RandSeed AS [random seed]\r\n\t\t\t, si.ActiveProcessors\r\n\t\tFROM #RunningParameters AS p\r\n\t\t\tCROSS JOIN #SystemInfo AS si;\r\n\t\r\n\t--===========================================================================================\r\n\t-- Time span\r\n\t--===========================================================================================\r\n    SELECT  rv.Path\r\n\t\t\t, rv.BlockSize\r\n\t\t\t--rv.WriteRatio ,\r\n\t\t\t, CONVERT(VARCHAR(3), ( 100 - rv.WriteRatio )) + '\/' + CONVERT(VARCHAR(3), rv.WriteRatio) AS [Read\/Write Ratio]\r\n\t\t\t, rv.ThreadsPerFile\r\n\t\t\t, rv.IOPriority\r\n\t\t\t, rv.BaseFileOffset\r\n\t\t\t, rv.SequentialScan\r\n\t\t\t, rv.RandomAccess\r\n\t\t\t, rv.TemporaryFile\r\n\t\t\t, rv.UseLargePages\r\n\t\t\t, rv.DisableOSCache\r\n\t\t\t, rv.WriteThrough\r\n\t\t\t, rv.ParallelAsyncIO\r\n\t\t\t, rv.StrideSize\r\n\t\t\t, rv.InterlockedSequential\r\n\t\t\t, rv.ThreadStride\r\n\t\t\t, rv.MaxFileSize\r\n\t\t\t, rv.RequestCount\r\n\t\t\t, rv.Throughput \r\n\t\tFROM #RunningValues AS rv;\r\n\t--===========================================================================================\r\n\t-- Processor\r\n\t--===========================================================================================\r\n    SELECT  Id AS CPU\r\n\t\t\t, UsagePercent\r\n\t\t\t, UserPercent\r\n\t\t\t, KernelPercent\r\n\t\t\t, IdlePercent\r\n\t\tFROM #ProcessorUsage\r\n\t--===========================================================================================\r\n\t-- Latency\r\n\t--===========================================================================================\r\n\t;WITH cte AS(\r\n    SELECT  ISNULL(CONVERT(VARCHAR(5), th.ThreadId), 'Total') AS ThreadId\r\n\t\t\t, th.Path + ' (' + CONVERT(VARCHAR(10), th.FileSize \/ 1024 \/ 1024)\r\n\t\t\t+ 'MB)' AS [file]\r\n\t\t\t-- Total Read + Writes\r\n\t\t\t, ( th.BytesCount ) AS [Total bytes]\r\n\t\t\t, ( th.IOCount ) AS [Total I\/Os]\r\n\t\t\t, CEILING(( th.IOCount ) \/ ts.TestTimeSeconds) AS [Total IOps]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(( th.BytesCount ) \/ 1024. \/ 1024 \/ ts.TestTimeSeconds, 2)) AS [Total MB\/s]\r\n\t\t\t-- Reads\r\n\t\t\t, ( th.ReadBytes ) AS [Read bytes]\r\n\t\t\t, ( th.ReadCount ) AS [Read I\/Os]\r\n\t\t\t, CEILING(( th.ReadCount ) \/ ts.TestTimeSeconds) AS [Read IOps]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(( th.ReadBytes ) \/ 1024. \/ 1024 \/ ts.TestTimeSeconds, 2)) AS [Read MB\/s]\r\n\t\t\t-- Writes\r\n\t\t\t, ( th.WriteBytes ) AS [Write bytes]\r\n\t\t\t, ( th.WriteCount ) AS [Write I\/Os]\r\n\t\t\t, CEILING(( th.WriteCount ) \/ ts.TestTimeSeconds) AS [Write IOps]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(( th.WriteBytes ) \/ 1024. \/ 1024 \/ ts.TestTimeSeconds, 2)) AS [Write MB\/s]\r\n\t\t\t-- Latency Total\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(( ISNULL(th.AverageLatencyMilliseconds, 0) ), 2)) AS [AverageLatencyMilliseconds]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(( ISNULL(th.LatencyStdev, 0) ), 2)) AS [LatencyStdev]\r\n\t\t\t-- Latency Read\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(( ISNULL(th.AverageReadLatencyMilliseconds, 0) ), 2)) AS [AverageReadLatencyMilliseconds]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(( ISNULL(th.ReadLatencyStdev, 0) ), 2)) AS [ReadLatencyStdev]\r\n\t\t\t-- Latency Write\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(( ISNULL(th.AverageWriteLatencyMilliseconds, 0) ), 2)) AS [AverageWriteLatencyMilliseconds]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(( ISNULL(th.WriteLatencyStdev, 0) ), 2)) AS [WriteLatencyStdev]\r\n\t\tFROM #ResultsPerThread AS th\r\n\t\t\tCROSS JOIN #TimeSpan AS ts\r\n\t\t\tCROSS JOIN (SELECT TOP 1 * FROM #RunningValues) AS rv\r\n\t\t\tCROSS JOIN #SystemInfo AS si\r\n\tUNION\t\r\n\tSELECT \r\n\t\t\t'Total'\r\n\t\t\t, '-'\r\n\t\t\t-- Total Read + Writes\r\n\t\t\t, SUM(th.BytesCount) AS [Total bytes]\r\n\t\t\t, SUM(th.IOCount) AS [Total I\/Os]\r\n\t\t\t, CEILING(SUM(th.IOCount) \/ ts.TestTimeSeconds) AS [Total IOps]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(SUM(th.BytesCount) \/ 1024. \/ 1024 \/ ts.TestTimeSeconds, 2)) AS [Total MB\/s]\r\n\t\t\t-- Reads\r\n\t\t\t, SUM(th.ReadBytes) AS [Read bytes]\r\n\t\t\t, SUM(th.ReadCount) AS [Read I\/Os]\r\n\t\t\t, CEILING(SUM(th.ReadCount) \/ ts.TestTimeSeconds) AS [Read IOps]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(SUM(th.ReadBytes) \/ 1024. \/ 1024 \/ ts.TestTimeSeconds, 2)) AS [Read MB\/s]\r\n\t\t\t-- Writes\r\n\t\t\t, SUM(th.WriteBytes) AS [Write bytes]\r\n\t\t\t, SUM(th.WriteCount) AS [Write I\/Os]\r\n\t\t\t, CEILING(SUM(th.WriteCount) \/ ts.TestTimeSeconds) AS [Write IOps]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(SUM(th.WriteBytes) \/ 1024. \/ 1024 \/ ts.TestTimeSeconds, 2)) AS [Write MB\/s]\r\n\t\t\t-- Latency Total\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.AverageLatencyMilliseconds, 0)), 2)) AS [AverageLatencyMilliseconds]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.LatencyStdev, 0)), 2)) AS [LatencyStdev]\r\n\t\t\t-- Latency Read\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.AverageReadLatencyMilliseconds, 0)), 2)) AS [AverageReadLatencyMilliseconds]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.ReadLatencyStdev, 0)), 2)) AS [ReadLatencyStdev]\r\n\t\t\t-- Latency Write\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.AverageWriteLatencyMilliseconds, 0)), 2)) AS [AverageWriteLatencyMilliseconds]\r\n\t\t\t, CONVERT(DECIMAL(15, 2), ROUND(AVG(ISNULL(th.WriteLatencyStdev, 0)), 2)) AS [WriteLatencyStdev]\r\n\t\tFROM #ResultsPerThread AS th\r\n\t\t\tCROSS JOIN #TimeSpan AS ts\r\n\t\t\tCROSS JOIN (SELECT TOP 1 * FROM #RunningValues) AS rv\r\n\t\t\tCROSS JOIN #SystemInfo AS si\r\n\t\tGROUP BY ts.TestTimeSeconds\r\n\t)\r\n\r\n\tSELECT * FROM cte\r\n\t\tORDER BY CASE WHEN ISNUMERIC(ThreadId) = 0 THEN 999 ELSE CONVERT(INT, ThreadId) END ASC;\r\n\t--===========================================================================================\r\n\t-- Percentiles\r\n\t--===========================================================================================\r\n\tSELECT CASE WHEN Percentile = 0\t\t\t\tTHEN 'min' \r\n\t\t\t\tWHEN Percentile = 100\t\t\tTHEN 'max'\r\n\t\t\t\tWHEN Percentile = 100\t\t\tTHEN 'max'\r\n\t\t\t\tWHEN Percentile % 1 = .9\t\tTHEN '3-nines'\r\n\t\t\t\tWHEN Percentile % 1 = .99\t\tTHEN '4-nines'\r\n\t\t\t\tWHEN Percentile % 1 = .999\t\tTHEN '5-nines'\r\n\t\t\t\tWHEN Percentile % 1 = .9999\t\tTHEN '6-nines'\r\n\t\t\t\tWHEN Percentile % 1 = .99999\tTHEN '7-nines'\r\n\t\t\t\tWHEN Percentile % 1 = .999999\tTHEN '8-nines'\r\n\t\t\t\tWHEN Percentile % 1 = .9999999\tTHEN '9-nines'\r\n\t\t\t\tELSE CONVERT(VARCHAR(10), CONVERT(INT, Percentile)) + 'th'\r\n\t\t\tEND AS [%-ile]\r\n\t\t\t, ISNULL(CONVERT(VARCHAR(10), ReadMilliseconds), 'N\/A') AS [Read (ms)]\r\n\t\t\t, ISNULL(CONVERT(VARCHAR(10), WriteMilliseconds), 'N\/A') AS [Write (ms)]\r\n\t\t\t, TotalMilliseconds AS [Total (ms)]\r\n\t\tFROM #LatencyPercentiles\r\n\t\tWHERE Percentile IN (0, 25, 50, 75, 90, 95)\r\n\t\t\tOR Percentile >= 99;\r\nEND; \r\n\r\nEND;\r\n\r\nGO\r\n<\/pre>\n
He decidido devolver datos de dos formas diferentes, uno muy simple con datos agregados y otro m\u00e1s detallado con la mayor\u00eda de los datos que encontramos en la versi\u00f3n de texto del resultado.<\/p>\n
La versi\u00f3n simplificada, que recomiendo almacenar en una tabla para el futuro, se ver\u00e1 as\u00ed.<\/p>\n
\"\"<\/a><\/p>\n
 
\nVed c\u00f3mo el rendimiento no es tan malo, en este caso estoy interesado en el rendimiento de MB\/s me dir\u00e1 eso. Dado que ejecuto la prueba solo para escrituras, el total coincidir\u00e1 con las escrituras, de lo contrario ser\u00e1 la combinaci\u00f3n de lecturas y escrituras.<\/p>\n
La versi\u00f3n extendida se ver\u00e1 as\u00ed, <\/p>\n
\"\"<\/a><\/p>\n
 
\nObviamente, el procesamiento de esta salida ser\u00eda m\u00e1s laborioso, pero posible, y le dar\u00e1 m\u00e1s informaci\u00f3n sobre la prueba que ha realizado.<\/p>\n
 
\nConclusi\u00f3n<\/strong><\/p>\n
Esta ha sido una publicaci\u00f3n corta, pero espero que sea \u00fatil que comparta y controle su subsistema de almacenamiento.<\/p>\n
Gracias por leer y cualquier pregunta, por favor use los comentarios a continuaci\u00f3n.<\/p>\n
 <\/p>\n
<\/p>","protected":false},"excerpt":{"rendered":"
Una de las cosas m\u00e1s importantes cuando algo se tuerce, es tener una idea de cu\u00e1l es el rendimiento normal que podemos esperar de nuestro entorno. Dej\u00e1dme mostraros como f\u00e1cilmente podemos testar…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,19],"tags":[58,25,15],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/399"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=399"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/399\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=399"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=399"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=399"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}