Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rerichards
Starting Member
10 Posts |
 Posted - 2006-01-26 : 07:27:27
|
| 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 12. 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? |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2006-01-26 : 09:54:08
|
| Separating Data and Log files is definitely a good idea. As for separating tables and indexes onto separate arrays, try to discover where you are reading your data from, and where you are writing it to. Many systems tend to use tempdb a lot, so moving tempdb files/logs onto separate arrays can yield more benefit then separation of tables and indexes. And make sure you keep statistics up to date (try specifying higher sampling rates for larger tables), look at optimizing your queries, adding indexes etc... A lot of times these things would yield more performance gains then simply throwing more hardware at the problem. |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2006-01-26 : 15:51:13
|
| It is best to start from a known baseline. My recommendation would be to quantize your hardware performance counters and to get specific response time measurements even if all you are doing is confirming what you already believe to be true. It sounds like you are already doing some or most of this now. This exercise might even point out some bottlenecks that you were not aware of. If nothing else, it allows you to stop worrying about these items and focus your engineering on other potential culprits.Specifically, I would want to know the values for the following performance counters: Memory Pages/sec Physical Disk % Disk time Physical Disk Avg. Disk queue Length SQL Server: Buffer Manager Buffer cache hit ratio SQL Server: Cache Manager Cache hit ratio SQL Server: Locks Lock Wait Time (ms) SQL Server: Locks Number of Deadlocks per sec. SQL Server: SQL Statistics SQL Compilations/sec SQL Server: SQL Statistics SQL Re-Compilations/secNow when you implement an improvement, you will be able to measure the boost you got. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-01-26 : 16:17:25
|
quote:
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 12. 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?
If you have the cash, RAID 1 for the lot. Your write/read ratio is high.. DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
|
|
|
|
|
Seeking Recommendation Confirmation