jsTree tri-state checkboxes with ASP.NET MVC revisited

My original jsTree checkboxes post is still my most popluar, yet it is 18 months old and no longer works with the latest version of jsTree (pre 1.0). Since that post is so popular I thought I should update it.

My first surprise was how much the jsTree guys changed their API between releases – I was only going from version 0.9.9 to version 1.0, but  I had to practically start from scratch.

So, without further ado:

jsTree is a jQuery plugin for creating a treeviews, and jsTree’s checkbox plugin allows you to create a treeview with tri-state checkboxes, like so:

Notice how “Origination” appears half-checked because only some of its children are checked.

Getting started

For this demo I am using ASP.NET MVC 3 and jsTree pre 1.0 fixed. Let’s start with a new “ASP.NET MVC 3 Web Application”, choose Internet Application, name it jsTreeDemo, and add the required jsTree files to our solution:

In the View, create a div which you want to become a treeview. I’ll name mine demoTree. Also add references to the jsTree script, and add a new index.js file for our custom javascript.

Views/Home/Index.cshtml

@{
 ViewBag.Title = "Home Page";
}

<h2>@ViewBag.Message</h2>

<div id="demoTree">

</div>

treeModel = [{"data":"Confirm Application","attr":{"id":"10"},"children":null},{"data":"Things","attr":{"id":"20"},"children":[{"data":"Thing 1","attr":{"id":"21"},"children":null},{"data":"Thing 2","attr":{"id":"22"},"children":null},{"data":"Thing 3","attr":{"id":"23"},"children":null},{"data":"Thing 4","attr":{"id":"24"},"children":[{"data":"Thing 4.1","attr":{"id":"241"},"children":null},{"data":"Thing 4.2","attr":{"id":"242"},"children":null},{"data":"Thing 4.3","attr":{"id":"243"},"children":null}]}]},{"data":"Colors","attr":{"id":"40"},"children":[{"data":"Red","attr":{"id":"41"},"children":null},{"data":"Green","attr":{"id":"42"},"children":null},{"data":"Blue","attr":{"id":"43"},"children":null},{"data":"Yellow","attr":{"id":"44"},"children":null}]}];



Scripts/index.js

/// 

$(function () {
   $("#demoTree").jstree({
      json_data : {
         data : treeModel
      },
      plugins : ["themes", "json_data", "ui", "checkbox"]
 });
});

Briefly, jstree has a number of ways of poplulating the tree with data – in my previous jsTree post I was populating the tree via AJAX but for now the tree is populated via hardcoded JSON (using the built-in "json_data" plugin). Don’t forget to specify the "checkbox" plugin too.

Populate the tree from a viewmodel

Just like last time, let’s create a viewmodel for creating the tree structure.

Models/JsTreeModel.cs

namespace jsTreeDemo.Models
{
  public class JsTreeModel
  {
    public string data;
    public JsTreeAttribute attr;
    public JsTreeModel[] children;
  }
  
  public class JsTreeAttribute
  {
    public string id;
    public bool selected;
  }
}

Now let’s change our controller to create a viewmodel, serialize it to JSON, and pass that string to our View.
Controllers/HomeController.cs

public ActionResult Index()
{
    ViewBag.Message = "Welcome!";
            
    var model = GetTreeData();
            
    string jsonModel = new JavaScriptSerializer().Serialize(model);
            
    return View("Index", "_Layout", jsonModel);
}

private JsTreeModel[] GetTreeData()
{
var tree = new JsTreeModel[] 
{
    new JsTreeModel { data = "Confirm Application", attr = new JsTreeAttribute { id = "10", selected = true } },
    new JsTreeModel 
    { 
        data = "Things",
        attr = new JsTreeAttribute { id = "20" },
        children = new JsTreeModel[]
            {
                new JsTreeModel { data = "Thing 1", attr = new JsTreeAttribute { id = "21", selected = true } },
                new JsTreeModel { data = "Thing 2", attr = new JsTreeAttribute { id = "22" } },
                new JsTreeModel { data = "Thing 3", attr = new JsTreeAttribute { id = "23" } },
                new JsTreeModel 
                { 
                    data = "Thing 4", 
                    attr = new JsTreeAttribute { id = "24" },
                    children = new JsTreeModel[] 
                    { 
                        new JsTreeModel { data = "Thing 4.1", attr = new JsTreeAttribute { id = "241" } }, 
                        new JsTreeModel { data = "Thing 4.2", attr = new JsTreeAttribute { id = "242" } }, 
                        new JsTreeModel { data = "Thing 4.3", attr = new JsTreeAttribute { id = "243" } }
                    },
                },
            }
    },
    new JsTreeModel 
    {
        data = "Colors",
        attr = new JsTreeAttribute { id = "40" },
        children = new JsTreeModel[]
            {
                new JsTreeModel { data = "Red", attr = new JsTreeAttribute { id = "41" } },
                new JsTreeModel { data = "Green", attr = new JsTreeAttribute { id = "42" } },
                new JsTreeModel { data = "Blue", attr = new JsTreeAttribute { id = "43" } },
                new JsTreeModel { data = "Yellow", attr = new JsTreeAttribute { id = "44" } },
            }
    }
};

return tree;
}

Views/Index.cshtml

@model string
       
@{
    ViewBag.Title = "Home Page";
}

...


    treeModel = @Html.Raw(Model);

Determining which items are checked when posting

Let’s put our tree inside a <form> and submit it.
Views/Index.cshtml

...
@using (Html.BeginForm("Results", "Home", FormMethod.Post))
{ 
    <div id="demoTree">
    </div>
    <br />
    <div>
        
    </div>
} 
...

And now let’s add the Results method to the HomeController

Controllers/HomeController.cs

[HttpPost]
public ActionResult Results(FormCollection form)
{
    return View(form);
}

and the View

Views/Home/Results.cshtml

@model FormCollection

@{
    ViewBag.Title = "Results";
}

<h2>Results</h2>
<p>
    You chose:
    @foreach (var item in Model.AllKeys)
    {
        @Model[item]@: 
    } 
</p>

<p>
    @Html.ActionLink("Home", "Index")
</p>

If you press the Submit button, nothing will happen as nothing is passed through in the FormCollection to Results() in HomeController. Despite appearances, jsTree doesn’t by default render any HTML <input>s for the checkboxes. But it’s easy enough to tell it to render them using the real_checkboxes flag:

Scripts/index.js

$(function () {
    $("#demoTree").jstree({
        json_data: {
            data: treeModel
        },
        checkbox: {
            real_checkboxes: true,
            real_checkboxes_names: function (n) {
                return [("check_" + (n[0].id || Math.ceil(Math.random() * 10000))), n[0].id]
            }
        },
        plugins: ["themes", "json_data", "ui", "checkbox"]
    });
});

Now when we submit our form, we should see the ids of the values which were checked:

What about telling the tree to pre-check some items?

So how do we render the tree with some items already checked? Notice how we added “public bool selected” to JsTreeAttribute? This doesn’t do anything as far as the checkboxes are concerned, but it does add a custom property called “selected” to each node’s <li>. We can use that to tell the jsTree to check the given node, by binding to the ‘loaded.jstree’ event.

Scripts/index.js

$(function () {
    $("#demoTree").jstree({
        json_data: {
            data: treeModel
        },
        checkbox: {
            real_checkboxes: true,
            real_checkboxes_names: function (n) {
                return [("check_" + (n[0].id || Math.ceil(Math.random() * 10000))), n[0].id]
            }
        },
        plugins: ["themes", "json_data", "ui", "checkbox"]
    }).bind("loaded.jstree", function (event, data) {
        $('#demoTree').jstree('check_node', 'li[selected=selected]');
    });
});

You’ll see that the nodes that are marked with selected=true in GetTreeData() are now pre-checked when you first load the page.

Here’s a link to the solution (VS 2010).

Running a Win 7 VM on a Macbook Air (2011)

A few days ago I bought a new Macbook Air. I went for the fully pimped one which comes with an i7 and a 256Gb SSD. With the VMware Fusion demo I was able to bring my old laptop over and run it as a VM inside the Mac.

A few notes:

1. VMware’s Migration Assistant wanted to create a VM of my entire laptop, with both C: and D: disks. I didn’t want to bring over the 200Gb D: and the Migration Assistant didn’t have any options to prevent that from happening. So on my old laptop, I installed VMware’s free VMware converter. That had many more options including the option to exclude D:

2. It looks like I don’t need to buy a new Windows license. So far my VM hasn’t phoned home to Microsoft – maybe it will?

3. For networking, although I’m connected on WiFi to my flat’s network on the Mac, the VM sees it as an ethernet connection. Initially I couldn’t connect to the network on the VM because it was complaining that there were no ethernet drivers installed. I had to edit the .vmx file and add a line ethernet0.virtualDEV = “e1000”.

4. Visual Studio is completely red and unusable! I haven’t figured that one out yet.

Update (Dec 2011)

The Visual Studio red problem I had was with VMWare Fusion 3 and Parallels 6, but it seems to be fixed in VMWare Fusion 4 and Parallels 7. Having said that, I don’t use virtualisation and reboot and run Win7 in Boot Camp.

Visual Studio 2010 PerfWatson extension

Today I installed the Visual Studio PerfWatson extension. Briefly, it automatically reports when Visual Studio hangs (for > 2 seconds) to Microsoft so that they can one day fix it.

But a nicety is the PerfWatson Monitor extension, which adds a cool little graphic it adds to the bottom of Visual Studio:

Since I’m running Visual Studio on my lowely netbook which constantly hangs (i.e. doesn’t respond for a few seconds), Microsoft will be getting plenty of data from me!

The Perf Watson blog is here but if you want to install it just go to Tools -> Extension Manager (in VS) and search for Watson in the Online Gallery. Install Visual Studio PerfWatson and PerfWatson Monitor.

iTunes sucks balls

Every time I use iTunes, I hate it. And now I have yet another reason to hate it:

itunes upgrade required

Oh fuck off, I don’t believe you. I’m running 10.2.2 and you’re telling me that I need to upgrade to the latest version just to stream a video? Bullshit. You’re making me upgrade because you think you can make me upgrade. Well fuck you, I’m not going to, so I won’t bother watching the iTunes Festival performances either.

SortedDictionary of countries in the world

In case you need it – this might come in handy.

A dictionary of all countries in the world with their ISO 3166 two digit country code.

SortedDictionary countries = new SortedDictionary {

{“AF”, “Afghanistan”},
{“AX”, “Aland Islands”},
{“AL”, “Albania”},
{“DZ”, “Algeria”},
{“AS”, “American Samoa”},
{“AD”, “Andorra”},
{“AO”, “Angola”},
{“AI”, “Anguilla”},
{“AQ”, “Antarctica”},
{“AG”, “Antigua and Barbuda”},
{“AR”, “Argentina”},
{“AM”, “Armenia”},
{“AW”, “Aruba”},
{“AU”, “Australia”},
{“AT”, “Austria”},
{“AZ”, “Azerbaijan”},
{“BS”, “Bahamas”},
{“BH”, “Bahrain”},
{“BD”, “Bangladesh”},
{“BB”, “Barbados”},
{“BY”, “Belarus”},
{“BE”, “Belgium”},
{“BZ”, “Belize”},
{“BJ”, “Benin”},
{“BM”, “Bermuda”},
{“BT”, “Bhutan”},
{“BO”, “Bolivia “},
{“BQ”, “Bonaire”},
{“BA”, “Bosnia and Herzegovina”},
{“BW”, “Botswana”},
{“BV”, “Bouvet Island”},
{“BR”, “Brazil”},
{“IO”, “British indian Ocean Territory”},
{“BN”, “Brunei Darussalam”},
{“BG”, “Bulgaria”},
{“BF”, “Burkina Faso”},
{“BI”, “Burundi”},
{“KH”, “Cambodia”},
{“CM”, “Cameroon”},
{“CA”, “Canada”},
{“CV”, “Cape Verde”},
{“KY”, “Cayman Islands”},
{“CF”, “Central African Republic”},
{“TD”, “Chad”},
{“CL”, “Chile”},
{“CN”, “China”},
{“CX”, “Christmas Island”},
{“CC”, “Cocos (keeling) Islands”},
{“CO”, “Colombia”},
{“KM”, “Comoros”},
{“CG”, “Congo”},
{“CD”, “Congo, The Democratic Republic of the”},
{“CK”, “Cook Islands”},
{“CR”, “Costa Rica”},
{“CI”, “Cote d’Ivoire”},
{“HR”, “Croatia”},
{“CU”, “Cuba”},
{“CW”, “Curacao”},
{“CY”, “Cyprus”},
{“CZ”, “Czech Republic”},
{“DK”, “Denmark”},
{“DJ”, “Djibouti”},
{“DM”, “Dominica”},
{“DO”, “Dominican Republic”},
{“EC”, “Ecuador”},
{“EG”, “Egypt”},
{“SV”, “El salvador”},
{“GQ”, “Equatorial guinea”},
{“ER”, “Eritrea”},
{“EE”, “Estonia”},
{“ET”, “Ethiopia”},
{“FK”, “Falkland Islands (Malvinas)”},
{“FO”, “Faroe Islands”},
{“FJ”, “Fiji”},
{“FI”, “Finland”},
{“FR”, “France”},
{“GF”, “French Guiana”},
{“PF”, “French Polynesia”},
{“TF”, “French Southern Territories”},
{“GA”, “Gabon”},
{“GM”, “Gambia”},
{“GE”, “Georgia”},
{“DE”, “Germany”},
{“GH”, “Ghana”},
{“GI”, “Gibraltar”},
{“GR”, “Greece”},
{“GL”, “Greenland”},
{“GD”, “Grenada”},
{“GP”, “Guadeloupe”},
{“GU”, “Guam”},
{“GT”, “Guatemala”},
{“GG”, “Guernsey”},
{“GN”, “Guinea”},
{“GW”, “Guinea-Bissau”},
{“GY”, “Guyana”},
{“HT”, “Haiti”},
{“HM”, “Heard Island and Mcdonald Islands”},
{“HN”, “Honduras”},
{“HK”, “Hong Kong”},
{“HU”, “Hungary”},
{“IS”, “Iceland”},
{“IN”, “India”},
{“ID”, “Indonesia”},
{“IR”, “Iran,”},
{“IQ”, “Iraq”},
{“IE”, “Ireland”},
{“IM”, “Isle of Man”},
{“IL”, “Israel”},
{“IT”, “Italy”},
{“JM”, “Jamaica”},
{“JP”, “Japan”},
{“JE”, “Jersey”},
{“JO”, “Jordan”},
{“KZ”, “Kazakhstan”},
{“KE”, “Kenya”},
{“KI”, “Kiribati”},
{“KP”, “Korea, Democratic People’s Republic of”},
{“KR”, “Korea, Republic of”},
{“KW”, “Kuwait”},
{“KG”, “Kyrgyzstan”},
{“LA”, “Lao People’s Democratic Republic”},
{“LV”, “Latvia”},
{“LB”, “Lebanon”},
{“LS”, “Lesotho”},
{“LR”, “Liberia”},
{“LY”, “Libya”},
{“LI”, “Liechtenstein”},
{“LT”, “Lithuania”},
{“LU”, “Luxembourg”},
{“MO”, “Macao”},
{“MK”, “Macedonia,”},
{“MG”, “Madagascar”},
{“MW”, “Malawi”},
{“MY”, “Malaysia”},
{“MV”, “Maldives”},
{“ML”, “Mali”},
{“MT”, “Malta”},
{“MH”, “Marshall Islands”},
{“MQ”, “Martinique”},
{“MR”, “Mauritania”},
{“MU”, “Mauritius”},
{“YT”, “Mayotte”},
{“MX”, “Mexico”},
{“FM”, “Micronesia,”},
{“MD”, “Moldova, Republic of”},
{“MC”, “Monaco”},
{“MN”, “Mongolia”},
{“ME”, “Montenegro”},
{“MS”, “Montserrat”},
{“MA”, “Morocco”},
{“MZ”, “Mozambique”},
{“MM”, “Myanmar”},
{“NA”, “Namibia”},
{“NR”, “Nauru”},
{“NP”, “Nepal”},
{“NL”, “Netherlands”},
{“NC”, “New Caledonia”},
{“NZ”, “New Zealand”},
{“NI”, “Nicaragua”},
{“NE”, “Niger”},
{“NG”, “Nigeria”},
{“NU”, “Niue”},
{“NF”, “Norfolk Island”},
{“MP”, “Northern Mariana Islands”},
{“NO”, “Norway”},
{“OM”, “Oman”},
{“PK”, “Pakistan”},
{“PW”, “Palau”},
{“PS”, “Palestinian Territory, Occupied”},
{“PA”, “Panama”},
{“PG”, “Papua New Guinea”},
{“PY”, “Paraguay”},
{“PE”, “Peru”},
{“PH”, “Philippines”},
{“PN”, “Pitcairn”},
{“PL”, “Poland”},
{“PT”, “Portugal”},
{“PR”, “Puerto Rico”},
{“QA”, “Qatar”},
{“RE”, “Reunion”},
{“RO”, “Romania”},
{“RU”, “Russian Federation”},
{“RW”, “Rwanda”},
{“BL”, “Saint Barthelemy”},
{“SH”, “Saint Helena,”},
{“KN”, “Saint Kitts and Nevis”},
{“LC”, “Saint Lucia”},
{“MF”, “Saint Martin (French Part)”},
{“PM”, “Saint Pierre and Miquelon”},
{“VC”, “Saint Vincent and the Grenadines”},
{“WS”, “Samoa”},
{“SM”, “San Marino”},
{“ST”, “Sao Tome and Principe”},
{“SA”, “Saudi Arabia”},
{“SN”, “Senegal”},
{“RS”, “Serbia”},
{“SC”, “Seychelles”},
{“SL”, “Sierra Leone”},
{“SG”, “Singapore”},
{“SX”, “Sint Maarten (Dutch part)”},
{“SK”, “Slovakia”},
{“SI”, “Slovenia”},
{“SB”, “Solomon Islands”},
{“SO”, “Somalia”},
{“ZA”, “South africa”},
{“GS”, “South Georgia and the South Sandwich Islands”},
{“ES”, “Spain”},
{“LK”, “Sri Lanka”},
{“SD”, “Sudan”},
{“SR”, “Suriname”},
{“SJ”, “Svalbard and Jan Mayen”},
{“SZ”, “Swaziland”},
{“SE”, “Sweden”},
{“CH”, “Switzerland”},
{“SY”, “Syrian Arab Republic”},
{“TW”, “Taiwan, Province of China”},
{“TJ”, “Tajikistan”},
{“TZ”, “Tanzania,”},
{“TH”, “Thailand”},
{“TL”, “Timor-leste”},
{“TG”, “Togo”},
{“TK”, “Tokelau”},
{“TO”, “Tonga”},
{“TT”, “Trinidad and Tobago”},
{“TN”, “Tunisia”},
{“TR”, “Turkey”},
{“TM”, “Turkmenistan”},
{“TC”, “Turks and Caicos Islands”},
{“TV”, “Tuvalu”},
{“UG”, “Uganda”},
{“UA”, “Ukraine”},
{“AE”, “United Arab Emirates”},
{“GB”, “United Kingdom”},
{“US”, “United States”},
{“UM”, “United States Minor Outlying Islands”},
{“UY”, “Uruguay”},
{“UZ”, “Uzbekistan”},
{“VU”, “Vanuatu”},
{“VA”, “Vatican City State”},
{“VE”, “Venezuela,”},
{“VN”, “Vietnam”},
{“VG”, “Virgin Islands, British”},
{“VI”, “Virgin Islands, U.S.”},
{“WF”, “Wallis and Futuna”},
{“EH”, “Western Sahara”},
{“YE”, “Yemen”},
{“ZM”, “Zambia”},
{“ZW”, “Zimbabwe”}
};

Using EF code first with an mdf database in App_Data

I’ve been playing around some more with EF code first. Today’s task was to try to get it to work with a .mdf file located in the App_Data folder of the web project.

It wasn’t much fun.

I hijacked the existing aspnetdb.mdf and tried to get that to work with EF Code First. Short answer: don’t bother, for these reasons:

  1. I couldn’t get EF code first to create my Category table
  2. So then I’d created the table manually myself in SQL Server Express, but EF still wouldn’t recognise it – “Invalid object name dbo.Category”
  3. So then I decided to start with a new .mdf file and yay EF code first created my Category table
  4. run aspnet_regsql.exe on it to get the ASP.NET Membership tables in there – fail
  5. Visual Studio has a lock on the mdf file – delete it from VS’s Server Explorer, run aspnet_regsql, yay
  6. Open DB in SQL Server Management studio to see what my database looks like – fail, locks on file

I’m not sure how to debug EF code first, I mean if it doesn’t work (i.e. your table isn’t created) you have no idea why.

So I guess I got it working in the end, but all the problems with the files being locked made me realise it’s not worth the hassle so I started again with a normal database inside SQL Express.

  1. Add your connection string:
    <add name="SiteDB" 
         connectionString="data source=.\SQLEXPRESS;Initial Catalog=Site;Integrated Security=SSPI;"
         providerName="System.Data.SqlClient" />

I recommend explicitly setting a connection string otherwise your database will have a big silly namespaced name. You don’t need to create the database though, EF will do that much for you.

  1. Run your app and hit the Categories page so that EF will create the tables
  2. Run aspnet_regsql.exe on it to add the membership tables
  3. Change your membership connection string to point to the same DB – or tell the AspNetSqlMembership provider to use that connection string blah blah

Announcing MVC3 starter site

Inspired by Rob Conery’s MVC starter site, I’ve created an MVC3 starter site.

It basically takes Visual Studio’s File -> New Project -> ASP.NET MVC 3 Web Application and builds on that, adding all the things we need to get started – IOC (Unity), Unit of work pattern, Logging (NLog), Reporting, oh and some CSS from freecsstemplates.org.

It’s also my first open source project in a while…

You can download it from codeplex at http://mvc3starter.codeplex.com

mvc3 starter app screenshot

Organise your digital life – Part 1 Photos

I got my first digital camera in 1998. It was a low-end Kodak model but it was still quite expensive, around US$200, and it took photos at a whopping 493×373 resolution. I think it held about 20 photos in it’s internal memory. It didn’t even have a flash!

Back then I didn’t have much of a strategy for organising my digital photos – and the photos were so crap I didn’t use it all that often anyway:

My student workspace in 1998, taken on my 1998 era Kodak digital camera. Spray-painted keyboard and spray-painted big-ass server case, oh yeah. Looks like the 14″ monitor is running Winamp.

My next digital camera was in 2003 – a Pentax Optio S, the original “Altoids tin” camera. This camera served me well and I still have it lying around somewhere. Alas the flimsy battery compartment lid broke off but other than that it still works.

Pentax Optio S

Anyway, about 2004 I realised I needed a strategy for organising all of my photos, and the strategy I chose and which have stuck to is this.

Under My Pictures I have a Camera folder. In the Camera folder is a folder for each year:

Each year is divided up into sub-folders named after the locations where I have taken photos.

The naming format of each subfolder is “## – Place name”. E.g. in 2010 the first place I took photos was at my friend Kelvin’s wedding in New Plymouth so that is the first folder listed.

It’s really quite straightforward (and I think worthwhile) to organise your photos into folders based on year and location.

Finally, at the photos level is where I get a bit more pedantic (and it’s probably not necessary).

I usually rename my folders “Event – ##” or if the photo contains people then “Event – ## – Person Name”. I use a tool for renaming the photos in this way (an old version of ACDSee that came bundled with my Pentax camera in 2003) but I still need to manually add my friend’s names to the filename.

And that’s it. I know that most people aren’t going to bother to rename each photo the way I do (fair enough), but I think it’s not much effort to organise your photos into folders by year and location. And for god’s sake, delete your crap photos. I am notoriously economical with photos, i.e. I might take 10 or 20 shots of a landmark but I’ll only keep 1 or 2 of them.

Improve ASP.NET page performance using Database Engine Tuning Advisor

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!