I was recently working on a legacy ASP.NET application which was taking like 30 seconds to load a simple page with a Gridview.

The culprit was Linq to SQL lazy-loading combined with an inefficient architecture. By tidying that up I was able to get the page load time down to about one a second.

I could have stopped there but I thought it would be nice to see if the SQL performance could be improved further.

Step 1. Profile

Start SQL Server Profiler and create a new trace. Choose the "Tuning" profile.

If your database server has more than one active database on it, you will want to choose to filter activity to your database only.

Once the trace starts recording, go to your website and hit the page you are trying to improve. In my case it was a page listing Help Requests, so I hit that page and did a couple of searches. Stop recording the trace and save it. I called mine “help requests.trc”.

Step 2. Analyse

Start SQL Server’s Database Engine Tuning Advisor. It will automatically start a new Tuning session when you connect to your database.

Choose your trace file that you’ve just recorded (1), choose your database (2), tick your database again (3), and finally hit Start Analysis (4).

Results:

Oops, that’s not quite right. It’s reporting errors, "SHOWPLAN permission denied in database ‘DBTest’"

At first this didn’t make sense, because I’m a dbo so I have permission to do everything. But remember – we’re running a trace we recorded earlier. IIS’s application pool for the website is setup to use a specific account – COMPANYDOMAIN\IIS_APPUSER, and this account doesn’t have SHOWPLAN permission. So grant it:

GRANT SHOWPLAN TO [COMPANYDOMAIN\IIS_APPUSER]

Now run the trace again, and you should see the recommendations screen.

Step 3. Tune

As you can hopefully see, SQL Server recommends creating 4 indexes and a bunch of statistics, and estimates a whopping 92% speed improvement! Press the Save recommendations button in the toolbar to save those recommendations to a SQL script which you can review and tweak, and then run that script against your database.

Done!