Indexes: An Overview and Maintenance for Performance

Written by Desiree Harris


Continued from page 1

Let's observe an example:

Runningrepparttar query on a table called member:  DBCC SHOWCONTIG (member) WITH ALL_INDEXES

DBCC SHOWCONTIG scanning 'member' table... Table: 'member' (786101841); index ID: 2, database ID: 14 LEAF level scan performed. - Pages Scanned................................: 192 - Extents Scanned..............................: 26 - Extent Switches..............................: 187 - Avg. Pages per Extent........................: 7.4 - Scan Density [Best Count:Actual Count].......: 12.77% [24:188] - Logical Scan Fragmentation ..................: 48.96% - Extent Scan Fragmentation ...................: 96.15% - Avg. Bytes Free per Page.....................: 6721.0 - Avg. Page Density (full).....................: 16.96% DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Runningrepparttar 133386 query on a table named provider:  DBCC SHOWCONTIG (provider) WITH ALL_INDEXES

DBCC SHOWCONTIG scanning 'provider' table... Table: 'provider' (850102069); index ID: 2, database ID: 14 LEAF level scan performed. - Pages Scanned................................: 3 - Extents Scanned..............................: 1 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 3.0 - Scan Density [Best Count:Actual Count].......: 100.00% [1:1] - Logical Scan Fragmentation ..................: 33.33% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 5596.0 - Avg. Page Density (full).....................: 30.86% DBCC execution completed. If DBCC printed error messages, contact your system administrator.

There are a few specific things to take note of and will help determine if index pages are full or if they are heavily fragmented.

The fullness ofrepparttar 133387 index pages can be determined by readingrepparttar 133388 "Avg. Bytes free per page" and "Avg. Page density (full)" statistics. The "Avg. Bytes free per page" figure should be low andrepparttar 133389 "Avg. Page density (full)" figure should be high. You'll notice that both tables likely have very full pages.

The fragmentation level of an index can be determined by comparingrepparttar 133390 values of "Extent Switches" and "Extents Scanned" and having a clear understanding "Logical Scan Fragmentation" and "Extent Scan Fragmentation" values. The "Extent Switches" should be almost equal to "Extents Scanned." Based onrepparttar 133391 examples above, this isrepparttar 133392 way it should look. "Logical Scan Fragmentation" and "Extent Scan Fragmentation" values give a good indication of a table's fragmentation level. These values should be as close to zero as possible (10% may be acceptable). The 'member' table is highly fragmented andrepparttar 133393 provider table is slightly fragmented based onrepparttar 133394 numbers above.

These issues can be corrected by dropping and recreating a clustered index withrepparttar 133395 FILLFACTOR option specified. Also,repparttar 133396 DBCC INDEXDEFRAG command will compact an index, taking into account its FILLFACTOR, which will improverepparttar 133397 statistics.

After runningrepparttar 133398 queries below:  DBCC DBREINDEX (member, '', 80)  DBCC DBREINDEX (provider, '', 80)

Runningrepparttar 133399 queries:  DBCC SHOWCONTIG (member) WITH ALL_INDEXES  DBCC SHOWCONTIG (provider) WITH ALL_INDEXES

DBCC SHOWCONTIG scanning 'member' table... Table: 'member' (786101841); index ID: 2, database ID: 14 LEAF level scan performed. - Pages Scanned................................: 41 - Extents Scanned..............................: 6 - Extent Switches..............................: 5 - Avg. Pages per Extent........................: 6.8 - Scan Density [Best Count:Actual Count].......: 100.00% [6:6] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 1657.0 - Avg. Page Density (full).....................: 79.53% DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'provider' table... Table: 'provider' (850102069); index ID: 2, database ID: 14 LEAF level scan performed. - Pages Scanned................................: 2 - Extents Scanned..............................: 2 - Extent Switches..............................: 1 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 50.00% [1:2] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 4346.0 - Avg. Page Density (full).....................: 46.31% DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As a result of runningrepparttar 133400 DBCC SHOWCONTIG and DBCC INDEXDEFRAG commands, we were able to diagnose and greatly reduce fragmentation onrepparttar 133401 'member' and 'provider' tables. The member table is almost perfect andrepparttar 133402 'provider' table shows great improvement. This will result in an extraordinary performance increase on queries that are run against these tables.

*For more information on fragmentation, visit this link: www.sql-server-performance.com/

Desiree Harris is a support specialist with ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.

Desiree Harris is a support specialist with ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.


RichDataPicker

Written by Ben Higgins


Continued from page 1

In your code behind page or between your script tags addrepparttar following code. If you are using inline ASP.NET code not Visual Studio .NET then you will need to remove "Handles button1.click".

Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click

Calendar1.SelectedDate = RDP1.SelectedDate

End Sub

In this example,repparttar 133385 DP.RichDatePicker tag by default creates a text box with today’s date in it. Next torepparttar 133386 text box is a browse button. Once you select a date by clicking onrepparttar 133387 browse button and submitting it,repparttar 133388 date you selected will be hi-lighted onrepparttar 133389 ASP.NET Calendar.

For a complete list of featuresrepparttar 133390 RichDatePicker component offers and an evaluation copy, go to www.richercomponents.com

Ben Higgins is a Internet Support Specialist with ORCS Web, Inc. Webteam - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.

Ben Higgins is a Internet Support Specialist with ORCS Web, Inc. Webteam - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.


    <Back to Page 1
 
ImproveHomeLife.com © 2005
Terms of Use