| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2011-10-25 : 12:32:03
|
| I have five separate drive arrays on my sql server...c,d,s,t,u drivesThey are all RAID 5 (couldn't afford RAID 10 at the time).The OS is on the C drive. Our staging database in on the D drive. Our production database in on the S drive. The transaction logs are on the T drive and the tempdb is on the u drive.I have 12 separate tempdb files (one for each processor core). Since the C and D drives have very little IO stress I was wondering if putting two of the tempdb files on the C drive and two on the D drive with the remaining 8 staying on the U drive would improve performance? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-25 : 12:50:42
|
| Are you seeing IO contention on TempDB?btw, re that 1 tempDB per processor core myth: http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx--Gail ShawSQL Server MVP |
 |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2011-10-25 : 13:02:59
|
quote:
Originally posted by GilaMonster
Are you seeing IO contention on TempDB?
The disk queue lengths aren't very high on any of the drives but they are 1000 times higher on the tempdb drive then on the C drive. They do spike periodically on the tempdb drive. My thinking is that there is lots of unused IO on the C drive relative to the other drives so it may help flatten those spikes out. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-25 : 13:12:42
|
| Queue lengths are almost meaningless on a SQL Server, it can and will drive queue length high by design. (http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx)You need first to check for IO contention. If you don't have IO contention, then moving files for more IO throughput is a waste of time.--Gail ShawSQL Server MVP |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2011-10-25 : 13:27:03
|
quote:
Originally posted by GilaMonster
Queue lengths are almost meaningless on a SQL Server, it can and will drive queue length high by design. (http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx)You need first to check for IO contention. If you don't have IO contention, then moving files for more IO throughput is a waste of time.
Which perfmon counters do you recommend for measuring contention? % Disk Time? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-25 : 14:48:39
|
| http://technet.microsoft.com/en-us/library/cc966545.aspx--Gail ShawSQL Server MVP |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2011-10-25 : 15:10:34
|
quote:
Originally posted by GilaMonster
http://technet.microsoft.com/en-us/library/cc966545.aspx
Great link. I've added these to my perfmon counters...Avg. Disk Sec/Read: The average time, in seconds, of a read of data from the disk. Use the following to analyze numbers in the output. Less than 10 milliseconds (ms) = very good Between 10-20 ms = okay Between 20-50 ms = slow, needs attention Greater than 50 ms = serious IO bottleneckAvg. Disk Sec/Write: The average time, in seconds, of a write of data to the disk. See the guidelines for the previous item, Avg. Disk Sec/Read. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2011-10-25 : 15:35:52
|
| Looks like I'm wasting my time ;)"\\360767-DB2\PhysicalDisk(3 U:)\Avg. Disk sec/Write"" ""0.0016223221895480069""0.001769095846099052""0.0017202380917765794""0.0015415392299887444""\\360767-DB2\PhysicalDisk(4 T:)\Avg. Disk sec/Write"" ""0.0014507793457436741""0.0017915002437460048""0.0015534928527516185""0.0016581634862800668" |
|
|
|
|
tempdb and physical disks