Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Number of files for database MDF Expand / Collapse
Posted Friday, October 11, 2013 4:04 PM

Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 18, 2016 2:29 PM
Points: 592, Visits: 1,330
Nadrek (10/11/2013)
Gail and Jeff hit the nail on the head in the first two replies.

With the benefit of all the posts ahead of mine, I suspect your actual question is closer to "My IOStall for writes is much slower than for reads; what can I do to improve performance".

I can say that RAID5 parity with a modern controller is extremely unlikely to be the cause of the slowness; evidence from a TempDB (one data file, one log file, both on the same RAID5, with 6 dedicated SSD's) is that even with signficant usage, average IOStall for reads is 2ms, and average IOStall for writes is also 2ms (2TB of total reads, 3TB of total writes since last restart).

I'd definitely look into whether your spindles are dedicated or not - if they're not, your measurements are necessary but insufficient. I'd also check into all the usual suspects first - wait types, index fragmentation/split IOs, filesystem (NTFS) fragmentation, etc.

Everything else is fine, no "panic" wait types, no index fragmentation (job for that), NTFS no problem either (64K alloc units) and occasionally defrag....
It's easy to have RAID5 with 2ms with SSDs... Heck, I have 2ms without RAID5 on a single SSD...
These are 15K discs and a very intensive write software on tempdb... other databases have 20ms to 5ms, but tempdb is getting killed...
I'll just try adding a single SSD disk and move tempdb there to see the new stalls..


If you need to work better, try working less...
Post #1504163
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse