design keys that affect SQL Server performance

server| Design | performance

1 logical database and table design

The logical design of database, including the relationship between table and tables, is the core of optimizing the performance of relational database. A good logical database design can lay a good foundation for optimizing databases and applications.

The standardized database logic design involves the use of multiple, interconnected narrow tables to replace long data tables for many columns. Here are some of the benefits of using standardized tables.

A: Because of the narrow table, you can make sorting and indexing more rapid

B: Multiple arrowheads indexes are possible due to multiple tables

C: Narrower and more compact index

D: You can have fewer indexes per table, so you can increase the speed of Insert update Delete, because these operations can have a significant impact on system performance in the case of multiple indexes

E: Fewer nulls and fewer redundant values increase the compactness of the database as a result of standardization, it increases the complexity of referencing the number of tables and the connections between them when fetching data. Too many tables and complex connections can degrade the performance of the server, so there is a need to take a holistic view between the two.

The main thing to be aware of when defining primary and foreign keys that have related relationships is that the key that is used to connect multiple tables and references must have the same data type.

2 Design of the index

A: Avoid table scans as much as possible

Check the WHERE clause of your query statement, as this is where the optimizer pays attention. Each column included in the Where (column) is a possible candidate index, and for optimal performance, consider the example given below: The column given column1 in the WHERE clause.

The following two conditions can improve the optimization query performance of the index!

First: There is a single index on the Column1 column in the table

Second: There are multiple indexes in the table, but Column1 is the first indexed column

Avoid defining multiple indexes and Column1 is the second or subsequent index, such indexes do not optimize server performance

For example, the following example uses the pubs database.

SELECT au_id, au_lname, au_fname from authors

WHERE au_lname = ' White '

The indexes established on the following columns will be useful indexes for the optimizer

? au_lname

? au_lname, au_fname

The indexes that are built on the following columns will not work well for the optimizer

? au_address

? au_fname, au_lname

Consider using narrow indexes on one or two columns, narrow indexes are more efficient than multiple indexes and composite indexes. With a narrow index, there will be more rows and fewer index levels on each page (relative to multiple indexes and composite indexes), which will drive system performance.

For a multiple-column index, SQL Server maintains a density statistic (for syndication) on all the columns ' indexes and a histogram (histogram) statistic on the first index. According to statistical results, if the first index on a composite index is rarely selected, the optimizer will not use the index for many query requests.

Useful indexes improve the performance of the SELECT statement, including Insert,uodate,delete.

However, changing the contents of one table will affect the index. Each INSERT,UPDATE,DELETE statement will degrade performance. Experiments have shown that you do not use a large number of indexes on a single table, and do not share overlapping indexes on shared columns (referring to reference constraints in multiple tables).

Check the number of unique data on a column, comparing it with the number of rows in the table. This is the selectivity of the data, and the results will help you decide whether to use a column as a candidate for the indexed column, and if so, which index to build. You can use the following query statement to return the number of different values for a column.

Select COUNT (Distinct cloumn_name) from table_name

Assuming column_name is a 10000-row table, look at the column_name return value to determine whether it should be used and what indexes should be used.

Unique Values Index

5000 Nonclustered index

Clustered Index

3 No Index

Selection of arrowheads index and non-arrowheads index

<1:> arrowheads Index is the physical order of the rows and the order of the indexes is consistent. Page-level, low-level, and so on, all levels of the index contain the actual data page. A table can have only one arrowheads index. Because the Update,delete statement requires a relatively few read operations, arrowheads indexing can often speed up such operations. In a table with at least one index, you should have a arrowheads index.

In the following few cases, you may consider using the arrowheads index:

For example, the number of different values included in a column is limited (but not very small)

The State of the Customer table has an abbreviated value of 50 or so different state names, and you can use the arrowheads index.

For example, you can use a arrowheads index for a column that returns a range of values, such as a column that operates on a column with between,>,>=,<,<= and so on.

SELECT * FROM sales where ord_date between ' 5/1/93 ' and ' 6/1/93 '

For example, a column that returns a large number of results on a query can use the arrowheads index.

SELECT * from phonebook WHERE last_name = ' Smith '

When a large number of rows are being inserted into a table, avoid establishing a arrowheads index on a column that is naturally growing (for example, the identity column) in this table. If you build a arrowheads index, the performance of the insert will be greatly reduced. Because each inserted row must go to the end of the table, the last data page of the table.

When a data is being inserted (at which point the data page is locked), all other inserted rows must wait until the current insertion has ended.

The leaf page of an index includes the actual data page, and the order of the data pages on the hard disk is the same as the logical order of the arrowheads index.

<2:> a arrowheads index is that the physical order of the rows differs from the order of the indexes. The leaf level of a arrowheads index contains a pointer to the row data page.

You can have more than one arrowheads index in a table, and you can consider using a arrowheads index in the following situations.

You can consider using a arrowheads index on a column that has many different values

For example: A part_id is listed in a part table

SELECT * FROM employee WHERE emp_id = ' pcm9809f '

You can consider using the arrowheads index on a column of the ORDER BY clause in a query statement

3 Design of query statement

The SQL Server optimizer automatically optimizes queries and determines the most effective execution scenarios by analyzing query statements. The optimizer analyzes the query to determine that the clause can be optimized and selects useful indexes for the clauses that can be optimized for queries. Finally, the optimizer compares all possible execution scenarios and selects the most effective one.

When executing a query, a WHERE clause is used to limit the number of rows that must be processed, and you should avoid reading and processing all rows in one table indefinitely unless you fully need them.

For example, the following example,

Select qty From Sales where stor_id=7131

is very effective than the following unlimited query

Select Qty From Sales

Avoid returning a large number of result sets to the customer's final data selection. It is more efficient to allow SQL Server to run functions that satisfy its purpose to limit the size of the result set.

This reduces network I/O and increases the application performance of multiple user-related concurrency. Because the optimizer focuses on the query of the WHERE clause to take advantage of useful indexes. Each index in a table can become a candidate index that is included in the WHERE clause. For best performance, you can follow the following index for a given column column1.

First: There is a single index on the Column1 column in the table

Second: There are multiple indexes in the table, but Column1 is the first indexed column do not use a query statement that does not have a column1 column index in the WHERE clause, and avoid indexing with a multiple indexed non-first index in the WHERE clause.

Multiple indexes are not used at this time.

For example, given a multicolumn index on the au_lname, au_fname columns to the authors table in

The pubs database,

The following query statement leverages the index on the au_lname

SELECT au_id, au_lname, au_fname from authors

WHERE au_lname = ' White '

and au_fname = ' Johnson '

SELECT au_id, au_lname, au_fname from authors

WHERE au_lname = ' White '

The following query does not take advantage of the index because he uses indexes that are not the first index of multiple indexes

SELECT au_id, au_lname, au_fname from authors

WHERE au_fname = ' Johnson '