Microsoft Consultancy Services (UK) has recently started blogging. These are team-based blogs rather than individual blogs. I have listed a few of these blogs below
MCS UK Microsoft Business Intelligence Team Blog http://blogs.msdn.com/b/mcsukbi. This blog focuses on the Microsoft business intelligence technology stack e.g. SSIS, SSAS, PerformancePoint rather than transactional-based systems.
MCS UK Solution Development Team Blog http://blogs.msdn.com/b/mcsuksoldev. This blog focuses on application development.
MCS UK SharePoint Team Blog http://blogs.msdn.com/b/uksharepoint. This blog focuses on SharePoint solutions.
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 http://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.
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 http://msdn.microsoft.com/en-us/library/ms404658.aspx.
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
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 http://msdn.microsoft.com/en-us/library/ms404667(VS.90).aspx
As a side note, here are a few tips:
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 http://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.
BPA is back for SQL Server 2008 and SQL Server 2008 R2 http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591