• Notice how the leading edge of three of the indexes is the [Kind] column. That starts to look like a better candidate for your clustered index than the primary key (although, as a varchar(256) it's an amazingly bad choice for a clustered index). But that would be the start of trying to solve this dilema. You have the cluster (which Gail explained, never needs a lookup) on the PK, but, at least from what you've shown, the PK is not being used to determine access to the table. That at least suggests you should be looking elsewhere for the PK.

    Also, having the same column, [Kind], as the leading edge of all your indexes, will cause the optimizer to not use some indexes that might be better suited to a given query and instead use an index that isn't as well suited. This is because the histogram, part of the statistics of the index, is only made on the first column of the index. It's not the only thing used to determine index usage, but it is a primary one.

    Don't even get me started on just how bad nHibernate could be making your queries. Just a hint, unless your developers have specifically coded for it, nHibernate doesn't use the data type size when setting it's parameters, but uses the length of the value passed. So 'Dog' becomes varchar(3). 'Horse' is varchar(5). 'Elephant' is a varchar(8). When each of these is passed to SQL Server, a whole new execution plan is created instead of reusing an existing one, which would have happened if the datatype was set to varchar(125) as it should have been. Oh yes, you have lots of joy coming from nHibernate. I'd suggest looking up the N+1 problem and seeing if you can find evidence of that in your code because, again, it's extremely common unless your developers specifically coded around it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning