Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Index and Statistics Maintenance Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 4:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:00 AM
Points: 153, Visits: 573
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.

Post #1395354
Posted Tuesday, December 11, 2012 5:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:04 PM
Points: 450, Visits: 991
How often you run the job for the re-org and re-index maintainance ?


Scan density <100% means to frangmentaion existance.



--SQLFRNDZ
Post #1395374
Posted Wednesday, December 12, 2012 8:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:00 AM
Points: 153, Visits: 573
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 ?
Post #1395712
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse