October 25, 2016 at 1:15 pm
Phil Parkin (10/25/2016)
Luis Cazares (10/25/2016)
However, the OUTPUT clause will generate an empty result set when no rows are updated, so that should work.And with a single statement and the error handling just throwing the original error, I see no reason for the TRY...CATCH...
I should test stuff more before I make assumptions :blush:
But that does lead me to a question. Is there a reason that you chose to use a table variable rather than simply doing this?
UPDATE cteTest1SET StartedAt = GETDATE()
OUTPUT inserted.PK, inserted.Col1, inserted.Col2;
Honestly, I wasn't sure it could be done. I've never returned the results like this, most of the time I insert them somewhere else.
October 25, 2016 at 1:20 pm
DesNorton (10/25/2016)
Luis Cazares (10/25/2016)
The reason for the CTE was to include the ORDER BY which isn't available in the simple UPDATE.The correct covering index also takes care of the order by
I wouldn't rely on a covering index for the order. The only way to ensure order is by using an ORDER BY. The index also requires additional space.
October 25, 2016 at 1:25 pm
Luis Cazares (10/25/2016)
Phil Parkin (10/25/2016)
Luis Cazares (10/25/2016)
However, the OUTPUT clause will generate an empty result set when no rows are updated, so that should work.And with a single statement and the error handling just throwing the original error, I see no reason for the TRY...CATCH...
I should test stuff more before I make assumptions :blush:
But that does lead me to a question. Is there a reason that you chose to use a table variable rather than simply doing this?
UPDATE cteTest1SET StartedAt = GETDATE()
OUTPUT inserted.PK, inserted.Col1, inserted.Col2;
Honestly, I wasn't sure it could be done. I've never returned the results like this, most of the time I insert them somewhere else.
Made the change and retested it – works fine & it's very compact code now.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 28, 2016 at 2:59 pm
I assume you're implementing some kind of work queue table. As you reach high concurrency levels, you may experience blocks because the shared lock on read can block the update lock. The other problem you may experience is redundant reads that don't necessarily cause a block.
I implemented a work queue table to get the 'next' job on a server with 48 cores. Even though the work queue table rarely had more than 1k rows, I would occasionally experience blocks. The other problem I ran into was redundant updates, in which multiple threads would receive the same record. They would play nice and not block each other, but it was adding additional overhead and updating some records multiple times. In one of my concurrency tests, I saw 460 updates to 300 rows (one of the rows was updated six times).
My strategy for a high concurrency work table:
1. Keep the work table as focused as possible. No joins, no reporting, only the columns needed to schedule the job. It should be entirely focused on providing the next job. Everything else should be moved to other tables.
2. Separate the inventory of work from the active work. Work that is not a candidate for the NEXT job should not be in the work table. That would include work that has already been finished. Every additional record will add some overhead.
3. Use only inserts and deletes for the active work table. Don't bother to update the record, just get rid of it. Use another table to capture the start time.
An example of my solution:
tActiveWork
- Col1 as BatchID
- Col2 as WorkSequence
- WorkID
tWorkList
- BatchID
- WorkSequence
- WorkID (Clustered Index Key)
- StartedAt
- FinishedAt
- Result
1. Schedule the batch of work
INSERT INTO tActiveWork()
SELECT BatchID, WorkSequence, WorkID
FROM tWorkList
WHERE BatchID = @BatchID
2. Determine the NEXT work
WITH CTE AS (
SELECT top 1 *
FROM tActiveWork WITH(READPAST)
WHERE BatchID = @BatchID
ORDER BY WorkSequence
)
DELETE FROM CTE
OUTPUT WorkID INTO @Something
3. Mark the NEXT work as started
UPDATE tWorkList SET StartedAt = GETDATE()WHERE WorkID = @WorkID;
---
#1 My work table is a heap because it rarely has more than 1k rows. Inserts into a heap won't cause locking because existing rows are not affected and there is no index to maintain.
#2 Use Readpast to skip already locked rows. By using the output from the delete, you're guaranted the record will not get selected twice. Only the thread that deleted it will output the ID.
#3 The update to the WorkList table can use the PK for a laser strike update of a single row.
Also, investigate using an in-memory work table if you have the option. That will likely improve performance even more and eliminate some of the concerns entirely.
I used this general model for my work table and it has been going strong now for over a year without a problem, running thousands of jobs each day.
Good luck.
Wes
(A solid design is always preferable to a creative workaround)
October 30, 2016 at 1:09 pm
Thank you for the detailed response, whenriksen.
You are right in thinking that this is for a work-queue table ... in this case, for queues of SSIS packages waiting to be executed, within different ETL jobs.
The volumes I am dealing with are much lower than yours and I wish to retain a history of items executed, so I do not intend to delete 'done' items.
But the READPAST hint is one I have not used before and it looks to be particularly suited to this type of function. I shall look into adding that to my existing solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 31, 2016 at 6:48 am
Phil Parkin (10/30/2016)
But the READPAST hint is one I have not used before ...
Where most folks have a genuine need for something like WITH(NOLOCK), I usually end up using READPAST, instead.
I have an "Audit staging table" where the data the data going out has as high a priority as that coming in. I use WITH(READPAST) there to ensure that the transfer job of existing rows in the table don't have to wait on anything. After all, why would I try to do a transfer on rows still in the process of being written. 🙂 That's another large advantage over WITH(NOLOCK) and it also means I don't need to do any dupe checks in the process because only completed rows will be affected.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2016 at 8:03 am
Phil Parkin (10/30/2016)
in this case, for queues of SSIS packages waiting to be executed, within different ETL jobs.The volumes I am dealing with are much lower than yours and I wish to retain a history of items executed, so I do not intend to delete 'done' items.
Same use case for my scenario.
In my implementation, I have five tables. Batch, Package List, Next Package, Active Batch Execution, Archive Batch Execution. I have two execution tables, one for the active batches, another for the archive of executions. I found that as my volumes grew, the size of the archive table was slowing down the update speed of the current batches. By splitting it, the currently executing batch can use a very small table for the high performance updates (start time, end time, result, etc). The archive table has an identical definition, other than indexing. When a batch of packages finishes, the execution results are moved to the archive table. I've unioned the two execution tables together in a view so I can report on work in progress and work completed. We keep a year of execution history so we can do some job analytics to help identify problems.
If you're interested, I can script out my objects so you can see my implementation. It has been running solid for about a year now. This is a general overview. There is some additional complexity because of the number of different sources, batches, packages and enterprise integration points, but the general strategy should be transferable.
On a side note, you may see some deadlocking in the SSISDB when using catalog.create_execution or catalog.set_execution_parameter_value in high volume situations. Microsoft uses serializable transactions and touches a number of tables in those SPs within the transaction. I believe the deadlock occurs on [internal].[execution_parameter_values]. Let me know if you experience that problem and I'll dig out the details for that as well.
Wes
(A solid design is always preferable to a creative workaround)
November 3, 2016 at 5:50 am
whenriksen (10/31/2016)
Phil Parkin (10/30/2016)
in this case, for queues of SSIS packages waiting to be executed, within different ETL jobs.The volumes I am dealing with are much lower than yours and I wish to retain a history of items executed, so I do not intend to delete 'done' items.
Same use case for my scenario.
In my implementation, I have five tables. Batch, Package List, Next Package, Active Batch Execution, Archive Batch Execution. I have two execution tables, one for the active batches, another for the archive of executions. I found that as my volumes grew, the size of the archive table was slowing down the update speed of the current batches. By splitting it, the currently executing batch can use a very small table for the high performance updates (start time, end time, result, etc). The archive table has an identical definition, other than indexing. When a batch of packages finishes, the execution results are moved to the archive table. I've unioned the two execution tables together in a view so I can report on work in progress and work completed. We keep a year of execution history so we can do some job analytics to help identify problems.
If you're interested, I can script out my objects so you can see my implementation. It has been running solid for about a year now. This is a general overview. There is some additional complexity because of the number of different sources, batches, packages and enterprise integration points, but the general strategy should be transferable.
On a side note, you may see some deadlocking in the SSISDB when using catalog.create_execution or catalog.set_execution_parameter_value in high volume situations. Microsoft uses serializable transactions and touches a number of tables in those SPs within the transaction. I believe the deadlock occurs on [internal].[execution_parameter_values]. Let me know if you experience that problem and I'll dig out the details for that as well.
This is excellent info, thank you.
At the moment, my solution is in testing and so far works well, but thanks for the offer. I will keep it in mind.
I have a question about the READPAST hint. Have you ever found that it has locked rows (at a page level) which are not and have not been processed? Resulting in job steps being missed from your job?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 3, 2016 at 10:20 am
Phil Parkin (11/3/2016)
I have a question about the READPAST hint. Have you ever found that it has locked rows (at a page level) which are not and have not been processed? Resulting in job steps being missed from your job?
Yes, that is very possible. If your update isn't using the Top 1 approach, SQL may think that multiple rows are candidates for the update and lock the page instead. Page locks are more efficient than rowlocks, but can lock unnecessary rows. You'll need to read up on lock types and lock escalation.
A few things you can try:
- rowlock hint to limit the lock type within a query,
- sp_indexoption to disable page level locks for the table/index overall,
- changing the column order in your index, or the asc/desc setting for specific columns to minimize index updates
Make sure you're aware of the implications of each option. Troubleshooting concurrency issues can be time-consuming, but you'll need to understand why locks/blocks are happening before you can determine how to resolve them.
I do not know which locks READPAST looks for, but I assume it looks for U and X locks and ignores the basic shared lock (IS) used for general select statements. Maybe one of the SQL gurus can chime in on that.
Rowlocks are more expensive than table locks, but they are obviously more focused. If SQL feels all the rows in a page need to be locked, it may still do so, but forcing row locks instead will be much more time consuming and may exasperate your concurrency issues further.
You've reached the point where pretty much every answer will include "It Depends" and your specific implementation and needs will determine exactly what you need to do. As I suggested before, design your drain table to meet this single purpose. It can feel counter-intuitive to normal database design, but it needs to be very focused to avoid unnecessary activity. If there are any queries hitting it that are not directly related to getting the next job, you're overusing it.
Wes
(A solid design is always preferable to a creative workaround)
November 3, 2016 at 10:59 am
I took a look at how I populate my drain table. I'm using TABLOCKX to lock the table entirely before I insert. This causes all other activities to wait until the insert is complete.
With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level
https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
In this scenario, TablockX will force your READPAST to wait while another batch queues up. The competing reads will be delayed while it is happening, although the insert will complete a little bit quicker. For me, the slight delay is better than skipping rows or ending up in a deadlock.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply