sp_password 'currentPSWD', 'newPSWD'

Be sure to use a strong password, "newPSWD" is just an example. I always like to test any change like this right away. For a quick test to confirm that new password is working, close Query Analyzer and then re-connect with new password. That is all there is to it, except for changing your connections string. It is obvious, but still critical to remember to change password in your connection string if this SQL Username is used in your code. Once password has been changed and tested with Query Analyzer, update connection string with new password and test that e verything still works correctly, and you're done.

David Weber 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.

Let's observe an example:

Running 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.

Running 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 of index pages can be determined by reading "Avg. Bytes free per page" and "Avg. Page density (full)" statistics. The "Avg. Bytes free per page" figure should be low and "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 comparing 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 on examples above, this is 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 and provider table is slightly fragmented based on numbers above.

These issues can be corrected by dropping and recreating a clustered index with FILLFACTOR option specified. Also, DBCC INDEXDEFRAG command will compact an index, taking into account its FILLFACTOR, which will improve statistics.

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

Running 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 running DBCC SHOWCONTIG and DBCC INDEXDEFRAG commands, we were able to diagnose and greatly reduce fragmentation on 'member' and 'provider' tables. The member table is almost perfect and '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.