SQL Server Index knowledge-application, maintenance

Create a clustered index

A the index key is best unique (if not the only one will be built uniquier column (4 bytes) to ensure the uniqueness, that is, this column will be copied to all nonclustered indexes)

b The clustered index column should be as small as possible (otherwise, the space for the nonclustered index will become larger)

C Clustered index should be fixed, can not be arbitrarily changed (otherwise it will cause paging, fragmentation, nonclustered index forced modification, such as some column problems)

d The clustered index key is generally separate from the primary key (primary key) (the primary key can be a clustered index based on convenient business tuning, such as the business logic store data that is consistent with the clustered index key and does not change)

Application Examples:

When selecting a clustered key, try to avoid paging caused by insertion

The time column alone is not a good clustered index, because it is not unique, but can be combined for example (Date+int)

Index (16byte) to use Newsequentialid () without NEWID (), because newid () random, will cause fragmentation paging, etc.

To create a nonclustered index

To create an index option:

FILLFACTOR---Fill factor to preserve the page space ratio (avoid paging, set according to index entry properties, if splitting is unavoidable, high ratio values are recommended)

Pad_index---with fillfactor connection, preserving free space on intermediate nodes

Gnore_dup_key---Discard duplicate key values without rolling back the entire statement when there is a unique (clustered/nonclustered) constraint

Drop_existing--Avoid clustered indexes when rebuilding a clustered index two rebuild (nonclustered index line environment should use to delete existing indexes after creating new indexes to minimize index maintenance impact)

SORT_IN_TEMPDB---Create (rebuild) index use the SORT_IN_TEMPDB option to sort key values in tempdb to reduce the source database requirement space, and if tempdb and all operations databases are on separate disks, performance can be improved

Statistics_norecompute---whether statistics on indexes are automatically updated

MAXDOP---Control the maximum number of processors that can be used for new indexes (data skew can cause CPU waste, and bottlenecks typically occur on disk when created. Tens data MAXDOP recommendation 4)

Online---whether to create indexes on-line (for index maintenance, to minimize impact, generally set to ON)

ON filegroup---Select a filegroup for the index store. (The maintenance plan type is specified, depending on the index type.)

ASC/DESC---index collation

Indexed views

Check

SELECT objectproperty (object_id (' product_totals '), ' IsIndexable ');---see if the view can be indexed

SELECT objectproperty (object_id (' Vdiscount1 '), ' isindexed ');----See if the view has an index created

Conditions

The ANSI_NULLS and QUOTED_IDENTIFIER options must be set to ON

Must use SCHEMABINDING

Views cannot reference any other views, only base tables can be referenced

All base tables referenced by the view must be in the same database as the view, and the owner is also the same as the view

All functions referenced by an expression in a view must be deterministic (computed columns, which can be confirmed using isdeterministic)

Attention:

You must first create a unique clustered index on an indexed view

Even if an indexed view is created, the optimizer does not necessarily use the index of the indexed view when the query is made, possibly directly indexed by the table in the index definition

Recommendation: Indexed views cause the source table to expand the size of the lock when it is DML, causing potential blocking and even deadlock problems. Not recommended in frequently updated business.

Index Best Practices

Choosing a clustered index is a good idea when creating a table (the cost is very high when a heap table of huge data becomes a clustered table)

Nonclustered index selection, high-selectivity columns (index density), and frequent access predicates are selected for creation.

The first column of a composite nonclustered index should be selected high.

Note Composite index key column collation (based on actual business logic)

Use good index containment columns (overwrite queries to avoid possible random IO)

Number of nonclustered indexes (balanced query versus DML operation consumption)

Filtered indexes are used (special data structures are used. such as a large number of null values in the data, etc.)

Nonclustered index Inclusion column shows the addition of a clustered index (does not consume extra space if you include a clustered predicate when using a nonclustered index to avoid key lookups)

Use the missing index related view sys.dm_db_missing_index_xx (need to locate the specific query specific analysis, System scale optimization, as appropriate, set rules for batch creation)

index maintenance.

Create an index.

Check whether the runtime environment is suitable for creation before creation (low load, long time consuming corresponding lock resource causing blocking creation process)

Online environment should be created on-line

Replace/Resize index with CREATE, delete step

Determine if a rebuild is required based on a specific scenario, reorganize the index (fragmentation affects area scan, pre-read.) The re-organization of indexes is not helpful for read-ahead. Sys.dm_db_index_physical_stats Retrieving index fragmentation

Deletes an index.

According to the DMV (sys.dm_db_index_usage_stats, etc.) to establish rules to determine whether the index is useless. (Rule order: Whether the index is useless, whether the index is repeated, whether it is reasonably indexed, whether it can be merged)

Marking a useless index should be disabled for some time. (fully confirm the situation can be deleted immediately)

Before deleting, verify that there is a long time consuming the appropriate lock resource causing the blocking removal process.