ROWLOCK

hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMERThe only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our querie
s
> on that table
> Thanks in advance
> Erdem KEMER
>|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing th
e
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. I
f
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especiall
y
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduc
e
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.sql