SQL Server Performance

Seeking Recommendation Confirmation

Discussion in 'Performance Tuning for DBAs' started by rerichards, Jan 26, 2006.

  1. rerichards New Member

    I have a SQL2K enterprise box with performance issues. It is composed of a single RAID 5 with 6 disks, identified as the "D" drive. It has 8Gb RAM, a fixed memory setting of 6079, with AWE enabled and 3GB/PAE set in boot.ini. Once the 8GB was added and AWE enabled, performance increased by about 50%, but performance is still unacceptable.

    I have posted Memory and Physical Disk counters on this website and was told that both are acceptable. While I have not posted the CPU counters on this website, I am fairly confident that they are acceptable too.

    I have run Profiler and it does appear some queries could benefit from some optimization. However, my instinct is that due to the across board, unacceptable performance, it goes beyond query tuning, though I am sure that would be of some help.

    In looking at the Log file and Data file placements, both the Log and Data files are place on the RAID 5, "D" drive. From the Physical Disk counters (as posted on this website) it appears I have 1% Read Time versus 70% Write Time. My recommendation would be:
    1. Place the Log files on RAID 1
    2. Obtain another RAID 5, separating the tables and indexes into separate filegroups, placing tables on one RAID 5 and indexes on the other RAID 5.

    Would you concur with the above as a reasonable recommendation?
  2. Twan New Member

    HI ya,

    if you were going to the trouble of getting another RAID 5 then you might as well put the log onto RAID1 and the data on another RAID1 (or 10)

    having said that query optimisation is always the first port of call for an application in trouble. It could give you performance gains that you could never get using hardware upgrades

    Cheers
    Twan
  3. brimba New Member

    To start with you could make a RAID 1 for the log file. If that isnt enough I would suggest that you put the data files on a Raid 10 array and increase the number of disks. Start with 8 or 10.

    You should only follow these recommendations if you do have disk performance issues.

    There could be other stuff that makes it goes slow.

    You mention that some quires could need some optimazation. Bad designed tables and/or if you are missing index or they are not used these kind of problems can show up real quick. They can slow down your entire system and all counters can seem fine because there is alot of waiting going on. That could be waiting for locks or some other things.

    I would suggest that you post performance counter result here again (cpu as well). And also that you do start hunting your bad (no index) and long running queries.
  4. joechang New Member

    i do not see how you can draw such conclusions from the counters that you posted,
    if the data and logs are on the same set of physical disks,
    then this could be one cause of poor performance,
    so you may as well split it,

    but i would not make the claim that this would actually solve any problems because you still have no idea what
    is the true cause of your problems
    based on the very limited set of counters that you have posted, i would say your problems are not in the disks, so why is the logical course of action change the disks?
    why are you sure your cpu is acceptable?

    if you feel that your system throughput it not what it should be or needs to be, and the problem is not in the disks, and not due to lack of memory, then that leaves you cpu and network or the client application

    every time you recommend to your boss a course of action that requires money, and requires taking your production database (?) offline, ie, notifying every department this is going to happen,
    and it turns out to have no meaningful impact, you lose credibility, until no one listens to any thing you have to say any more.

Share This Page