Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

MsSql2005Dialect paging queries does not scale

Description

During release of a new version our application we discovered that the disk activity increased and hit the roof of what the database server was able to handle.

It turns out that we modified our configuration to use MsSql2005Dialect instead of MsSql2000Dialect. The flaw was not visible during our test runs but when letting all the users access the new version the site went down in a DoS.

After closer examining we determined that the culprit was that data was written and read to/from the TempDB in an awful rate. So we had a look at the generated queries and there seem to be a new way of paging in the 2005 dialect. However this way of paging utilize a implicit temporary table, which in turn is created in the TempDB.

This is probably something that is more of a scalability issue, but our database contains over 20 million rows and we have about 100 requests a second. Perhaps there should be a way to hint the paging mechanism of this fact that the dataset is large.

The workaround is easy just to go back to the MsSql2000Dialect but this is probably something that should be fixed.

Environment

None

Activity

Show:

OrenE 
November 21, 2008 at 7:30 PM

We are using the recommended approach for that version, I don't see anything that we can do here that wouldn't cause adverse results elsewhere.
The recommended solution is to created a custom dialect to teach NHibernate how to deal with the specific scenario that you have.

OrenE 
November 21, 2008 at 7:29 PM

Charlie,
I am afraid that this is something that NHibernate can't help you much with and that you need to take this up with the SQL team.

NHibernate is using the recommended approach for SQL 2005 and paging:
http://msdn.microsoft.com/en-us/library/ms186734.aspx

The SQL 2000 dialect has another problem, it can only do top paging, so if you want to get the next 10 rows from the 100th row, it will load the first 110 rows.

Fabio Maulo 
August 23, 2008 at 4:56 PM

Do you can try using NH2.0 ?

BTW there is another possibility... you can inherit from MsSql2000Dialect and create your own dialect; if you do it please send a patch here.
Thanks.

Won't Fix

Details

Assignee

Reporter

Components

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created August 23, 2008 at 6:52 AM
Updated January 6, 2009 at 9:24 AM
Resolved November 21, 2008 at 7:30 PM
Who's Looking?