SQL Server Performance

updating table on different machine

Discussion in 'Performance Tuning for DBAs' started by ekampel, Oct 4, 2005.

  1. ekampel New Member

    Hi,
    I use updates like this to keep data updated every night on certain tables across databases.

    update A
    set A.description = b.description,
    A.shortdescription = b.shortdescription,
    A.keywords = b.keywords,
    A.smallimagepath = b.smallimagepath
    from sfproducts A
    join databaseb.dbo.sfproducts b on A.prodid = b.prodid
    where A.description != b.description,
    or A.shortdescription != b.shortdescription,
    or A.keywords != b.keywords,
    or A.smallimagepath != b.smallimagepath

    The example above has 150,000+ rows and 170 columns.
    Updating across databases on the same machine, it usually updates 5000 records a night in less than a minute.

    We recently split the master tables off to another machine and the updates run for hours before I break them. Is this a limitation of sql server distributed transactions?
    Any help would be greatly appreciated.
    A linked server is being used and the servers are on the same internal network.

    eric
    this is my first post here, so let me know if you need more info.
  2. ghemant Moderator

    Hi,
    Usally it will not take this much time , have you set your network packet size !


    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  3. ekampel New Member

    could you guide me where to look for this ?
  4. ghemant Moderator

    Hi,
    read BOL for sp_configure , it will shows you the current settings for sql server.

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  5. ekampel New Member

    network packet size is 4096
  6. ghemant Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><b>it usually updates 5000 records a night in less than a minute.</b><br />We recently <b>split</b> the master tables off to another machine and the updates run for hours before I break them. Is this a limitation of sql server distributed transactions?<br />Any help would be greatly appreciated.<br />A linked server is being used and the servers are on the same internal network.<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />And one more thing , is it production site ! and how many concurrent users are accessing database in day time !<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  7. ekampel New Member

    database A supports ecommerce site with 200,000+ page views daily.
    database B is central admin database where orders get fed into from the 3 frontend databases (1 of which is A in this case) and new product info gets fed out from to the 3 frontend databases. Database B supports 60 concurrent users inside here.

    Also, I think I may have finally found the problem last night.
    Updating distributed like this does not seem to like NTEXT fields.
    1 of the major tables I was having problems with is working now.

Share This Page