SQL Server Performance

SQL 7 v.s. SQL 2000

Discussion in 'Performance Tuning for DBAs' started by ncblues, Dec 16, 2002.

  1. ncblues New Member

    We have an application that takes 4 times longer to run in the production SQL 2000 environment than it did in the development SQL 7 environment.
    I've looked at the performance monitor, but I don't see any problems using that.
    Any suggestions?

    thank you
  2. tkelley New Member

    Wow, are the machine's hardware configuration the same? If the Performance Monitor looks the same, then try looking at how the application accesses the datbase.. ADO, ODBC... won't hurt to check, you may need an ODBC driver update for 2000.?

    Check your table indexes.

    T Kelley

  3. satya Moderator

    With the help of PROFILER you can capture the trace which gives better information where its taking time on queries as Kelley referred.

    Satya SKJ
  4. bradmcgehee New Member

    Try rebuilding the indexes on the SQL Server 2000 databases. Whenever you convert a 7.0 database to a 2000 database, you need to do this to ensure that the statistics are up-to-date for the 2000 query optimizer.

    If that doesn't fix the problem, let us know.

    Brad M. McGehee
  5. ncblues New Member

    thanks, I'll try that
  6. satya Moderator

    True, having regular DB maintenance jobs and DBCC checks will keep the database healthy and well performed. You should look at the application behaviour when its querying the data with the help of PROFILER (as I'd referred above).


    Satya SKJ
  7. HarryArchibald New Member

    I experienced a similar problem when upgrading from 7 to 2000. After much investigation, the problem was from an unexpected source. At the same time as the database upgrade, the number of processors was increased from one to two. One query then ran over one hundred times slower than before and this query brought the rest of the system to its knees. Once the MAXDOP=1 option was used, the query ran as before.
    It may worth checking to see if something similar is happening.
  8. satya Moderator

    But before that you need to check for max degree of parallelism configuration option after you upgrade.

    Satya SKJ
  9. ncblues New Member

    I tried the rebuild and it helped a tiny bit. Where do I check for the degree of parallel?
  10. satya Moderator

    You can use either EM(Enterprise Manager) or QA(Query Analyzer):

    From EM right click on server to see the properties, goto Processor and you can find Parallelism box to see the contents.

    From QA you can use SP_CONFIGURE to list the current options, but intially you should execute

    USE master
    EXEC sp_configure 'show advanced option', '1'
    ... which will show advanced options, and then

    EXEC sp_configure

    Books online has got information required in this regard.


    Satya SKJ
  11. ncblues New Member

    found it!
    do i need to restart the server?
  12. satya Moderator

  13. ncblues New Member

    Under parallelism, I changed to Use 1 processor.
    It didn't help the transaction.
    The "minimum query plan" is still set to 5. Should that be changed also?

    thanks for all the input
  14. satya Moderator

    What is your server setup and hardware details, it may help to resolve?

    Satya SKJ
  15. ncblues New Member

    SQL Server standard edition
    Microsoft Windows NT 5.0
    SQL 8.00.534(SP2)
    NT Intel X86
    OS memory 2175(MB)
    2 Processors
    2,227,688(KB) RAM
  16. satya Moderator

    Did you run PROFILER trace and submitted to INdex tuning wizard for any recommendations, can you see any delays in the query?

    Satya SKJ
  17. ncblues New Member

    I did, but maybe I didn't run it long enough or trace the right things because it didn't show any problems.
  18. sqljunkie New Member

    try recompiling the stored procedures
  19. bradmcgehee New Member

    Have you identified the query or the queries that are now slower? That would be my next step, trying to identify specific problems by reviewing the execution plans of the slow performing queries. Also, is this the same hardware as before? Did you do a upgrade in place, or rebuild the server from scratch?

    Brad M. McGehee
  20. satya Moderator

    Actually in max degree of parallelism, set value to 1 to suppress parallel plan generation. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available CPUs is specified, the actual number of available CPUs is used.

    Satya SKJ
  21. ncblues New Member

    Whoops, I was gonna reply to the question about id'ing the query, etc. and left a quote by mistake.
    I'm an old mainframer. I've done some Unix and I've been to 1 SQL class and now I'm the SQL DBA. So I'm a bit dumb at this stuff right now. How do I id the query?
    The development server is SQL 7. I imported everything over to the SQL 2000 server.
    Does the programmer really need to recompile for the MAXDOP=1 to take effect?
    If I run another trace, which things should I specifically request?
    Sorry, I can't always get back to this right away. I'll try to leave the window open for a while.
    I really appreciate everyone's input.
  22. satya Moderator

    Using SP_RECOMPILE on SPs would help to recompile for a better execution. When you open PROFILER you can see the counters for SP and Transactions where you can see the activity.


    Satya SKJ
  23. bradmcgehee New Member

    You need to use the Profiler to capture a trace. You can learn the basics in the Books Online, and some tips for using it at:http://www.sql-server-performance.com/sql_server_profiler_tips.asp.

    What you can do is to sort the queries by duration, which makes it easy to identify the longest running queries. Then once you have identified one or more long running queries, then cut and paste the SQL code into Query Analyzer to view the execution plan. If you can identify the poorly performing queries, and get us an execution plan, you can post the excecution plan(s) here for some feedback. They are hard to interpret at first until you get some experience.

    Brad M. McGehee
  24. ncblues New Member

    ok, let me see what I can get for you - thanks again...
  25. mjm11 New Member

    When running the queries in Query Analyzer, you can also run SET STATISTICS IO ON to see the io cost of each query in text, Its a little easier to read than the execution plan. Good luck.
  26. ncblues New Member

    ok, i've got the trace. how do i get it to you guys.
  27. satya Moderator

    If not a problem for you zip it and send to my email satyaskj@yahoo.co.uk I will have a look and come back to you.

    As specified above make a regular maintenance checks on the database which helps in performance. If possible archive historic data which can be quieried using other database which will reduce the overhead on resources.


    Satya SKJ
  28. ncblues New Member

    I emailed the file - did you get a chance to look at it?
  29. satya Moderator

    Yeah I have recived it and in process to compile, will get back to you soon.

    Satya SKJ
  30. ncblues New Member

    Just wondering how things were coming. I haven't heard anything yet.

    Happy New Year!
  31. satya Moderator

    Hi Ncblues,

    Apologies for not updating, being my laptop crashed was bit refreshing all the s/w again. Will update in a day.

    Satya SKJ
  32. satya Moderator

    Right I had lookup into the trace provided and unfortunately nothing to suspect, as most of the queries deal with sensible timings. Its really hard now to say where it lacks the performance. I suggest you to put this trace into Index Tuning wizard for a better recommendation, if you haven't done yet.


    Satya SKJ
  33. ncblues New Member

    I tried the index tuning wizard.
    Thanks for all your efforts.

Share This Page