Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Number of files for database MDF


Number of files for database MDF

Author
Message
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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;-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45145 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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 Sad... 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...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 2235
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."
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 2673
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search