Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Number of files for database MDF Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 8:43 AM


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: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Hi,

I have a system with 1 Quad core CPU and 32GB of RAM.
The database files, the MDF only, are on a RAID 10 system (the LDF is on a RAID1 and tempdb on a SSD disk).
The database is for an ERP application that's quite write and read intensive.
Is it advisable to create an "extra" FILEGROUP and add another file (NDF) to the database and move some tables to that FILEGROUP?
Some queries use UNIONs between two large tables. If on table was on PRIMARY FILEGROUP and the other table on my extra FILEGROUP would there be any advantage?

Thanks,
Pedro




If you need to work better, try working less...
Post #1503144
Posted Wednesday, October 9, 2013 9:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
see http://blog.idera.com/sql-server/performance-and-monitoring/increase-sql-server-performance-using-multiple-files/

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1503168
Posted Wednesday, October 9, 2013 9:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 42,437, Visits: 35,491
First question, why are you splitting it, recoverability or performance? If performance, have you checked whether the current bottleneck is IO?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1503172
Posted Wednesday, October 9, 2013 11:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
PiMané (10/9/2013)
Some queries use UNIONs between two large tables. If on table was on PRIMARY FILEGROUP and the other table on my extra FILEGROUP would there be any advantage?


Unless you could guarantee that the two filegroups where on physically different drives/spindles, the answer is probably "No".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1503219
Posted Wednesday, October 9, 2013 12:59 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: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
GilaMonster (10/9/2013)
First question, why are you splitting it, recoverability or performance? If performance, have you checked whether the current bottleneck is IO?

Yes, the average stalls per write on tempdb is 60ms and read isn't much better...
The big SELECT .. FROM (SELECT ... FROM .. UNION ALL SELECT ... FROM ...) t ORDER BY ... probably uses loads of tempdb... Probably would get better results by putting tempdb on it's own LUN, probably SSD disk... (I mixed up two servers I'm checking ... this one has tempdb on a RAID5 15K disks along with the data files...)

Thanks,
Pedro




If you need to work better, try working less...
Post #1503268
Posted Wednesday, October 9, 2013 2:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 42,437, Visits: 35,491
60ms isn't bad. Not perfect, but way better than I've seen in many cases.
If you're seeing IO bottleneck on TempDB, splitting the user DB into multiple files isn't going to do much.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1503290
Posted Wednesday, October 9, 2013 2:58 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: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
GilaMonster (10/9/2013)
60ms isn't bad. Not perfect, but way better than I've seen in many cases.
If you're seeing IO bottleneck on TempDB, splitting the user DB into multiple files isn't going to do much.

I think that time is mainly because it's raid 5, the parity check delays a bit...
The user databases have from 22ms to 9ms...
Tempdb has 4 500MB files but it's real size is already 2.5MB witch means it has grown over the estimated start size (2GB).
Since there are only 4 cores I'll change tempdb to 2 2GB files and see what happens...
Also the main user database has 100GB and it's file growth is set to....... 1MB. 1 single sales operation makes the database grow 5 times... I'll set that to 200MB witch leaves "room" for almost 2 months of data.

Thanks,
Pedro




If you need to work better, try working less...
Post #1503313
Posted Thursday, October 10, 2013 3:19 AM


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: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
GilaMonster (10/9/2013)
60ms isn't bad. Not perfect, but way better than I've seen in many cases.
If you're seeing IO bottleneck on TempDB, splitting the user DB into multiple files isn't going to do much.


Read operations are much faster... 2,7ms...
Each tempdb file, of the 4, has:
* io stalls read ms:
3.162.979
3.310.405
3.221.457
2.897.618
* # reads:
1.232.588
1.231.180
1.231.815
1.231.330
* io stalls writes ms:
77.075.513
76.980.693
76.948.258
76.979.508
* # writes:
1.332.084
1.331.643
1.331.944
1.330.562

These stats have been taken from Set 25th at 6PM till Oct 8th at 10AM... 12 and 1/2 days with 2 weekends (4 days with low ERP usage). 12,5 day = 1.080.000.000 ms.... 30% of the time tempdb has been io stalling for writes! Is this right or since it's 4 different files is just 1/4 of 30% ?!

Thanks,
Pedro




If you need to work better, try working less...
Post #1503430
Posted Thursday, October 10, 2013 4:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:56 AM
Points: 2,603, Visits: 2,061
Agreed with Jeff, it all depends on the desing of the LUNs.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1503455
Posted Friday, October 11, 2013 3:49 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 861, Visits: 2,356
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.
Post #1504159
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse