title: Understanding effects of with (updlock, rowlock) on a query in SQL Server
body: <p>I am using NHibernate to query data from a reporting database and would like to optimize READ performance. "Dirty reads" are not a concern at all. NHibernate provides Lock Modes. If I use the LockMode.UpgradeNoWait mode I can see that in the generated SQL a 'with (updlock, rowlock)' appears. I've been reading up on this, but my SQL is unfortunately not sophisticated  enough to really understand the ramifications. I believe what I really want is a 'with (nolock)' statement to appear, but NHibernate does not see to provide any way of doing that.</p>
  
  <p>My question then is this: Let's say my table is being written to in other transactions.  Will the 'with (updlock, rowlock)' hint in a query against this table cause it to return any faster? Can it hurt performance? The query is a very simple select against a single table. No joins. There is a non-clustered index on the table that covers the query. </p>