| Author |
Topic |
|
lebedev
Posting Yak Master
126 Posts |
 Posted - 2010-01-07 : 18:52:59
|
| We are running SQL Server 2005 on Windows Server 2003.Our MDF file has grown to 5GB. We are having some performance problems, but I don't know if they are directly related to the file size itself. Can the sheer size of the MDF file decrease performance? Is it common to have multi-terrabyte MDF files?The database file group has only one MDF file. In general, is there a guideline when more files should be added to the database file group?Thanks,Alec |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2010-01-08 : 12:07:05
|
| Thank you for the great response. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-08 : 14:53:31
|
| by the way, i'll bet your performance issues are due to bad or missing indexes. examine the execution plan for queries that are performing poorly, with an eye out for table scans and index scans. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-08 : 16:13:34
|
| As well the suggestion above , consider the nature of the data , and consider coming up with an appropriate maintenance plan -Jack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2010-01-08 : 17:16:56
|
| 5GB is not a big database, we have 80GB in one data file. Try to update statistics or rebuild indexes. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-09 : 07:05:12
|
| Just to clarify , what is the main advantage of breaking a large MDF down into smaller data files?Jack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2010-01-10 : 18:28:07
|
| Just to follow-up on my original post. We analyzed query plans and found that performance problems were linked to obsolete query plans.We access the database using JDBC PreparedStatements which create stored procedures and reuse them over and over without recompiling the execution plans. So, over time execution plans get out of date causing performance problems.We found other performance problems but all of them were unrelated to the size of the MDF file.However, the 60GB guideline that Tara suggested will still be very useful for us in the near future. |
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 2012-11-14 : 21:24:49
|
| Hi Tara,I have large(200 GB) with 1 mdf file.? will i have any issue? Is there any microsoft article explaining the consequences? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-15 : 14:12:06
|
| >>I've removed my posts regarding the topic you are referring to as it was bad advice.Man, if we have to delete our posts where we gave bad advice I've got a lot of work to do!Be One with the OptimizerTG |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-15 : 15:59:10
|
TG  I will also have to see it. |
|
|
|
|
Can large size of MDF files decrease performance?