SQL Server Performance

Issues configuring new replacement server

Discussion in 'Performance Tuning for Hardware Configurations' started by bravo5, Oct 15, 2003.

  1. bravo5 New Member

    Hi, I am a vb/sql programmer but inherited SQL Admin responsibilities when our admin left the company about a year ago. So far I have managed to resolve any issues, but am now facing what I have been dreading - configuring a new server to replace our current production SQL server. I have installed SQL, set up databases, etc., and am now testing our apps against this new server and running into an issue with the cpu usage level.

    I am running apps against the new server at less than 1/4 of what would be normal usage on our current server, and it causes the cpu usage (in task manager) to consistently run at between 85-100%. The cpu usage levels on the current server run on average between 40-50%. I tested an app that I know hits the processor hard - against the current server with other normal production activity it only caused it to run at 55-60%, but running this app alone with no other activity against the new server caused the processor to run at about 95%.

    Server Info:

    Current Server
    - 2 733 Processors
    - 2GHz memory
    - no RAID

    New Server
    - 1 Xeon 2.6GHz processor (tried with hyperthreading on and off with same results)
    - 1GHz memory (ordered another 1GHz today)
    - RAID level 1

    Both servers:
    - Win2KPro, SP4
    - SQL2K Standard, SP2
    - Same SQL databases


    Any suggestions about what to do would be greatly appreciated!! If you need more info on the servers, please let me know.

    Thanks,
    Jennifer
  2. Twan New Member

    I'd suggest using profiler on the new server to find any queries taking longer than 100ms to complete (RPC:Complete and Batch:Complete) This might give you some queries to compare...?

    also is the RAID1 hardware or software? are both servers using the same default colations

    Cheers
    Twan
  3. satya Moderator

    Follow thru articles about PERFMON, PROFILER on this website homepage for more information.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  4. bravo5 New Member

    Thanks for the responses!

    Twan - The RAID1 is hardware. And I will run some queries against both servers to see the results.

    Satya - Thanks for the advice. I read many of the articles here and started some of the monitoring recommended. There are so many things to monitor it's a bit overwhelming! I already have seem some potential issues; I will keep going and may post back here for further suggestions when I have some concrete results.

    Thanks!

    Jennifer
  5. Luis Martin Moderator

    Did you run Upadate Statistics after install data base in new server?



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  6. bravo5 New Member

    Ok, I have read through an overwhelming amount of information on this site, including an article series on performing a "SQL Performance Audit". I've been running perfmon on both old and new servers, compared hardware/software configurations, etc. Made some recommended tweaks here and there, and have had no change with my main processor issue on the new server. It was suggested to me by my hardware team to make a support call to Microsoft, but I want to see if there is anything else I can do first. Are there other monitors specific to processor bottlenecks I can run based on my results below? Any further suggestions would be greatly appreciated!! I have more info if needed - just let me know.

    Thanks so much!
    Jennifer

    New Server now has been changed to:
    - 2GHz memory
    - System volume is RAID1
    - SQL data files are on RAID10 SAN volume (9 databases)
    - SQL transaction log files are on separate RAID10 SAN volume (don't have enough resources to have each trans log on it's own volume)

    ** Perfmon results from my new server are within normal range except:

    - Processor: % Processor Time - Average 87.047, Low 0.417, High91.563
    - System: Processor Queue Length - Average 4.587, Low 1.000, High 12.000
    (This is when only running the ONE app against the new server.)

    ** Perfmon results from my old server are all within normal range.
    - Processor: % Processor Time (Total) - Average 55.901, Low 26.196, High 81.269
    - System: Processor Queue Length (Total) - Average 1.195, Low 0.000, High 8.000
    (This is with the same app running against it plus normal daily production)
  7. bravo5 New Member

    Almost forgot...
    LuisMartin - yes, did run update statistics.

    Jennifer
  8. satya Moderator

    How about regular DBCC checks on the database where you're getting less performance?
    How about memory settings on the SQL Server?

    Better not to touch hyperthreading.
    Make sure to run regular dbcc checks and maintain backups to use just in case of failure.
    Other than that

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. bravo5 New Member

    (Please forgive my lack of knowledge here)

    - I am running integrity checks in a maintenance plan on a regularly scheduled basis, and I was under the assumption that this is running DBCC checks - is this correct?
    - I also am doing regular backups, but this server is not in production yet (won't be until I figure this out!), so I can always recreate databases if needed.
    - I'm letting SQL dynamically manage memory.
    - I have monitored with hyperthreading on as well - no improvement at all.

    I guess my main quesion at this point is - Could this be a "normal" result of only having 1 proccessor in this new server, even if it is much faster? And if so, shouldn't the results improve with hyperthreading? I've already suggested getting a 2nd processor, but need to prove that this is the reason. Could this be a bad processor? (Dell has already had to replace bad hardware on an identical server that was purchased at the same time.)

    Thanks so much,
    Jennifer

  10. satya Moderator

    You're correct about running maint.plan which includes dbcc checks.
    If the database is not critical then yes you can play around and recreated when required.
    Hyperthreading is new technology but can be used only if multiple processors are involved.
    Check this linkhttp://www.sql-server-performance.com/q&a113.asp for hyperthread Q&A.

    So if the hardware had played a part earlier then make sure to watch the counters and event viewer closely for an assessment.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page