June 3, 2011 at 4:32 am
--The Script
Create table ATable
(
ID bigint identity(1,1)
,SomeColumn tinyint
)
Create clustered index Atable_ID_Clust
On ATable(ID)
Create nonclustered index ATable_Somecolumn
On Atable(SomeColumn)
/*Some more code and F5 and ATable has 480 mill rows*/
--Problem Statement
Update Atable
Set SomeColumn = 1
Where somecolumn = 0
Rowcount for Atable with SomeColumn = 0 is 477mill
Execution time 16 hours elapsed
I executed Select count(*) from ATable with (NOLOCK) where SomeColumn = 0
Results = 0
Investigations
Ok this was just the start. This now lead to many other investigations. Starting with the basics, locks? none, only user on machine, Server is paging? No perfmon shows 0.6 and 0.9 % of pagefiles used
SQL tells me that is is waiting for the disc subsystem WaitType = “PageIOLatch_EX” Again Perfmon comes handy. Check disc throughput 4 – 6 MB/sec. This could not be possible. Ran SQLIO and pushed a 1GB file through to the drive with DBs on, Throughput 130MB/sec – 250MB/sec
If I create an exact duplicate of ATable and do insert into BTable (Select * from ATable) the throughput is 10 MB/sec to 30MB/sec
CPU’s Idle @ 9% (16 CPU’S), Drives with page files idleing at 1MB/sec if not 0, Memory cap 32 GB, SQL using 27GB
Only issue I found was memory page faults / Sec that are excessively high am unable to do memory test.
My issue is that SQL is not using the underlying disc subsystem as it should/can. The query above is only a sample of one that I took to investigate server performance.
Any help PLEASE that does not include violence like kicking / shooting / throwing or formatting
June 3, 2011 at 3:14 pm
This may be counterintuitive, but - chunk up the update. i.e. do it as a bunch of smaller updates. Since you have an identity column as the clustered index, you can "walk" the CI and look for columns to update.
declare @batchsize bigint
declare @maxId bigint
declare @currentID bigint
set @currentID=0
set @batchsize=50000
select @maxId=MAX(id) from atable
while @currentID<@maxId
BEGIN
update atable
set somecolumn=1
where somecolumn=0 and ID between @currentID and @currentID+@batchsize
set @currentID = @currentID+@batchsize
END
You will probably do better if you drop your non-clustered index before running this (since your update will keep changing values in the NCI). So if it's needed for something other than this purpose, drop it, run this type of update, then rebuild it.
vary the batchsie up or down to suit your system.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 3, 2011 at 5:19 pm
If you need to update more than 50% of the table rows (your table has 480 mil rows and you want to update 477 mil rows - almost every row!), it will be much faster to create a new table with desired values (no idexes, constraints, triggers yet). Only AFTER that (filling rows to new table) build indexes, triggers, check constraints etc, and then replace the "old" table (rename old to temp, rename new to old, drop temp).
HTH,
Vedran
June 6, 2011 at 1:41 am
Hey guys
My pain is not the update but rather that SQL is throttleing the IO. The scenario discribed above is only one of many. The server in general does not push the hard drives and appears to be "slow" but that is not the case. The hardware is capable of far more that what SQL is throwing at it.
June 6, 2011 at 5:40 am
Experiment with UPDATE TOP(n) as Matt suggested (n between 100 and 10 000), and with OPTION(MAXDOP m) where m is between 1 and 10. You wait info says "your disc is slow". It's not the same to send huge chunk of 1G data to the storage, and to send milion (1024x1024) chunks of 1KB data.
June 6, 2011 at 5:19 pm
Try running SQLIO to test out there isn't a bottleneck in your DAS system.
If everything appears fine you can check off one part of the checklist.
But that is a good place to start.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply