locked
oracle to Sql Server 2008 Migration - performance issue with IMPL extended procedures RRS feed

  • Question

  • Hi,

    We are facing performance issues with converted functions which calls procedures. These have been converted to extended procedure calls which calls the $IMPL procedures. We have noticed significant degradation in performance when we moved from Sql Server 2008 to 2008 R2. Is there any guidelines available for setting up master/sysdb since they are used by these converted functions.

    Please let me know if any additional details are required.

    Thanks

    Thursday, November 11, 2010 2:04 PM

All replies

  • I do not know much about this from experience but I noticed the following link...

    http://ssma.wordpress.com/2009/08/04/deadlock-occurs-during-execution-of-converted-code/

    ...it suggests bypassing the function calls and directly calling the stored procedures to avoid a deadlock situation, perhaps your performance issues are related to locking and there is some relationship with the deadlock issue?

    On the system database setup question what are the differences between the 2008 and 2008 R2 setups? Did you specifically find IO performance problems after the move to 2008 R2?

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    Friday, November 12, 2010 1:10 AM
  • I checked the link. We are using parameter as false in the converted code when executing xp_ora2ms_exec2_ex, which means Sql Server is spawning autonomous transactions. We have replaced the $IMPL with regular UDF where ever possible. My main issue is why does the $IMPL call works better in a slower server when it should perform in equal or less time in the new server.

    Old Server

    • SQL Server 2008
    • Xenon Quad Core x4 - 2.93GHz, 64GB RAM

    New Server

    • SQL Server 2008 R2 RTM
    • Opteron 6 core x8 - 2.4GHz, 64GB RAM
    Monday, November 15, 2010 4:52 AM