SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Can large size of MDF files decrease performance?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lebedev
Posting Yak Master

USA
126 Posts

Posted - 01/07/2010 :  18:52:59  Show Profile  Reply with Quote
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
35940 Posts

Posted - 01/07/2010 :  19:02:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
A 5GB file is tiny and would not impact performance. If it were 1TB then it'd be a different story.


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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."

Edited by - tkizer on 11/15/2012 13:49:07
Go to Top of Page

lebedev
Posting Yak Master

USA
126 Posts

Posted - 01/08/2010 :  12:07:05  Show Profile  Reply with Quote
Thank you for the great response.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 01/08/2010 :  12:47:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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."
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 01/08/2010 :  14:53:31  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 01/08/2010 :  16:13:34  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

Peter99
Constraint Violating Yak Guru

481 Posts

Posted - 01/08/2010 :  17:16:56  Show Profile  Reply with Quote
5GB is not a big database, we have 80GB in one data file. Try to update statistics or rebuild indexes.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 01/09/2010 :  07:05:12  Show Profile  Visit jackv's Homepage  Reply with Quote
Just to clarify , what is the main advantage of breaking a large MDF down into smaller data files?

Jack Vamvas
--------------------
http://www.ITjobfeed.com
Go to Top of Page

lebedev
Posting Yak Master

USA
126 Posts

Posted - 01/10/2010 :  18:28:07  Show Profile  Reply with Quote
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.
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 11/14/2012 :  21:24:49  Show Profile  Reply with Quote
Hi Tara,

I have large(200 GB) with 1 mdf file.? will i have any issue? Is there any microsoft article explaining the consequences?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 11/15/2012 :  13:50:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by SCHEMA

Hi Tara,

I have large(200 GB) with 1 mdf file.? will i have any issue? Is there any microsoft article explaining the consequences?



I've removed my posts regarding the topic you are referring to as it was bad advice.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 11/15/2012 :  14:12:06  Show Profile  Reply with Quote
>>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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/15/2012 :  15:59:10  Show Profile  Reply with Quote
TG
I will also have to see it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000