CODEWITHSUNDEEP
sqlsql-server
Jeremy
Jeremy

Reputation: 2536

How does SQL Server handle large physical database file

Coming from MySQL and PostgreSQL, i would very much like to know how SQL Server stores and handles large physical database file.

According to this article here

http://msdn.microsoft.com/en-us/library/aa174545%28SQL.80%29.aspx

SQL Server has 3 types of file, .mdf, .ndf and .ldf

Due to the nature of how data grows, a database can contain hundreds of thousand of files. This would eventually affect the size of these .mdf.

So the question is, how does SQL Server handle large physical database files?

I might seem to ask a lot of question, but i would like to have an answer also covers the sub-question below:

  1. Theoretically, .mdf filesize could grow to GB or perhaps TB. Is this common in real world scenario?
  2. Since SQL Server deals with a single file, it would have a considerably large read/write operation performed on the same file. How would this impact the performance?
  3. Is it possible (has there been any case) to split .mdf into parts. Instead of having 1 uber large .mdf file, would it be better to split it into chunks?

note: I am new to SQL Server, basic query in SQL Server appears to be similar to MySQL, I would like to know a bit about what is going on "under the hood".

Upvotes: 1

Views: 1995

Answers (1)

Igor Borisenko
Igor Borisenko

Reputation: 3866

1 Theoretically, mdf filesize could grow to GB or perhaps TB. Is this common in real world scenario?

Yes, it is common. It depends on amount of read-write operations per second and your disk subsystem. Nowadays, a database with size of hundreds GB is considered to be small.

2 Since MSSQL deals with single file, it would have a considerably large read/write operation performed on the same file. How would this impact the performance?

This is one of the most common performance bottlenecks. You need to choose appropriate disk subsystem and maybe divide your database into several filegroups and place them on different disk subsystems.

3 Is it possible (has there been any case) to split mdf into parts. Instead of having 1 uber large mdf file, would it be better to split it into chunks?

Yes you can. This "chunks" are called filegroups. You can create different tables, indexes, objects or even parts of tables in different filegroups (if version and edition of SQL-Server allows it). But it will give you advantage only if you create filegroups across multiple disks, RAIDs and so on. For more information you can read Using Files and Filegroups

Upvotes: 4

Related Questions