July 7, 2010 at 7:13 am
I assume many DBA's will have this kind of problems and most of the time it is challenging to convince network admins to get things done in our way.
Disk I/O issues are very common in my environment though we deal with terabytes of data with better hardware, I/O is the only bottle neck we expreince all the times.
Here is the Configuration:
32 GB RAM
8 CPU's (PIII Xeon)
Win 2003 Ent / SQL 2005 Ent
RAID 5 on SAN (shares LUN)
Most of our drives are very big ranging from 2TB-6TB in single volume.I always recommened to have data files physically seperated with log files to get better performance but my manager(network admin) argue that it doesnt matter as they are laid out on SAN, still in debate.
Recommendations to avoid Disk I/O Issues
1. I am planning to add more memory as i feel that is the cause os more disk activity. Though % of CPU's are utilised only 30% but still performance is very bad due to I/O problems.
2. As most of our users use tempdb(which can not be avoided) by pulling millions of records in temp tables, i thought i have better RAID config for the drive that has tempdb file, any comments?
Please suggest any other recommendations.....
July 7, 2010 at 1:35 pm
any comments on this topic ?
July 7, 2010 at 2:27 pm
You didn't post any evidence to support your contention that IO is an issue. Perf mon counters, etc. What are the results you saw when you ran standard IO performance testing, like SQLIO?
It very likely that badly written queries are a major source of the problems. What have you done to investigate this?
July 7, 2010 at 10:15 pm
Comment on your second recommendation:
[ 2. As most of our users use tempdb(which can not be avoided) by pulling millions of records in temp tables, i thought i have better RAID config for the drive that has tempdb file, any comments? ]
a. If tempdb is heavily used, performance will benefit if TEMPDB is placed on RAID 1+0 (or RAID 5)
b. Make sure to pre-size TEMPDB to avoid auto-growth
c. Create 1 data file per CPU for TEMPDB
July 8, 2010 at 5:21 am
Oh god I have the same issues with the hardware guys claiming a SAN doesn't need a data/log split because its a fast SAN. Why is it so hard to get through to them? There is an MS Best Practise somewhere (google it) that shows that the physical split for best practise still applies, just that you need to split the Data and Logs over different LUNs.
Also, the number of Host Bus Adaptors comes into play for data access speed (i.e. If you have 2 LUNs of 10 disks. Each LUN has its own HBA, and you have Data on 1, and Logs on the other - it isn't rocket science to see (other than a stubborn attitude) that it will perform much better than Data and Logs sharing 20 disks on 1 LUN through one HBA.
Hardware admin assume that because the load is spread over the 10-20 super fast drives of the SAN that performance will not be an issue but then they are not necessarily up on what makes SQL perform best.
EDIT: First return on Google is a good one and should help your cause -
July 8, 2010 at 7:20 am
shark, i agree with you but its turns out to bev ery diffucult to make them understand.
July 8, 2010 at 7:22 am
could you please explain how you will create seperate data file fro each processor, i know how to create multiple data files but how would i allocate them for each processor ?
July 8, 2010 at 8:46 am
If you are referring to the tempdb tip above, I personally have never explicitly set each tempdb data file to a CPU. Its just matching the numbers by creating 1 per CPU.
July 8, 2010 at 12:03 pm
Tara-1044200 (7/8/2010)
shark, i agree with you but its turns out to bev ery diffucult to make them understand.
I find that it is easier to just shoot them when they act like that.
The probability of survival is inversely proportional to the angle of arrival.
July 8, 2010 at 3:00 pm
SQL Marathoner (7/7/2010)
c. Create 1 data file per CPU for TEMPDB
That "best practice tip" is not entirely true and could very well be counter productive to performance. If you don't suffer from allocation page contention. I'm not saying that adding more tempdb data files would be bad, as always "it depends". Too many files could cause the disk read/write heads and the platters to move unnecessary. Instead of writing "sequentially" to one file the heads and platters will have to move to write to all the files.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply