Data File Size

Does the % full of a datafile or a database impact performance?
For instance if I have a database that is 90-99% full will that have a
negative impact on performance are there any rules of thumb regarding this.
This is of course prior to the database being autmatically grown."JDS" <JDS@.discussions.microsoft.com> wrote in message
news:6D4CD763-FC56-4660-9097-8AC4E188D9AF@.microsoft.com...
> Does the % full of a datafile or a database impact performance?
> For instance if I have a database that is 90-99% full will that have a
> negative impact on performance are there any rules of thumb regarding
> this.
> This is of course prior to the database being autmatically grown.
It really depends on how the DB is being used.
If you're doing strictly queries, no.
If you're doing strictly inserts, then yes.
If you're doing strictly updates, then maybe.
If you're doing some mixture of above "it depends".
Obviously with inserts, you'll need more room once you hit 100% full. Before
then it probably won't matter.
If you're doing updates and the data fits in the same space as the current
data, you're probably fine. If you update a varchar(500) from a two
character entry to a 400 character entry, odds the DB will need to move
stuff around.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Best practice is to size your database proactively and only let autogrowth
act in 'emergency' cases. Size the database to allow for 1-2 years of
expected growth, and revisit at least every 6 months. This will allow for
maintenance operations like index rebuilds/reorgs to be able to lay data
down sequentially on disk for optimal performance and also avoid autogrowth
slowdowns during peak periods.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"JDS" <JDS@.discussions.microsoft.com> wrote in message
news:6D4CD763-FC56-4660-9097-8AC4E188D9AF@.microsoft.com...
> Does the % full of a datafile or a database impact performance?
> For instance if I have a database that is 90-99% full will that have a
> negative impact on performance are there any rules of thumb regarding
> this.
> This is of course prior to the database being autmatically grown.|||Thanks Guys,
I understand that if the database is at a point that it's forced to autogrow
that will degrade performance while the database is growing. I want to know
what the impact of a say 90-95% full database is. This is with out an
autogrow situation. There will be updates, inserts, and reads but we never
reach full capicity. Does a database say that is 60-75% full perfom better
than a database that is 90-95% full or is there no known impact.
"TheSQLGuru" wrote:
> Best practice is to size your database proactively and only let autogrowth
> act in 'emergency' cases. Size the database to allow for 1-2 years of
> expected growth, and revisit at least every 6 months. This will allow for
> maintenance operations like index rebuilds/reorgs to be able to lay data
> down sequentially on disk for optimal performance and also avoid autogrowth
> slowdowns during peak periods.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:6D4CD763-FC56-4660-9097-8AC4E188D9AF@.microsoft.com...
> > Does the % full of a datafile or a database impact performance?
> > For instance if I have a database that is 90-99% full will that have a
> > negative impact on performance are there any rules of thumb regarding
> > this.
> > This is of course prior to the database being autmatically grown.
>
>|||Just to add more to what Kevin was getting at. Chances are (not 100%
guaranteed) that the closer you get to a full database the more the data
will be non-contiguous in the data files. When you reindex an index with
DBCC DBREINDEX or ALTER INDEX REBUILD the engine will create an entirely new
copy of the index in the file and then drop the old one when done. So first
off you need about 1.2 times the size of the index in free space just to
rebuild it. But if you only have that exact amount or anything close you
will most likely get the new index built in small fragments or pockets
within the data file(s) where ever there happened to be room. Where as with
plenty of free space the chances are much greater that you will have the
indexes built in a contiguous fashion. And if you do any range or table
scans, read - ahead's etc. this can make a big difference in performance.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JDS" <JDS@.discussions.microsoft.com> wrote in message
news:F5291FF9-8516-437D-9053-3814FB5835B1@.microsoft.com...
> Thanks Guys,
> I understand that if the database is at a point that it's forced to
> autogrow
> that will degrade performance while the database is growing. I want to
> know
> what the impact of a say 90-95% full database is. This is with out an
> autogrow situation. There will be updates, inserts, and reads but we
> never
> reach full capicity. Does a database say that is 60-75% full perfom
> better
> than a database that is 90-95% full or is there no known impact.
> "TheSQLGuru" wrote:
>> Best practice is to size your database proactively and only let
>> autogrowth
>> act in 'emergency' cases. Size the database to allow for 1-2 years of
>> expected growth, and revisit at least every 6 months. This will allow
>> for
>> maintenance operations like index rebuilds/reorgs to be able to lay data
>> down sequentially on disk for optimal performance and also avoid
>> autogrowth
>> slowdowns during peak periods.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "JDS" <JDS@.discussions.microsoft.com> wrote in message
>> news:6D4CD763-FC56-4660-9097-8AC4E188D9AF@.microsoft.com...
>> > Does the % full of a datafile or a database impact performance?
>> > For instance if I have a database that is 90-99% full will that have a
>> > negative impact on performance are there any rules of thumb regarding
>> > this.
>> > This is of course prior to the database being autmatically grown.
>>