SQL Server index illustrates series five index parameters and fragmentation

--Create a clustered indexCreate Table [dbo].[pub_stocktest] Add  constraint [pk_pub_stocktest] Primary Key Clustered ([Sid] ASC) with(Pad_index= off, Statistics_norecompute= off, sort_in_tempdb= off, Ignore_dup_key= off,
Online= off, Allow_row_locks=  on, Allow_page_locks=  on) on [Primary]--To create a nonclustered index Create nonclustered Index [Ix_model]  on [dbo].[pub_stocktest](    [Model] ASC) include ([name]) with(Pad_index= off, Statistics_norecompute= off, sort_in_tempdb= off, drop_existing= off,
Online= off, Allow_row_locks=  on, Allow_page_locks= On , FILLFACTOR =) on [Primary]

1.1 Filefactor Parameters

You can use Filefactor to reserve some space for each leaf paging store in the index. For a clustered index, the leaf level contains the data, using Filefactor to control the table's reserved space, through reserved space, to avoid the new data in order to insert, the need to free up the vacancy for page separation.
Filefactor setting effective note that the reserved space size is determined only when the index is created based on the data that already exists, such as the need to alter INDEX to rebuild the indexes and reset the previously specified filefactor values.
When you create an index, if you do not specify Filefactor, the default value of 0 is filled, and the global instance can be configured by sp_configure. Filefactor is also used only on leaf-level pagination. If you want to control index paging at the intermediate level, you can do so by specifying the pad_index option. This selection notifies all hierarchies on the index to use the same filefactor. PAD_INDEX also only indexes are useful when new or rebuilt.

1.2 Drop_existing parameters

Deletes or rebuilds a specified index as a single transaction to handle. This item is particularly useful when rebuilding a clustered index, and when a clustered index is deleted, SQL Server rebuilds each nonclustered index to change the bookmark from the clustered index key to rid. If you re-create or rebuild the clustered index again, SQL Server rebuilds the entire nonclustered index again, and if the new or rebuilt clustered index key value is the same, you can set the Drop_existing=on.

1.3 Ignore_dup_key

means that if an UPDATE or INSERT statement affects multiple rows of data, but a row of keys is found to produce a heavy value, the entire statement is rolled back, and when a duplicate key value is generated at Ignore_dup_key=on, the entire statement is not rolled back, and the duplicate rows are discarded and other rows are inserted or updated.

1.4 Statistics_norecompute

option determines whether statistics on indexes need to be automatically updated, each index maintains a histogram of the numerical distribution of the first field in the index, and the query optimizer uses these statistics to determine the validity of a particular index when it queries the execution plan. When the data reaches a threshold, the statistic value changes. The Statistics_norecompute option allows an associated index to not automatically update statistical values when data is modified. The selection overrides the on value of the auto_update_statistics.

1.5 ONLINE

Value default off, whether the underlying table and associated indexes are available for query and data modification operations during an index operation.
When the value is on, the underlying table and index can continue to be queried or updated, but for a short time to get the SCH_M schema modification lock, you must wait for all blocked transactions on this table to complete, and this lock will block all other transactions during the operation.
When the value is off, a shared lock can be obtained, preventing the underlying table from being updated, but allowing read operations .

1.6 MAXDOP

Overriding the max degree of parallelism instance configuration during the index operation, the default value is 0, and the actual number of processors is used based on the current system workload.

1.7 inclusive column (included columns)
The containing column appears only in the leaf level, does not control the order of the index rows, it does so that the leaf level contains more information to override the index's tuning ability, the overlay index appears only in the nonclustered index, and at the leaf level, all the information that satisfies the query is found.

1.8 on [primary]

The last clause of CREATE INDEX when creating indexes allows the user to specify where the index is placed. You can specify a specific filegroup or predefined partitioning scheme. The default storage is the same as the table filegroup, which is typically in the primary filegroup.

1.9 Constraints and indexes

When we create a primary key or uniqueness constraint, a unique index is created, and the index name that is created to support the constraint is the same as the constraint name.
Constraints are a logical concept, and indexes are a physical concept, and indexing is actually creating a physical structure that takes up storage space and must be maintained in data modification operations.
There is no difference between creating a constraint on the internal structure of an index or the selection of an optimizer.

Two-index fragmentation

2.1 Showcontig

--    SQL Server 2000 uses SHOWCONTIG to view index fragmentation (OBSOLETE)DBCC showcontig (tablename,'indexname  '

For example, the following query for a Ix_stockmodel index under a pub_stockcollect table

(1) Page scanned-Scan pages: If you know the approximate size of a row and the number of rows in a table or index, you can estimate how many pages are in the index. Look at the number of pages scanned, if it is significantly higher than the number of pages you estimate, there is internal fragmentation.

(2) Extents scanned-scan extents: Divide the number of scanned pages by 8, rounding to the next highest value. The value should match the number of scan extents returned by DBCC SHOWCONTIG. If DBCC SHOWCONTIG returns a high number, it indicates that there is an external fragment. The severity of the fragment depends on how much higher the value you just showed than the estimated value.

(3) Extent switches-Expansion Switch Number: This number should be equal to the number of scan extents minus 1. High, the external fragments are indicated.

(4) Avg. pages per extent-average pages on each extents: this is the number of scanned pages divided by the number of extents scanned, typically 8. Less than 8 indicates an external fragment.

(5) Scan Density [best count:actual count]-scanning density [optimal value: Actual value]:DBCC Showcontig returns the most useful percentage. This is the ratio of the best and actual values of the extents. The percentage should be as close to 100% as possible. Low, it means that there are external fragments.

(6) Logical Scan fragmentation-Logical Scan Fragmentation: Percentage of unordered pages. The percentage should be between 0% and 10%, and high will indicate an external fragment.

(7) Extent Scan fragmentation-Extents scanning fragment: the percentage of the unordered extents in the scan index leaf level page. The percentage should be 0%, and high will indicate an external fragment.

(8) Avg. Bytes free per page-The average number of bytes available on each page: the average number of bytes available on the scanned page. The higher is the internal fragment, but you should consider fill factor (fill factor) before you use this number to determine if there are internal fragments.

(9) Avg. page Density (full)-average page density (complete): the inverse of the average percentage of bytes available on each page. A low percentage indicates an internal fragment.

　Summary: (1) Logical scan fragment: the lower the Better (2) The average page density: 80% or so best, below the%60 rebuild index, (3) The best count differs from the actual count by a large reconstructed index.

SQL Server index illustrates series five index parameters and fragmentation