February 14, 2020 at 11:52 am
Hello,
I read in book of sql server administration
the number of files used for the same database must be equivalent to the level of parallelism defined for the cpu cores (maxdop) is a question included in the transaction log file or not?
So if I have maxdop 4, I make 1 mdf and 3 ndf + next to the ldf or I have 1 mdf, 2 ndf and 1 ldf included in the 4?
Thank you in advance,
Regards,
February 14, 2020 at 12:10 pm
could you post a link to that book - that used to be something that applied only to tempdb files, and even that is really a myth.
for most cases you do not need to split the files - and not based solely on that info you got and on a per CPU.
February 14, 2020 at 2:04 pm
Nope, nope, nope. That book is either wildly wrong, or you may have misread a section of it. A database only has to have two files, data & log. You can add additional files as a way to manage storage, or, if you have additional disks and additional disk controllers, as a way to help I/O performance. But I wouldn't add files to a database because of parallel execution. As was already noted, that prescription for tempdb is junk.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2020 at 3:58 pm
filegroups in a database can be used for a few things (filestream, memory optimised tables etc etc) but splitting your storage is your main gain.. (perhaps a tiny improvement if you are on separate spindles due to seek times)
I really wouldn't bother splitting, grant is correct - plus it will make your life a misery when you come to do scripting for restores
files in tempdb …. I stick with cpu count -1 (up to 8) mainly due to reading brent ozar's articles about locks and latches...
we fixed a lot of stuff by doing that, but tempdb is different to any other db, it is used by a lot more things (unindexed tables, bad joins etc)
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply