SQL Server Performance Forum – Threads Archive

RAID for Transaction Logs Involved in Replication

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

---

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

---

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

---

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

---

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

---

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

---

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

---

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

---

True, but on the cost element RAID 10 is expensive. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

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

---

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 />

---

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

---

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

---

]]>