SQL Server Performance

Win2K - SQL2K - performance question

Discussion in 'Performance Tuning for DBAs' started by dajelen, Feb 3, 2004.

  1. dajelen New Member

    We have an 8-way server running just SQL on Windows.

    The performance of the system was OK when it was running WinNT and SQL7.
    When we installed Win2000 with SQL7, performance was terrible.
    Now that we have Win2000 and SQL2000, performance is a little better, but still too slow.

    We are running a third-party application and don't have access to the code
    for tuning purposes. (The application is the same, no changes to it.)

    Any ideas on why performance took such a dive?

    Dave
  2. Chappy New Member

    Impossible to say without understanding what the app is doing.
    Run a profiler trace and capture a set of queries which perform badly for a given session.
    Maybe they are forcing optimiser hints which are no longer optimal, maybe the statistics are out of date etc, many potential reasons.
  3. dajelen New Member

    Could you elaborate a little on other potential reasons?

    We did update statistics on the all of the databases on the system.
    The application itself didn't change nor did the database, except for
    being updated from SQL7 to SQL2000.

    Thanks
  4. Luis Martin Moderator

    What kind of Update Statistic do you run?.
    If default, then statistics where updated 10%.
    If was in that way, try running with full scan (UPDATE STATISTICS (.....) with fullscan).
    Or via scheduled job, change 10% to 100%.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  5. joechang New Member

    i would run profiler first to find which queries are the cause, before looking at statistics, sometimes its not the detailed statistics but rather the pages which can be fixed with dbcc updateusage(0)
  6. dajelen New Member

    The script to update statistics changed to the database, then "exec sp_updatestats".
    Is there a better way that this should have been done?

    The databases are on an external disk array.
    They were detached from SQL7, data cable unplugged, then WinNT was updated to Win2K and
    the databases reattached.
    For the next upgrade, the databases were detached from SQL7, data cable unplugged, then
    SQL7 was uninstalled and SQL2000 was installed. Then, the databases were reattached.
    The system went through the upgrade process on the databases - there was a whole
    string of messages like "database version 501 upgraded to 502" for each database, then
    the system said that the database had been upgraded (presumably to SQL2000).
    Except for the SQL2000 upgrade process and the sp_updatestats process the
    data and log files for these databases weren't touched.

    Again, we run a third-party application with encrypted stored procedures,
    would running profiler be that helpful?

    Can I presume that this syntax for UPDATE STATISTICS would need exclusive access to the database (as would DBCC UPDATEUSAGE)?

    Dave
  7. Chappy New Member

    although you cant modify the software, it might still help to profile it.
    From this, you may be able to see indexes which would now be beneficial but perhaps werent as evident on sql 7. Or if the 3rd part ysql is using optimiser hints at least you have some ammo to ring up the vendor and ask for a version optimised for 2k
  8. dajelen New Member

    Interesting comment about indexes.

    Would I be able to tell that they are using optimizer hints in
    encrypted stored procedures?

    Thanks for the input, I'll start profiling to see what I can see.
  9. gaurav_bindlish New Member

    I would recommend rebuilding the indexes in the database. I had faced a similar situation where rebuilding indexes actually helped and reduced the space used in the database.

    My 2 cents....

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  10. dajelen New Member

    Do you recommend DBCC CHECKDB or DBCC REINDEX ?
    If DBCC REINDEX, is there a way to have it run across all tables in the database?

    2 cents is still valuable.

    Dave
  11. gaurav_bindlish New Member

    DBCC DBREINDEX

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  12. Chappy New Member

    No, without knowing the sql behind each stored proc, you cannot see if they use optimiser hints. <br />However, the encryption used by sql is very poor, and there are tools to decrypt stored procs. <br /><br />Be aware however, that this is considered reverse engineering and may be frowned upon under your countries laws, but in your situation, if all else fails, I think you might want to consider these tools in the interests of research! <img src='/community/emoticons/emotion-5.gif' alt=';-)' />

Share This Page