February 3, 2003 at 11:50 am
Hi all, I'm looking for guidance on how to keep the Scan Density up
I have the following table (might be a canned MS table, the BSKT_ID, I'm told, comes from comm. server.) By the end of day 2:00am, I notice that the Scan Density degrades below 20%, I run reindex and everyhthing is fine.
The nature of the table as you can imagine is for an online store with insert, delete, update, and select. Currently there's a cluster index on BSKT_ID. We keep 30 days worth of data on the table which gives about 160-190K entries out of season.
CREATE TABLE [dbo].[BSKT] (
[BSKT_ID] [varchar] (100) NOT NULL ,
[CHG_DT] [datetime] NULL ,
[BSKT_IMG] [image] NULL ,
[USER_ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Here's the stats:
DBCC SHOWCONTIG For table: BSKT [SQLSTATE 01000]
DBCC SHOWCONTIG scanning 'BSKT' table... [SQLSTATE 01000]
Table: 'BSKT' (1109578991); index ID: 1, database ID: 9 [SQLSTATE 01000]
TABLE level scan performed. [SQLSTATE 01000]
- Pages Scanned................................: 3069 [SQLSTATE 01000]
- Extents Scanned..............................: 392 [SQLSTATE 01000]
- Extent Switches..............................: 2675 [SQLSTATE 01000]
- Avg. Pages per Extent........................: 7.8 [SQLSTATE 01000]
- Scan Density [Best Count:Actual Count].......: 14.35% [384:2676] [SQLSTATE 01000]
- Logical Scan Fragmentation ..................: 85.60% [SQLSTATE 01000]
- Extent Scan Fragmentation ...................: 30.87% [SQLSTATE 01000]
- Avg. Bytes Free per Page.....................: 4075.7 [SQLSTATE 01000]
- Avg. Page Density (full).....................: 49.65% [SQLSTATE 01000]
John Zacharkan
John Zacharkan
February 3, 2003 at 1:13 pm
What is the fill factor setting? You may consider adjustmnet of fill factor for this table alone.
Run sp_configure to see the server wide setting for fill factor.
select INDEXPROPERTY (YourTable_ID , YourIndex , IndexFillFactor) to check fill factor for a table.
February 3, 2003 at 1:39 pm
name minimum maximum config_value run_value
----------------- ----------- ----------- ------------ -----------
fill factor (%) 0 100 0 0
select INDEXPROPERTY (1109578991, 1, 0)
returns NULL
quote:
What is the fill factor setting? You may consider adjustmnet of fill factor for this table alone.Run sp_configure to see the server wide setting for fill factor.
select INDEXPROPERTY (YourTable_ID , YourIndex , IndexFillFactor) to check fill factor for a table.
John Zacharkan
John Zacharkan
February 3, 2003 at 1:51 pm
Can you run select INDEXPROPERTY (object_id('yourtablename'), 'yourclusterindexname', 'IndexFillFactor'). Sorry for gave you wrong syntax.
Use sp_helpindex yourtablename to get cluster index name
February 3, 2003 at 2:03 pm
sp_helpindex 'BSKT'
PK__BSKT__52AE4273 clustered, unique, primary key located on PRIMARY BSKT_ID
chg_dt_ndx nonclustered located on PRIMARY CHG_DT
select INDEXPROPERTY (1109578991, 'PK__BSKT__52AE4273', 0)
returns NULL
quote:
Can you run select INDEXPROPERTY (object_id('yourtablename'), 'yourclusterindexname', 'IndexFillFactor'). Sorry for gave you wrong syntax.Use sp_helpindex yourtablename to get cluster index name
John Zacharkan
John Zacharkan
February 3, 2003 at 2:16 pm
Don't replace the 'IndexFillFactor' with 0
Here it is.
select INDEXPROPERTY (1109578991, 'PK__BSKT__52AE4273', 'IndexFillFactor')
February 3, 2003 at 2:20 pm
🙂 0 (zero)
John Zacharkan
John Zacharkan
February 3, 2003 at 2:26 pm
Sorry I don't normally mess with fill factor. I thought the only reason for increasing this above zero is if you know that the data is static. In this case the data is anything but static.
John Zacharkan
John Zacharkan
February 3, 2003 at 2:32 pm
Quoted from BOL.
"fill factor Option
Use the fill factor option to specify how full Microsoft® SQL Server™ should make each page when it creates a new index using existing data. The fill factor percentage affects performance because SQL Server must take time to split pages when they fill up.
The fill factor percentage is used only at the time the index is created. The pages are not maintained at any particular level of fullness.
The default for fill factor is 0; valid values range from 0 through 100. A fill factor value of 0 does not mean that pages are 0 percent full. It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. It is different from 100 in that SQL Server leaves some space within the upper level of the index tree. There is seldom a reason to change the default fill factor value because you can override it with the CREATE INDEX statement.
Small fill factor values cause SQL Server to create new indexes with pages that are not full. For example, a fill factor value of 10 is a reasonable choice if you are creating an index on a table that you know contains only a small portion of the data that it will eventually hold. Smaller fill factor values cause each index to take more storage space, allowing room for subsequent insertions without requiring page splits.
If you set fill factor to 100, SQL Server creates both clustered and nonclustered indexes with each page 100 percent full. Setting fill factor to 100 is suitable only for read-only tables, to which additional data is never added.
fill factor is an advanced option. If you will be using the sp_configure system stored procedure to change the setting, you can change fill factor only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server."
You take the default value for fill factor when create cliuster index for your table. Because there are many insert, delete and update, pages in the table have to be splited frequently. The result of scan density will be definitely low. Try to adjust the fill factor for this table by re-creating the cluster index with 85 and run performance monitor to monitor SQL Server counter SQLSERVER : Access Methods Page Split/sec before and after you make the change to find out the best fill factor your table should have.
For more information, See BOL.
February 3, 2003 at 3:06 pm
I've added it to my monitor - it's currently running at .434/s with the 0 fill factor. I'll let it run and revisit it in the morning/lunch during our peak usage. Thanks for your help, I'll be sure to follow up tommorrow.
John Zacharkan
John Zacharkan
February 4, 2003 at 9:35 am
Thanks again, I made the change to 85% that seems to have had a positive effect. Here's the results
- Pages Scanned................................: 1815
- Extents Scanned..............................: 227
- Extent Switches..............................: 226
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [227:227]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 18.94%
- Avg. Bytes Free per Page.....................: 1181.7
- Avg. Page Density (full).....................: 85.40%
Page splitting is around an avg .60/s with a max 6.0/s
It has stayed that way for the last 2 hours, whereas yesterday I would see an immediate progressive drop in the scan density %.
Does a 0% fillfactor have a magical meaning or effect? I didn't see anything in BOL, but the default 7.0 install does set it up this way. Logically looking at it would start off with 0 records per page, at 1% percent it would start with 1600 records.
Thanks for the help, I will definitely keep fillfactor in mind while maintaining performance.
Zach
quote:
You take the default value for fill factor when create cliuster index for your table. Because there are many insert, delete and update, pages in the table have to be splited frequently. The result of scan density will be definitely low. Try to adjust the fill factor for this table by re-creating the cluster index with 85 and run performance monitor to monitor SQL Server counter SQLSERVER : Access Methods Page Split/sec before and after you make the changeto find out the best fill factor your table should have.
John Zacharkan
Edited by - zach_john on 02/04/2003 09:37:29 AM
John Zacharkan
February 5, 2003 at 1:33 pm
Allen - would you expect to see a rise in cpu utilization after making these changes. Average page split is around .900 with a max 5.039
John Zacharkan
John Zacharkan
February 5, 2003 at 2:11 pm
I don't see they are related directly.
By the way, In order to optimize the database performance, DBCC DNREINDEX should be performed regularly too.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply