Post reply

2005 to 2008 R2 upgrade issues

  • September 29, 2010 at 11:59 pm

    #224567

    Hi All,

    We recently upgraded you SQL Server cluster from 2005 x64 Standard to 2008 x64 R2 Enterprise. The boxes are running Windows 2003 R2 x64 SP2. Since the upgrade we have very high latency with returning data as well as updating/inserting data. However, the latency is not a constant and does not follow the load of the server. I can run a query that will take 20 seconds to return from a single row update, then immediately run it again and it will return in under a second. then run it with in a few seconds of it returning and it will take 10-20 seconds again.

    We have tried rolling up to Cumm Update 3 with no impact. Also have tried the "Lock Pages in Memory" option and did not help. It does not matter which server in the cluster it runs on and we also have another server that is not in the cluster with the same setup doing the exact same thing.

    Has anyone else had this issue?

  • October 11, 2010 at 9:57 am

    #1235092

    Rebuild all indexes or run update statistics agains't the database. Microsoft has said following the sql2008 r2 upgrade you must rebuild database indexes and/or statistics

    Thanks

  • October 11, 2010 at 10:04 am

    #1235097

    We did that, right after the upgrade actually.

    I forgot to reply, but we finally found the issue and it was nothing to do with the database server at all. There was a trigger on a table that was making a remote call via xp_cmdshell(no flame war about tjis please, I don't like the option either) to another server. Just happened that the server was in a different location on our site and connected by a fiber that was broken. So while it was trying to get to the server, it was backing up all the updates, then finally releasing the table blocks when the xp_cmdshell finally failed.

    We have since corrected it, but this had been running this way for over 2 years with no problems. Just happened that the broken fiber and the database upgrade both happened during the same outage at our plant. Complicating the problem was that we do not monitor the database during plant outages because so many things are down that normally push data in that it is not a true view of the load.

  • October 11, 2010 at 10:10 am

    #1235102

    atul-957304 (10/11/2010)

    Rebuild all indexes or run update statistics agains't the database. Microsoft has said following the sql2008 r2 upgrade you must rebuild database indexes and/or statistics

    Thanks

    Do you have a link for the rebuild index advice from Microsoft? as i can not find it anywhere..

  • October 11, 2010 at 11:03 am

    #1235134

    Kris Sledge (10/11/2010)

    ...There was a trigger on a table that was making a remote call via xp_cmdshell(no flame war about tjis please, I don't like the option either) to another server....

    You might want to look into turning that into a queue-driven system instead of a realtime trigger.

    You could, probably, have the trigger dump rows into a staging table, and a job run every few minutes (or every minute) that looks in that table, processes the data via a DLL (instead of xp_cmdshell), and then either dumps the rows or marks them as processed.

    That way, if it goes wrong (as it just did), it won't do weird things in your database. It'll just accumulate rows to process.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • October 11, 2010 at 11:27 am

    #1235154

    That's exactly what we are doing with the queues.

    As for the rebuilding index script, I have my personally script that we built to handle rebuilding all the indexes, but nothing from directly Microsoft.

  • February 23, 2011 at 5:46 am

    #1290316

    Do you have the linke were Msoft advise to rebuild all indexes

  • February 24, 2011 at 3:13 am

    #1290817

    http://msdn.microsoft.com/en-us/library/ms144267.aspx

    Look towards the bottom of the document under 'Next Steps'

  • February 24, 2011 at 4:19 am

    #1290849

    Shabba (2/24/2011)

    http://msdn.microsoft.com/en-us/library/ms144267.aspx

    Look towards the bottom of the document under 'Next Steps'

    It says to rebuild statistics which would be needed, not to rebuild all indexes which i am not sure is neccesary following an upgrade

  • February 24, 2011 at 4:37 am

    #1290861

    Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply