index fillfactor

How can you identify the current space available in a clustered index? I'm
aware of the commands to rebuild the index, but I'd like to be able to
monitor how "full" an index is relative to the original fill factor of 90%.
Thanks in advance.
MarkThanks Tom. Just what I was looking for. I pasted the results below. If
I'm reading the results correctly, the "Avg. Page Density (full)" indicates
that the table is currently existing with a fill factor of about 100%. In
other words, inserts wouldn't be pretty. Correct? Moreover, if a page is
roughly 8000 bytes, and only 75 or so of those bytes are free, that's again
saying that I have 1% or less of free space ... correct again?
Thanks again.
Mark
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (1672393027); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 166
- Extents Scanned.......................: 22
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 95.45% [21:22]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 4.55%
- Avg. Bytes Free per Page................: 75.8
- Avg. Page Density (full)................: 99.06%
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e7eROPRgDHA.2072@.TK2MSFTNGP10.phx.gbl...
Check out DBCC SHOWCONTIG in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mark" <field027_nospam_@.umn.edu> wrote in message
news:#5UwdNRgDHA.2984@.TK2MSFTNGP11.phx.gbl...
How can you identify the current space available in a clustered index? I'm
aware of the commands to rebuild the index, but I'd like to be able to
monitor how "full" an index is relative to the original fill factor of 90%.
Thanks in advance.
Mark|||This is a multi-part message in MIME format.
--=_NextPart_000_00F0_01C380F6.D2157E80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Your average page density says how full the pages are. Right after to =rebuild the index, this will be approximately equal to your fill factor. = the fact that you also have only 75 bytes free per page supports the =high page density that you have.
An insert is not necessarily going to lead to a page split. If you had =skinny rows, e.g. 20 bytes and inserted 2 rows, you would not get a page =split. Also, if the clustered index was on a monotonically increasing =value, you would be adding rows to the "end" of the table and would =simply pick up a new page.
Your table is so small that I would not worry about fragmentation.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mark" <field027_nospam_@.umn.edu> wrote in message =news:#hlqaWRgDHA.2260@.TK2MSFTNGP10.phx.gbl...
Thanks Tom. Just what I was looking for. I pasted the results below. =If
I'm reading the results correctly, the "Avg. Page Density (full)" =indicates
that the table is currently existing with a fill factor of about 100%. =In
other words, inserts wouldn't be pretty. Correct? Moreover, if a =page is
roughly 8000 bytes, and only 75 or so of those bytes are free, that's =again
saying that I have 1% or less of free space ... correct again?
Thanks again.
Mark
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (1672393027); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 166
- Extents Scanned.......................: 22
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 95.45% [21:22]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 4.55%
- Avg. Bytes Free per Page................: 75.8
- Avg. Page Density (full)................: 99.06%
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e7eROPRgDHA.2072@.TK2MSFTNGP10.phx.gbl...
Check out DBCC SHOWCONTIG in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mark" <field027_nospam_@.umn.edu> wrote in message
news:#5UwdNRgDHA.2984@.TK2MSFTNGP11.phx.gbl...
How can you identify the current space available in a clustered index? =I'm
aware of the commands to rebuild the index, but I'd like to be able to
monitor how "full" an index is relative to the original fill factor of =90%.
Thanks in advance.
Mark
--=_NextPart_000_00F0_01C380F6.D2157E80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Your average page density says how =full the pages are. Right after to rebuild the index, this will be approximately =equal to your fill factor. the fact that you also have only 75 bytes free =per page supports the high page density that you have.
An insert is not necessarily going to =lead to a page split. If you had skinny rows, e.g. 20 bytes and inserted 2 =rows, you would not get a page split. Also, if the clustered index was =on a monotonically increasing value, you would be adding rows to the "end" of =the table and would simply pick up a new page.
Your table is so small that I would =not worry about fragmentation.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mark" = wrote in message news:#hlqaWRgDHA.2260=@.TK2MSFTNGP10.phx.gbl...Thanks Tom. Just what I was looking for. I pasted the results =below. IfI'm reading the results correctly, the "Avg. Page Density (full)" indicatesthat the table is currently existing with a fill factor of =about 100%. Inother words, inserts wouldn't be pretty. Correct? Moreover, if a page isroughly 8000 bytes, =and only 75 or so of those bytes are free, that's againsaying that I =have 1% or less of free space ... correct again?Thanks again.MarkDBCC SHOWCONTIG scanning 'my_table' =table...Table: 'my_table' (1672393027); index ID: 1, database ID: 10TABLE level =scan performed.- Pages Scanned........................: 166- =Extents Scanned.......................: 22- Extent Switches.......................: 21- Avg. Pages per Extent..................: 7.5- Scan Density [Best Count:Actual =Count]......: 95.45% [21:22]- Logical Scan Fragmentation ..............: 0.00%- Extent Scan Fragmentation =...............: 4.55%- Avg. Bytes Free per Page................: 75.8- Avg. =Page Density (full)................: 99.06%"Tom Moreau" == wrote in messagenews:e7eROPRgDHA.2072=@.TK2MSFTNGP10.phx.gbl...Check out DBCC SHOWCONTIG in the BOL.-- Tom----=--Thomas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql"Mark" = wrote in messagenews:#5UwdNRgDHA.2984=@.TK2MSFTNGP11.phx.gbl...How can you identify the current space available in a clustered index? =I'maware of the commands to rebuild the index, but I'd like to be =able tomonitor how "full" an index is relative to the original fill =factor of 90%.Thanks in advance.Mark

---

--=_NextPart_000_00F0_01C380F6.D2157E80--

Index Corruption

Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor MauricioFrequent corruption is not normal. You would want to check
your hardware and the windows event logs for any indications
of hardware issues.
You can identify issues using dbcc checkdb. If you want to
limit the check to specific tables, you dbcc checktable.
The best approach to fixing the issues is to do root cause
analysis and determine what is causing the corruption and
then address those issues.
-Sue
On Mon, 18 Oct 2004 23:01:02 -0300, "Vitor Mauricio de N.
Silva" <vitor_mauricio@.newsgroup.nospam> wrote:
>Hi,
>What's the best approach to identify and fix index corruption problems. I
>have some tables that corruption is occuring frequently.
>Any idea ?
>Vitor Mauricio
>|||Hi Vitor Mauricio,
I wanted to post a quick note to see if you would like additional
assistance or information regarding the information Sue had sent to you. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Corruption could happen if you are performing a few operations on a
given table in batch without commiting them. Say for instance you
delete a bunch of records, and perform an insert, then update a field.
If you are doing this in a batch, then you could possibly run into a
corruption issue due to the fact that the datbase hasnt committed the
activity. The index "thinks" that there are more records there then
there actually are, so in this case the index becomes corrupt and you
must re-build it. One way around this is to separate your logic into
more compartmentalized code or update statistics between operations.
Vitor Mauricio de N. Silva wrote:
> *Hi,
> What's the best approach to identify and fix index corruption
> problems. I
> have some tables that corruption is occuring frequently.
> Any idea ?
> Vitor Mauricio *
mbroaddus
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1165249.html

Index Corruption

Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor Mauricio
Frequent corruption is not normal. You would want to check
your hardware and the windows event logs for any indications
of hardware issues.
You can identify issues using dbcc checkdb. If you want to
limit the check to specific tables, you dbcc checktable.
The best approach to fixing the issues is to do root cause
analysis and determine what is causing the corruption and
then address those issues.
-Sue
On Mon, 18 Oct 2004 23:01:02 -0300, "Vitor Mauricio de N.
Silva" <vitor_mauricio@.newsgroup.nospam> wrote:

>Hi,
>What's the best approach to identify and fix index corruption problems. I
>have some tables that corruption is occuring frequently.
>Any idea ?
>Vitor Mauricio
>
|||Hi Vitor Mauricio,
I wanted to post a quick note to see if you would like additional
assistance or information regarding the information Sue had sent to you. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Corruption could happen if you are performing a few operations on a given table in batch without commiting them. Say for instance you delete a bunch of records, and perform an insert, then update a field. If you are doing this in a batch, then you could possibly run into a corruption issue due to the fact that the datbase hasnt committed the activity. The index "thinks" that there are more records there then there actually are, so in this case the index becomes corrupt and you must re-build it. One way around this is to separate your logic into more compartmentalized code or update statistics between operations.

Quote:

Originally posted by Vitor Mauricio de N. Silva
Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor Mauricio

Index Corruption

Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor MauricioFrequent corruption is not normal. You would want to check
your hardware and the windows event logs for any indications
of hardware issues.
You can identify issues using dbcc checkdb. If you want to
limit the check to specific tables, you dbcc checktable.
The best approach to fixing the issues is to do root cause
analysis and determine what is causing the corruption and
then address those issues.
-Sue
On Mon, 18 Oct 2004 23:01:02 -0300, "Vitor Mauricio de N.
Silva" <vitor_mauricio@.newsgroup.nospam> wrote:

>Hi,
>What's the best approach to identify and fix index corruption problems. I
>have some tables that corruption is occuring frequently.
>Any idea ?
>Vitor Mauricio
>|||Hi Vitor Mauricio,
I wanted to post a quick note to see if you would like additional
assistance or information regarding the information Sue had sent to you. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Corruption could happen if you are performing a few operations on a
given table in batch without commiting them. Say for instance you
delete a bunch of records, and perform an insert, then update a field.
If you are doing this in a batch, then you could possibly run into a
corruption issue due to the fact that the datbase hasnt committed the
activity. The index "thinks" that there are more records there then
there actually are, so in this case the index becomes corrupt and you
must re-build it. One way around this is to separate your logic into
more compartmentalized code or update statistics between operations.
Vitor Mauricio de N. Silva wrote:
> *Hi,
> What's the best approach to identify and fix index corruption
> problems. I
> have some tables that corruption is occuring frequently.
> Any idea ?
> Vitor Mauricio *
mbroaddus
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1165249.html