SQL Server Performance

RAID for Transaction Logs Involved in Replication

Discussion in 'Performance Tuning for Hardware Configurations' started by DBADave, Nov 5, 2003.

  1. DBADave New Member

    I began documenting recommended RAID configurations for our environment. Typically I encourage using RAID 1 for T-Logs since log writes are sequential. But then I started to wonder about how replication impacts T-Logs. According to BOL "The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database." This being the case, doesn't that indicate the disk spindles will be repositioned when the Log Reader Agent reads the logs and copies the data to the Distribution database? If so, is RAID 1 still the best option?

    Please let me know your thoughts.

    Thanks, Dave
  2. bradmcgehee New Member

    You have brought up a good point, one that I had never thought about before. I would have to guess, as you do, that using replication on a transaction log located on its on RAID 1 array will probably end up negating much of the benefit of putting the logs on their own array. It is still useful to put the logs on its own drive in the sense that doing so breaks up I/O onto different arrays, helping overall throughput. But without some thorough testing, I am not sure what the best option would be for the logs under this situation.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. DBADave New Member

    I agree that separating log and data is still a good idea, I'm just not sure anymore if there's much of a benefit to using RAID 1 vs. RAID 5 in this situation. You would probably still see better performance from a RAID 10, but the idea of T-Logs performing sequential writes may be meaningless depending upon the frequency of the Log Reader Agent. If you find anything on this topic please post an update.

    Thanks, Dave
  4. DBADave New Member

    Brad,

    One more thing just came to mind. The benefit of keeping transaction logs on RAID 1 is due to the T-Log writes being sequential. Ideally you want to have a RAID 1 dedicated to one T-Log, but this is not always financially possible. On one of my servers we have about 8 databases writing log data to the same RAID 1. Wouldn't that mean the potential exists for a large amount of head movement, assuming that most of the 8 databases are heavily involved in OLTP? For example, one transaction may involve updates, inserts and deletes against 3 databases. Each database would need to record T-Log information on the shared RAID 1. The disk spindles would be repositioned for each database writing its log data.

    What are your thoughts?

    Dave
  5. satya Moderator

    I think its better to seperate the Tlog files which are big in size and will have huge updations and access.

    DO you have any issues so far?

    And if not I suggest to keep on RAID1 unless there is a huge performance bottleneck.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. DBADave New Member

    Satya,

    I haven't seen any performance issues related to the logs. We're in the process of moving our data center so I'm documenting recommendations for server configs. I became concerned about performance when I learned the new servers were purchased with 10k rpm drives when our current drives are 15k rpm. I also learned the servers contain one dual channel controller while our current servers have 2 3-channel controllers. The company building our servers wants to have one partition for the O/S and logs and another for tempdb and data. I'm very much against this idea and am in the process of documenting my concerns. I've been thinking about the appropriate RAID 1 configuration, which lead me to wonder if there is a benefit to using RAID 1 vs. RAID 5 for logs when all databases (in our environment) store their logs on the same set of disks. I'm beginning to think the answer may be no due to all the head movement being generated for each db log write and read.

    Dave
  7. satya Moderator

    Then follow as it is by using RAID1 and keeping TEMPDB on seperate drives will definetly fetch performance.

    WHat is your opinion in opposing this?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. DBADave New Member

    I'm not really opposed to using RAID 1 in a situation where multiple databases share the same RAID 1 for logs, I just don't believe the performance gain would be that noticeable compared to using a less expensive RAID 5 solution. Cost asside, RAID 10 may be the best approach in this situation. In the case of each database log having a dedicated RAID 1, I can definitely see the benefit.

    Dave
  9. satya Moderator

    True, but on the cost element RAID 10 is expensive.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  10. nasi_aslam New Member

    Today I work on performance Tools
    Monitoring and Optimizing Sql server

    •Performance Monitoring
    •Query analyzer
    •Profiler
    •Filtering the Trace Date
    •Replaying a trace file
    •Index Tuning Wizard

    Performance Monitoring:

    Counter Name Average Minimum Maximum
    Memory: Pages/sec 40.000 0.000 859.00
    Memory: Available Bytes 193973501712537619395824
    Physical Disk: % Disk time 0.0500.0001.408
    Physical Disk: Avg. Disk Queue Length 0.050 0.000 0.000
    Processor: % Processor Time 4.8212.9878.988/35.88
    System: Processor Queue Length 3.5002.0004.000
    SQL Server Buffer: Buffer Cache Hit Ratio 99.88999.88999.888
    SQL Server General: User Connections 1.0001.0001.000



    Memory: Pages/sec:

    Check that number of pages per second that are pages out of RAM to disk
    SQL Server only major application on your server
    The less paging to perform
    Buffer Hit Cache ratio
    Recommendation
    Near Zero averages grater that 1 but less than 20 over a 24 hour period, but if the counter average over
    the 20 servers need more RAM,
    Remove the other application of the server,
    Memory: Available Bytes:
    If your server enough physical RAM is to check the memory object. Near 5MB or lower.
    This value should be greater than 5MB. If not, then your SQL Server needs more physical RAM.
    Available byte is near 5MB when this happen need to increase the amount of physical RAM in the
    server, reduces the load on the server, or changes your SQL Server#%92s memory configuration settings
    appropriately.

    Physical Disk: % Disk time:
    How busy a physical array is
    The % Disk Time counters less than 55%
    If this exceed 55% then server an I/O bottleneck.
    Include adding drives drives to an array (if you can),
    Getting faster drives,
    Adding cache memory to the controller card (if you can),
    Using a different version of RAID,
    Getting a faster controller, or
    Reducing the load on the server.

    Physical Disk: Avg. Disk Queue Length: %Disk Time Counter exceed 2 you probable have an I/O
    bottleneck
    Recommended: 2 per physical disk to calculate this figure by Performance Monitoring
    Physical disk=6
    Avg disk queue length=10
    Avg disk queue length for each drive is =1.66(10/6=1.66)
    Processor: % Processor Time
    Each CPU (instance)
    Total CPU (Total)
    If %Total Processor time (Total) exceeds 80% then you may have a CPU bottleneck.
    Then you may want to consider
    Reducing the load on the server,
    Getting faster CPUs,
    Getting more CPUs, or
    Getting CPUs that have a larger on-board L2 cache.



    System Processor Queue Length:
    If it is exceed 2 per CPU you also have CPU bottleneck
    Then reducing the SQL Server “Maximum Worker Threads” configuration setting

    Processor Queue Length + Processor Time determines CPU bottleneck

    SQL Server Buffer: Buffer Cache Hit Ratio: how often SQL Server goes to the buffer to get data or
    not the hard disk?
    Ratio 90% exceed & ideally over 99%
    90% b/w 99% then you purchases more RAM to increase SQL Server Performance.

    User Connections:
    Number of users connected
    255 recommended.
    If exceed boost SQL server configuration
    Setting “Maximum Worker Threads”
    Setting for “Max Worker thread” higher than “Maximum no of user connection”


    Check the all these counters by the Performance Monitor

    1.Memory: Pages/sec
    1st 2nd 3rd
    Current view: 31.954, 200414464,
    Buffer Hit Cache ratio: 99.889
    Paging:
    Recommended: avg greater than 1 but less than 20
    Result: The current list show avg greater than 20 % so need more RAM.

    Solution: Need more RAM
    On the Server other applications not run
    If more RAM has then check to less paging to perform and paging avg near zero

    2.Memory: Available Bytes
    1st 2nd 3rd
    Current view:
    16449536.000, 20414464.00,
    Recommended:
    Near 5MB or lower
    Result:

    Solution:
    Increase the amount of Physical RAM
    Reduced the load on the server.
    Change your SQL server memory configuration setting.


    3.Physical Disk: %Disk Time

    1st 2nd 3rd
    Current view:
    46.250 59.567 0.00
    Recommended:
    Less than 55%

    Result: in some time it exceed 55%
    Solution:
    Including add drives.
    Getting faster drives.
    Adding cache memory to the controller card____ RAID

    Disadvantage: I/O bottleneck

    4.Physical Disk: Avg. Disk Queue Length

    1st 2nd 3rd
    Current view:
    00.02 0.666 0.000
    Recommended:
    2 per physical disk

    Result:
    Solution:
    Disadvantage:
    You probable have an I/O bottle neck.


    5.Processor: % Processor Time

    1st 2nd 3rd
    Current view:
    100.013 100.014 21.00
    Recommended:
    80%

    Result: Our DB performance list show greater than 80%
    Solution: Reducing the load on the server
    Getting more CPU.
    Getting CPU that has a larger on board L2 cache.
    Disadvantage:
    If 80% exceed then CPU bottleneck

    6.System: Processor Queue Length

    1st 2nd 3rd
    Current view: 5.00 3.00 8.00
    Recommended:
    2 per CPU

    Result: Our DB performance list show greater than 2 per CPU and on the server only 1 CPU attach
    Solution: Reducing the SQL Server “Max worker threads” configuration setting
    Disadvantage: CPU bottleneck

    7.SQL Server Buffer: Buffer Cache Hit Ratio
    1st 2nd 3rd
    Current view:
    99.889 99.899 96.86
    Recommended:
    90% up to 99%

    Result: Our DB performance list show greater than 90%
    Solution: To Purchase more RAM to increase SQL server performance


    8.SQL Server General: User Connections

    1st 2nd 3rd
    Current view:
    1.00 5.00 3.00
    Recommended:
    255
    Result:
    Solution: if exceed boost SQL server configuration setting “Max worker thread”
    Setting for”Max worker thread” higher than “Max number of user connection”

    SQL Server Hardware Characteristics Describe Here
    Number of CPUs 1
    CPU MHz 500 MHz
    CPU L2 Cache Size
    Physical RAM Amount 256
    Total Amount of Available Drive Space on Server 49.8 gb
    Total Number of Physical Drives in Each Array
    RAID Level of Array Used for SQL Server Databases No
    Hardware vs. Software RAID No
    Disk Fragmentation Level
    Location of Operating System C: Location of SQL Server Executables C: Location of Swap File
    Location of tempdb Database
    Location of System Databases
    Location of User Databases
    Location of Log Files
    Number of Disk Controllers in Server 4
    Type of Disk Controllers in Server
    Size of Cache in Disk Controllers in Server
    Is Write Back Cache in Disk Controller On or Off?
    Speed of Disk Drives
    How Many Network Cards Are in Server?
    What is the Speed of the Network Cards in Server?
    Are the Network Cards Hard-Coded for Speed/Duplex?
    Are the Network Cards Attached to a Switch?
    Are All the Hardware Drivers Up-to-Date?
    Is this Physical Server Dedicated to SQL Server?


    plz tell me about this am i right to tune his database aur not
    and give me other tips to tune database with all detail

    thanx alot

    Nasiha







    quote:Originally posted by bradmcgehee

    You have brought up a good point, one that I had never thought about before. I would have to guess, as you do, that using replication on a transaction log located on its on RAID 1 array will probably end up negating much of the benefit of putting the logs on their own array. It is still useful to put the logs on its own drive in the sense that doing so breaks up I/O onto different arrays, helping overall throughput. But without some thorough testing, I am not sure what the best option would be for the logs under this situation.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  11. satya Moderator

    For 1 & 2 - yes more RAM is required and would definetly help.<br />3 - About RAID refer thru other threads under &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />erformance tuning for Hardware Configuration&gt; forums and you should be fine using RAID 5, where general recommendation to keep data and log files seperately.<br />4- can be resolved by using correct RAID as per your requirement.<br />5 & 6 - A continually high CPU usage rate may indicate the need for a CPU upgrade or the addition of multiple processors. Alternately, a high CPU usage rate may indicate a poorly tuned or designed application. Optimizing the application can lower CPU utilization. <br /><br />If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor's time can be used. If this causes the performance of other applications to suffer, try changing the workload (for example, by dedicating the computer to running the instance of SQL Server).<br /><br />Resolve the problem by adding more powerful processors.<br /><br />8 - No need to increase value for user connections, if the current value performance is fine.<br /><br />Refer to this link<a target="_blank" href=http://www.sql-server-performance.com/q&a38.asp>http://www.sql-server-performance.com/q&a38.asp</a> about placing data & log files on RAID.<br /><br />Review the information on this website under TIPS category for database tuning and performance.<br />Adding more RAM will always help to gain the performance and provided keep the memory settings to dynamic and if its not a dedicated SQL Server then think of allocating require memory to SQL atleast.<br /><br />Ensure to schedule maint.jobs and DBCC checks against database regularly to fine tune the performance.<br /><br />_________<br />Satya SKJ<br />Moderator<br />SQL-Server-Performance.Com<br />
  12. satya Moderator

    Moreover your thread is unclear about what you want to achieve for these figures.
    Be specific about query.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  13. fullbrij New Member

    A couple counter you were missing:

    Disk Sec/write
    Disk sec/read

    There two counters are much more telling of an IO issue than queue length or %disk time. For SQL, we want writes to the T-logs to take less than 5 ms or .005 on Disk sec/write counter. We want reads of the database less than 40 ms or .040.

    Another important counter is split IO/sec. Ideally, we don't want to see any split IO on the database or T-Log drives. You'll see a significant number if you used the default allocation unit size when you formatted the drive. The default allocation unit size is 4K. SQL uses an 8K IO size for the database and T-Logs. If you went with the default allocation unit size, then each request from SQL will be split by the OS into two requests. That's a bad thing. If you are running an OLAP application that reads many sequential rows, SQL 2K will kick in with read-ahead. It reads ahead about 2000 pages with an IO request size of 64K. If you are running such an app, you my want to set the default allocation unit size at 32K to help minimize the impact. Backups usually read in 64K chunks, so if backup speed is a concern, again you may want to bump up the allocation unit size for the database drive to 32K.

    Why not 64K? In most drive geometries, a track contains 64 512 byte sectors, or 32K. If we cross a track boundary, it adds the head step time [about 1 ms] to each IO. That's a bad thing, and it also leads right into disk alignment. For a basic disk, Windows reservs the first 63 sectors of a volume for volume information. The first partition mormally starts on the 64th sector... that's the last sector of the first track. With an 8K IO size and allocation unit size, that means that evry 4th IO will cross a track bounday and incur a 1 MS penalty. You can use disk par to set the offset to 64, so that the partition wil start on the first track of the 2nd sector. Performing disk alingment in this way typically results in a 20% performance increase.

    Track alignment isn't the only think aligning your partition fixes. You also have to worry about chunk alignment. A typical chunk size is 64 or 128K in RAID 10 or RAID 5. If you cross a chunk boundary with a single IO, that results in IO against two spindles on the backend.

    Some food for thought anyway...





    John
    MOSMWNMTK

Share This Page