Isn't it true that Sql Server does not maintain the fill
factor/pad index? i.e. to redo the fill factor, you must
reindex/defrag? If your fill factor is 100%, you're
going to get poor write performance, correct? Could this
cause your Service Times (on an EMC SAN) to significantly
increase to unacceptable levels?
Having a 100% full page does not mean you will have write problems. It
depends on the clustered index and the order in which you insert the data.
But it is likely you will get page splits with a 100% fill factor. This is
not generally recommended on tables that are not static or read only. Each
time you split a page it has to move half the data onto a new page. This
can cause lots of I/O in a busy system.
Andrew J. Kelly SQL MVP
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:05b301c515e1$490caec0$a501280a@.phx.gbl...
> Isn't it true that Sql Server does not maintain the fill
> factor/pad index? i.e. to redo the fill factor, you must
> reindex/defrag? If your fill factor is 100%, you're
> going to get poor write performance, correct? Could this
> cause your Service Times (on an EMC SAN) to significantly
> increase to unacceptable levels?
|||Thx for the info.
I know that Service Time is a good indicator of the disk
struggling. Mb/Sec seems reasonable to our admin but he
was saying that IO's/sec looked high to him. What did he
mean by that? Is an I/O to him the same as "commit work"
to me?
>--Original Message--
>Having a 100% full page does not mean you will have
write problems. It
>depends on the clustered index and the order in which
you insert the data.
>But it is likely you will get page splits with a 100%
fill factor. This is
>not generally recommended on tables that are not static
or read only. Each
>time you split a page it has to move half the data onto
a new page. This
>can cause lots of I/O in a busy system.
>--
>Andrew J. Kelly SQL MVP
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
fill[vbcol=seagreen]
must[vbcol=seagreen]
this[vbcol=seagreen]
significantly
>
>.
>
|||just look at Physical Disk: Current Disk Queue Length That is a good
indicator of problems..
You want this number to be as low as possible (in a perfect world, Zero). IF
the number is pegged, you have serious issues.
Also monitor Physical Disk Split IO/Sec OR SqlServer Access Methods - Page
Splits/sec.
again you want splits to be Zero in a perfect world.
adjust fill factor as needed to reduce Splits.
If the table is really active, you might need to set fill factor to as low
as say "50". I usually start be setting it around 80% and monitor before
going nuts on it.
IF your clustered index is on an identity column or on some other
"Monotonically Increasing Value", then inserts *should* always go to bottom,
so splits may not be an issue.
over time, splits will massively fragement your indexes and performance will
totally tank.
Each table must have a clustered index as well otherwise Defrags are
fruitless.
Monitor fragmentation levels using DBCC ShowContig. The Scan Density Number
should stay ABOVE 80% or you need to consider defragging indexes.
hope this helps
Greg Jackson
PDX, Oregon
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:163401c515e7$89283730$a401280a@.phx.gbl...[vbcol=seagreen]
> Thx for the info.
> I know that Service Time is a good indicator of the disk
> struggling. Mb/Sec seems reasonable to our admin but he
> was saying that IO's/sec looked high to him. What did he
> mean by that? Is an I/O to him the same as "commit work"
> to me?
> write problems. It
> you insert the data.
> fill factor. This is
> or read only. Each
> a new page. This
> message
> fill
> must
> this
> significantly
|||Thx.
>--Original Message--
>just look at Physical Disk: Current Disk Queue Length
That is a good
>indicator of problems..
>You want this number to be as low as possible (in a
perfect world, Zero). IF
>the number is pegged, you have serious issues.
>Also monitor Physical Disk Split IO/Sec OR SqlServer
Access Methods - Page
>Splits/sec.
>again you want splits to be Zero in a perfect world.
>adjust fill factor as needed to reduce Splits.
>If the table is really active, you might need to set
fill factor to as low
>as say "50". I usually start be setting it around 80%
and monitor before
>going nuts on it.
>IF your clustered index is on an identity column or on
some other
>"Monotonically Increasing Value", then inserts *should*
always go to bottom,
>so splits may not be an issue.
>over time, splits will massively fragement your indexes
and performance will
>totally tank.
>Each table must have a clustered index as well otherwise
Defrags are
>fruitless.
>
>Monitor fragmentation levels using DBCC ShowContig. The
Scan Density Number
>should stay ABOVE 80% or you need to consider defragging
indexes.
>
>hope this helps
>Greg Jackson
>PDX, Oregon
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:163401c515e7$89283730$a401280a@.phx.gbl...
disk[vbcol=seagreen]
he[vbcol=seagreen]
he[vbcol=seagreen]
work"
>
>.
>
Showing posts with label pad. Show all posts
Showing posts with label pad. Show all posts
Friday, March 23, 2012
Fill Factor
Isn't it true that Sql Server does not maintain the fill
factor/pad index? i.e. to redo the fill factor, you must
reindex/defrag? If your fill factor is 100%, you're
going to get poor write performance, correct? Could this
cause your Service Times (on an EMC SAN) to significantly
increase to unacceptable levels?Having a 100% full page does not mean you will have write problems. It
depends on the clustered index and the order in which you insert the data.
But it is likely you will get page splits with a 100% fill factor. This is
not generally recommended on tables that are not static or read only. Each
time you split a page it has to move half the data onto a new page. This
can cause lots of I/O in a busy system.
--
Andrew J. Kelly SQL MVP
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:05b301c515e1$490caec0$a501280a@.phx.gbl...
> Isn't it true that Sql Server does not maintain the fill
> factor/pad index? i.e. to redo the fill factor, you must
> reindex/defrag? If your fill factor is 100%, you're
> going to get poor write performance, correct? Could this
> cause your Service Times (on an EMC SAN) to significantly
> increase to unacceptable levels?|||Thx for the info.
I know that Service Time is a good indicator of the disk
struggling. Mb/Sec seems reasonable to our admin but he
was saying that IO's/sec looked high to him. What did he
mean by that? Is an I/O to him the same as "commit work"
to me?
>--Original Message--
>Having a 100% full page does not mean you will have
write problems. It
>depends on the clustered index and the order in which
you insert the data.
>But it is likely you will get page splits with a 100%
fill factor. This is
>not generally recommended on tables that are not static
or read only. Each
>time you split a page it has to move half the data onto
a new page. This
>can cause lots of I/O in a busy system.
>--
>Andrew J. Kelly SQL MVP
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
>> Isn't it true that Sql Server does not maintain the
fill
>> factor/pad index? i.e. to redo the fill factor, you
must
>> reindex/defrag? If your fill factor is 100%, you're
>> going to get poor write performance, correct? Could
this
>> cause your Service Times (on an EMC SAN) to
significantly
>> increase to unacceptable levels?
>
>.
>|||just look at Physical Disk: Current Disk Queue Length That is a good
indicator of problems..
You want this number to be as low as possible (in a perfect world, Zero). IF
the number is pegged, you have serious issues.
Also monitor Physical Disk Split IO/Sec OR SqlServer Access Methods - Page
Splits/sec.
again you want splits to be Zero in a perfect world.
adjust fill factor as needed to reduce Splits.
If the table is really active, you might need to set fill factor to as low
as say "50". I usually start be setting it around 80% and monitor before
going nuts on it.
IF your clustered index is on an identity column or on some other
"Monotonically Increasing Value", then inserts *should* always go to bottom,
so splits may not be an issue.
over time, splits will massively fragement your indexes and performance will
totally tank.
Each table must have a clustered index as well otherwise Defrags are
fruitless.
Monitor fragmentation levels using DBCC ShowContig. The Scan Density Number
should stay ABOVE 80% or you need to consider defragging indexes.
hope this helps
Greg Jackson
PDX, Oregon
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:163401c515e7$89283730$a401280a@.phx.gbl...
> Thx for the info.
> I know that Service Time is a good indicator of the disk
> struggling. Mb/Sec seems reasonable to our admin but he
> was saying that IO's/sec looked high to him. What did he
> mean by that? Is an I/O to him the same as "commit work"
> to me?
>>--Original Message--
>>Having a 100% full page does not mean you will have
> write problems. It
>>depends on the clustered index and the order in which
> you insert the data.
>>But it is likely you will get page splits with a 100%
> fill factor. This is
>>not generally recommended on tables that are not static
> or read only. Each
>>time you split a page it has to move half the data onto
> a new page. This
>>can cause lots of I/O in a busy system.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"OX6" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
>> Isn't it true that Sql Server does not maintain the
> fill
>> factor/pad index? i.e. to redo the fill factor, you
> must
>> reindex/defrag? If your fill factor is 100%, you're
>> going to get poor write performance, correct? Could
> this
>> cause your Service Times (on an EMC SAN) to
> significantly
>> increase to unacceptable levels?
>>
>>.|||Thx.
>--Original Message--
>just look at Physical Disk: Current Disk Queue Length
That is a good
>indicator of problems..
>You want this number to be as low as possible (in a
perfect world, Zero). IF
>the number is pegged, you have serious issues.
>Also monitor Physical Disk Split IO/Sec OR SqlServer
Access Methods - Page
>Splits/sec.
>again you want splits to be Zero in a perfect world.
>adjust fill factor as needed to reduce Splits.
>If the table is really active, you might need to set
fill factor to as low
>as say "50". I usually start be setting it around 80%
and monitor before
>going nuts on it.
>IF your clustered index is on an identity column or on
some other
>"Monotonically Increasing Value", then inserts *should*
always go to bottom,
>so splits may not be an issue.
>over time, splits will massively fragement your indexes
and performance will
>totally tank.
>Each table must have a clustered index as well otherwise
Defrags are
>fruitless.
>
>Monitor fragmentation levels using DBCC ShowContig. The
Scan Density Number
>should stay ABOVE 80% or you need to consider defragging
indexes.
>
>hope this helps
>Greg Jackson
>PDX, Oregon
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message
>news:163401c515e7$89283730$a401280a@.phx.gbl...
>> Thx for the info.
>> I know that Service Time is a good indicator of the
disk
>> struggling. Mb/Sec seems reasonable to our admin but
he
>> was saying that IO's/sec looked high to him. What did
he
>> mean by that? Is an I/O to him the same as "commit
work"
>> to me?
>>--Original Message--
>>Having a 100% full page does not mean you will have
>> write problems. It
>>depends on the clustered index and the order in which
>> you insert the data.
>>But it is likely you will get page splits with a 100%
>> fill factor. This is
>>not generally recommended on tables that are not static
>> or read only. Each
>>time you split a page it has to move half the data onto
>> a new page. This
>>can cause lots of I/O in a busy system.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"OX6" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
>> Isn't it true that Sql Server does not maintain the
>> fill
>> factor/pad index? i.e. to redo the fill factor, you
>> must
>> reindex/defrag? If your fill factor is 100%, you're
>> going to get poor write performance, correct? Could
>> this
>> cause your Service Times (on an EMC SAN) to
>> significantly
>> increase to unacceptable levels?
>>
>>.
>
>.
>sql
factor/pad index? i.e. to redo the fill factor, you must
reindex/defrag? If your fill factor is 100%, you're
going to get poor write performance, correct? Could this
cause your Service Times (on an EMC SAN) to significantly
increase to unacceptable levels?Having a 100% full page does not mean you will have write problems. It
depends on the clustered index and the order in which you insert the data.
But it is likely you will get page splits with a 100% fill factor. This is
not generally recommended on tables that are not static or read only. Each
time you split a page it has to move half the data onto a new page. This
can cause lots of I/O in a busy system.
--
Andrew J. Kelly SQL MVP
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:05b301c515e1$490caec0$a501280a@.phx.gbl...
> Isn't it true that Sql Server does not maintain the fill
> factor/pad index? i.e. to redo the fill factor, you must
> reindex/defrag? If your fill factor is 100%, you're
> going to get poor write performance, correct? Could this
> cause your Service Times (on an EMC SAN) to significantly
> increase to unacceptable levels?|||Thx for the info.
I know that Service Time is a good indicator of the disk
struggling. Mb/Sec seems reasonable to our admin but he
was saying that IO's/sec looked high to him. What did he
mean by that? Is an I/O to him the same as "commit work"
to me?
>--Original Message--
>Having a 100% full page does not mean you will have
write problems. It
>depends on the clustered index and the order in which
you insert the data.
>But it is likely you will get page splits with a 100%
fill factor. This is
>not generally recommended on tables that are not static
or read only. Each
>time you split a page it has to move half the data onto
a new page. This
>can cause lots of I/O in a busy system.
>--
>Andrew J. Kelly SQL MVP
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
>> Isn't it true that Sql Server does not maintain the
fill
>> factor/pad index? i.e. to redo the fill factor, you
must
>> reindex/defrag? If your fill factor is 100%, you're
>> going to get poor write performance, correct? Could
this
>> cause your Service Times (on an EMC SAN) to
significantly
>> increase to unacceptable levels?
>
>.
>|||just look at Physical Disk: Current Disk Queue Length That is a good
indicator of problems..
You want this number to be as low as possible (in a perfect world, Zero). IF
the number is pegged, you have serious issues.
Also monitor Physical Disk Split IO/Sec OR SqlServer Access Methods - Page
Splits/sec.
again you want splits to be Zero in a perfect world.
adjust fill factor as needed to reduce Splits.
If the table is really active, you might need to set fill factor to as low
as say "50". I usually start be setting it around 80% and monitor before
going nuts on it.
IF your clustered index is on an identity column or on some other
"Monotonically Increasing Value", then inserts *should* always go to bottom,
so splits may not be an issue.
over time, splits will massively fragement your indexes and performance will
totally tank.
Each table must have a clustered index as well otherwise Defrags are
fruitless.
Monitor fragmentation levels using DBCC ShowContig. The Scan Density Number
should stay ABOVE 80% or you need to consider defragging indexes.
hope this helps
Greg Jackson
PDX, Oregon
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:163401c515e7$89283730$a401280a@.phx.gbl...
> Thx for the info.
> I know that Service Time is a good indicator of the disk
> struggling. Mb/Sec seems reasonable to our admin but he
> was saying that IO's/sec looked high to him. What did he
> mean by that? Is an I/O to him the same as "commit work"
> to me?
>>--Original Message--
>>Having a 100% full page does not mean you will have
> write problems. It
>>depends on the clustered index and the order in which
> you insert the data.
>>But it is likely you will get page splits with a 100%
> fill factor. This is
>>not generally recommended on tables that are not static
> or read only. Each
>>time you split a page it has to move half the data onto
> a new page. This
>>can cause lots of I/O in a busy system.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"OX6" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
>> Isn't it true that Sql Server does not maintain the
> fill
>> factor/pad index? i.e. to redo the fill factor, you
> must
>> reindex/defrag? If your fill factor is 100%, you're
>> going to get poor write performance, correct? Could
> this
>> cause your Service Times (on an EMC SAN) to
> significantly
>> increase to unacceptable levels?
>>
>>.|||Thx.
>--Original Message--
>just look at Physical Disk: Current Disk Queue Length
That is a good
>indicator of problems..
>You want this number to be as low as possible (in a
perfect world, Zero). IF
>the number is pegged, you have serious issues.
>Also monitor Physical Disk Split IO/Sec OR SqlServer
Access Methods - Page
>Splits/sec.
>again you want splits to be Zero in a perfect world.
>adjust fill factor as needed to reduce Splits.
>If the table is really active, you might need to set
fill factor to as low
>as say "50". I usually start be setting it around 80%
and monitor before
>going nuts on it.
>IF your clustered index is on an identity column or on
some other
>"Monotonically Increasing Value", then inserts *should*
always go to bottom,
>so splits may not be an issue.
>over time, splits will massively fragement your indexes
and performance will
>totally tank.
>Each table must have a clustered index as well otherwise
Defrags are
>fruitless.
>
>Monitor fragmentation levels using DBCC ShowContig. The
Scan Density Number
>should stay ABOVE 80% or you need to consider defragging
indexes.
>
>hope this helps
>Greg Jackson
>PDX, Oregon
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message
>news:163401c515e7$89283730$a401280a@.phx.gbl...
>> Thx for the info.
>> I know that Service Time is a good indicator of the
disk
>> struggling. Mb/Sec seems reasonable to our admin but
he
>> was saying that IO's/sec looked high to him. What did
he
>> mean by that? Is an I/O to him the same as "commit
work"
>> to me?
>>--Original Message--
>>Having a 100% full page does not mean you will have
>> write problems. It
>>depends on the clustered index and the order in which
>> you insert the data.
>>But it is likely you will get page splits with a 100%
>> fill factor. This is
>>not generally recommended on tables that are not static
>> or read only. Each
>>time you split a page it has to move half the data onto
>> a new page. This
>>can cause lots of I/O in a busy system.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"OX6" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
>> Isn't it true that Sql Server does not maintain the
>> fill
>> factor/pad index? i.e. to redo the fill factor, you
>> must
>> reindex/defrag? If your fill factor is 100%, you're
>> going to get poor write performance, correct? Could
>> this
>> cause your Service Times (on an EMC SAN) to
>> significantly
>> increase to unacceptable levels?
>>
>>.
>
>.
>sql
Fill Factor
Isn't it true that Sql Server does not maintain the fill
factor/pad index? i.e. to redo the fill factor, you must
reindex/defrag? If your fill factor is 100%, you're
going to get poor write performance, correct? Could this
cause your Service Times (on an EMC SAN) to significantly
increase to unacceptable levels?Having a 100% full page does not mean you will have write problems. It
depends on the clustered index and the order in which you insert the data.
But it is likely you will get page splits with a 100% fill factor. This is
not generally recommended on tables that are not static or read only. Each
time you split a page it has to move half the data onto a new page. This
can cause lots of I/O in a busy system.
Andrew J. Kelly SQL MVP
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:05b301c515e1$490caec0$a501280a@.phx.gbl...
> Isn't it true that Sql Server does not maintain the fill
> factor/pad index? i.e. to redo the fill factor, you must
> reindex/defrag? If your fill factor is 100%, you're
> going to get poor write performance, correct? Could this
> cause your Service Times (on an EMC SAN) to significantly
> increase to unacceptable levels?|||Thx for the info.
I know that Service Time is a good indicator of the disk
struggling. Mb/Sec seems reasonable to our admin but he
was saying that IO's/sec looked high to him. What did he
mean by that? Is an I/O to him the same as "commit work"
to me?
>--Original Message--
>Having a 100% full page does not mean you will have
write problems. It
>depends on the clustered index and the order in which
you insert the data.
>But it is likely you will get page splits with a 100%
fill factor. This is
>not generally recommended on tables that are not static
or read only. Each
>time you split a page it has to move half the data onto
a new page. This
>can cause lots of I/O in a busy system.
>--
>Andrew J. Kelly SQL MVP
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
fill[vbcol=seagreen]
must[vbcol=seagreen]
this[vbcol=seagreen]
significantly[vbcol=seagreen]
>
>.
>|||just look at Physical Disk: Current Disk Queue Length That is a good
indicator of problems..
You want this number to be as low as possible (in a perfect world, Zero). IF
the number is pegged, you have serious issues.
Also monitor Physical Disk Split IO/Sec OR SqlServer Access Methods - Page
Splits/sec.
again you want splits to be Zero in a perfect world.
adjust fill factor as needed to reduce Splits.
If the table is really active, you might need to set fill factor to as low
as say "50". I usually start be setting it around 80% and monitor before
going nuts on it.
IF your clustered index is on an identity column or on some other
"Monotonically Increasing Value", then inserts *should* always go to bottom,
so splits may not be an issue.
over time, splits will massively fragement your indexes and performance will
totally tank.
Each table must have a clustered index as well otherwise Defrags are
fruitless.
Monitor fragmentation levels using DBCC ShowContig. The Scan Density Number
should stay ABOVE 80% or you need to consider defragging indexes.
hope this helps
Greg Jackson
PDX, Oregon
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:163401c515e7$89283730$a401280a@.phx.gbl...[vbcol=seagreen]
> Thx for the info.
> I know that Service Time is a good indicator of the disk
> struggling. Mb/Sec seems reasonable to our admin but he
> was saying that IO's/sec looked high to him. What did he
> mean by that? Is an I/O to him the same as "commit work"
> to me?
>
> write problems. It
> you insert the data.
> fill factor. This is
> or read only. Each
> a new page. This
> message
> fill
> must
> this
> significantly|||Thx.
>--Original Message--
>just look at Physical Disk: Current Disk Queue Length
That is a good
>indicator of problems..
>You want this number to be as low as possible (in a
perfect world, Zero). IF
>the number is pegged, you have serious issues.
>Also monitor Physical Disk Split IO/Sec OR SqlServer
Access Methods - Page
>Splits/sec.
>again you want splits to be Zero in a perfect world.
>adjust fill factor as needed to reduce Splits.
>If the table is really active, you might need to set
fill factor to as low
>as say "50". I usually start be setting it around 80%
and monitor before
>going nuts on it.
>IF your clustered index is on an identity column or on
some other
>"Monotonically Increasing Value", then inserts *should*
always go to bottom,
>so splits may not be an issue.
>over time, splits will massively fragement your indexes
and performance will
>totally tank.
>Each table must have a clustered index as well otherwise
Defrags are
>fruitless.
>
>Monitor fragmentation levels using DBCC ShowContig. The
Scan Density Number
>should stay ABOVE 80% or you need to consider defragging
indexes.
>
>hope this helps
>Greg Jackson
>PDX, Oregon
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message
>news:163401c515e7$89283730$a401280a@.phx.gbl...
disk[vbcol=seagreen]
he[vbcol=seagreen]
he[vbcol=seagreen]
work"[vbcol=seagreen]
>
>.
>
factor/pad index? i.e. to redo the fill factor, you must
reindex/defrag? If your fill factor is 100%, you're
going to get poor write performance, correct? Could this
cause your Service Times (on an EMC SAN) to significantly
increase to unacceptable levels?Having a 100% full page does not mean you will have write problems. It
depends on the clustered index and the order in which you insert the data.
But it is likely you will get page splits with a 100% fill factor. This is
not generally recommended on tables that are not static or read only. Each
time you split a page it has to move half the data onto a new page. This
can cause lots of I/O in a busy system.
Andrew J. Kelly SQL MVP
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:05b301c515e1$490caec0$a501280a@.phx.gbl...
> Isn't it true that Sql Server does not maintain the fill
> factor/pad index? i.e. to redo the fill factor, you must
> reindex/defrag? If your fill factor is 100%, you're
> going to get poor write performance, correct? Could this
> cause your Service Times (on an EMC SAN) to significantly
> increase to unacceptable levels?|||Thx for the info.
I know that Service Time is a good indicator of the disk
struggling. Mb/Sec seems reasonable to our admin but he
was saying that IO's/sec looked high to him. What did he
mean by that? Is an I/O to him the same as "commit work"
to me?
>--Original Message--
>Having a 100% full page does not mean you will have
write problems. It
>depends on the clustered index and the order in which
you insert the data.
>But it is likely you will get page splits with a 100%
fill factor. This is
>not generally recommended on tables that are not static
or read only. Each
>time you split a page it has to move half the data onto
a new page. This
>can cause lots of I/O in a busy system.
>--
>Andrew J. Kelly SQL MVP
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
fill[vbcol=seagreen]
must[vbcol=seagreen]
this[vbcol=seagreen]
significantly[vbcol=seagreen]
>
>.
>|||just look at Physical Disk: Current Disk Queue Length That is a good
indicator of problems..
You want this number to be as low as possible (in a perfect world, Zero). IF
the number is pegged, you have serious issues.
Also monitor Physical Disk Split IO/Sec OR SqlServer Access Methods - Page
Splits/sec.
again you want splits to be Zero in a perfect world.
adjust fill factor as needed to reduce Splits.
If the table is really active, you might need to set fill factor to as low
as say "50". I usually start be setting it around 80% and monitor before
going nuts on it.
IF your clustered index is on an identity column or on some other
"Monotonically Increasing Value", then inserts *should* always go to bottom,
so splits may not be an issue.
over time, splits will massively fragement your indexes and performance will
totally tank.
Each table must have a clustered index as well otherwise Defrags are
fruitless.
Monitor fragmentation levels using DBCC ShowContig. The Scan Density Number
should stay ABOVE 80% or you need to consider defragging indexes.
hope this helps
Greg Jackson
PDX, Oregon
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:163401c515e7$89283730$a401280a@.phx.gbl...[vbcol=seagreen]
> Thx for the info.
> I know that Service Time is a good indicator of the disk
> struggling. Mb/Sec seems reasonable to our admin but he
> was saying that IO's/sec looked high to him. What did he
> mean by that? Is an I/O to him the same as "commit work"
> to me?
>
> write problems. It
> you insert the data.
> fill factor. This is
> or read only. Each
> a new page. This
> message
> fill
> must
> this
> significantly|||Thx.
>--Original Message--
>just look at Physical Disk: Current Disk Queue Length
That is a good
>indicator of problems..
>You want this number to be as low as possible (in a
perfect world, Zero). IF
>the number is pegged, you have serious issues.
>Also monitor Physical Disk Split IO/Sec OR SqlServer
Access Methods - Page
>Splits/sec.
>again you want splits to be Zero in a perfect world.
>adjust fill factor as needed to reduce Splits.
>If the table is really active, you might need to set
fill factor to as low
>as say "50". I usually start be setting it around 80%
and monitor before
>going nuts on it.
>IF your clustered index is on an identity column or on
some other
>"Monotonically Increasing Value", then inserts *should*
always go to bottom,
>so splits may not be an issue.
>over time, splits will massively fragement your indexes
and performance will
>totally tank.
>Each table must have a clustered index as well otherwise
Defrags are
>fruitless.
>
>Monitor fragmentation levels using DBCC ShowContig. The
Scan Density Number
>should stay ABOVE 80% or you need to consider defragging
indexes.
>
>hope this helps
>Greg Jackson
>PDX, Oregon
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message
>news:163401c515e7$89283730$a401280a@.phx.gbl...
disk[vbcol=seagreen]
he[vbcol=seagreen]
he[vbcol=seagreen]
work"[vbcol=seagreen]
>
>.
>
Sunday, February 26, 2012
File Size - Probably missing something basic...
We have one main data file, and I would like to track how
fast it grows.
In the Task Pad of SQL Server it gives the file size as X
By running "select * from sysfiles" I get Y,
But by looking at the actual file size I get Z.
Can anyone tell me whats happening ?
JI'm not sure what you mean by the taskpad of SQL Server.. but here goes.
The sysfiles table shows the size of your database is 8k data pages. So you
need to multiply that number by 8.
For example: select * from sysfiles (while in my master database) reveals
the following two files.
size: 1464
size: 96
If I multiply those out as: SELECT ((1464 * 8) + (96 * 8)) you get about 12
MBs
Now if I go out to my: \Program Files\Microsoft SQL Server\MSSQL\Data
My master.mdb file is 11.4 MB
and the ldf file is .7 MB
That makes just over 12 MB... So these are the same.
If I right-click the database in Enterprise Manager and choose properties, I
get just over 12 MB's...
So, these are all the same size as far as I can see...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
> We have one main data file, and I would like to track how
> fast it grows.
> In the Task Pad of SQL Server it gives the file size as X
> By running "select * from sysfiles" I get Y,
> But by looking at the actual file size I get Z.
> Can anyone tell me whats happening ?
> J|||Thanks for that.
What I mean by taskpad is if you go to SQL Server, select
the database, then View -> Taskpad it shows the size of
the file.
J
>--Original Message--
>I'm not sure what you mean by the taskpad of SQL
Server.. but here goes.
>The sysfiles table shows the size of your database is 8k
data pages. So you
>need to multiply that number by 8.
>For example: select * from sysfiles (while in my master
database) reveals
>the following two files.
>size: 1464
>size: 96
>If I multiply those out as: SELECT ((1464 * 8) + (96 *
8)) you get about 12
>MBs
>
>Now if I go out to my: \Program Files\Microsoft SQL
Server\MSSQL\Data
>My master.mdb file is 11.4 MB
>and the ldf file is .7 MB
>That makes just over 12 MB... So these are the same.
>If I right-click the database in Enterprise Manager and
choose properties, I
>get just over 12 MB's...
>
>So, these are all the same size as far as I can see...
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
>> We have one main data file, and I would like to track
how
>> fast it grows.
>> In the Task Pad of SQL Server it gives the file size as
X
>> By running "select * from sysfiles" I get Y,
>> But by looking at the actual file size I get Z.
>> Can anyone tell me whats happening ?
>> J
>
>.
>
fast it grows.
In the Task Pad of SQL Server it gives the file size as X
By running "select * from sysfiles" I get Y,
But by looking at the actual file size I get Z.
Can anyone tell me whats happening ?
JI'm not sure what you mean by the taskpad of SQL Server.. but here goes.
The sysfiles table shows the size of your database is 8k data pages. So you
need to multiply that number by 8.
For example: select * from sysfiles (while in my master database) reveals
the following two files.
size: 1464
size: 96
If I multiply those out as: SELECT ((1464 * 8) + (96 * 8)) you get about 12
MBs
Now if I go out to my: \Program Files\Microsoft SQL Server\MSSQL\Data
My master.mdb file is 11.4 MB
and the ldf file is .7 MB
That makes just over 12 MB... So these are the same.
If I right-click the database in Enterprise Manager and choose properties, I
get just over 12 MB's...
So, these are all the same size as far as I can see...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
> We have one main data file, and I would like to track how
> fast it grows.
> In the Task Pad of SQL Server it gives the file size as X
> By running "select * from sysfiles" I get Y,
> But by looking at the actual file size I get Z.
> Can anyone tell me whats happening ?
> J|||Thanks for that.
What I mean by taskpad is if you go to SQL Server, select
the database, then View -> Taskpad it shows the size of
the file.
J
>--Original Message--
>I'm not sure what you mean by the taskpad of SQL
Server.. but here goes.
>The sysfiles table shows the size of your database is 8k
data pages. So you
>need to multiply that number by 8.
>For example: select * from sysfiles (while in my master
database) reveals
>the following two files.
>size: 1464
>size: 96
>If I multiply those out as: SELECT ((1464 * 8) + (96 *
8)) you get about 12
>MBs
>
>Now if I go out to my: \Program Files\Microsoft SQL
Server\MSSQL\Data
>My master.mdb file is 11.4 MB
>and the ldf file is .7 MB
>That makes just over 12 MB... So these are the same.
>If I right-click the database in Enterprise Manager and
choose properties, I
>get just over 12 MB's...
>
>So, these are all the same size as far as I can see...
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
>> We have one main data file, and I would like to track
how
>> fast it grows.
>> In the Task Pad of SQL Server it gives the file size as
X
>> By running "select * from sysfiles" I get Y,
>> But by looking at the actual file size I get Z.
>> Can anyone tell me whats happening ?
>> J
>
>.
>
File Size - Probably missing something basic...
We have one main data file, and I would like to track how
fast it grows.
In the Task Pad of SQL Server it gives the file size as X
By running "select * from sysfiles" I get Y,
But by looking at the actual file size I get Z.
Can anyone tell me whats happening ?
JI'm not sure what you mean by the taskpad of SQL Server.. but here goes.
The sysfiles table shows the size of your database is 8k data pages. So you
need to multiply that number by 8.
For example: select * from sysfiles (while in my master database) reveals
the following two files.
size: 1464
size: 96
If I multiply those out as: SELECT ((1464 * 8) + (96 * 8)) you get about 12
MBs
Now if I go out to my: \Program Files\Microsoft SQL Server\MSSQL\Data
My master.mdb file is 11.4 MB
and the ldf file is .7 MB
That makes just over 12 MB... So these are the same.
If I right-click the database in Enterprise Manager and choose properties, I
get just over 12 MB's...
So, these are all the same size as far as I can see...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
> We have one main data file, and I would like to track how
> fast it grows.
> In the Task Pad of SQL Server it gives the file size as X
> By running "select * from sysfiles" I get Y,
> But by looking at the actual file size I get Z.
> Can anyone tell me whats happening ?
> J|||Thanks for that.
What I mean by taskpad is if you go to SQL Server, select
the database, then View -> Taskpad it shows the size of
the file.
J
>--Original Message--
>I'm not sure what you mean by the taskpad of SQL
Server.. but here goes.
>The sysfiles table shows the size of your database is 8k
data pages. So you
>need to multiply that number by 8.
>For example: select * from sysfiles (while in my master
database) reveals
>the following two files.
>size: 1464
>size: 96
>If I multiply those out as: SELECT ((1464 * 8) + (96 *
8)) you get about 12
>MBs
>
>Now if I go out to my: \Program Files\Microsoft SQL
Server\MSSQL\Data
>My master.mdb file is 11.4 MB
>and the ldf file is .7 MB
>That makes just over 12 MB... So these are the same.
>If I right-click the database in Enterprise Manager and
choose properties, I
>get just over 12 MB's...
>
>So, these are all the same size as far as I can see...
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
how[vbcol=seagreen]
X[vbcol=seagreen]
>
>.
>
fast it grows.
In the Task Pad of SQL Server it gives the file size as X
By running "select * from sysfiles" I get Y,
But by looking at the actual file size I get Z.
Can anyone tell me whats happening ?
JI'm not sure what you mean by the taskpad of SQL Server.. but here goes.
The sysfiles table shows the size of your database is 8k data pages. So you
need to multiply that number by 8.
For example: select * from sysfiles (while in my master database) reveals
the following two files.
size: 1464
size: 96
If I multiply those out as: SELECT ((1464 * 8) + (96 * 8)) you get about 12
MBs
Now if I go out to my: \Program Files\Microsoft SQL Server\MSSQL\Data
My master.mdb file is 11.4 MB
and the ldf file is .7 MB
That makes just over 12 MB... So these are the same.
If I right-click the database in Enterprise Manager and choose properties, I
get just over 12 MB's...
So, these are all the same size as far as I can see...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
> We have one main data file, and I would like to track how
> fast it grows.
> In the Task Pad of SQL Server it gives the file size as X
> By running "select * from sysfiles" I get Y,
> But by looking at the actual file size I get Z.
> Can anyone tell me whats happening ?
> J|||Thanks for that.
What I mean by taskpad is if you go to SQL Server, select
the database, then View -> Taskpad it shows the size of
the file.
J
>--Original Message--
>I'm not sure what you mean by the taskpad of SQL
Server.. but here goes.
>The sysfiles table shows the size of your database is 8k
data pages. So you
>need to multiply that number by 8.
>For example: select * from sysfiles (while in my master
database) reveals
>the following two files.
>size: 1464
>size: 96
>If I multiply those out as: SELECT ((1464 * 8) + (96 *
8)) you get about 12
>MBs
>
>Now if I go out to my: \Program Files\Microsoft SQL
Server\MSSQL\Data
>My master.mdb file is 11.4 MB
>and the ldf file is .7 MB
>That makes just over 12 MB... So these are the same.
>If I right-click the database in Enterprise Manager and
choose properties, I
>get just over 12 MB's...
>
>So, these are all the same size as far as I can see...
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
how[vbcol=seagreen]
X[vbcol=seagreen]
>
>.
>
File Size - Probably missing something basic...
We have one main data file, and I would like to track how
fast it grows.
In the Task Pad of SQL Server it gives the file size as X
By running "select * from sysfiles" I get Y,
But by looking at the actual file size I get Z.
Can anyone tell me whats happening ?
J
I'm not sure what you mean by the taskpad of SQL Server.. but here goes.
The sysfiles table shows the size of your database is 8k data pages. So you
need to multiply that number by 8.
For example: select * from sysfiles (while in my master database) reveals
the following two files.
size: 1464
size: 96
If I multiply those out as: SELECT ((1464 * 8) + (96 * 8)) you get about 12
MBs
Now if I go out to my: \Program Files\Microsoft SQL Server\MSSQL\Data
My master.mdb file is 11.4 MB
and the ldf file is .7 MB
That makes just over 12 MB... So these are the same.
If I right-click the database in Enterprise Manager and choose properties, I
get just over 12 MB's...
So, these are all the same size as far as I can see...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
> We have one main data file, and I would like to track how
> fast it grows.
> In the Task Pad of SQL Server it gives the file size as X
> By running "select * from sysfiles" I get Y,
> But by looking at the actual file size I get Z.
> Can anyone tell me whats happening ?
> J
|||Thanks for that.
What I mean by taskpad is if you go to SQL Server, select
the database, then View -> Taskpad it shows the size of
the file.
J
>--Original Message--
>I'm not sure what you mean by the taskpad of SQL
Server.. but here goes.
>The sysfiles table shows the size of your database is 8k
data pages. So you
>need to multiply that number by 8.
>For example: select * from sysfiles (while in my master
database) reveals
>the following two files.
>size: 1464
>size: 96
>If I multiply those out as: SELECT ((1464 * 8) + (96 *
8)) you get about 12
>MBs
>
>Now if I go out to my: \Program Files\Microsoft SQL
Server\MSSQL\Data
>My master.mdb file is 11.4 MB
>and the ldf file is .7 MB
>That makes just over 12 MB... So these are the same.
>If I right-click the database in Enterprise Manager and
choose properties, I
>get just over 12 MB's...
>
>So, these are all the same size as far as I can see...
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
how[vbcol=seagreen]
X
>
>.
>
fast it grows.
In the Task Pad of SQL Server it gives the file size as X
By running "select * from sysfiles" I get Y,
But by looking at the actual file size I get Z.
Can anyone tell me whats happening ?
J
I'm not sure what you mean by the taskpad of SQL Server.. but here goes.
The sysfiles table shows the size of your database is 8k data pages. So you
need to multiply that number by 8.
For example: select * from sysfiles (while in my master database) reveals
the following two files.
size: 1464
size: 96
If I multiply those out as: SELECT ((1464 * 8) + (96 * 8)) you get about 12
MBs
Now if I go out to my: \Program Files\Microsoft SQL Server\MSSQL\Data
My master.mdb file is 11.4 MB
and the ldf file is .7 MB
That makes just over 12 MB... So these are the same.
If I right-click the database in Enterprise Manager and choose properties, I
get just over 12 MB's...
So, these are all the same size as far as I can see...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
> We have one main data file, and I would like to track how
> fast it grows.
> In the Task Pad of SQL Server it gives the file size as X
> By running "select * from sysfiles" I get Y,
> But by looking at the actual file size I get Z.
> Can anyone tell me whats happening ?
> J
|||Thanks for that.
What I mean by taskpad is if you go to SQL Server, select
the database, then View -> Taskpad it shows the size of
the file.
J
>--Original Message--
>I'm not sure what you mean by the taskpad of SQL
Server.. but here goes.
>The sysfiles table shows the size of your database is 8k
data pages. So you
>need to multiply that number by 8.
>For example: select * from sysfiles (while in my master
database) reveals
>the following two files.
>size: 1464
>size: 96
>If I multiply those out as: SELECT ((1464 * 8) + (96 *
8)) you get about 12
>MBs
>
>Now if I go out to my: \Program Files\Microsoft SQL
Server\MSSQL\Data
>My master.mdb file is 11.4 MB
>and the ldf file is .7 MB
>That makes just over 12 MB... So these are the same.
>If I right-click the database in Enterprise Manager and
choose properties, I
>get just over 12 MB's...
>
>So, these are all the same size as far as I can see...
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:4cb201c490f4$f09a23b0$a501280a@.phx.gbl...
how[vbcol=seagreen]
X
>
>.
>
Subscribe to:
Posts (Atom)