Foliotek Developer Blog

A Programmers Guide to Performance Tuning SQL Server Queries

First off – I am not a DBA. What follows is just some general tools and tips I’ve found to help write better queries, find queries that are performing poorly, and find some ideas on how you might improve performance.

Here’s the general procedure I use to find and fix the poorly performing “low hanging fruit” in an SQL Server driven database application.

  1. Do you know a query you want to improve? ?If so, skip to step 3.
  2. Use SQL Profiler to identify candidate queries 1. Fire up SQL Profiler. ?You can find it under ‘SQL ….->Performance Tools’ in your start menu, or you can open it in the ‘Tools” menu of SQL Management Studio.
  3. Start a new trace. ? Use the event selections tab to filter for queries you care about. 1. I usually check ‘Stored Procedures->RPC:Completed’ and ‘TSQL->SQL:BatchCompleted’
  4. I also use the column filters to limit to the ApplicationName/DatabaseName/LoginName/HostName I care about and the performance characteristics I’m looking for ?(a good place to start is >100 cpu, >50 duration or >5000 reads. ?Adjust as necessary to find the worst performing in each category)
  5. Run the trace until you see some queries you are interested in. ?Pause or stop it and select the query text from the detail window.
  6. Copy your identified query into SQL Management Studio. ?Right click to turn on ‘Execution Plan’ and ‘Client Statistics’ if they aren’t on already. ?Assuming you’ve selected a SELECT query that won’t change data, run it to get a feel for the amount of time you want to improve. ?Look at the execution plan and the client statistics to see what parts of the query cost most – you might identify ways to rewrite the query to improve performance. ? Common ways to improve are to shrink the amount of data you are returning by limiting columns returned, etc.
  7. If you can’t find a way to improve performance with a rewrite (or want to improve it even more) you can often improve performance by adding SQL Statistics or Indexes. ?You might be able to guess appropriate indexes based on the execution ?plan/client statistics – but it’s just as easy to let SQL identify candidates for you with SQL Tuning Advisor. 1. Highlight the query, right-click, and select Analyze Query in Database Engine Tuning Advisor
  8. Click the play button ‘start analysis’
  9. Look over the recommended statistics and indexes, especially the size column. ?Indexes are a size/speed tradeoff – you want to make sure you are getting enough benefit out of the extra index space. ?Statistics generally have a lower impact to both speed and storage space (they are stored in a blob in an internal table). ?NOTE: I’ve generally found the ‘Estimated improvement’ number useless. ?I’ve applied an estimated 2% improvement that realized a 2x speed benefit.
  10. Uncheck any you don’t want, and then click ‘Save Recommendations’ to create a .sql file you can apply to the server to create the indexes and statistics.

Web Application Functional Regression Testing Using Selenium

At Foliotek, we use a rapid development methodology.? Typically, a new item will go from definition through coding to release in a month’s time (bucketed along with other new items for the month).? A bugfix will nearly always be released within a week of the time it was reported.? In fact, we are currently experimenting with a methodology that will allow us to test and deploy new items individually as well – which means that a new (small) item can go from definition to release in as little as a week, too.

Overall, this kind of workflow is great for us, and great for our customers.? We don’t need to wait a year to change something to make our product more compelling, and customers don’t have to wait a year to get something they want implemented.? We also avoid the shock of suddenly introducing a year’s worth of development to all our customers all at once – a handful of minor changes every month (or week) is much easier to cope with.

However, it also means that Foliotek is never exactly the same as it was the week before.? Every time something changes, there is some risk that something breaks.?? We handle this risk in two ways:

  1. We test extremely thoroughly
  2. We fix any problems that arise within about a week (severe problems usually the same day)

At first, we did all testing manually.? This is the best way to test, assuming you have enough good testers with enough time to do it well.? Good testers can’t be just anyone – they have to have a thorough knowledge of how the system should work,they have to care that it does work perfectly,and they have to have a feel for how they might try to break things.? Having enough people like this with enough time to do testing is expensive.

Over time two related things happened.? One was that we added more developers to the project, and started building more faster.? Two was that the system was growing bigger and more complex.

As more people developed on it and the system grew more complex, our testing needs grew exponentially.? The rise in complexity and people developing led to much, much more potential for side-effects – problems where one change affects a different (but subtly related) subsystem.? Side-effects by their nature are impossible to predict.? The only way to catch them was to test EVERYTHING any time ANYTHING changed.

We didn’t have enough experienced testers to do that every month (new development release) let alone every week (bugfix release).

To deal with that, we started by writing a manual regression test script to run through each week.? While this didn’t free up any time overall – it did mean that once the test was written well, anyone could execute it.? This was doable, because we had interns who had to be around to help handle support calls anyways – and they were only intermittently busy.? In their free time they could execute the tests.

Another route we could have gone would have been to write automated unit tests (http://en.wikipedia.org/wiki/Unit_testing).? Basically, these are tiny contracts the developers would write that say something like “calling the Add function on the User class with name Luke will result in the User database table having a new row with name Luke”.? Each time the project is built, the contracts are verified.? This is great for projects like code libraries and APIs where the product of the project IS the result of each function.? For a web application, though, the product is the complex interaction of functions and how they produce an on screen behavior.? There are lots of ways that the individual functions could all be correct and the behavior still fails.? It is also very difficult to impossible to test client-side parts of a web application – javascript, AJAX, CSS, etc.? Unit testing would cost a non trivial amount (building and maintaining the tests) for a trivial gain.

Eventually, we discovered the Selenium project (http://seleniumhq.org/download/).? The idea of Selenium is basically to take our manual regression test scripts, and create them such that a computer can automatically run the tests in a browser (pretty much) just like a human tester would.? This allows us to greatly expand our regression test coverage, and run it for every single change we make and release.

Here are the Selenium tools we use and what we use them for:

  • Selenium IDE (http://release.seleniumhq.org/selenium-ide/) : A Firefox plugin that lets you quickly create tests using a ‘record’ function that builds it out of your clicks, lets you manually edit to make your tests more complex, and runs them in Firefox.
  • Selenium RC (http://selenium.googlecode.com/files/selenium-remote-control-1.0.3.zip):? A java application that will take the tests you create with Selenium IDE, and run them in multiple browsers (firefox, ie, chrome, etc).? It runs from the command line, so its fairly easy to automate test runs into build actions/etc as well.
  • Sauce RC (http://saucelabs.com/downloads): A fork of RC that adds a web ui on top of the command line interface.? It’s useful for quickly debugging tests that don’t execute properly in non-firefox browsers.? It also integrates with SauceLabs – a service that lets you run your tests in the cloud on multiple operating systems and browsers (for a fee).
  • BrowserMob (http://browsermob.com/performance-testing): An online service that will take your selenium scripts and use them to generate real user traffic on your site.? Essentially, it spawns off as many real machines and instances of FireFox at once to run your test – each just as you would do locally – for a fee.? It costs less than $10 to test up to 25 “real browser users” – which actually can map to many more users than that since the automated test doesn’t have to think between clicks.? It gets expensive quickly to test more users than that.

Selenium is a huge boon for us.? We took the manual tests that would occupy a tester for as much as a day, and made it possible to run those same tests with minimal interaction in a half hour or less.? We’ll be able to cover more test cases, and run it more – even running them as development occurs to catch issues earlier.

In my next post, I’ll talk about the details of how you build tests, run them, maintain them, etc. with the tools mentioned above. See it here: Selenium Tips and Tricks

Also, for Selenium 2 with ASP.NET Web Forms, see Simplifying C# Selenium 2 Tests for ASP.NET WebForms


Controlling IIS7 With Custom Build Script

Handling IIS after doing a publish was always a bit of a pain.

We use Web Deployment Projects which automates much of the build process, such as minifying CSS and JavaScript and copying new files to the web servers. But we still had to deal with the pain of manually controlling IIS after a publish. This actually required remoting into the servers, and changing the home directories of the site in IIS.

This was always a small headache, but if we only published once a month or so, it wasn’t a big deal. A few months ago, we started doing weekly releases in Foliotek, our assessment portfolio tool to take care of any problems that customers were having on the live site.

Here is what we used to do:

  1. Build the WebDeploy project in ‘release’ mode. This will copy files to a StagingWeb folder on both servers.
  2. Load up the staging website (which is hosted on Server1 and pointing directly at the StagingWeb folder). Verify any fixes.
  3. (Here is the annoying part): Remote into both servers, and open IIS Manager. At approximately the same time, switch the home folder over to StagingWeb and make sure the site is still up. Back up the Web folder, delete it and copy StagingWeb then rename the copy to Web. Point IIS back to the Web folder.

We have extra steps in part 3 to prevent any downtime, but it is tedius and prone to error, timing problems,etc. And it is not something to look forward to at the start of the week. Why should we do something manually that should be easy to automate?

Here is what we do now:

  1. Build the WebDeploy project in ‘release’ mode. This will copy files to a StagingWeb folder on both servers.
  2. Load up the staging website (which is hosted on Server1 and pointing directly at the StagingWeb folder). Verify any fixes.
  3. Run a batch script to handle backups and switching IIS

The solution uses the PsExec utility which allows remote execution of batch scripts,and the AppCmd.exe command line tool for managing IIS7.

It is just a couple of batch scripts. You can call them on the command line, or through MSBuild. We actually have a Windows Forms project that gets run on the AfterBuild event of our Web Deployment project. This executable has links to all of the sites, shortcuts to remote desktop, status of the servers, and a link to the publish script.

Run it using this command:

[sourcecode lang="bash"]
publishserverconnector.bat \Server1
publishserverconnector.bat \Server2
[/sourcecode]

The source code is below. It would probably need to be modified for different environments, and I’m sure there are better ways to handle this problem, at least this is simple to read and modify.

publishserverconnection.bat

This file basically just calls PSExec on the given server passing up the myproject_publish.bat script.
[sourcecode lang="bash"]
@ECHO off
SETLOCAL

REM Publish Connection Script
REM This will call the publish.bat script on the server specified in the first argument
REM Usage: publishserverconnection.bat [servername]

SET server=”%1″
SET serverdir=”%1c$inetpubwwwrootMyProject”
SET psexec=”%~dp0psexec.exe”
SET publish=”%~dp0myproject
publish.bat”
SET usage=USAGE: publishserverconnection.bat [servername]

if not exist %serverdir% (
ECHO ERROR: The path %server
dir% does not exist! Exiting..
ECHO %usage%
GOTO End
)
if not exist %psexec% (
echo ERROR: Could Not Find PSEXEC at path: %psexec%
GOTO End
)
if not exist %publish% (
echo ERROR: Could Not Find PUBLISH at path: %publish%
GOTO End
)

ECHO Starting publish on %server%.
%psexec% %server% -c -v %publish%

if ErrorLevel 1 (
ECHO.
ECHO ERROR: Having problems starting PSExec. Please verify access to the server, and retry.
ECHO If the problem persists, then you will need to manually publish.
ECHO.
)

:End

ENDLOCAL

[/sourcecode]

myproject_publish.bat

This file will be copied to the servers using PSExec, and will be executed locally in the server environment.
[sourcecode lang="bash"]
@ECHO off
SETLOCAL

REM Publish Script
REM This is called from development machines, using the PSExec Command.

CLS
ECHO.
ECHO =======================================================
ECHO Build Script.
ECHO %COMPUTERNAME%
ECHO This script will:
ECHO 1. Point the IIS entry for the LIVE website to StagingWeb
ECHO 2. Backup the original Web folder
ECHO 3. Copy StagingWeb over the original Web folder
ECHO 4. Point the IIS entry to the new Web folder
ECHO =======================================================
Echo.
ECHO Make sure you go to the staging site and confirm it is ready to go live.

For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (SET startdate=%%c-%%a-%%b)
For /f “tokens=1-2 delims=/:” %%a in (“%TIME%”) do (SET starttime=%%a%%b)

SET livedir=”C:inetpubwwwrootMyProjectWeb”
SET stagingdir=”C:inetpubwwwrootMyProjectStagingWeb”
SET livedirrevert=”C:inetpubwwwrootMyProjectWebRevert”
SET backupdir=”C:inetpubwwwrootMyProjectbackups%startdate%
%starttime%”
SET appcmd=”C:WindowsSystem32inetsrvappcmd.exe”
SET appcmdchange=%appcmd% set vdir “MyProject/” -physicalPath:%stagingdir%
SET appcmd
revert=%appcmd% set vdir “MyProject/” -physicalPath:%livedir%

IF NOT EXIST %livedir% (
ECHO Could not find path %livedir% on %COMPUTERNAME%
GOTO End
)
IF NOT EXIST %stagingdir% (
ECHO Could not find path %stagingdir% on %COMPUTERNAME%
GOTO End
)

Choice /M “Are you ready to start?”
If Errorlevel 2 GOTO End REM Only proceed if ready, exit if user types “N”

ECHO.
ECHO Pointing website at the StagingWeb folder…

CALL %appcmd_change%
If Errorlevel 1 GOTO IISError

ECHO New site is live
ECHO.

Choice /M “Does the site still work? If NO, IIS will be reverted.”
If Errorlevel 2 GOTO Revert

GOTO Backup

:Backup
ECHO Starting Web Backup to archives folder, and WebRevert in case you need to revert changes.
if exist %livedirrevert% (
rmdir %livedir
revert% /s /q
)
xcopy %livedir% %backupdir% /E /Y /q
xcopy %livedir% %livedir_revert% /E /Y /q

ECHO.
ECHO Removing old Web folder and copying StagingWeb to Web.
rmdir %livedir% /s /q
xcopy %stagingdir% %livedir% /E /Y /q

If Errorlevel 1 GOTO BackupError

ECHO.
ECHO Backup path is: %backupdir%
ECHO Backup Success! Resetting IIS to the Web/ folder…

REM Reset IIS to Web/ (which is copied from StagingWeb/)
CALL %appcmd_revert%
If Errorlevel 1 GOTO IISError

ECHO.
ECHO Great job! Now published on %COMPUTERNAME%. Don’t forget to set the other live web servers!
GOTO End

:BackupError
ECHO IMPORTANT: There was an error backing up the files.
ECHO This could be caused by lack of permissions when trying to remove the old Web directory, if IIS has a lock on the folder.
ECHO Don’t worry, the live site should still be ok, but you will need to manually remote into the server to sort out the backups.
GOTO End

:IISError
ECHO IMPORTANT: There was an error switching IIS over (error code = %ErrorLevel%)
ECHO Please manually remote into the server to sort things out.
GOTO End

:Revert
echo Resetting to the original Web folder…
CALL %appcmd_revert%
If Errorlevel 1 GOTO IISError
GOTO End

:End

ENDLOCAL
[/sourcecode]


Handy ASP.NET Debug Extension Method

Most of the programmers I know (myself included) don’t bother with the built in Visual Studio debugging tools. They are slow and resource intensive. Usually, its more efficient to just do one or more Response.Write calls to see key data at key steps.

That can be a hassle, though. Most objects don’t print very well. You have to create a loop or write some LINQ/String.Join to write items in a collection.

Inspiration struck – couldn’t I write an extension method on object to write out a reasonable representation of pretty much anything? I could write out html tables for lists with columns for properties, etc.

Then I thought – I love the javascript debug console in firebug. I can drill down into individual items without being overwhelmed by all of the data at once. Why not have my debug information spit out javascript to write to the debug console? That also keeps it out of the way of the rest of the interface.

Here’s the code:

[sourcecode lang="csharp"] public static void Debug(this object value) { if (HttpContext.Current != null) { HttpContext.Current.Response.Debug(value); } } public static void Debug(this HttpResponse Response, params object[] args) { new HttpResponseWrapper(Response).Debug(args); } public static void Debug(this HttpResponseBase Response, params object[] args) { ((HttpResponseWrapper)Response).Debug(args); } public static void Debug(this HttpResponseWrapper Response, params object[] args) { if (Response != null && Response.ContentType == "text/html") { Response.Write("