nHibernate Database, First Look

February 15, 2010 at 1:39 pm (nHibernate, SQL Server 2008) (, , , )

I’m getting my first look at a full-fledged nHibernate database developed by consultants for our company. I thought I’d share my initial impressions. I’ll be capturing trace events from the database over the next couple of weeks, so I’ll be following up on the behavior of nHibernate within this database as well.

The first thing I saw & thought was, “Foreign key constraints. Thank the gods.” That really is good news. I was frankly concerned that they might go with the “let the code handle it” approach. There are quite a few null columns. I’m also seeing tons & tons of nvarchar(255) which must the default string size. Lots of bit fields too. They also used bigint in a lot of places too. None of this is definitively good or bad, just observations.

There are tables that lack a primary key. That raises a bit of a concern. The primary keys I’ve looked at have all been clustered, which isn’t too problematic since that’s probably the primary access path. There are a few unique constraints on some of the tables too.

Overall though, I don’t see anything that, at first glance, makes me want to run screaming from the room (or pick up a big stick & start looking for developers).  The devil is no doubt in the details. Time to get started looking at the trace events.


  1. Shawn said,

    I have been having to deal with SQL Profiler a good bit lately on an application to figure out what it is doing (or actually what it is NOT doing). Would you be able to share what you configure your trace to capture in order to get a good picture of what the database is doing (if it is other than a default trace)?

  2. scarydba said,

    Currently I’m just running a slightly modified default trace. I only care about RPC Complete and SQL Batch Complete for initial data collection. Just make sure that you’re not running those through the GUI and you’re outputing to file and you’ll effectively have no impact on the system you’re monitoring at all. You just need to deal with the data you collect.

    After that, it really depends on what you need to look at. For example, we have systems that get quite a few deadlocks, so we capture deadlock graphs in the trace, but other systems don’t need it. Check out Brad McGehee’s excellent book for more details: http://www.amazon.com/Mastering-Server-Profiler-Brad-McGehee/dp/1906434158/ref=sr_1_1?ie=UTF8&s=books&qid=1266327665&sr=8-1

  3. ivowiblo said,

    Great and subtle post.
    A few comments:

    -There are quite a few null columns.
    You should define it in the mappings. NHibernate doesn’t know what you want if you don’t tell him.

    – I’m also seeing tons & tons of nvarchar(255)
    It’s the default, you could change it in the mapping

    -Lots of bit fields too
    It’s for booleans. You can change the type in the mapping and add a substitution in the configuration.

    – They also used bigint in a lot of places too.
    I suppose you have properties o type long, that’s correct?

    – There are tables that lack a primary key.
    Mhhh. Many-to-Many relationships doesn’t force pk’s, at least if you use collection type. That’s because you could have repeated stuff in a collection. I think you could manage this in the mapping.

    – There are a few unique constraints on some of the tables too.
    You can add your unique constraints by using NaturalId mapping: I read somewhere that it’s useful in caching, but I don’t know.

    Hope it helped!

  4. scarydba said,

    Useful info thanks. I’m still exploring this stuff, so information is good.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: