7

I have to update all records (add Guids) on two (indexed) empty columns of 150 tables, each table with around 50k records (using a script to create 40k updates at once in c# and post it to the server) and exactly 4 existing columns.

On my local machine (16GB RAM, 500GB Samsung 850, SQL Server 2014, core i5) when I try to run 10 tables in parallel it takes a total of 13 minutes, while if I run 5 the process finishes in mere 1.7 minutes.

I do understand that something is busy on the disk level, but I need some help in how to quantify this huge difference in timings.

Is there a exact SQL Server DB view that I can check this discrepancy? Is there an exact way to figure out for a given hardware how many table updates can I run in parallel?? (the real test server has more RAM and 10k rpm disks).

Can anyone point to something that I can improve on the SQL Server to improve the timings for the running 10 tables in parallel?

I already tried increasing the Auto Growth size to 100MB from 10MB which improves the Disk Queue length (from around 5 to 0.1) but it does not actually decrease the total time that much.

I have asked the exact same question on stackoverflow, but not getting any helpful answers so far, so some or any insight/help would be immensely helpful. :)

  • Deb, capture more statistics on the database while the queries are running to pinpoint the bottleneck. I see you referred to disk queue length, but that's just one small piece of a much larger picture. I would recommend tracking CPU usage, memory usage, and also other disk statistics (writes/sec, ms per write would be a good place to start). – Chris Bergin Jan 19 '16 at 20:53
  • On the other end is one write head. Parallel just ends up in a queue. Your best bet is to keep keep one thread busy. I use a BlockingCollection - create the commands on the producer. – paparazzo Jan 23 '16 at 13:08
3

Given the code in your answer, you would most likely improve performance by doing the following two changes:

  • Start the query batch with BEGIN TRAN and end the batch with COMMIT TRAN:

  • Decrease the number of updates per batch to less than 5000 to avoid lock escalation (which generally occurs at 5000 locks). Try 4500.

Doing those two things should decrease the massive amount of tran log writes and lock / unlock operations that you are currently generating by doing individual DML statements.

Example:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"BEGIN TRAN;
        UPDATE [TestTable] SET Column5 = 'some unique value' WHERE ID = 1;
        UPDATE [TestTable] SET Column5 = 'some unique value' WHERE ID = 2;
        ...
        UPDATE [TestTable] SET Column5 = 'some unique value' WHERE ID = 4500;
        COMMIT TRAN;
        ", conn));

UPDATE

The Question is a bit sparse on the details. The example code is only shown in an answer.

One area of confusion is that the description mentions updating two columns, yet the example code only shows a single column being updated. My answer above was based on the code, hence it only shows a single column. If there really are two columns to update, then both columns should be updated in the same UPDATE statement:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"BEGIN TRAN;
        UPDATE [TestTable]
        SET    Column5 = 'some unique value',
               ColumnN = 'some unique value'
        WHERE  ID = 1;
        UPDATE [TestTable]
               SET Column5 = 'some unique value',
               SET ColumnN = 'some unique value'
        WHERE  ID = 2;
        ...
        UPDATE [TestTable]
               SET Column5 = 'some unique value',
               SET ColumnN = 'some unique value'
        WHERE  ID = 4500;
        COMMIT TRAN;
        ", conn));

Another issue that is unclear is where is the "unique" data coming from? The Question mentions that the unique values are GUIDs. Are these being generated in the app layer? Are they coming from another data source that the app layer knows about and the database does not? This is important because, depending on the answers to these questions, it might make sense to ask:

  1. Can the GUIDs be generated in SQL Server instead?
  2. If yes to #1, then is there any reason to generate this code from app code instead of doing a simple batch loop in T-SQL?

If "yes" to #1 but the code, for whatever reason, needs to be generated in .NET, then you can use NEWID() and generate UPDATE statements that work on ranges of rows, in which case you do not need the BEGIN TRAN / 'COMMIT` since each statement can handle all 4500 rows in one shot:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"UPDATE [TestTable]
        SET    Column5 = NEWID(),
               ColumnN = NEWID()
        WHERE  ID BETWEEN 1 and 4500;
        ", conn));

If "yes" to #1 and there is no real reason for these UPDATEs to be generated in .NET, then you can just do the following:

DECLARE @BatchSize INT = 4500, -- this could be an input param for a stored procedure
        @RowsAffected INT = 1, -- needed to enter loop
        @StartingID INT = 1; -- initial value

WHILE (@RowsAffected > 0)
BEGIN
  UPDATE TOP (@BatchSize) tbl
  SET    tbl.Column5 = NEWID(),
         tbl.ColumnN = NEWID()
  FROM   [TestTable] tbl
  WHERE  tbl.ID BETWEEN @StartingID AND (@StartingID + @BatchSize - 1);

  SET @RowsAffected = @@ROWCOUNT;
  SET @StartingID += @BatchSize;
END;

The code above only works if the ID values are not sparse, or at least if the values do not have gaps larger than @BatchSize, such that there is at least 1 row updated in each iteration. This code also assumes that the ID field is the Clustered Index. These assumptions seem reasonable given the provided example code.

However, if the ID values do have large gaps, or if the ID field is not the Clustered Index, then you can just test for rows that do not already have a value:

DECLARE @BatchSize INT = 4500, -- this could be an input param for a stored procedure
        @RowsAffected INT = 1; -- needed to enter loop

WHILE (@RowsAffected > 0)
BEGIN
  UPDATE TOP (@BatchSize) tbl
  SET    tbl.Column5 = NEWID(),
         tbl.ColumnN = NEWID()
  FROM   [TestTable] tbl
  WHERE  tbl.Col1 IS NULL;

  SET @RowsAffected = @@ROWCOUNT;
END;

BUT, if "no" to #1 and the values are coming from .NET for a good reason, such as the unique values per each ID already exist in another source, then you can still speed this up (beyond my initial suggestion) by supplying a derived table:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"BEGIN TRAN;

        UPDATE tbl
        SET    tbl.Column5 = tmp.Col1,
               tbl.ColumnN = tmp.Col2
        FROM   [TestTable] tbl
        INNER JOIN (VALUES
          (1, 'some unique value A', 'some unique value B'),
          (2, 'some unique value C', 'some unique value D'),
          ...
          (1000, 'some unique value N1', 'some unique value N2')
                   ) tmp (ID, Col1, Col2)
                ON tmp.ID = tbl.ID;

        UPDATE tbl
        SET    tbl.Column5 = tmp.Col1,
               tbl.ColumnN = tmp.Col2
        FROM   [TestTable] tbl
        INNER JOIN (VALUES
          (1001, 'some unique value A2', 'some unique value B2'),
          (1002, 'some unique value C2', 'some unique value D2'),
          ...
          (2000, 'some unique value N3', 'some unique value N4')
                   ) tmp (ID, Col1, Col2)
                ON tmp.ID = tbl.ID;

        COMMIT TRAN;
        ", conn));

I believe the limit on the number of rows that can be joined via VALUES is 1000, so I grouped two sets together in an explicit transaction. You could test with up to 4 sets of these UPDATEs to do 4000 per transaction and keep below the limit of 5000 locks.

3

Based on your own answer, it looks like:

  1. You're updating the first and second empty columns in separate update statements

  2. The empty columns are varchar datatype

I don't have enough rep yet on DBA to comment (I originally saw the version of this you cross-posted on Stack Overflow), so will answer on that assumption.

If so, you're possibly making a common mistake for people who come to SQL from procedural languages: thinking about SQL tables procedurally, updating each row and column one-at-a-time.

SQL wants you to do set based operations, where you tell SQL what you want to do to all rows in one query/statement. The SQL Server query engine can then internally work out the best way to actually make that change happen to all rows. By doing updates row-at-a-time you're preventing SQL Server from doing the thing it does best.

It's possible you're well aware of this and the nature of the values you have to update makes row-by-row updates essential, but even then I think you could be updating both columns for a single table row in one go, halving the total number of updates you have to do.

If you have a degree of flexibility over what the unique values in your columns are, you can probably update one entire table with a single SQL query. For true GUID values, a query along the lines of:

update TestTable
set    Column5 = NEWID()
       ,Column6 = NEWID()

will give you unique uniqueidentifier values in each cell. NEWID is documented here if you've not seen it before. You would then just need to repeat this query 150 times for the separate tables, which could be parallelized easily; I'd bet on it being massively faster too.

Or, if you need something number-based, you can apply unique numbers like this:

with cteNumbers as (
    select  Column5
            ,Column6
            ,Row_Number() over (order by id) as RowNo
    from TestTable
    )
update cteNumbers
set Column5=RowNo
    ,Column6=RowNo

Though I suspect that's not what you're trying to do; and in any case, if your id column is an autoincrementing int, you could just use that directly rather than generating a Row_Number() over it.

If you want something based on incrementing numbers but not just consisting of the number, you can build an expression around the RowNo to achieve what you want.

Wherever possible, employing set-based operations is an absolute necessity for efficient SQL performance.

  • thanks for your reply, indeed I had looked into the SET based approach but cannot use it since the data for those columns are coming from somewhere else. I am just basically updating the new GUID to the ID of the specific record. – Deb Jan 27 '16 at 14:04
2

Found the solution. :)

Instead of running the 40K update queries at once (I create an update script of 40k update statements as stated in the comment above) if I decrease the that number to half of it - 20k update queries at once there is a huge improvement - 10 tables in parallel it takes a total of 1.3 minutes now - I can now continue.

Here is code which does the update: enter image description here

Now the code has been changed to do 20k at a single time.

So basically previously it was running 10 (threads) X 40k update queries = 400k simultaneous update queries at the first run and then the rest 10 (threads) X 10k update queries, to update the all 50k records in those 10 different types.

And, now it does:

  1. 10 (threads) X 20k update queries = 200k simultaneous update queries
  2. 10 (threads) X 20k update queries = 200k simultaneous update queries
  3. 10 (threads) X 10k update queries = 100k update queries

Result: Before: 13 minutes, After: 1.8 minutes

I am now checking to find out the best (fastest!) combination to update those 150 tables using multiple threads at the same time. Probably I can update a higher number of tables in parallel with a lower simultaneous update like 5k (from 20k) but I will be busy testing that now.

  • Not sure if I can develop this into a fully fledged answer but just as an idea, have you considered using a table-valued parameter for this? Each thread would just load a portion of data into this parameter and the script would go something like this: UPDATE t SET column5 = tvp.Value FROM [TestTable] AS t INNER JOIN @YourTableValuedParameter AS tvp ON t.id = tvp.id;. (I think @srutzky's suggestion about keeping the number of updates per thread at below 5k would still apply.) – Andriy M Jan 22 '16 at 7:52
  • Please tell you are actually updating both columns in one statement – paparazzo Jan 23 '16 at 13:11
  • @Frisbee yes, both columns at once. Here it's an example in a other scenario I had to update (actually sync from another table) more than two columns also. Indeed srutzky's suggestion about keeping the number of updates per thread at below 5k does still apply, and I have now accepted his answer as the best answer for my scenario. – Deb Jan 27 '16 at 14:08
0

There isn't a magic view that will tell you how many threads work better for the given hardware and as with all good questions, the answer is "it depends". You have to consider that there may be other load happening at the time or that your query is more weighted in CPU or I/O. But what you can do, and sounds like you are doing is testing. You might want to throw in another variable as well, MAXDOP.

If possible, in C#, just allow the number of threads to be variable (read from a db or from a config file), then you can tune your query on the fly.

While there may be no magic view, you could probably sum the wait time on the spids during each run to see where the waits and the limits are.

  • MAXDOP seems like a Sql Server level option (higher) and not a database level option, and, thereby I cannot change it on production database. Are you absolutely sure that increasing (now I use the default) would help in doing updates?? Maybe you can share some useful links supporting you theory perhaps..... – Deb Jan 19 '16 at 15:08
  • I have now tested with MAXDOP = 4 (for the 4 physical CPUs) and there is no improvement. Reverting back to default. – Deb Jan 19 '16 at 15:33
  • I wasn't saying it would definitely help, but that it's just another variable to allow for better configuration. If you have 4 cores and you set MAXDOP=4 then it's the same as leaving it alone. Let's set that aside for a minute and focus on the other part of the comment. Configure the number of threads and test for each environment. 4 threads might be the best in test and 20 might be best in production. We use an SSIS package that allows a variable input and we can set the number of threads. – paulbarbin Jan 21 '16 at 14:42
  • This allows us two things, the ability to tune in different environments but also the ability to tune in production when different things are going on. When we first started, the original process was taking 24 hours. We "parallelized" it and got the monthly process down to 4 hours without a change to the stored procedure AT ALL. Then later when production became busier with other processes, we had to cut back the number of threads (and MAXDOP) to allow other processes time to finish. – paulbarbin Jan 21 '16 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.