SQL Server Introduction Order: Level 15th, index best Practices

In this article we will recommend 14 rules throughout this series that help you create the best index structure for your database.

The format is derived from the Framework Design guidelines. Each recommendation is summed up in four words: do, consider consider, void to avoid, do not don't do.

• Do. Always obey the rules.
• Consider. Usually it should be followed, but if you fully understand the reasons behind the rules and why you do not follow them.
• Avoid. Contrary to consideration, it is generally advisable not to do so, but if you fully understand why you should not do so, you have the reason to do so, you can do so.
• Don't do it. Better than avoiding the mood, some things on the surface never do.

Guiding Principles

Do know your application/users

The primary purpose of the index is to improve the performance of query and operational data, unless you know what these actions are, or you don't want to improve them.

It is best to consider it at the beginning of the application and add it in design and development. If you inherit an existing database and application, understand in two ways what you inherit: internal and external.

External include from the user perspective, chat with them, watch them use apps, read user documentation and manuals, view current tables and reports.

The internal includes checking the application itself, the definition of the application, and the execution of the application. Use tools, activity monitor,profiler,sys.dm_db_index usage_stats Dynamic View, and Sys.dm_db_missing_index_xxx series of dynamic views to observe common queries, slow query, Commonly used indexes, unused indexes, and indexes that should exist but are not established.

Check the source of common queries and slow queries, such as templates for Reporting Services. The steps of the TSQL job, the TSQL task in the SSIS app, the stored procedure, should all be optimized.

After understanding this information, for those indexes are good, those indexes are not good, you can make a better decision.

Do not over Index

Too many indexes and too few are bad. There is no "optimal number of indexes" for a table. The case for each table is different. But if you want to index the primary key, candidate key, appropriate foreign key, potential query column, do some analysis before building.

Do understand That:same Database + Different situation = Different indexing

Whether it's during the day or off-peak processing, whether it's on-line processing or report processing of a database copy, the indexes are different in different cases.

Do has a Primary Key on every Table

Although the primary key is not required by SQL Server, a table without a primary key is very risky in a transaction because the row is not guaranteed to be unique. If duplicate rows are allowed, you do not know whether the same entity was repeatedly inserted two times, or if there is not enough information to differentiate between the two entities.

Although SQL Server does not require, the primary key is the basis of the relational theory, the basic composition of all relational systems. A constraint without a primary key, or a unique index, can result in unexpected results or poor performance.

In addition, many client development tools and components require that your table have a primary key. The name of the primary KEY constraint is the name of the index.

Consider has a Clustered Index on every Table

At the third level, the clustered index describes the benefits of a clustered index. Make the table a clustered index table instead of a heap table. The main benefit is a simple fact that the user will certainly be in a default order when viewing the table data, so in which order to maintain the table.

If you follow the rules in this article, each table has a primary key, and each table has at least one index, or even more. Therefore, a clustered index will not increase the number of indexes, but it will give your table a good structure compared to the heap table.

When deciding on a clustered index column, remember the sixth level, the guidelines in the tag: a clustered index should be unique, short, and unchanging.

Consider Using a Foreign key in the Search key of the Clustered Index

Consider the foreign key as the leftmost column in the clustered index key, so that the information of the child item can be aggregated around the parent, which is a typical requirement. Your credit card's consumption information is associated with the credit card, my consumption information and my card. The relationship is stronger than that of consumer records and businesses, or financial institutions that consume records and process consumption records. The card number is the foreign key that is contained in the clustered index key of the consumer table, not the merchant number and the bank number. Place the card number on the leftmost side of the clustered index, and the same cardholder's consumption record will be aggregated on the same page.

Consider has Included Columns in your Indexes

Consider adding include columns in your nonclustered index.

Because a generic nonclustered index looks at a table from a certain angle, or is based on a foreign key that is established, but in addition to the key columns of a nonclustered index, some other columns are required, but these columns are not used as query criteria, but they need to be displayed or counted, and these columns can be added as included columns. You do not have to access the data row, you can complete the request directly in the nonclustered index.

Avoid nonclustered, Unfilterd Indexes on Columns that has few Distinct Values

There is an old saying: "Do not index on the sex column." A page in the table will be half the value of a male, half the value of a woman, whether the request is male or female, the scan table is the best decision. Therefore, such an index is never used by the query optimizer.

Consider Create a Filtered Index for Columns that has a dominate Value

If there is a column in the table, the values for most rows are the same, or both are null, then a filtered index is created on this column. When you query a small number of values, the index is used, and the table is scanned when most values are queried.

Consider specifying Fill Factor Values that anticipate future Size Requirements

Consider specifying Fill Factor Values that Reflect the Table ' s steady-state Page fragmentation Value

Do Create a Table ' s Clustered Index before Creating its nonclustered Indexes

One corollary of this rule is that a nonclustered index is deleted before the clustered index is deleted. Doing so causes an unnecessary rebuild of the nonclustered index. The conversion of a table from a heap table to a clustered index table always results in the rebuilding of a nonclustered index, because the content of a nonclustered indexed bookmark changes from the row number to the key of the clustered index.

Do Plan Your Index defragmenting and rebuilding Based upon Usage

If an index is frequently scanned, the external fragmentation of the index is important, which can have an important effect on full scan or scanning part of the leaf layer. If this is the case, consider re-organizing the index when the external fragment reaches 10%, and consider rebuilding the index when it reaches 30%.

However, if the index is queried only by a key, the impact of external fragmentation on performance is minimal or even unaffected. The IO required from the root page to one page of the leaf layer will ignore the external fragments and will be the same. At this point, re-organizing and rebuilding the index does not improve performance.

Do Update Index Statistics on a Regular Basis

The keyword is "regular" because you only know what your app is doing and you can decide when the stats need to be updated. There is an introduction to this section in level 14th.

Conclusion

These guidelines come from a lot of developers who have worked on SQL Server for many years, and according to these guidelines, you can create the best indexes on your database.

SQL Server Introduction Order: Level 15th, index best Practices