Recently I've been doing a few demos of ANTS Performance Profiler 6 at user groups, and last week I did a talk entitled "Real World Performance Profiling" at The Developers Group Meeting down in London, for which I'll publish my notes later.

For both the demos and the talk I've been using an ASP.NET application, which my colleague Alex wrote some time ago: a simple Digg clone called Shovell. Originally this application used MS Access 2003 and ran in Cassini, otherwise known as Web Development Server, however I hastily converted it to use SQL Server and IIS, so that I could showcase the new SQL support in ANTS Performance Profiler 6.

I've been planning to publish the application for a while, and have finally got round to posting it up here, along with full installation and usage instructions. These are available in the readme included in the zip file, but I've also posted them here because it means I can put up some screenshots to point you in the right direction with IIS and SQL Server settings.

As I said, I'll post up the content of the talks shortly as well.

Installing Shovell

Pre-requisites:

• A local non-Express instance of SQL Server - I've tested it on Developer Editions of SQL Server 2008 and SQL Server 2008R2
• IIS (tested on 7.5)
• .NET 2.0 or later

You'll obviously also need ANTS Performance Profiler 6.0 if you want to profile the site, which is the main point of it. You can download a 14-day fully functional free trial from http://www.red-gate.com/products/ants_performance_profiler/index.htm. If you want to see information about what's happening on SQL Server you'll also need to be running on Windows Vista, Server 2008, or later. I've tested the application on Windows 7 64-bit Enterprise Edition.

The code for the site contains two different methods for choosing the list of articles to display:

• A naive method which reads all the articles from the database, then orders and tops them in .NET code.
• A much more efficient method which uses a single SQL query to retrieve, order, and top the data.

You can choose which method is used by tweaking a setting in the web.config file (see below). You can also change the number of articles that will be displayed in the same way.

You may well find that IIS is not available by default on your system. If so you'll need to enable it via Start > Control Panel > Programs and Features > Turn Windows features on or off. You must also ensure that ASP.NET support is enabled. Figure 1 shows the settings I've used.

Figure 1. Enabling IIS. Note that for all unexpanded notes I've just accepted the defaults suggested by Windows after I checked the top level "Internet Information Services" node. I drilled down and checked "ASP.NET", which also enables a number of other features, manually. Unless you've any particular reason to turn on any other features I'd leave them switched off because it'll minimise system startup time, and also reduce the potential attack surface exposed by your machine.

Once you've enable IIS, you're ready to install Shovell itself. There are a fair number of individual steps here, but they're all pretty simple (NB. these are for IIS 7.5/SQL Server 2008R2, but you should find they're good for IIS 7/SQL Server 2008 as well; earlier versions may require you to slightly alter these actions):

1. Extract ShovellComplete.zip.
2. Move the resulting ShovellComplete folder to your web publishing path. By default this is C:\inetpub\wwwroot. I found that trying to unzip directly to this folder caused problems with UAC, at least when using 7-zip, hence I've split this into two steps.
3. In Explorer open the extracted ShovellComplete folder.
4. Run ShovellPackage.exe - this is a SQL Packager package that will install the demo database.
5. Select your target SQL Server instance. This should be an instance on the local machine if you want to be able to see SQL Server information in ANTS Performance Profiler.
6. Select Make a database.
7. Make sure the database is called ShovellDB, which it should be by default.
8. Click Run and wait for the package to create and populate the database - this will probably take a few minutes.
9. Now we need to make sure that IIS has access to the demo database, so open SQL Server Management Studio and connect to the SQL Server instance on which you created the database.
10. In the Object Explorer navigate to the newly created ShovellDB database, and expand ShovellDB > Security > Users.
11. Right click on Users and click New User.
12. Add NT AUTHORITY\NETWORK SERVICE as a user, and make it a member of the db_datareader and db_datawriter roles (see figure 2). You should find that this user already has an associated identity within the server, but if not you'll need to create one.

Figure 2. Assigning roles to user for IIS in ShovellDB database.

1. Now we need to enable the web application and set the identity IIS will use when connecting to the database. Go to Start > Control Panel > Administrative Tools.
2. Open Internet Information Services (IIS) Manager.
3. Go to {YOURHOSTNAME} > Sites > Default Web Site > Shovell Complete > Shovell.
4. Right click on Shovell and click Convert to Application.
5. Click OK.
6. Now open {YOURHOSTNAME} > Application Pools.
7. Right-click on DefaultAppPool and click Advanced Settings.
8. Change the value of Identity to NetworkService (see figure 3).

Figure 3. Configuring the default application pool identity in IIS Manager.

1. Click OK.
2. Now we need to make sure the connection string in the Shovell web.config file is correct. Open the web.config file, which you'll find in {WEBPUBLISHINGPATH}\ShovellComplete\Shovell.
3. Make sure the connection string refers to the SQL Server instance on which you created the ShovellDB database.

Now you should be ready to go. To test that everything's working properly open your web browser and navigate to http://127.0.0.1/ShovellComplete/Shovell/. After a few seconds the web site should appear.

Note that there is also a web service and associated client that can be used to add articles to the site, but I haven't used these as part of my demo so they are completely untested since the port to SQL Server. I have included them in the download, so if you fancy a play, go ahead. I have also included the original Cassini/MS Access version of the application in an enclosed zip file, should you wish to use them instead.

Profiling Shovell

I mentioned that there are two modes in which the web app can run - naive and efficient. To switch between these modes:

• Open the site's web.config file.
• Set the value of the slowMode setting to True or False, to select the naive or efficient mode, respectively.

Finally, to profile Shovell:

1. Make sure you have ANTS Performance Profiler 6 installed. If not, you can download it from http://www.red-gate.com/products/ants_performance_profiler/index.htm.
2. Open ANTS Performance Profiler 6. NOTE: you will need to Run as Administrator in order to see performance counters in the timeline, until version 6.1 is released within the next few weeks.
3. Choose ASP.NET web application (IIS).
4. Enter http://127.0.0.1/ShovellComplete/Shovell/ as the value for ASP.NET web application (URL).
5. Click Start Profiling.
6. Accept any UAC requests.

The login information for Shovell lives in {WEBPUBLISHINGPATH}\ShovellComplete\Shovell\App_Data\MembershipUsers.xml.

You should find that in the naive mode it takes significantly longer to display the list of articles than in the efficient mode, and you should also be able to easily identify the bottlenecks with ANTS Performance Profiler 6. You should see that in the naive mode, whilst the biggest overhead is the sort, the SQL query also takes around 3-4 times longer to run, in general, than in the efficient mode.

Download

ShovellComplete.zip

PS. Apologies for the daft list item numbering - it's down to Community Server, which just ignores/strips any attempt by me to restart numbering at any value other than 1. Inlining the images with the list items unfortunately doesn't work either because it screws with the formatting.