Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
 Posted - 2009-11-15 : 04:42:31
|
| Source: http://www.sql-server-performance.com/tips/filegroups_p1.aspxIf your database is very large and very busy, multiple files can be used to increase performance. Here is one example of how you might use multiple files. Let's say you have a single table with 10 million rows that is heavily queried. If the table is in a single file, such as a single database file, then SQL Server would only use one thread to perform a read of the rows in the table. But if the table were divided into three physical files, then SQL Server would use three threads (one per physical file) to read the table, which potentially could be faster. In addition, if each file were on its own separate physical disk or disk array, the performance gain would even be greater.The paragraph talkes about three physical files, I assume is the "ndf" file. How do you divide one table in three physical files?If you have an example it would be great. Thanks. |
|
|
Pradip
Starting Member
32 Posts |
Posted - 2009-11-17 : 06:21:01
|
| Partitions are used to improve the scalability and manageability of large tables. Steps1. Create File Groups stored at different location or disk as per requirement.2. Create the Partition Function for a Range Partition3. Create the Partition Scheme4. Create Table with on clause at last for Partition Scheme and table fieldwww.silicus.compradipjain |
 |
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2009-11-19 : 01:28:19
|
| Pradip,Thanks for the response. But could you answer the question I posted. I didn't ask about Partition. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-19 : 01:32:41
|
| SQL Server uses a proportional fill algorithm when you have multiple data files. You don't specify what data goes where unless you use a filegroup. So if you don't use any filegroups other than the primary one, then SQL Server will manage keeping the free space in each of the files about the same. If you do use filegroups, then you can put objects into them and those filegroups can be put on a certain file.We have an 800GB database that is mission critical and has extremely high performance requirements. We spread our IO load across 21 data files that hit 7 different RAID controllers. The first 14 files are set to a maximum file size of 60GB (to avoid a Windows sparse file issue) and then the other 7 are set to 1GB and are allowed to autogrow. I'll cap the 7 to 60GB eventually, but I wanted to let them autogrow so that I am alerted to disk space issues.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Pradip
Starting Member
32 Posts |
Posted - 2009-11-20 : 01:13:21
|
| Could I know your good name? My answer is to your question only. Let's take example you write you have a table with 10 million rows and that is heavily queried. If you could divide these depend upon say year column, randomly data is from last 10 years and each year data having size of 1 million rows.In this case if 10 file groups are created and you could put the data in 10 files stored at own separate physical disk or disk array. Each year data will have separate file stored at separate physical location.May this help you.pradipjain |
|
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2010-02-06 : 05:05:45
|
quote:
Originally posted by tkizer
We have an 800GB database that is mission critical and has extremely high performance requirements. We spread our IO load across 21 data files that hit 7 different RAID controllers. The first 14 files are set to a maximum file size of 60GB (to avoid a Windows sparse file issue) and then the other 7 are set to 1GB and are allowed to autogrow. I'll cap the 7 to 60GB eventually, but I wanted to let them autogrow so that I am alerted to disk space issues.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
Just a quick question, why do you use 21 files? I didn't really get it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Question about filegroups