To follow along with this Performance Profiler article, you need to create and use a simple .NET application. For the purposes of this article you'll need VS 2010 Professional or above. If you choose to download the sample and use it, be aware that while I've included a couple of sample performance runs you'll need to engage the Performance Profiler in your environment.
Unzip the downloaded solution under your typical project location for VS, then double-click the solution file to open the sample application in VS. The solution might load and, depending on your security settings, you might get a warning about the code coming from an unsafe location. Once the solution loads however, VS's layout should look similar to what you normally see, which is the challenge. If you look in the Solution Explorer, you should see the Solution Items folder. By default, this will probably be empty. Right-click this folder in the Solution Explorer, and from the context menu, select Add then Existing Item… to open File Explorer. You'll see several *.psess files listed in the solution folder. Select one or more of these, in particular the perfTest.psess file.
Once these have been added to your solution, you'll see the Performance Explorer open automatically, along with copies of my sample test runs for this application. At that point you can either review the historical data, or create your own sample data based on my very basic application. If you intend to run the application, don't forget to install the custom stored procedures, the scripts for which are part of the download package.
The alternative to using the downloaded files is creating your own solution from scratch. This sidebar isn't large enough for me to include all of my custom code, but I'll describe what I did to create a project suitable for this article. I started by using the File menu to select New… and open the new project template dialog. From this I selected a new Windows Forms project, which I named perfTest. At this point VS generated the shell of my project, including the main form. To the main form I added a comboBox control, below that a button and below that a grid. I added a label control to the right of the comboBox control.
Next I double-clicked the button control so that VS would generate an event handler for the button and take me to the code for my solution. In the code, I made several modifications. I started by adding a private integer value called mgrID, which is used to hold the internal ID value used in the queries. As part of the Form1_Load event, I added the three stored procedure names and defaulted to the uspGetManagerEmployee entry. Remember that both uspGetManagerEmployee109 and uspGetManagerEmployeeOneLevel are custom stored procedures that you need to manually add to your copy of AdventureWorks using the provided scripts.
Next, within the generated event handler for the button, I retrieved the currently selected entry in the combo box and then branched to a different private method for each of the three stored procedures. Each private method returns a datatable that is then assigned as the DataSource for the Grid to display the query results.
The private methods for each stored procedure are similar in that each creates a new List of SqlParameter objects, creates the appropriate parameters for the stored procedcure and then calls a function to return the resulting data table. Of course, the details are what make this code work, but each eventually calls a standard function to actually access the database. The class DatabaseFunctions is more custom code that I included to encapsulate my database call and basic error handling for this sample.
Whether you use my sample code or create your own, you'll need a project that accesses a database in order to leverage the Performance Profiler in VS 2010. My sample code is just a very basic implementation to allow you to quickly set up and test a few different scenarios with this tool. You're welcome to enhance my code if you want to build a reusable database query test tool, but that wasn't its purpose.
