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...
>
>
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...[vbcol=seagreen]
> 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:
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Thursday, March 29, 2012
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.
>>
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.
>>
Tuesday, March 27, 2012
Data File Growth operations
Are any operations suspended during a data file growth? Or is it just a
performance hit?
It appears from a trace of mine that [during a data file growth] some (the
majority), but not all of my inserts are timing out. However, all updates
succeed, no updates time out. So, it seems I can partially answer my
question above, I think? Just looking for some clarification.
--
Message posted via http://www.sqlmonster.comAny operation that depends on a new extent allocation will suspend. If you
are inserting on a non-full page, it will work. In-place updates will work.
New rows that trigger an extent allocation will suspend.
You get the picture.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:6bedf6ba67e1467f933788e8e0e9fad3@.SQLMonster.com...
> Are any operations suspended during a data file growth? Or is it just a
> performance hit?
> It appears from a trace of mine that [during a data file growth] some (the
> majority), but not all of my inserts are timing out. However, all updates
> succeed, no updates time out. So, it seems I can partially answer my
> question above, I think? Just looking for some clarification.
> --
> Message posted via http://www.sqlmonster.com|||Thanks Geoff. That is all clear except for when a 10% automatic growth
takes place.
My inserts all succeed in milliseconds outside of a data file growth.
During data file growth the applications initiating the inserts time out at
30 seconds. While some inserts are timing out at 30 seconds, during this
same time, the ones that are succeeding complete in milliseconds.
To try to clarify, if I have an approximate ratio of 10:1, where 10 equals
inserts that timeout for every 1 insert that succeeds, during a data file
growth ( the data file is set to automatically grow by 10% ) what is
happening is:
1. New extent allocations are taking place due to the 10% automatic growth.
2. The inserts that are succeeding are the ones going on a non-full page.
3. The inserts that are failing (timing out) are either triggering an
extent allocation, or are waiting for the extent allocation due to the 10%
automatic growth.
Am I clear in my understanding?
--
Message posted via http://www.sqlmonster.com|||You have a good grasp of the concepts. The solution is to not rely on
automatic growth. I use it as an emergency safety valve only. I
pro-actively manage the data space used in each database/filegroup so my
production systems never have to pause while they grow. Also, 10% is OK for
about 60-70% of databases. Many databases are too small or too large for
percentage growth to be effective (Think about a 2MB database. Now think
about a 300GB database.) I prefer to set an absolute amount to grow by,
just in case. That way, I know exactly how long it should take for every
auto-grow even though I hope not to use it.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e5a3374e183c4893a73356c7666d88b3@.SQLMonster.com...
> Thanks Geoff. That is all clear except for when a 10% automatic growth
> takes place.
> My inserts all succeed in milliseconds outside of a data file growth.
> During data file growth the applications initiating the inserts time out
at
> 30 seconds. While some inserts are timing out at 30 seconds, during this
> same time, the ones that are succeeding complete in milliseconds.
> To try to clarify, if I have an approximate ratio of 10:1, where 10 equals
> inserts that timeout for every 1 insert that succeeds, during a data file
> growth ( the data file is set to automatically grow by 10% ) what is
> happening is:
> 1. New extent allocations are taking place due to the 10% automatic
growth.
> 2. The inserts that are succeeding are the ones going on a non-full page.
> 3. The inserts that are failing (timing out) are either triggering an
> extent allocation, or are waiting for the extent allocation due to the 10%
> automatic growth.
> Am I clear in my understanding?
> --
> Message posted via http://www.sqlmonster.com|||In light of this, I need further understanding.
When I manually expand the data file 1 GB, the expansion takes
approximately 2 minutes. When it automatically grows, and inserts are
timing out at the same time, the expansion takes approimately 20 minutes,
for 1 GB (the 10% equivalent).
How does all this we have been discussing equate to the increased expansion
time?
--
Message posted via http://www.sqlmonster.com|||It may be that the client operation that triggered the specific expansion
has timed out. This may result in the expansion being rolled back. It
probably repeats until the expansio happens for a transaction that doesn't
time out. Whatever causes it, you now have an even stronger reason to avoid
auto-grow.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:bb7f723949324ec5886d55b0e29ea65a@.SQLMonster.com...
> In light of this, I need further understanding.
> When I manually expand the data file 1 GB, the expansion takes
> approximately 2 minutes. When it automatically grows, and inserts are
> timing out at the same time, the expansion takes approimately 20 minutes,
> for 1 GB (the 10% equivalent).
> How does all this we have been discussing equate to the increased
expansion
> time?
> --
> Message posted via http://www.sqlmonster.com
performance hit?
It appears from a trace of mine that [during a data file growth] some (the
majority), but not all of my inserts are timing out. However, all updates
succeed, no updates time out. So, it seems I can partially answer my
question above, I think? Just looking for some clarification.
--
Message posted via http://www.sqlmonster.comAny operation that depends on a new extent allocation will suspend. If you
are inserting on a non-full page, it will work. In-place updates will work.
New rows that trigger an extent allocation will suspend.
You get the picture.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:6bedf6ba67e1467f933788e8e0e9fad3@.SQLMonster.com...
> Are any operations suspended during a data file growth? Or is it just a
> performance hit?
> It appears from a trace of mine that [during a data file growth] some (the
> majority), but not all of my inserts are timing out. However, all updates
> succeed, no updates time out. So, it seems I can partially answer my
> question above, I think? Just looking for some clarification.
> --
> Message posted via http://www.sqlmonster.com|||Thanks Geoff. That is all clear except for when a 10% automatic growth
takes place.
My inserts all succeed in milliseconds outside of a data file growth.
During data file growth the applications initiating the inserts time out at
30 seconds. While some inserts are timing out at 30 seconds, during this
same time, the ones that are succeeding complete in milliseconds.
To try to clarify, if I have an approximate ratio of 10:1, where 10 equals
inserts that timeout for every 1 insert that succeeds, during a data file
growth ( the data file is set to automatically grow by 10% ) what is
happening is:
1. New extent allocations are taking place due to the 10% automatic growth.
2. The inserts that are succeeding are the ones going on a non-full page.
3. The inserts that are failing (timing out) are either triggering an
extent allocation, or are waiting for the extent allocation due to the 10%
automatic growth.
Am I clear in my understanding?
--
Message posted via http://www.sqlmonster.com|||You have a good grasp of the concepts. The solution is to not rely on
automatic growth. I use it as an emergency safety valve only. I
pro-actively manage the data space used in each database/filegroup so my
production systems never have to pause while they grow. Also, 10% is OK for
about 60-70% of databases. Many databases are too small or too large for
percentage growth to be effective (Think about a 2MB database. Now think
about a 300GB database.) I prefer to set an absolute amount to grow by,
just in case. That way, I know exactly how long it should take for every
auto-grow even though I hope not to use it.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e5a3374e183c4893a73356c7666d88b3@.SQLMonster.com...
> Thanks Geoff. That is all clear except for when a 10% automatic growth
> takes place.
> My inserts all succeed in milliseconds outside of a data file growth.
> During data file growth the applications initiating the inserts time out
at
> 30 seconds. While some inserts are timing out at 30 seconds, during this
> same time, the ones that are succeeding complete in milliseconds.
> To try to clarify, if I have an approximate ratio of 10:1, where 10 equals
> inserts that timeout for every 1 insert that succeeds, during a data file
> growth ( the data file is set to automatically grow by 10% ) what is
> happening is:
> 1. New extent allocations are taking place due to the 10% automatic
growth.
> 2. The inserts that are succeeding are the ones going on a non-full page.
> 3. The inserts that are failing (timing out) are either triggering an
> extent allocation, or are waiting for the extent allocation due to the 10%
> automatic growth.
> Am I clear in my understanding?
> --
> Message posted via http://www.sqlmonster.com|||In light of this, I need further understanding.
When I manually expand the data file 1 GB, the expansion takes
approximately 2 minutes. When it automatically grows, and inserts are
timing out at the same time, the expansion takes approimately 20 minutes,
for 1 GB (the 10% equivalent).
How does all this we have been discussing equate to the increased expansion
time?
--
Message posted via http://www.sqlmonster.com|||It may be that the client operation that triggered the specific expansion
has timed out. This may result in the expansion being rolled back. It
probably repeats until the expansio happens for a transaction that doesn't
time out. Whatever causes it, you now have an even stronger reason to avoid
auto-grow.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:bb7f723949324ec5886d55b0e29ea65a@.SQLMonster.com...
> In light of this, I need further understanding.
> When I manually expand the data file 1 GB, the expansion takes
> approximately 2 minutes. When it automatically grows, and inserts are
> timing out at the same time, the expansion takes approimately 20 minutes,
> for 1 GB (the 10% equivalent).
> How does all this we have been discussing equate to the increased
expansion
> time?
> --
> Message posted via http://www.sqlmonster.com
Wednesday, March 7, 2012
data and tran log location
I am new to SQl server and would apreciate help in
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchI don't follow you. You say that you have one physical drive, so how can any
thing be mirrored?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:1353a01c41289$79c4d9e0$a
501280a@.phx.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>|||"miro" <anonymous@.discussions.microsoft.com> wrote in message
news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>
If you have only one physical disc you have _no_ fault tolerance, regardless
of your file locations.
If you only have one disc and you put your transaction logs on a different
logical drive you will actually slow your performance down, as the heads of
the drive will have further to travel.
The only acceptable minimum configuration if you want any fault tolerance at
all is to have at least 2 physical drives, data on one, log on the other, so
that if you loose the data disk you can restore from your backup and roll
your logs forward to the point of failure. If this is a mission critical
server then you need hardware RAID and as much redundancy as you can afford.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||Hi Tibor,
you are right the number of physical drives is two. They
are mirrored. I am wondering now if two logical drives
would be suficient and appropriate for 1.- OS, SQL
Server and 2 - database files. What is the best practice ?
Would creating additional logical drives be of any
benefit ?
Thanks
>--Original Message--
>I don't follow you. You say that you have one physical
drive, so how can anything be mirrored?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"miro" <anonymous@.discussions.microsoft.com> wrote in
message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
logical
>
>.
>|||Whether you create logical drives or not does not in any way affect performa
nce as all logical drives are on
the same mirrored physical drive.
Assuming that you only have two drives to work with, and you estimate that i
t will give sufficient
performance:
Having the OS on it's own partition can be a good thing so autogrow (if you
use that) of the database files
cannot starve the OS from disk. Apart from that, it doesn't matter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:10b1b01c412a5$55098e90$a
601280a@.phx.gbl...
> Hi Tibor,
> you are right the number of physical drives is two. They
> are mirrored. I am wondering now if two logical drives
> would be suficient and appropriate for 1.- OS, SQL
> Server and 2 - database files. What is the best practice ?
> Would creating additional logical drives be of any
> benefit ?
> Thanks
> drive, so how can anything be mirrored?
> message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> logical
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchI don't follow you. You say that you have one physical drive, so how can any
thing be mirrored?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:1353a01c41289$79c4d9e0$a
501280a@.phx.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>|||"miro" <anonymous@.discussions.microsoft.com> wrote in message
news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>
If you have only one physical disc you have _no_ fault tolerance, regardless
of your file locations.
If you only have one disc and you put your transaction logs on a different
logical drive you will actually slow your performance down, as the heads of
the drive will have further to travel.
The only acceptable minimum configuration if you want any fault tolerance at
all is to have at least 2 physical drives, data on one, log on the other, so
that if you loose the data disk you can restore from your backup and roll
your logs forward to the point of failure. If this is a mission critical
server then you need hardware RAID and as much redundancy as you can afford.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||Hi Tibor,
you are right the number of physical drives is two. They
are mirrored. I am wondering now if two logical drives
would be suficient and appropriate for 1.- OS, SQL
Server and 2 - database files. What is the best practice ?
Would creating additional logical drives be of any
benefit ?
Thanks
>--Original Message--
>I don't follow you. You say that you have one physical
drive, so how can anything be mirrored?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"miro" <anonymous@.discussions.microsoft.com> wrote in
message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
logical
>
>.
>|||Whether you create logical drives or not does not in any way affect performa
nce as all logical drives are on
the same mirrored physical drive.
Assuming that you only have two drives to work with, and you estimate that i
t will give sufficient
performance:
Having the OS on it's own partition can be a good thing so autogrow (if you
use that) of the database files
cannot starve the OS from disk. Apart from that, it doesn't matter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:10b1b01c412a5$55098e90$a
601280a@.phx.gbl...
> Hi Tibor,
> you are right the number of physical drives is two. They
> are mirrored. I am wondering now if two logical drives
> would be suficient and appropriate for 1.- OS, SQL
> Server and 2 - database files. What is the best practice ?
> Would creating additional logical drives be of any
> benefit ?
> Thanks
> drive, so how can anything be mirrored?
> message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> logical
data and tran log location
I am new to SQl server and would apreciate help in
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchIf you have only one logical drive you cannot separate log and data aiming b
oost performance. For this kind of tuning to work, you must have a bunch of
logical drives, with different RAID levels.
Mirror volumes are better for writting on disk than reading compared to RAID
5, so would be good for tran lob, that is writing intensive. But read perfor
mance for RAID5(when all disks are working fine) is better, so, storing rea
d-only data here would be n
ice. RAID 5 overall performance is better then mirrored volume, but it need
s at least 3 physical disks to be built.
So, if you have only 2 disks, separating log, data and index is worthless.
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchIf you have only one logical drive you cannot separate log and data aiming b
oost performance. For this kind of tuning to work, you must have a bunch of
logical drives, with different RAID levels.
Mirror volumes are better for writting on disk than reading compared to RAID
5, so would be good for tran lob, that is writing intensive. But read perfor
mance for RAID5(when all disks are working fine) is better, so, storing rea
d-only data here would be n
ice. RAID 5 overall performance is better then mirrored volume, but it need
s at least 3 physical disks to be built.
So, if you have only 2 disks, separating log, data and index is worthless.
Data and log sits on different disk drive.
Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.
Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/de...us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.
Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/de...us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Data and log sits on different disk drive.
Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Data and log sits on different disk drive.
Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different d
isk drive? Is there any benefit of doing it in term of performance? How abou
t backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/d...-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Can I create a database with the .mdf (data) and .ldf (log) on 2 different d
isk drive? Is there any benefit of doing it in term of performance? How abou
t backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/d...-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Subscribe to:
Posts (Atom)