May 16, 2008 at 1:37 am
Hello
I am trying to insert records into a fairly large table (>25Mil records). The table contains notes for a call center (CustomerNotes), and contains all the text entered by the agents during phone conversations. Since the software reading from the table is not an in house app i'm not allowed to modify the tables in any way, and I cant add any tables to the database. The database calls are all in the code (which I don't have) and horribly don't have any (NOLOCK)'s so blocking is a serious issue on this table.
Another internal system allows the bulk inserting of records into this table, and I have recently inherited this system (its under my control). At the moment these bulk inserts are run daily to weekly and was discovered that only about 50% of the records ever make it into the destination table.
The original strategy:
1) Read the rows from the source tables (on ServerA) with a cursor
2) Join the single row from the cursor onto the destination table (on ServerB) to calculate the next NoteId for the customer
3) Insert this row into the table (on serverB).
I know that both cursors and cross server queries are both performance no-no's so I removed both and put the whole query into a transaction to make sure if it deadlocked I could rollback and try again. Unfortunately the next NoteId needs to be calculated for each insert (hence the grouping).
My strategy
1) Precalculate everything (other than the NoteId)
2) Move the data from ServerA to ServerB into a new database
3)
INSERT INTO CustomerNotes
SELECT tn.CustomerId, ISNULL(MAX(cn.NoteId), 0) + 1 AS [NoteId], tn.NoteText
FROM TempNotes WITH (NOLOCK) AS tn
LEFT JOIN CustomerNotes WITH (READPAST) AS cn
ON tn.CustomerId = cn.CustomerId
GROUP BY tn.CustomerId, tn.NoteText
If I open a transaction (in another query window) and lock some of the rows the whole query just waits indefinitely. I removed the insert and just tried the select, and it does not READPAST - 15 hours later (on our test server) it still hadn't selected the rows. Without the locked rows the select runs in 1min19sec.
The code above is run in a while loop to keep trying to insert the locked records, rolling back if locking occurs, but this was all removed for my tests.
Any Ideas / Suggestions?
Regards
Rob
May 16, 2008 at 2:07 am
Have you considered enabling snapshot isolation level?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2008 at 8:36 am
Thanks for the speedy reply 🙂
We do have it enabled for that database, and I'm looking at breaking the set into chunks and inserting them in transactions with timeouts.
What I found is (on inserting 53000 records):
1) Without locking the insert happens in about 3min45s
2) Without locking snapshot isolation inserts in 4min04s
3) With locking, and snapshot isolation the insert completes 4min10s after the lock is resolved.
I'm going to put this in as an interim solution and get the original developers to auto-increment the key on the table, since thats the real problem.
Still worries me that READPAST is a documented feature and it doesn't work as expected (or at all for more than one record).
May 16, 2008 at 3:56 pm
Actually, this all seems pretty consistent with the documented behavior for READPAST. From BOL:
READPAST
Specifies that the Database Engine not read rows that are locked by other transactions. Under most circumstances, the same is true for pages. The Database Engine skips past the rows or pages instead of blocking the current transaction until the locks are released. READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK. When READPAST is specified both row-level and page-level locks are skipped. READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified. READPAST cannot be specified for tables in the INTO clause of an INSERT statement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 19, 2008 at 5:32 am
Hello, Perhaps I don't understand BOL properly...
Firstly - I ran this only as a select in my tests above, no insert's, update's or delete's in sight and it still doesn't work.
Secondly - When I was using it in an insert (never an update), I was using it in the pre-selection of data (to calculate a sequential key) as specified in BOL not in the into.
Readpast seems to works for a single record (or very small sets) not a batch, and it is not possible for me to have a while loop joining every one (of 52333 rows) onto a 25 million row table to get the notes into the table.
My tests on SNAPSHOT (an excellent solution) have their own problems, if the locking query was a select it works perfectly if its an update it blocks, although I will get round this using timeouts, batch processing and Try-Catch blocks, to retry locked records in another batch.
Regards
Robin
May 19, 2008 at 5:39 am
It could be that you're getting a lock escalation to a table lock. Readpast only goes past page and row locks. A table lock will still block it.
I have no personal experience with readpast, so I can't really help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2008 at 6:49 am
NOLOCK would just give you a bunch of inconsistent data anyway, so using it here is probably a really bad idea (it is usually a bad idea to use that hint).
READPAST is going to be a problem in your situation as suggested because of lock escalation - even page locks will give you some trouble because it is going to skip large amounts of data.
I would suggest trying to create an index with leaf-level data that covers your query. As long as you are not getting a table lock, you may be able to get your query to read the index and skip the table (thus reading the previously committed version). Your query will only be impacted during the index update, so this may be able to at least significantly shorten the window.
May 19, 2008 at 8:38 am
Hello
Ironically the actual problem here is the lack of nolock's in the user application which is whats blocking me in the first place. They are absolutely essential, and I don't quite know what you mean by "a bunch of inconsistent data"? The application reads should only be shared locks since the data isn't edited, a new record is added each time like an audit table.
READPAST skipping data is not a problem since the code runs in a while loop, so if it misses it on the first loop it will catch it in the next loop when the application read has (hopefully) released the lock. The insert takes over 4 min on 50000 records which is more time than a call center agent spends on the call with the record locked. (BOL suggests the only real use of READPAST is in write queues - using a while loop)
Please advise how I can read the index, as this would also be a valid solution, since i'm only reading the destination table to increment the clustered key. The entire table doesn't get locked in this query, so thats not a problem either.
As soon as I have some time I'm going to look into a variation of GilaMonster's SNAPSHOT suggestion, and join on the destination table into a temp table to get the 'latest' (possibly dirty) key, and then use try catch's to insert the records, incrementing the key if the insert fails due to uniqueness constraints and try again till it goes in.
Thanks for all the suggestions and help - this is a great community.
🙂
Robin
May 19, 2008 at 8:55 am
NOLOCK reads dirty pages, so by inconsistent data I mean your application may present data not only uncommitted data that could be rolled back, but you cound, in fact, read a pre-updated record and a post-updated record that need to be in sync for your data to make sense.
A simple example is a GL that needs to be in balance, if you have an insert that adds a record and a corresponding reversal, you could query and get a result that only includes one of the two records - giving you an out-of-balance GL.
Now, onto the indexing option. If the query being run by the UI against this table does not get all of the fields in the table (even if it does you can do this, but it will end up inefficient) you can create an index on the table that will completely satisfy the query. If this index is better suited to handle the request (better index key), the optomizer will choose this index rather than using the table. If all of the returned fields are either in the index key or are added as leaf-level fields, the query will use the index entirely and not doo a bookmark lookup.
Oddly, if SQL does not do the bookmark lookup, locks on the table are ignored and only locks on the index will prevent access to the data. Your index will not get exclusive locks until the index is being updated, so it should be a smaller window.
If you cannot get the optimizer to choose your index because the clustered index is perfect for the retrieval, you would have to use a plan guide to get this to work. Also, a plan guide would also allow you to add the NOLOCK hint to your query without changing the application code - that is what they are there for. I would, however, look at using an indexing workaround or snapshot isloation before hacking in a NOLOCK query hint - these should be a last-resort.
May 20, 2008 at 12:00 am
Michael Earl (5/19/2008)
NOLOCK reads dirty pages, so by inconsistent data I mean your application may present data not only uncommitted data that could be rolled back, but you cound, in fact, read a pre-updated record and a post-updated record that need to be in sync for your data to make sense.
In addition, it is possible hen using nolock to read rows more than once and to mis rows entirely. Especially if there are inserts going against the table that force page splits.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 1:52 am
The queries are already reading the indexes not the tables, I moved the groupings around and checked the performance and unfortunately it doesn't make much difference either.
What I have is a clustered key on ServerId, CustomerId, NoteId in the CustomerNotes table and the only reason I have to read the table (before inserting) is to get the next CustomerId to insert. The best solution would be if there was a way to get Sql Server to generate the next Id for me on some sort of an insert trigger, because the CustomerID is sequential for the customer, not for the table.
Thanks for the info on the evils of nolocks, I have always been told to put them in by all my bosses along the way, I will definitely give it some thought before using them now.
Rob
May 20, 2008 at 5:13 am
They are bosses because they don't know how to do the work...
Create another table to keep track of the next "CustomerID". Or, if you have a customer table and the number is sequential based on those records, add a field to that table and increment it when you need a new one. A function that increments the number and returns it would be easy to use in your insert.
Be careful of two operations for the same customer at the same time generating the same number - this is a place in which you will need to get a rowlock to prevent duplicates.
April 16, 2015 at 8:54 am
It may be possible if you are using set-based SQL and avoiding cursor loops to use SQL Server's ROW_NUMBER ( ) OVER (ORDER BY ... ) function and add a base ID # to the calculated RowNumber. See
April 16, 2015 at 8:59 am
NO LOCK will allow reads of "dirty data" -- that's what's meant by "inconsistent data". Read up on why not to use NO LOCK or when it is OK. (Reading static data, say from a data warehouse or data mart that is not in the process of being written to, or a static lookup table.)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply