multiple data files for larger databases?

Question

Post reply

multiple data files for larger databases?

akarczewska

Mr or Mrs. 500

Points: 535
More actions
October 19, 2010 at 3:30 pm

#225288

Hi
Is the advice to have multiple data files for larger databases still accurate in SQL 2008 R2?
Do you have any documentation for this?
Thank you
AK

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The recommendation is "It Depends."

What do you consider a larger database?

What is your disk configuration?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Another factor to consider is how frequently is the database accessed (is it a high volume transactional database)?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 3

In general, yes.

there are a bunch of considerations. But, as a genreal dividing line, Is this for a OLTP or OLAP database?

Here are some articles:

http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx

http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

If you are doing a data warehouse you might want to use the -E to increase the number of extents that are allocated for each file group.

http://msdn.microsoft.com/en-us/library/ms190737.aspx

akarczewska Mr or Mrs. 500 Points: 535 More actions · Answer 4

It’s an ERP database about 300 users and will be about 100GB in 3-4 years.