Does SQL Server write log files in parallel when multiple databases are involved? -

Does SQL Server write log files in parallel when multiple databases are involved? -

i've read quite bit on stack overflow , other sites that, respect sql server, giving single database multiple log files not help improve performance. number of people have separately made blanket statements splitting 1 database many not improve performance, haven't explained why. 1 of colleagues insists using multiple databases in fact improve performance, because says log files can written in parallel if multiple databases used, reducing transaction-log-related io bottleneck. unfortunately, can't find online--either on or otherwise--to back upwards position.

the web site , associated windows services i'm developing receive huge amount of database traffic, i've been told need split database multiple smaller databases transaction logs don't cause bottleneck (i.e., so, example, 3 heavily accessed tables in 3 separately databases can updated simultaneously). i'm hesitant because i'd lose ability utilize foreign keys , lose referential integrity.

i sent colleague number of links stated multiple databases don't improve performance, responded one:

http://dba.stackexchange.com/questions/62344/multiple-transaction-log-files-and-performance-impact

notice how top reply asserts "transaction log writes sequential. 1 of log files ever written @ 1 time, having multiple files - in , of - can't perchance alter i/o patterns for database."

could please shed lite on how transaction log io works across multiple databases, , whether sequential logging in fact per-database limitation?

sql server needs single sequential log file per database, in order maintain it's acid properties. while can create sec log file, shouldn't unless under emergency situations, drive ran out of space don't have improve alternative @ time.

you can see sql server sec log file on test server. while under active workload, watching in resource monitor or perfmon, create sec log file. you'll see brief amount of activity while gets allocated , initialized, engine ignore new file virtual log file chain stays in original file(as long file healthy). if have write contention in log file way want stick single file on separate dedicated drive optimized sequential write i/o performance raid 10.

for more on write ahead logging , multiple log files see these articles: https://technet.microsoft.com/en-us/library/ms186259%28v=sql.105%29.aspx http://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/

throwing out foreign keys bad idea. sql server can utilize multiple database(not log) files actively, can stick specific objects specific file, , partition single tables different files.

really depends on i/o pattern on how configure things. of course of study situation if info doesn't need acid properties , write performance of import thing, utilize sort of nosql database. microsoft has been doing lot of work bring hadoop sql server eco system.

it recommended utilize multiple database files, check out paul randal's blog on sql skills more information.

sql database-performance