SQL Server Performance

Poor Performance on New Server

Discussion in 'Performance Tuning for DBAs' started by darrenwhite, Jan 18, 2003.

  1. darrenwhite New Member

    Just bought a Dual Xeon (Hyper-threading switched off), 1.5GB DDR Compaq box to run our main database on. We've brought a load of 6.5 data across to 7.0 because the support on our old database system has ceased, and we've had to purchase a new product. We are licensed for MS Backoffice 4.5, and the business didn't want to spend money on SQL2000.

    It's got 5x 15K drives on a Compaq 5312 controller. (Nice) RAID5 is used, because there would be no benefit in having a separate mirrored OS partition, given the already fast disk subsystem.
    Page file for 2K is set to 3GB. (Double the server memory)

    I'm running WIN2KS & SQL7 Standard.

    The database has been on the server since Monday.

    However, the 1GB database on it is used for two purposes.

    1) Simple record entry and query. This doesn't seem to be a problem. Until:

    2)The 'MIS' system is run. Which is basically a database 'View' which is then further manipulated to produce specific results requested at the front end.

    Problem is, when an MIS query is performed in test (Not released to the users yet), the client front end for (1) above starts having connection problems, with user errors.

    I've run Performance Monitor which goes wild as soon as MIS is run.

    Counters:
    CPU1: Between 90% and & 100% whilst MIS query is run.
    CPU2: Ditto
    Processes/SQLserver: Ditto
    Processor queue: between 4 & 10 whilst MIS is run.

    The counters stay like this for about a 45 seconds, and then even the MIS system dies at the front end.

    The 'SQL-Total memory' counter stays at about 1290MB, which suggests that SQL knows to leave 210MB or so to the OS. (Memory is configured dymanically)

    I've spoken to the developers, and they've shown me the MIS database 'View' which basically a 2 page query on the 1GB database.

    My question is, firstly, does the 1290MB setting that SQL has found for itself suggest I should buy another 1GB? Would this help performance?

    Or is it likely that the 'View' query needs to be thought out more carefully.

    Many thanks,

    Darren
  2. Argyle New Member

    SQL Server 7.0 storage and query optimization works differently than SQL 6.5 so I would recommend checking your indexes first of all.

    Small checklist:
    - Apply latest service pack for SQL 7.0 (SP4)
    - Run index tuning wizard when running the query to see if it suggests different indexes
    - Run sp_updatestats on the database
    - If that doesn't help, tune the query

    Are you running the databases in 6.5 or 7.0 compability mode?

    /Argyle
  3. darrenwhite New Member


    No, the 6.5 tables were DTSed into the new 7.0 server, and a new database was then created from this data.

    So much for assuming that dual 2.4GHz would be a new era! (Should have realised that from when we bought a dual Xeon 550 a couple of years back)

    The indexes haven't been looked at yet, so I suppose I need to take care of them before we proceed.
  4. bradmcgehee New Member

    My gut feeling is that it is also, as Argyle said, an index issue, or a poorly written query. If you have not done so yet, get an execution plan of the "View" query and see what is happening under the covers. This will provide good clues as to what you need to do first to boost performance.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page