?
Solved

Can't Update a Bit Column in 227 Million Row Table

Posted on 2011-02-15
9
Medium Priority
?
480 Views
Last Modified: 2012-05-11
I tried last night (after turning of all websites, applications and even SQL jobs that use the DB) to update a bit column to 0 on a table that contains 227 million rows.   After 5.5 hours and it being 5:30 AM, I had to give up and rollback the change (which probably took about 3 hours).

Strangely, a week prior I update a DateTime field to '1/1/1900' in the same table without any issue.  I don't understand why I'm not able to run the update in a reasonable amount of time.  Perhaps I need to delete all the indexes first and then build them back up after?  That's a royal pain.

0
Comment
Question by:davidcahan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34898835
Can you post the table / indexes definition including the update statement?
0
 

Author Comment

by:davidcahan
ID: 34898918
FileDisplay (FDID int, CMID int, LMID int, Date datetime, Dateadded Datetime, IsFileDrop bit)

IX_FD primary key, nonclustered, 70% fill
CMID, nonclustered, 70% fill
LMID, nonclusterd, 70% fill
Date nonclustered, 70% fill

Update FileDisplay
Set IsFileDrop = 0
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34900463
I don't see anything special about your table or update.

Concurrency issues is what comes to my mind.  Do you know what other processes were doing at that time?  Those processes may have locked rows on the FileDisplay table or stressed the server.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:davidcahan
ID: 34900606
I actually shut down all sql jobs, web sites and other processes that would have been hitting the DB.  I was getting a lot of pageiolatch_sh wait times with an Average Latch Wait of 876 ms.  I may try a simple reboot of the server and see if anything seems to clear up.

0
 

Author Comment

by:davidcahan
ID: 34900620
either that, or I may have to batch it out somehow.  what a pain that would be...
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 34907935
I would write a batch update code and run it in a SQL job until all values are updated. Something like below should help and please always keep in mind that bit field datatype is different than int or smallint therefor you should use 0x0 = 0, and 0x1 = 1 for bit fields especialy if they are indexed.

SET NOCOUNT ON;

                  --Set rowcount to 200 to limit number of deletes per batch
                  SET ROWCOUNT 200

                  DECLARE @rc int
                  SET @rc=200

                  WHILE @rc=200
                  BEGIN
                            UPDATE TOP(200) FileDisplay  WITH(ROWLOCK)
                            SET IsFileDrop  = 0x1
                            WHERE IsFileDrop is null

                        SELECT @rc=@@rowcount
                  END

                  SET ROWCOUNT 0      
SET NOCOUNT OFF;
0
 

Author Comment

by:davidcahan
ID: 34910562
Little confused by code snippet.  If I'm only updating the top 200 that are null then why would I need to use any rowcount or while loop.  Everyday (or hour, or 30 mins) I could just update top X rows until it's updated.

0
 
LVL 40

Expert Comment

by:lcohan
ID: 34910702
You should test and change the batch size to a value that works and is NOT impacting your production assuming this is done in production database. As far as how often you could run it even every minute as if it's a sql job next run won't start is ther's one running already.
The code is written like that on purpose - think you have an update less than 200 rows because a value in the top 200 already changed so if you use this batch in a loop it may be broken.
Hope all this makes sense - please let me know if you have more questions.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 34910834
Oh and I forgot to mention one other thing - hopefully there are no triggers on the table you need to update.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question