Take the 2-minute tour ×

I'm working on a web2 project that i would like has thousands of rows per day by users. for handling this size of data i designed database like this: one .mdf and .ldf file as Minor DataBase and 1 Major DB to save and query the User Accounts and DataBase Files addresses.

i have worked several months for this plan and now i can manage it easily. i want to know if it is good idea to handle huge size of Independent datas ? witch has better performance in your opinion ? opening connection of many small .mdf files or just a huge dataBase.

afterwards i'll divide the mdf Repository in several computers.

all of them are handled by C# and linq (.net4)

// Later Descriptions

i built this plan and it works fine. for example: opening every small mdf file takes 1sec time and query it in 0.0sec. it makes static time for every connection but in single Database for 50rows system must find them in for instance 200,000 rows and takes about 4-5sec in my system with simple select query with Primary key.

for other instance i want to get a row between 500,000 rows to bind page content and select 50 Comments between 2milmions row, and get count of votes of every comment, view count in day, week, month and total. count of likes, answer of comments and get more datas from 2-3 other tables, this querys are heavy and take more time than small slave database.

i think a good design and processes must work easy for system.

the only problem is that small slave databases with sql server files takes more physical size about 3MB per DataBase.

share|improve this question

3 Answers 3

There is no reason to split something that could/should exist as a single database in to multiple independent parts.

There are already mechanisms to partition a single logical database across multiple files: Files and Filegroups Architecture as well as to partition large tables (A few thousand rows per day doesn't really qualify as a large table).

share|improve this answer
Thanks for your help. i have worked on this application already. so as your opinion it is better to Commence it all again, right ? –  FARHAD AFSAR Apr 21 '11 at 12:09

its difficult to manage small MDF file u have to go with SQL server and SQL server database provide 10GB data storage per one database os its easy

share|improve this answer

"Thousands of rows per day" should be pocket change for Sql Server.

First, I voted up Alex K answer. File groups will get you to where you want to be most likely. Partitioned tables may be overkill, and is only available in Enterprise version and is not for the light hearted.

What I will add is:


You need to tweak your indexes. In the good vs better vb best category, Glenn Berry's DMV queries are "better". Those queries will help you fix the majority of issues. In the "best" category is pain staking looking at each stored procedure, and looking at the execution plan and trying out different things. This is what a good dba is able to provide.

Here are some "basics" on file setup considerations. Pay attention the TEMP database setup. http://technet.microsoft.com/en-us/library/cc966534.aspx

share|improve this answer

Your Answer


