| Author |
 Topic  |
|
|
lebedev
Posting Yak Master
USA
126 Posts |
 Posted - 01/07/2010 : 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
USA
35007 Posts |
|
|
lebedev
Posting Yak Master
USA
126 Posts |
Posted - 01/08/2010 : 12:07:05
|
| Thank you for the great response. |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
russell
Pyro-ma-ni-yak
USA
4968 Posts |
Posted - 01/08/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 01/08/2010 : 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
471 Posts |
Posted - 01/08/2010 : 17:16:56
|
| 5GB is not a big database, we have 80GB in one data file. Try to update statistics or rebuild indexes. |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 01/09/2010 : 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
USA
126 Posts |
Posted - 01/10/2010 : 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 - 11/14/2012 : 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
USA
35007 Posts |
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/15/2012 : 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 Optimizer
TG |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/15/2012 : 15:59:10
|
TG 
I will also have to see it. |
|
|
| |
Topic |
|
Can large size of MDF files decrease performance?
