|
|
Posted by Erland Sommarskog on 09/19/05 00:40
laurenq uantrell (laurenquantrell@hotmail.com) writes:
> I have a table dbo.myTableName to which hundreds of users are
> UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
> against that table 24/7.
> I am tring to remove the slowdown caused by rows that might be in use
> when users run a query that looks like: SELECT myID, myRow1 etc. FROM
> dbo.myTableName WITH (READPAST) WHERE StartDate = @DateParam
I echo what Hugo said: try to avoid NOLOCK and READPAST as long as you
can. Rather investigate if indexes can help. A query like the one
above, might excute faster with an index on StartDate.
As for whether you should use NOLOCK or READPAST, there are two things
consider: a) what result do you want and b) and what is your blocking
problem?
a) Both NOLOCK and READPAST can result in the queries giving incorrect
result.
NOLOCK means that you read uncommitted data, which could violate business
rules, and that will be rolled back the next second (or is in fact in the
process of being rolled back). In more devilish cases an updating process
may first delete some data to re-inserted it in some new version, leading
to that you get no data at all.
This last thing is also very typical for READPAST. "SELECT SUM(amt) FROM tbl
WITH (READPAST) WHERE date = @somdate". Oops, a bunch of rows were locked,
and you get back a value which os 40% of the right one.
If the queries that run are reports that is mainly interested in general
trends, and not used for reconcilliation etc, then it may be OK to run
with NOLOCK, but you should really investigate the consequences.
b) READPAST will not help if SELECTs that performs table scans block
UPDATE statements, the SELECT gets a lock on table level, and the updaters
will have to wait. READPAST makes sense if selects are fast, but your
UPDATE/INSERT operations are complex and long-running.
Generally, first try to see if better indexing can help. But if you have
queries that comes from search functions where the user can select
conditions wildly can be difficult to have an index for everything.
Investing in a second server for reports, may be worth the effort.
In SQL 2005 there is a new isolation level, SNAPSHOT. With this isolation
level, SELECT statements can run on a snapshot of the state of the database
in a given moment. This can help a lot to prevnent this sort of problems.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|