Performance is always a concern, but unless it's a problem, it's rarely a priority. That's why so many database applications run into performance problems—most applications are built around a "get it done yesterday" schedule and performance concerns are secondary to release. In fact, in most cases, all a developer focuses on is how an application works for a single user on a high-end developer machine. "It works on my machine" is the response to most bugs that emerge just prior to, during, or shortly after deployment.
However, performance is anything but a new issue, and over the years more and more resources and tools have been created to help quickly identify performance bottlenecks. Visual Studio (VS) 2010 includes the latest version of one such tool, the VS Performance Profiler. This feature-rich tool includes several new capabilities, including some that are specific to checking the performance of your ADO.NET data access. This article will provide an introduction to this tool and then walk you through using it to debug a couple of specific performance scenarios.
To follow along with the examples discussed in this article, you'll need a version of VS 2010 above the Express level (Professional, Premium, or Ultimate). The analysis tab is available in all of these versions. To use the analysis tools, you'll need an application to test. There's a simple application solution associated with this article with VB and C# versions available for you to download (click here). The sidebar "Create a Sample Application for the Performance Profiler to Test" describes loading or creating the solution. This article is going to gloss over creating the sample application and mainly look at testing this code's behavior.
These samples were developed against a local SQL Server 2008 R2 database and tested with SQL Server 2008—your results may vary with other database versions. However, the version of the database isn't a dependency for using the performance profiling tool. The provided examples use the standard Adventure Works sample database available for download from CodePlex at msftdbprodsamples.codeplex.com/. Download the example code from sqlmag.com, InstantDoc ID 125847.
The Profiler
The profiler has enough features and capabilities that I could write a book chapter and still not cover them all. So it's important that I start by looking at some of the key features and defining some of the areas that won't be explored in this article. At its core, this article is aimed at introducing database developers to some of the tools that are available for performance monitoring, so anything dealing with the high-end High Performance Cluster testing and elements related to parallelism is outside its scope.
Your first step is to go to VS 2010 and start it with administrator privileges. (Note that it's possible to performance test applications using the command line, but I'll be working from within VS.) VS should be started with Administrator privileges because to use the performance tools, you'll access system level information that's protected. The sample application used for this article is a Windows Forms application, but you can create your own. The Performance Profiler can be used with both ASP.NET and WPF applications (but not Silverlight applications) and can even be configured to profile JavaScript functions, but I'll focus on ADO.NET.
Having opened or created a new Windows Forms application, you now have access to the Analyze menu within VS. The first item is Launch Performance Wizard…, which opens the screen in Figure 1. This initial screen is useful as a starting reference because it illustrates the top level options for performance profiling. Before I move on I'll describe the top level focus of each of these options.