UPDATE Performance Issues

[talenx]

Hello all,
This has to be the weirdest thing I have every seen, I have a very simple update routine. The routine update about 18 columns in a fairly large indexed table est.( 86 million and change) up until a few weeks ago the updates ran without a hitch, in fact this same update was tested on a identical server with no issues.
the odd thing here is when this update is ran on the current indexed table it pegs ALL 4 X 3Ghz CPUs at 95 - 100 % and sits there until I am forced to cancel the query.
I have re-created the index(s) removed indexes and place it in is own db environment (for testing) and receive the same results

NOTE performing this update to 1-40 records it works fine.. but once i attempt anything over 100 it invokes paralisim and pegs ALL of the CPUs

Here is the dbcc showcontig
DBCC SHOWCONTIG scanning 'Eligibility_Dimension_Stage' table...
Table: 'Eligibility_Dimension_Stage' (690101499); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 223419
- Extents Scanned..............................: 28021
- Extent Switches..............................: 28020
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.67% [27928:28021]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 3.45%
- Avg. Bytes Free per Page.....................: 396.1
- Avg. Page Density (full).....................: 95.11%


Server specs
Windows server enterprise sp1
Server 2000 enterprise sp4 with hot fix
RAID 10 with 3 fiber channel racks
6 GB of RAM
4 3.3 GHz CPUs

Any Thoughts

Thanks
TalenX

 

[SQLDenis]

update statistcs by using sp_updatestats or UPDATE STATISTICS
And let us know if that did it



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[talenx]

Thanks for the quick reply.
Unfortunately not, I applied a UPDATE STATISTICS to the table in question only, seeing that it would update all indexes in that table.


Thanks
TalenX

 

[talenx]

Hello all,

After some testing over the weekend, I found that if I where to batch say 100 update transactions with in a single routine then the server invokes parallelism,(which is good.) problem is it seems to hang its self. I have attempted to place the MAXDOP 1 option and the query completed, but it took approx. 55% slower to run.
but if I change the routine to say perform a JOIN update verses the SARG update it has no problems( BTW the SARG value is an index INT that I was also used in the successful JOIN update query (in fact I was able to update 2.5 million rows with in a couple mins)
I’ll say it again WEIRD!!! lol

Any Thoughts
TalenX

 

[talenx]

Well after banging my head to a bloody pulp... i found the issue. ( I really thought I was losing my head...believe me I CHECKED EVERYTING!!

the ID field that was being used to define the unique update was a decimal 19.0 ( BTW not my design ) used for an ID field ( why not big int ...I don't know.) anyways the value being passed in from the update program was a decimal 19(8) hence causing SQL server to perform a conversion, and forces the SQL processor to perform the conversion for each record ( neglecting the index)

Turns out Sp4 had mad some changes concerning decimal conversions, causing the exec plan to perform a index scan ( VERY SLOW!!!!) instead of a index seek.

Here is the MS KB

http://support.microsoft.com/?kbid=899976

Though you can enable the trace flag with this fix MS states that future versions of SQL will still have this functionally.

Hope this help someone else from losing 38 hours of sleep.

Cheers
TalenX