Using Visual Studio Test Tools to troubleshoot and reproduce database performance issues

  • Article

Visual Studio test suite contains a very useful set of tools to help troubleshoot and reproduce performance issues.  From a SQL Server perspective, I have found the tools invaluable as it allows me to replay HTTP workloads at volume against web servers and therefore the underlying database server.  

The Visual Studio tools provide a number of great features such as test mixes, step load plans, real-time visualisations, support for performance monitor counters and SQL Server tracing. 

Fiddler is a HTTP capture tool which also supports the ability to save the output to a .webtest file https://www.fiddlertool.com/Fiddler/help/WebTest.asp.  Client activity can be captured easily using Fiddler and this can then be imported into a Visual Studio Test Project as a workload. 

This workload can be replayed against servers using a step pattern or constant load.  There are many different configuration options which are conceptually referred to as a scenario.

image

Options such as warm-up or cool-down can be configured via the Run settings, as can the ability to capture SQL trace data although, when enabled, this captures a pre-defined set of event classes as defined here https://msdn.microsoft.com/en-us/library/ms404658.aspx.

image

SQL Server performance monitor counters can be added in the load test configuration as shown below.  Performance counters for named instances must be added manually (for some reason they are not

image

Real-time test data is displayed in Visual Studio and the results are saved either into a SQL Server Express database (installed as part of Visual Studio) or an existing SQL Server instance.  The latter requires manually configuring the data store connection string. 

Load balancers can also introduce some interesting behaviour such as binding your IP address to one web server however it is possible to avoid this using the Visual Studio Test Load Agent software to circumvent this behaviour: How to: Use IP Switching with Agents https://msdn.microsoft.com/en-us/library/ms404667(VS.90).aspx

As a side note, here are a few tips:

  • Be careful when using SQL Server Express Edition to store the results as the database has a size limitation (SQL Server 2005 and SQL Server 2008 have a 4GB limitation whilst, SQL Server 2008 R2 10GB limitation)
  • Review the SQL Sizing Considerations information in the Load Test Agent readme. e.g. The SQL Express database is license-limited to store 10 GB of data, which is around 24 hours of load test data for a typical load test.
  • Install SQL Server Management Studio Express to manage the instance
  • Schema differs for LoadTest between VS 2005 and VS 2008
  • Schema for LoadTest exists in C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE. (or Visual Studio 8.0 if you’re using Visual Studio 2005)
  • If you want to use a different instance of SQL Server to host the database and store the test results then execute the loadtestresultsrepository.sql (stored in folder listed above) and creates the database called LoadTest
  • You will need to change the connection string in Visual Studio, this can be found in Test\Administer Test Controllers.
  • The LoadTest schema differs between Visual Studio 2005 and Visual Studio 2008 so you can’t point Visual Studio 2008 at a previous LoadTest schema

 

I also used implemented DMV_Stats, a blocked process trace and performance monitor counter logs on the database server as I like to have a record of waitstats, blocking/deadlocking and also any (perfmon) queues.   From a web server perspective, I recorded a counter log of the key IIS metrics as defined here https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/7898b860-462c-4846-a3a8-1179f287ad88.mspx?mfr=true. 

In summary, Visual Studio and Fiddler provide the ability to capture and replay HTTP traffic at load.  This can be useful for investigating and recreating web server and database performance issues.