Scan Density

Scan Density

Post by Tina Smit » Sun, 16 Jan 2000 04:00:00



On Monday I did a dbreindex on a table thus giving me a 100% scan density.
Today I did a showcontig on this same table and it showed a 40% scan
density.     I'm seeing between 70 - 100 rows being added to this table on a
daily basis.   Can this amount of activity create fragmentation in such a
short time?    I do have 5 indecies on this table.

Thanks
Tina

 
 
 

Scan Density

Post by BPMargoli » Sun, 16 Jan 2000 04:00:00


Tina,

You don't mention the size of the table, so we have no idea whether 70 - 100
rows is a significant percentage of the total number of rows in the table.
Also, you have to remember that an update to any one of the column
components of a clustered key can also cause page splits, and thus reduced
density.


Quote:> On Monday I did a dbreindex on a table thus giving me a 100% scan density.
> Today I did a showcontig on this same table and it showed a 40% scan
> density.     I'm seeing between 70 - 100 rows being added to this table on
a
> daily basis.   Can this amount of activity create fragmentation in such a
> short time?    I do have 5 indecies on this table.

> Thanks
> Tina


 
 
 

Scan Density

Post by Kalen Delane » Sun, 16 Jan 2000 04:00:00


We also need to know how big the rows are.
You should consider leaving room on the pages when you reindex by specifying
a FILLFACTOR (see BOL)

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


Quote:> On Monday I did a dbreindex on a table thus giving me a 100% scan density.
> Today I did a showcontig on this same table and it showed a 40% scan
> density.     I'm seeing between 70 - 100 rows being added to this table on
a
> daily basis.   Can this amount of activity create fragmentation in such a
> short time?    I do have 5 indecies on this table.

> Thanks
> Tina

 
 
 

Scan Density

Post by Tina Smit » Sun, 16 Jan 2000 04:00:00


Sorry for leaving out some key information.

Total Rows:  11685
Avg. Rows Updated Daily:   150
Rows Added Daily:  70 - 100
Estimated Row Size:  250 bytes
Primary Key:  NonClustered index on identity column
Current Fill Factor: 0

Statistics as of this afternoon after doing a dbreindex this morning.

TABLE level scan performed.
- Pages Scanned................................: 1513
- Extent Switches..............................: 214
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 88.37% [189:215]
- Avg. Bytes free per page.....................: 633.8
- Avg. Page density (full).....................: 68.53%
- Overflow Pages...............................: 586
- Avg. Bytes free per Overflow page............: 1506.7
- Avg. Overflow Page density...................: 25.2%
- Disconnected Overflow Pages..................: 0

Thanks
Tina

Quote:> Tina,

> You don't mention the size of the table, so we have no idea whether 70 -
100
> rows is a significant percentage of the total number of rows in the table.

 
 
 

Scan Density

Post by BPMargoli » Sun, 16 Jan 2000 04:00:00


Tina,

Thanks for the additional information. I believe your original posting
indicated that you had about 5 indexes. I will assume that one of them is
clustered. You haven't indicated whether updates normally change the value
of a column that comprises the clustered index.

Let's work on a worse case scenario: there are about 250 rows inserted and
updated daily. If every one of them requires a page split, then on a basis
of only 11, 685 rows in the entire table, it would not surprise me that the
table quickly becomes fragmented.

A rule of thumb is that the columns of a clustered index should be stable,
i.e., not be likely to participate in updates. Of course remember that is
only a rule of thumb. It other words it is not cast in concrete.

The more important question is: why are you concerned about the
fragmentation? Is performance significantly reduced because of the
fragmentation? Consider taking Kalen's advice and read up about both
FILLFACTOR and PAD_INDEX in the SQL Server Books Online.


> Sorry for leaving out some key information.

> Total Rows:  11685
> Avg. Rows Updated Daily:   150
> Rows Added Daily:  70 - 100
> Estimated Row Size:  250 bytes
> Primary Key:  NonClustered index on identity column
> Current Fill Factor: 0

> Statistics as of this afternoon after doing a dbreindex this morning.

> TABLE level scan performed.
> - Pages Scanned................................: 1513
> - Extent Switches..............................: 214
> - Avg. Pages per Extent........................: 7.7
> - Scan Density [Best Count:Actual Count].......: 88.37% [189:215]
> - Avg. Bytes free per page.....................: 633.8
> - Avg. Page density (full).....................: 68.53%
> - Overflow Pages...............................: 586
> - Avg. Bytes free per Overflow page............: 1506.7
> - Avg. Overflow Page density...................: 25.2%
> - Disconnected Overflow Pages..................: 0

> Thanks
> Tina


> > Tina,

> > You don't mention the size of the table, so we have no idea whether 70 -
> 100
> > rows is a significant percentage of the total number of rows in the
table.

 
 
 

Scan Density

Post by Tina Smit » Sun, 16 Jan 2000 04:00:00


The column that comprises the clustered index typically would not be a
column changed during an update.   The table under discussion is an
Insurance Claim table.    Once the row is added to the table, an update
would occur for amount paid and date sent which are not columns for the
clustered index.

I'm concerned about the fragmentation because I'm looking into page splits
as a possible cause for deadlocks.   As mentioned, 70-100 claims get added
to this table a day.   During the day at any given point a user will decide
to process these new claims that were added while others are continuing to
add new ones.  During the claim processing  I'll get a deadlock between the
processor ( user  running the processing function ) and a user adding a new
claim.    100% of the time the deadlock victim is the one adding a new
claim.

Thanks
Tina


Quote:> Tina,

> Thanks for the additional information. I believe your original posting
> indicated that you had about 5 indexes. I will assume that one of them is
> clustered. You haven't indicated whether updates normally change the value
> of a column that comprises the clustered index.

> Let's work on a worse case scenario: there are about 250 rows inserted and
> updated daily. If every one of them requires a page split, then on a basis
> of only 11, 685 rows in the entire table, it would not surprise me that
the
> table quickly becomes fragmented.

> A rule of thumb is that the columns of a clustered index should be stable,
> i.e., not be likely to participate in updates. Of course remember that is
> only a rule of thumb. It other words it is not cast in concrete.

> The more important question is: why are you concerned about the
> fragmentation? Is performance significantly reduced because of the
> fragmentation? Consider taking Kalen's advice and read up about both
> FILLFACTOR and PAD_INDEX in the SQL Server Books Online.

 
 
 

Scan Density

Post by Kalen Delane » Sun, 16 Jan 2000 04:00:00


You also didn't tell us you were running 6.5, that would have been VERY
useful information.
From this showcontig output, it looks like either you don't have a clustered
index, so all new inserts are going at the end of the table, or else that
you have lots of duplicates on your clustering key.  I would suggest you
make sure you have a clustered index, but that you make it one with out a
large percentage of duplicates. E.g. clustered on last name would be OK,
clustered on state if you just serve one region of the country would not be
ok.

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


> Sorry for leaving out some key information.

> Total Rows:  11685
> Avg. Rows Updated Daily:   150
> Rows Added Daily:  70 - 100
> Estimated Row Size:  250 bytes
> Primary Key:  NonClustered index on identity column
> Current Fill Factor: 0

> Statistics as of this afternoon after doing a dbreindex this morning.

> TABLE level scan performed.
> - Pages Scanned................................: 1513
> - Extent Switches..............................: 214
> - Avg. Pages per Extent........................: 7.7
> - Scan Density [Best Count:Actual Count].......: 88.37% [189:215]
> - Avg. Bytes free per page.....................: 633.8
> - Avg. Page density (full).....................: 68.53%
> - Overflow Pages...............................: 586
> - Avg. Bytes free per Overflow page............: 1506.7
> - Avg. Overflow Page density...................: 25.2%
> - Disconnected Overflow Pages..................: 0

> Thanks
> Tina


> > Tina,

> > You don't mention the size of the table, so we have no idea whether 70 -
> 100
> > rows is a significant percentage of the total number of rows in the
table.

 
 
 

Scan Density

Post by Tina Smit » Mon, 17 Jan 2000 04:00:00


Kalen,

I appreciate you taken time to give me a hand.

I'm running 6.5 and have a clustered index on an integer column.   As
mentioned, this table currently has 11685
rows in it.    I have 4128 distinct rows in my table based on the column I'm
using for the clustered index.   So on an averge I have nearly 2.83
duplicate rows for my clustered column.     The highest number of duplicate
rows for my clustered column is 22.

Forgive me for asking, what shows in my showcontig output that would tell
you that my clustered index has alot of duplicates.

Thanks
Tina


Quote:> You also didn't tell us you were running 6.5, that would have been VERY
> useful information.
> From this showcontig output, it looks like either you don't have a
clustered
> index, so all new inserts are going at the end of the table, or else that
> you have lots of duplicates on your clustering key.  I would suggest you
> make sure you have a clustered index, but that you make it one with out a
> large percentage of duplicates. E.g. clustered on last name would be OK,
> clustered on state if you just serve one region of the country would not
be
> ok.

> HTH

 
 
 

Scan Density

Post by Kalen Delane » Mon, 17 Jan 2000 04:00:00


It seems like scan density is dropping a bit too much for what you are
doing, but I would have to do a lot more analysis to find the exact cause. I
would suggest you try using a Fillfactor when you REINDEX; that will
probably alleviate the problem. If not, put the clustered index on a
composite key that is more unique.

The high number of overflow pages means you have a lot of duplicates. Inside
SQL Server 6.5 by Ron Soukup has a lot of information about overflow pages.
They don't exist in 7.0 because all clustered indexes are made unique
internally.

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


> Kalen,

> I appreciate you taken time to give me a hand.

> I'm running 6.5 and have a clustered index on an integer column.   As
> mentioned, this table currently has 11685
> rows in it.    I have 4128 distinct rows in my table based on the column
I'm
> using for the clustered index.   So on an averge I have nearly 2.83
> duplicate rows for my clustered column.     The highest number of
duplicate
> rows for my clustered column is 22.

> Forgive me for asking, what shows in my showcontig output that would tell
> you that my clustered index has alot of duplicates.

> Thanks
> Tina



> > You also didn't tell us you were running 6.5, that would have been VERY
> > useful information.
> > From this showcontig output, it looks like either you don't have a
> clustered
> > index, so all new inserts are going at the end of the table, or else
that
> > you have lots of duplicates on your clustering key.  I would suggest you
> > make sure you have a clustered index, but that you make it one with out
a
> > large percentage of duplicates. E.g. clustered on last name would be OK,
> > clustered on state if you just serve one region of the country would not
> be
> > ok.

> > HTH

 
 
 

Scan Density

Post by Tina Smit » Mon, 17 Jan 2000 04:00:00


Kalen,

I'll give the fillfactor a shot.  I do have Ron's book so I'll read up on
all this stuff.

Thanks for your help.
Tina


> It seems like scan density is dropping a bit too much for what you are
> doing, but I would have to do a lot more analysis to find the exact cause.
I
> would suggest you try using a Fillfactor when you REINDEX; that will
> probably alleviate the problem. If not, put the clustered index on a
> composite key that is more unique.

> The high number of overflow pages means you have a lot of duplicates.
Inside
> SQL Server 6.5 by Ron Soukup has a lot of information about overflow
pages.
> They don't exist in 7.0 because all clustered indexes are made unique
> internally.

> HTH

> --
> Kalen Delaney
> MCSE, SQL Server MCT, MVP
> www.InsideSQLServer.com
> Feed Someone for Free Today:
>      www.TheHungerSite.com



> > Kalen,

> > I appreciate you taken time to give me a hand.

> > I'm running 6.5 and have a clustered index on an integer column.   As
> > mentioned, this table currently has 11685
> > rows in it.    I have 4128 distinct rows in my table based on the column
> I'm
> > using for the clustered index.   So on an averge I have nearly 2.83
> > duplicate rows for my clustered column.     The highest number of
> duplicate
> > rows for my clustered column is 22.

> > Forgive me for asking, what shows in my showcontig output that would
tell
> > you that my clustered index has alot of duplicates.

> > Thanks
> > Tina



> > > You also didn't tell us you were running 6.5, that would have been
VERY
> > > useful information.
> > > From this showcontig output, it looks like either you don't have a
> > clustered
> > > index, so all new inserts are going at the end of the table, or else
> that
> > > you have lots of duplicates on your clustering key.  I would suggest
you
> > > make sure you have a clustered index, but that you make it one with
out
> a
> > > large percentage of duplicates. E.g. clustered on last name would be
OK,
> > > clustered on state if you just serve one region of the country would
not
> > be
> > > ok.

> > > HTH

 
 
 

1. FILLFACTOR (SCAN DENSITY VS. AVERAGE PAGE DENSITY)

Scenario:
 Created a clustered index and nonclustered indexes on a
table with 5,953,675 rows using a fillfactor of "95" for
clustered index and "98" for nonclustered indexes.
  After 82,119 inserts (.01 of table), the nonclustered
indexes had a value of "83" for scan density, "4" for
logical frag, and "96" for avg. page density.  The
clustered index had a value of "96" for scan density, "1"
for logical frag, and "88" for avg. page density.
  Methinks I should have used a fillfactor for the
nonclustered indexes a value of "96", and a fillfactor
value of "96" for the clustered index.  The table and
indexes span multiple files.  Confused on trade-off on
scan density versus avg. page density with nightly inserts.
Am in an OLAP (Data Warehouse) environment.  Have other
tables with similiar scenarios (roughly one percent of
table being done nightly with inserts).
  Am open to anything.  Thank you

2. Load Database Error

3. Scan Density

4. Row and column totals in Apex DBGrid 6 Pro

5. Tables in queries from different databases which reside on different SQL 6.5 Servers

6. Scan Density doesn't change

7. Import DELimited File Format Files to DB2 for OS/390 Tables with DB2 Connect

8. syscomments table - low Scan Density

9. 'Scan Density' any ideas ?

10. Scan Density,Page splitting,heaps... Am I missing something?

11. Table scan, Table scan, Table scan

12. Clustered Index Scan vs. Table Scan