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"
--=_NextPart_000_00F0_01C380F6.D2157E80--
No comments:
Post a Comment