Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Yes it sounds like a very generic issue, but I have not been able to narrow it down very much yet.

So I have an UPDATE statement in a sql batch file:

UPDATE A
SET A.X = B.X
FROM A JOIN B ON A.B_ID = B.ID

B has 40k records, A has 4M records and they are related 1-to-n via A.B_ID, although there is no FK between the two.

So basically I am pre-calculating a field for data mining purposes. Although I changed the name of the tables for this question, I didn't change the statement, it's really that simple.

This takes hours to run, so I decided to cancel everything. The DB got corrupted, so I deleted it, restored a backup I did just before running the statement and decided to go more into details with a cursor:

DECLARE CursorB CURSOR FOR SELECT ID FROM B ORDER BY ID DESC -- Descending order
OPEN CursorB 
DECLARE @Id INT
FETCH NEXT FROM CursorB INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Msg VARCHAR(50) = 'Updating A for B_ID=' + CONVERT(VARCHAR(10), @Id)
    RAISERROR(@Msg, 10, 1) WITH NOWAIT

    UPDATE A
    SET A.X = B.X
    FROM A JOIN B ON A.B_ID = B.ID
    WHERE B.ID = @Id

    FETCH NEXT FROM CursorB INTO @Id
END

Now I can see it running with a message with the id descending. What happens is it takes around 5min to go from id=40k to id = 13

And then at id 13, for some reason, it seems to hang. The DB does not have any connection to it besides SSMS, but it's not actually hanged:

  • the hard drive is running continuously so it's definitely doing something (I checked in Process Explorer that it's indeed the sqlserver.exe process using it)
  • I ran sp_who2, found the SPID (70) of the SUSPENDED session then ran the following script:

    select * from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id where r.session_id = 70

This gives me the wait_type, which is PAGEIOLATCH_SH most of the time but actually changes to WRITE_COMPLETION sometimes, which I guess happens when it's flushing the log

  • the log file, which was 1.6GB when I restored the DB (and when it got to id 13), is now 3.5GB

Other maybe useful information:

  • the number of records in table A for B_ID 13 is not big (14)
  • My colleague does not have the same issue on her machine, with a copy of this DB (from a couple of months ago) with the same structure.
  • table A is by far the biggest table in the DB
  • It has several indexes, and several indexed views use it.
  • There is no other user on the DB, it's local and no application is using it.
  • The LDF file is not limited in size.
  • Recovery model is SIMPLE, compatibility level is 100
  • Procmon does not give me much information: sqlserver.exe is reading and writing a lot from the MDF and the LDF files.

I'm still waiting for it to finish (it's been 1h30) but I was hoping that maybe someone would give me some other action I could try to troubleshoot this.

Edited: adding extract from procmon log

15:24:02.0506105    sqlservr.exe    1760    ReadFile    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TA.mdf  SUCCESS Offset: 5,498,732,544, Length: 8,192, I/O Flags: Non-cached, Priority: Normal
15:24:02.0874427    sqlservr.exe    1760    WriteFile   C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TA.mdf  SUCCESS Offset: 6,225,805,312, Length: 16,384, I/O Flags: Non-cached, Write Through, Priority: Normal
15:24:02.0884897    sqlservr.exe    1760    WriteFile   C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TA_1.LDF    SUCCESS Offset: 4,589,289,472, Length: 8,388,608, I/O Flags: Non-cached, Write Through, Priority: Normal

From using DBCC PAGE it seems to be reading from and writing to fields that look like table A's (or one of its indexes), but for different B_ID that 13. Rebuilding indexes maybe?

Edited 2: execution plan

So I cancelled the query (actually deleted the DB and its files then restored it), and checked the execution plan for:

UPDATE A
SET A.X = B.X
FROM A JOIN B ON A.B_ID = B.ID
WHERE B.ID = 13

The (estimated) execution plan is the same than for any B.ID, and looks fairly straightforward. The WHERE clause uses an index seek on a non-clustered index of B, the JOIN uses a clustered index seek on both PKs of the tables. The clustered index seek on A uses parallelism (x7) and represents 90% of the CPU time.

More importantly, actually executing the query with ID 13 is immediate.

Edited 3: index fragmentation

The structure of indexes is as follows:

B has one clustered PK (not the ID field), and one non-clustered unique index, which first field is B.ID - this second index seems to be used always.

A has one clustered PK (field not related).

There are also 7 views on A (all include the A.X field), each one with its own clustered PK, and an other index which also includes the A.X field

The views are filtered (with fields that are not in this equation), so I doubt there is any way the UPDATE A would use the views themselves. But they do have an index including A.X, so changing A.X means writing the 7 views and the 7 indexes they have that include the field.

Although the UPDATE is expected to be slower for this, there is no reason why a specific ID would be so much longer than the others.

I checked the fragmentation for all the indexes, all were at <0.1%, except the secondary indexes of the views, all between 25% and 50%. Fill factors for all indexes seem ok, between 90% and 95%.

I reorganized all the secondary indexes, and reran my script.

It is still hanged, but at a different point:

...
(0 row(s) affected)

        Updating A for B_ID=14

(4 row(s) affected)

Whereas previously, the message log looked like this:

...
(0 row(s) affected)

        Updating A for B_ID=14

(4 row(s) affected)

        Updating A for B_ID=13

This is weird, because it means it's not even hanged at the same point in the WHILE loop. The rest looks the same: same UPDATE line waiting in sp_who2, same PAGEIOLATCH_EX wait type and same heavy HD usage from sqlserver.exe.

Next step is to delete all indexes and views and recreate them I think.

Edited 4: deleting then rebuilding indexes

So, I deleted all the indexed views I had on the table (7 of them, 2 indexes per view including the clustered one). I ran the initial script (without cursor), and it actually ran in 5 minutes.

So my issue originates from the existence of these indexes.

I recreated my indexes after running the update, and it took 16 minutes.

Now I understand indexes take time to rebuild, and I am actually fine with the complete task taking 20 minutes.

What I still don't understand is, why when I run the update without deleting the indexes first, it takes several hours, but when I delete them first then recreate them, it takes 20 minutes. Shouldn't it take about the same time either way?

share|improve this question

migrated from stackoverflow.com Jan 29 '13 at 14:56

This question came from our site for professional and enthusiast programmers.

1  
Anything in the SQL Server error log? Also from procmon what are the offsets in the file that it is writing to? You can divide by 8,192 to get the page and then use DBCC PAGE to see what is being written to. –  Martin Smith Jan 29 '13 at 14:59
    
3.5GB looks like the maximum amount of RAM a 32bits windows worksation can handle.. hazard ? –  tschmit007 Jan 29 '13 at 15:05
    
@MartinSmith There is absolutely nothing since I restored in the SSMS SQL Server Logs and nothing either in the Windows event log –  GFK Jan 29 '13 at 15:09
    
What do your indexes on table A look like (what columns, etc)? Are they fragmented? –  Stuart Ainsworth Jan 29 '13 at 15:12
    
@tschmit007 Its SQL 2008 R2 x64 Dev Edition on Win Server 2008 R2 x64. It is a VM running itself on Hyper-V (host is 2008 R2 x64 too); the VM has 4.2GB physical memory used out of 5GB, and 4.6GB commit out of 10GB max; the host has 7.2GB physical memory used out of 8GB, and 7.8 commit out of 16GB max. Both machines are slower due to HD usage but not clogged. –  GFK Jan 29 '13 at 15:15

3 Answers 3

up vote 0 down vote accepted
  1. Stick with UPDATE command. CURSOR will be slower for what you are trying to do.
  2. Drop/disable all indexes, including those for indexed views. If you have foreign key on A.X, drop it.
  3. Create index which will contain only A.B_ID, and another one for B.ID.
  4. Even though you are using Simple Recovery model, the last transaction will be always in transaction log before it is flushed to disk. That's why you need to pre-grow your transaction log and set it to grow for some larger amount (eg. 100 MB).
  5. Also, set data file growth to some larger amount.
  6. Make sure you have enough disk space for further growth of log and data files.
  7. When update finishes recreate/enable indexes which you dropped/disabled in step 2.
  8. If you don't need them anymore, drop indexes created in step 3.

Edit: Since I can't comment on your original post, I'll answer here your question from Edit 4. You have 7 indexes on A.X. Index is a B-tree, and each update to that field causes B-tree to rebalance. It's faster to rebuild those indexes from scratch than to rebalance it each time.

share|improve this answer
    
For point 1 see my answer to ik_zelf. The cursor is there for investigation reasons and doesn't have that much impact. I am going to implement the rest of your suggestions, I think it's all I have left to do. If it works, I will still be left without an explanation on what happens now though... –  GFK Jan 30 '13 at 10:15
    
You can post DDL for your tables (including all indexes, constraints, etc.). Maybe there is something slowing down your performance and you are missing that. –  bojan Jan 30 '13 at 11:13
    
Drop indexes / Update / Rebuild indexes works, and although I'd prefer not to have to do something that drastic, I don't see that I have a choice. Thanks! –  GFK Jan 31 '13 at 11:03

The update scenario is always faster than using a procedure.

Since you are updating column X of all rows in table A, make sure you drop the index on that one first. Also make sure there are no things like triggers and constraints active on that column.

Updating indexes is a costly business, as is validating constraints and executing row level triggers that do a lookup in other data.

share|improve this answer
    
I don't think that's the point. I realize the update of indexed records takes time, and I know that, overall, part of the time it takes is due to this. But I expect this, and I'm ok with it: as I said, updating 99% of the lines takes 5 min (even using the cursor), but for some reason, one line (and not always the same) takes 5h. What worries me is this particular behaviour. –  GFK Jan 30 '13 at 10:13
    
locks is no issue you said .... how about filesystem utilization, reaching 90% or higher? –  ik_zelf Jan 30 '13 at 11:07
    
no, it's 31GB free out of 120GB, so I think it's ok –  GFK Jan 30 '13 at 11:54
    
what happens if you try to copy the table like create table a_copy as select * from a; –  ik_zelf Jan 30 '13 at 13:14

One thing to look at are the system resources (Memory, Disk, CPU) during this process. I attempted to insert 7 million individual rows into a single table in one big job and my server hung in a manner similar to yours.

Turns out I didn't have enough memory on my server to run this mass insert job. In situations like this SQL likes to hold onto the memory and not let it go....even after said insert command may or may not have completed. The more commands that are processed in large jobs, the more memory is eaten up. A quick reboot freed up said memory.

What I would do is start this process from scratch with your Task Manager running. If the memory usage gets over 75% the chances of your system/processes freezing skyrockets astronomically.

If your memory/resources are indeed limited as noted above then your options are to cut the process into smaller pieces (with the occasional reboot if memory usage is high) instead of one big job or upgrade to a 64 bit server with lots of memory.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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