0

We are working on a Oracle to SQL Server 2008 migration project. So we have lot of oracle package functions which used to call other Stored procedures. These functions have been converted by SSMA to IMPL procedures which are executed via extended procedures from the functions. We had converted most of such functions to simple UDF where ever possible. Now we are facing a peculiar performance problem. The queries invoking such functions with IMPL procedure calls are taking a longer time to run. Whats interesting is that the same queries used to run on old server with Sql Server 2008 in 2 mins. Now in the new server with SQL Server 2008 R2, its taking very long time (around 25-30 mins).

We have verified that the indexes and statistics are also up to date. We also noticed that IMPL calls go though master and sysdb databases and uses internal tables to store and pass the result back from the IMPL procedure call to the function. Their space allocation is different from old server. But they are not running out of space. Can they be causing the problem? Is there any guidelines available for the space allocation of master/sysdb databases?

The databases are of around 300GB and tempdb is around 50GB.

Old Server

  • SQL Server 2008 / Windows
  • Xenon Quad Core x4 - 3GHz, 64GB RAM

New Server

  • SQL Server 2008 R2 RTM
  • Opteron 6 core x6 - 2.2GHz, 64GB RAM

Please let me know if any more details are needed.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
askids
  • 1,406
  • 1
  • 15
  • 32
  • What is an IMPL procedure? And can you show an example of some code? – Pondlife Nov 11 '10 at 22:54
  • Could you check out the query plans on the old server and the new server, and compare them to see if there is a difference? See this question: http://stackoverflow.com/questions/3944039/stored-procedure-hangs-seemingly-without-explanation – littlegreen Nov 11 '10 at 23:48

1 Answers1

0

User-defined functions in SQL Server are not just SPs that can return value (they cannot modify database tables, for example). Oracle functions can do basically the same what procedures can. Therefore a lot of Oracle functions are converted to a func_name$IMPL stored procedure + some wrapper function (that calls corresponding $IMPL procedure through SSMA-specific extended SP as far as I can remember). In cases where possible, SSMA will try to avoid calling functions directly (generating call to appropriate ...$IMPL SP instead), but some cases are not covered. Those harder cases will result in direct call to generated wrapper function. I believe they tend to be quite slow indeed :( Just no better automatic conversion was found in your case by SSMA... Therefore you should try to change your generated SQL Server code to use func_name$IMPL stored procedures directly (just using normal EXECs, and not calling wrapper functions as much as you can till performance is fine for you).

IgorK
  • 886
  • 9
  • 26