Tags: 100x, crawls, database, fairly, microsoft, mysql, onserver, oracle, plan, production, rows, saturated, server, servers, slower, sql, update, workstation

IO saturated update crawls

On Database » Microsoft SQL Server

3,167 words with 1 Comments; publish: Wed, 04 Jun 2008 03:15:00 GMT; (25078.00, « »)

Fairly simple update, same plan on dev workstation and server, 100x slower on

server - on two different servers, production and qa.

3m rows on dev, 7m on server. Reads scale accordingly. Writes are 60x

greater on servers. Clustered on bigint (not identity) PK.

SQL is basically

update mytable

set myfk = yourpk -- both simple ints, nullable, and myfk is null when first

run

from mytable inner join yourtable on myvalue = yourvalue

yourtable is a just 100 or so rows.

Disk queue pegs over 200, latch waits pegs over 200 - but only on servers!

I'm waiting for this fat update to finish to get a dbcc showcontig on the QA

server.

Previously this same code ran harmlessly, when production volumes were about

50% lower. Is it possible we've crossed some volume level at which SQLServer

has to spool for scalability and this doesn't show on the plans? Hmm, will

it show if I turn on one of the verbose showplans that logs to profiler?

Thanks.

Josh

ps - posting thru the web site, gotta love the terms of use:

"Also, by offering comments and/or materials through this page you give

Microsoft full permission to use them freely to improve our products."

Go to it, guys!

pps - SQL2K standard Server 2003, vs SQL2K dev XP sp2

All Comments

Leave a comment...

  • 1 Comments
    • Hello JRStern,

      What is the plan of the update. It could be you have a parallel plan. Try

      forcing a non parallel plan using OPTION (MAXDOP 1)

      Simon Sabin

      SQL Server MVP

      http://sqlblogcasts.com/blogs/simons

      > Fairly simple update, same plan on dev workstation and server, 100x

      > slower on server - on two different servers, production and qa.

      > 3m rows on dev, 7m on server. Reads scale accordingly. Writes are

      > 60x greater on servers. Clustered on bigint (not identity) PK.

      > SQL is basically

      > update mytable

      > set myfk = yourpk -- both simple ints, nullable, and myfk is null when

      > first

      > run

      > from mytable inner join yourtable on myvalue = yourvalue

      > yourtable is a just 100 or so rows.

      > Disk queue pegs over 200, latch waits pegs over 200 - but only on

      > servers!

      > I'm waiting for this fat update to finish to get a dbcc showcontig on

      > the QA server.

      > Previously this same code ran harmlessly, when production volumes were

      > about 50% lower. Is it possible we've crossed some volume level at

      > which SQLServer has to spool for scalability and this doesn't show on

      > the plans? Hmm, will it show if I turn on one of the verbose

      > showplans that logs to profiler?

      > Thanks.

      > Josh

      > ps - posting thru the web site, gotta love the terms of use:

      > "Also, by offering comments and/or materials through this page you

      > give Microsoft full permission to use them freely to improve our

      > products."

      > Go to it, guys!

      > pps - SQL2K standard Server 2003, vs SQL2K dev XP sp2

      >

      #1; Wed, 04 Jun 2008 03:16:00 GMT