Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>From my perspective your server has a serious performance problem. Even if we assume that none of the records in the query </p> <pre><code>select some_col with (nolock) where id_col between 57000000 and 57001000 </code></pre> <p>was in memory, it shouldn't take 21 seconds to read the few pages sequentially from disk (your clustered index on the id_col should not be fragmented if it's an auto-identity and you didn't do something stupid like adding a "desc" to the index definition).</p> <p>But if you can't/won't fix that, my advice would be to make the update in small packages like 100-1000 records at a time (depending on how much time the lookup function consumes). One update/transaction should take no more than 30 seconds. </p> <p>You see each update keeps an exclusive lock on all the records it modified until the transaction is complete. If you don't use an explicit transaction, each statement is executed in a single, automatic transaction context, so the locks get released when the update statement is done.</p> <p>But you can still run into deadlocks that way, depending on what the other processes do. If they modify more than one record at a time, too, or even if they gather and hold read locks on several rows, you can get deadlocks.</p> <p>To avoid the deadlocks, your update statement needs to take a lock on all the records it will modify at once. The way to do this is to place the single update statement (with only the few rows limited by the id_col) in a serializable transaction like</p> <pre><code>IF @@TRANCOUNT &gt; 0 -- Error: You are in a transaction context already SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- Insert Loop here to work "x" through the id range BEGIN TRANSACTION UPDATE SOMETABLE SET [some_col] = dbo.ufn_SomeFunction(CONVERT(NVARCHAR(500), another_column)) WHERE [some_col] = 243 AND id_col BETWEEN x AND x+500 -- or whatever keeps the update in the small timerange COMMIT -- Next loop -- Get all new records while you where running the loop. If these are too many you may have to paginate this also: BEGIN TRANSACTION UPDATE SOMETABLE SET [some_col] = dbo.ufn_SomeFunction(CONVERT(NVARCHAR(500), another_column)) WHERE [some_col] = 243 AND id_col &gt;= x COMMIT </code></pre> <p>For each update this will take an update/exclusive key-range lock on the given records (but only them, because you limit the update through the clustered index key). It will wait for any other updates on the same records to finish, then get it's lock (causing blocking for all other transactions, but still only for the given records), then update the records and release the lock.</p> <p>The last extra statement is important, because it will take a key range lock up to "infinity" and thus prevent even inserts on the end of the range while the update statement runs.</p>
    singulars
    1. This table or related slice is empty.
    1. PORunning large queries in the backgroud MS SQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTToni
    1. USTToni
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PORunning large queries in the backgroud MS SQL
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COi am sorry there might have been a misunderstanding - running the stored procedure between 57000000 and 57001000 took 21 seconds.. as you can see the select of 100 items took 0 sec... anyway your answer seems reasonable and i will give it a try and post back. thank you for your help!
      singulars
      1. PO
      1. USMatan L
    2. COYes, that's why I referenced the 1000-item query in the answer :-). I think that 21 seconds to select 1000 integers through a primary key with a clustered index and (nolock) is way too much. Because of the (nolock) the 21 seconds could not come from another blocking lock, and if you let it run directly on the server it can't be a network issue either. It's just raw performance of moving to the first index entry and taking 1000 ints from the following records. On what I would consider a normal server that should be well under one second, even with a pretty long disk queue.
      singulars
      1. PO
      1. USTToni
    3. COAnyway, just experiment with the window size to get reasonable times for each update.
      singulars
      1. PO
      1. USTToni
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload