Testing The Testing Strategy

How does a test harness work? Why are these results different than those? The best way to figure things out is to try them out...

How to Export Perfmon Counter Values from the Visual Studio Load Test Results Database

How to Export Perfmon Counter Values from the Visual Studio Load Test Results Database

  • Comments 2

How Are Perfmon Results Stored

When Visual Studio collects perfmon data during a load test, it stores the data in the SQL database using four tables that allow it to normalize the data into a relational hierarchy. The four tables are:

  • Dbo.LoadTestPerformanceCounterCategory - holds the list of machine names and counter category names (such as Process, Memory, etc.) that were collected for each run.
  • Dbo.LoadTestPerformanceCounter - holds the list of counters (such as '% Processor Time' and 'Available MBytes') that were collected for each category in the LoadTestPerformanceCounterCategory table.
  • Dbo.LoadTestPerformanceCounterInstance – holds the list of instance names (including _Total_ or any named instance) collected for each counter in the LoadTestPerformanceCounter table. NOTE: Visual Studio defines a special named instance ('systemdiagnosticsperfcounterlibsingleinstance') that it uses for counters that do not have a named instance. A good example of this is 'Available MBytes', which does not have any named instances. Because of this, the routines below will do name substitution so that the results are compatible with perfmon.
  • Dbo.LoadTestPerformanceCounterSample – holds the actual recorded values for every sampling interval for each of the instances in the LoadTestPerformanceCounterInstance table.

 Visual Studio uses this data, along with some built in views to build the graphs it shows as part of the load test results. It does NOT use this data to build any of the tables shown in the results (see the example below to understand what results I am describing here. For more information on the LoadTest2010 DB Schema, see this post from Sean Lumley).

 

 How to Export The Results

In order to export the results, we need to create a CSV file that uses the counter names as column names, along with a column of the sample times (the first column). We then populate all of the computed values into the table. This requires dynamic SQL that first builds the list of items to get, then uses that list to retrieve the values. Once they are retrieved, we need to pivot the entire table to move the counter names into columns instead of rows.

Since the full counter names are split across three of the four tables, I created a view that builds the counter names. I can then reference this when creating the dynamic SQL. I also perform name substitution for the 'systemdiagnosticsperfcounterlibsingleinstance' counters, since these will not be recognized by perfmon. I return the data as a single DatTable from the stored procedure. I then use C# code on the client to read and parse the data table, writing it out to a CSV file. I have included the code for the SQL stored procedure and the SQL View, as well as sample code that can be used to create a C# Console app to test out the sproc. To use the code:

  • Copy the SQL code into SQL Management Studio and execute it against the LoadTest2010 database.
  • Create a new C# Console application in Visual Studio (called ExportToPerfmonCsv) and replace all of the code in program.cs with the C# code below.
  • Compile the application and run it with the following command syntax ExportToPerfmonCsv.exe <LoadTestRunId> <machineNameFilter> <resultsFolder> <sqlConnectionString> where:
    1. LoadTestRunId is the run id of the run you wish to use
    2. machineNameFilter is the name of the machine that the perfmon counters were collected from. NOTE: This string needs to include certain leading and trailing characters that help define how to filter the results. The syntax is <backslash><backslash><machineName><backslash>[<optional counter category filter><backslash>]<%>: \\CLIENT01\%
    3. resultsFolder is the location to store the results. It needs a trailing backslash
    4. sqlConnectionString is the string used to connect to the LoadTest2010 database.

 

Here is a sample I ran:

ExportPerfmonToCsv.exe 16 "\\CLIENT01\%" "c:\\temp\\" "Data Source=geoffgr2;Integrated Security=SSPI;Initial Catalog=LoadTest2010"

 

 

After running the tool, You can run perfmon and load the results:

 

 

Then choose to add some counters:

 

 

And the results will show up:

 

 

Code

 Sql Code to create the stored procedure and the accompanying view. Copy this into SQL Management Studio and execute it against the LoadTest2010 database

/*== TSL_prc_PerfCounterCollectionInCsvFormat ============================================================

* Returns a list of perfmon counter names and values in a format that can be written

* to a CSV file for import into Perfmon.

* EXAMPLE:

EXEC   TSL_prc_PerfCounterCollectionInCsvFormat

              @RunId = N'9',

              @InstanceName = N'\\controller\%'

===============================================================================*/

--START CODE--

CREATE PROCEDURE TSL_prc_PerfCounterCollectionInCsvFormat

       @RunId nvarchar(10),

       @InstanceName nvarchar(1024)

AS

 

       DECLARE @CounterName nvarchar(max), @CounterNameColumns nvarchar(max)

 

       --Get List of columns to use in query. Shove them into a single long XML string

       SELECT @CounterNameColumns = (

                                  SELECT ', [' + REPLACE(InstanceName, ']', ']]') + ']' FROM MTSL_View_PerfmonInstanceNamesAndIds

                                  WHERE LoadTestRunId = @RunId

                                  AND InstanceName LIKE @InstanceName

                     FOR XML PATH(''))

 

       --Make a copy of the list WITHOUT the comma at the very beginning of the string

       SELECT @CounterName = RIGHT(@CounterNameColumns, LEN(@CounterNameColumns) - 1)

 

       -- Use the previous strings to build the query string that can be pivoted

       DECLARE @SQL nvarchar(max)

        SELECT @SQL = N'

              select

              IntervalStartTime AS [(PDH-CSV 4.0) (Eastern Daylight Time)(240)]' +

          --IntervalStartTime' +

              @CounterNameColumns + '

        from (

                     select

                           interval.IntervalStartTime,

                           MTSL_View_PerfmonInstanceNamesAndIds.InstanceName,

                           countersample.ComputedValue

                     FROM

                           MTSL_View_PerfmonInstanceNamesAndIds

                           INNER JOIN LoadTestPerformanceCounterSample AS countersample

                           ON countersample.InstanceId = MTSL_View_PerfmonInstanceNamesAndIds.InstanceId

                           AND countersample.LoadTestRunId = MTSL_View_PerfmonInstanceNamesAndIds.LoadTestRunId

 

                           INNER JOIN LoadTestRunInterval AS interval

                           ON interval.LoadTestRunId = countersample.LoadTestRunId

                           AND interval.TestRunIntervalId = countersample.TestRunIntervalId

 

                     WHERE

                           MTSL_View_PerfmonInstanceNamesAndIds.LoadTestRunId = ' + @RunId + '

                           AND

                           MTSL_View_PerfmonInstanceNamesAndIds.InstanceName LIKE '''+@InstanceName+'''

        ) Data

        PIVOT (

          SUM(ComputedValue)

          FOR InstanceName

          IN (

               ' + @CounterName + '

          )

        ) PivotTable

        ORDER BY IntervalStartTime ASC

        '

 

        print @SQL

       -- Execute the generated query

       exec sp_executesql @SQL

GO

--START CODE--

GRANT EXECUTE ON TSL_prc_PerfCounterCollectionInCsvFormat TO PUBLIC

GO

 

/*===============================================================================

MTSL_View_PerfmonInstanceNamesAndIds

===============================================================================*/

CREATE VIEW MTSL_View_PerfmonInstanceNamesAndIds AS

---------------------------------------------------------------------------------

       SELECT

              instance.LoadTestRunId

              ,instance.InstanceId

              ,(

                     '\\' + category.MachineName

                     + '\' + category.CategoryName

                     +      case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'

                                   then ''

                                   else '(' + instance.InstanceName  + ')'

                                   end

                     + '\' + counter.CounterName

              ) AS InstanceName

       FROM LoadTestPerformanceCounterCategory AS category

 

       INNER JOIN LoadTestPerformanceCounter AS counter

              ON category.LoadTestRunId = counter.LoadTestRunId

              AND category.CounterCategoryId = counter.CounterCategoryId

 

       INNER JOIN LoadTestPerformanceCounterInstance AS instance

              ON counter.CounterId = instance.CounterId

              AND counter.LoadTestRunId = instance.LoadTestRunId

GO

 

C# code method to call the stored procedure and write the results to a file. This is sample code that shows you how I use the stored procedure inside a reporting tool I am developing. You can modify it as needed.

//*********************************************************

// Copyright (c) Microsoft. All rights reserved.

// THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF

// ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY

// IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR

// PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.

//

// Sample code written by Geoff Gray.

//*********************************************************

     

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Globalization;

 

namespace ExportToPerfmonCsv

{

    class Program

    {

        static void Main(string[] args)

        {

            // Note: This code does not do any meaningful check for proper input args.

            // It is intended solely as a means of showing how to invoke the processing

            // method(s) below.

 

            if (args.Length == 0)

                Console.Write(@"The syntax is: ExportToPerfmonCsv.exe <LoadTestRunId> <machineNameFilter> <resultsFolder> <sqlConnectionString>");

            else

            {

                Console.Write(String.Format("Executing ExportToPerfmonCsv with the following arguments: "

                    + "\r\nLoadTestRunId={0}\r\nmachineNameFilter={1}\r\nresultsFolder={2}\r\nsqlConnectionString={3}"

                    , args[0].ToString(), args[1].ToString(), args[2].ToString(), args[3].ToString()));

                int nRows, nCols;

                TimeSpan ts = SavePerfmonAsCsv(args[0].ToString(), args[1].ToString(), args[2].ToString(), args[3].ToString(), out nRows, out nCols);

                Console.Write(String.Format("\r\nProcessing completed in {0} seconds.\r\nCSV file contains {1} rows of data with {2} counter instances per row"

                    , ts.TotalSeconds, nRows, nCols));

            }

            return;

        }

 

        static TimeSpan SavePerfmonAsCsv(string sRunId, string sMachineFilterString, string sFolderToStoreResults, string sqlConn, out int numRows, out int numCols)

        {

            DateTime dateTime = DateTime.Now;

            numRows = 0;

            numCols = 0;

            try

            {

                string sqlCmd = String.Format("exec TSL_prc_PerfCounterCollectionInCsvFormat @RunId = {0}, @InstanceName='{1}'", sRunId, sMachineFilterString);

                using (SqlConnection _conn = new SqlConnection(sqlConn))

                {

                    _conn.Open();

                    SqlCommand _cmd = new SqlCommand(sqlCmd, _conn);

                    SqlDataReader _rdr = _cmd.ExecuteReader();

                    DataTable dt = new DataTable();

                    dt.Load(_rdr);

                    _rdr.Close();

 

                    string sFileName = String.Format("{0}Run{1}-{2}-Perfmon.csv", sFolderToStoreResults, sRunId, sMachineFilterString.Replace('\\', ' '));

                    sFileName = sFileName.Replace('%', ' ');

                    sFileName = sFileName.Replace(" ", "");

                    StringBuilder sb = new StringBuilder();

                    IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().Select(column => column.ColumnName);

                    sb.AppendLine("\"" + string.Join("\",\"", columnNames) + "\"");

                    foreach (DataRow row in dt.Rows)

                    {

                        List<string> fields = new List<string>();

                        for (int x = 0; x < row.ItemArray.Length; x++)

                        {

                            if (x == 0)

                                fields.Add(((DateTime)row.ItemArray[0]).ToString("G", DateTimeFormatInfo.InvariantInfo));

 

                            else

                                fields.Add(row.ItemArray[x].ToString());

                        }

                        sb.AppendLine("\"" + string.Join("\",\"", fields) + "\"");

                    }

                    File.WriteAllText(sFileName, sb.ToString());

                    numRows = dt.Rows.Count;

                    numCols = dt.Columns.Count;

                }

            }

            catch (Exception ex)

            {

                Console.Write(ex.ToString());

            }

            return (TimeSpan)(DateTime.Now - dateTime);

        }

    }

}

 

Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post
  • Running this I get the following error "the number of elements in the select list exceeds the maximum allowed number of 4096 elements".

    Could you please let me know what needs to be done.

  • I am guessing that the number of unique counters stored for the run in question is too large for the type of SQL I am using to export the results. Since I have to build a dynamic query and then pivot it, I am limited to a total of 4096 unique columns in the final set.

    The best thing to try is to narrow the scope of the result set by making the instance name more specific. You'd limit the number of counters you get back that way. Maybe something like '\\COMPUTERNAME\ASP%' or '\\COMPUTERNAME\Process%'

Page 1 of 1 (2 items)