SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Index and Statistics Maintenance


SQL Server Index and Statistics Maintenance

Author
Message
anzz
anzz
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1235 Visits: 704
Just showing an example in our environment.

BCC SHOWCONTIG scanning 'Table_Name' table...
Table: 'Table_Name' (274100017); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 663139
- Extents Scanned..............................: 83063
- Extent Switches..............................: 166349
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 49.83% [82890:169350]
- Logical Scan Fragmentation ..................: 1.94%
- Extent Scan Fragmentation ...................: 24.29%
- Avg. Bytes Free per Page.....................: 277.4
- Avg. Page Density (full).....................: 96.57%

When I check the Scan Density it is 49.8% and Best and Actual count is not matching to close. It is double the count.
I would like to increase the Scan Density %. If I do re index then Best and Actual count is matching to close.

Currently for our Index Maintenance: we are running
If Logical Scan Fragmentation is 5-30% then it would do Reorg.
If Logical Scan Fragmentation is above 30% then it would do Rebuilt.

But, we are unhappy about the scan Density, How Automate a job process on increasing the Scan density?

Any help is greatly appreciated.
Thanks.
@SQLFRNDZ
@SQLFRNDZ
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2137 Visits: 1218
How often you run the job for the re-org and re-index maintainance ?


Scan density <100% means to frangmentaion existance.


--SQLFRNDZ

anzz
anzz
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1235 Visits: 704
We run daily index maint job.

here is the link which used to set up our index Maint job: http://ola.hallengren.com/

Any help how to increase the scan density ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search