ORM and database indexes

Question

What approach do you have towards creating and maintaining database indexes when using ORM such as NHibernate/Hibernate.

Since the ORM is generating the queries, are there any tools you could recommend that could analyze query plans of those and suggest the kind of indexes that should be created?

My current approach is ... wait until something works slow and then find the slow query and optimize it ... but this is sort of lame isn't it? My goal is not to end up with tens or hundreds of indexes of which nobody knows which are actually being used by the system and which aren't. So again index maintenance.

My environment is NHibernate + SQL Server 2005.

Answer 1

I find that the columns that need indexing are typically "obvious". By that I mean if you create queries like "select p from Person p where p.surname = :surname" then whatever column surname refers to needs an index.

Likewise every foreign key should be indexed.

And no I don't wait until performance is actually a problem. Indexes are just something I do right from the start.

Oh the other thing I wanted to add was that most (if not all) ORMs have the ability to turn on statement logging. These often aren't particularly readable (single line, table names of t0, t1, t2, etc) but this could tell you what queries were run and how often.

Answer 2

The standard tools you would use to analyse slow queries / poor indexing apply whether or not you are using an ORM. You can use sql server profiler to examine the sql statements that are running against your database and then use the index plan features in the query window in sql server management studio / sql query analyser to see the details of your query plans and get an idea of which indexes you may need to add.

You can also use the Database Engine Tuning Advisor in sql management studio, although whether or not that tool is actually more useful than simply spending some time thinking about your database design and querying patterns is open to question.