SQL Server Performance

SQL 7 v.s. SQL 2000

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

Page 1 of 2

  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
    ncblues, Dec 16, 2002
    #2

  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
    MS, MCDBA, OCA, CIW

    tkelley, Dec 16, 2002
    #2

  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
    satya, Dec 16, 2002
    #2

  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
    Webmaster
    SQL-Server-Performance.Com
    bradmcgehee, Dec 16, 2002
    #2

  5. ncblues New Member

    thanks, I'll try that
    ncblues, Dec 16, 2002
    #2

  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).

    HTH

    Satya SKJ
    satya, Dec 17, 2002
    #2

  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.
    Cheers,
    Harry
    HarryArchibald, Dec 17, 2002
    #2

  8. satya Moderator

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

    Satya SKJ
    satya, Dec 17, 2002
    #2

  9. ncblues New Member

    I tried the rebuild and it helped a tiny bit. Where do I check for the degree of parallel?
    ncblues, Dec 17, 2002
    #2

  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'
    RECONFIGURE
    ... which will show advanced options, and then

    EXEC sp_configure

    Books online has got information required in this regard.

    HTH



    Satya SKJ
    satya, Dec 17, 2002
    #2

  11. ncblues New Member

    found it!
    do i need to restart the server?
    ncblues, Dec 17, 2002
    #2

  12. satya Moderator

    satya, Dec 17, 2002
    #2

  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
    ncblues, Dec 17, 2002
    #2

  14. satya Moderator

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

    Satya SKJ
    satya, Dec 17, 2002
    #2

  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
    ncblues, Dec 17, 2002
    #2

  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
    satya, Dec 17, 2002
    #2

  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.
    ncblues, Dec 17, 2002
    #2

  18. sqljunkie New Member

    try recompiling the stored procedures
    sqljunkie, Dec 17, 2002
    #2

  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
    Webmaster
    SQL-Server-Performance.Com
    bradmcgehee, Dec 17, 2002
    #2

  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.
    ~
    HTH



    Satya SKJ
    satya, Dec 18, 2002
    #2

  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.
    ncblues, Dec 18, 2002
    #2

  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.

    HTH

    Satya SKJ
    satya, Dec 18, 2002
    #2

  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
    Webmaster
    SQL-Server-Performance.Com
    bradmcgehee, Dec 18, 2002
    #2

  24. ncblues New Member

    ok, let me see what I can get for you - thanks again...
    ncblues, Dec 18, 2002
    #2

  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.
    mjm11, Dec 18, 2002
    #2

  26. ncblues New Member

    ok, i've got the trace. how do i get it to you guys.
    ncblues, Dec 19, 2002
    #2

  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.

    HTH

    Satya SKJ
    satya, Dec 19, 2002
    #2

  28. ncblues New Member

    I emailed the file - did you get a chance to look at it?
    ncblues, Dec 20, 2002
    #2

  29. satya Moderator

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

    Satya SKJ
    satya, Dec 20, 2002
    #2

  30. ncblues New Member

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

    Happy New Year!
    ncblues, Jan 2, 2003
    #2

  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
    satya, Jan 3, 2003
    #2

  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.

    HTH

    Satya SKJ
    satya, Jan 5, 2003
    #2

  33. ncblues New Member

    I tried the index tuning wizard.
    Thanks for all your efforts.
    ncblues, Jan 6, 2003
    #2
Page 1 of 2

Share This Page