Key Points Affecting SQL Server Performance

First, the design of logical databases and tables

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

Standardized database logic design involves replacing long columns of many columns with a large, interrelated, narrow table. Here are some of the benefits of using a standardized table.

A: Because of the narrow table, you can make sorting and indexing faster.

B: Due to the multi-table, so many indexes are possible.

C: A narrower and more compact index.

D: There can be fewer indexes in each table, so you can improve the speed of insert update delete, etc., because these operations will have a great impact on system performance in the case of many indexes.

E: Fewer null values ​​and fewer extra values, increasing the compactness of the database. Because of standardization, it increases the complexity of the number of reference tables and the connection relationship between them when acquiring data. Too many tables and complex connections can degrade the performance of the server, so a comprehensive consideration needs to be made between the two.

The main things to note when defining primary and foreign keys with correlations are: the primary key used to join multiple tables and the referenced key must have the same data type.

Second, the design of the index

A: Try to avoid the table scan

Check the where clause of your query, because this is the important focus of the optimizer. Each column contained in where is a possible candidate index. To achieve optimal performance, consider the example given below: for the column column1 given in the where clause.

The following two conditions can improve the performance of the index's optimized query!

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 column of the first index.

Avoid defining multiple indexes and column1 is the second or later index. Such an index cannot 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'

Indexes built on the following columns will be useful indexes for the optimizer

au_lname

au_lname, au_fname

Indexes built on the following columns will not work well for the optimizer

au_address

au_fname, au_lname

Consider using a narrow index on one or two columns. A narrow index is more efficient than a multi-index and composite index. With a narrow index, there will be more rows and fewer index levels on each page (as opposed to multiple indexes and composite indexes), which will drive system performance. For multi-column indexes, SQL Server maintains a density statistic (for unions) on all column indexes and a histogram (histogram) statistic on the first index. According to the statistics, if the first index on the composite index is rarely used, the optimizer will not use the index for many query requests.

Useful indexes will improve the performance of select statements, including insert, uodate, delete. However, changing the contents of a table will affect the index. Each insert, update, and delete statement will degrade performance. Experiments have shown that you should not use a large number of indexes on a single table, and do not use overlapping indexes on shared columns (meaning that reference constraints are used in multiple tables).

Check the number of unique data on a column and compare it to the number of rows in the table. This is the selectivity of the data, and the result of this comparison will help you decide whether to use a column as a candidate index, and if so, which index to build. You can use the following query to return the number of different values ​​in a column.

select count(distinct cloumn_name) from table_name

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

Unique values ​​Index

5000 Nonclustered index

20 Clustered index

3 No index

镞 Index and non-镞 index selection

<1> The index is the physical order of the rows and the order of the indexes is the same. The actual data pages are included at each level of the page level, lower level, etc. index. A table can only have one index. Because of the update, the delete statement requires a relatively large number of read operations, so the index can often speed up such operations. In a table with at least one index, you should have an index.

In the following cases, you can consider using the index:

For example: The number of different values ​​included in a column is limited (but not very rare)

The state list of the customer table lists the abbreviated values ​​of about 50 different state names. You can use the 镞 index.

For example: You can use a 镞 index for columns that return a range of values, such as columns that operate on columns with between, >, >=, <, <= and so on.

select * from sales where ord_date between '5/1/93' and '6/1/93'

For example: You can use a 镞 index for columns that return a large number of results when querying.

SELECT * FROM phonebook WHERE last_name = 'Smith'

When there are a large number of rows being inserted into the table, avoid a column that grows naturally in this table (for example, the identity column) Create an index on it. If you create a flawed index, the performance of the insert will be greatly reduced. Because each inserted row must go to the last page of the table, the last data page of the table.

When a piece of data is being inserted (when this page is locked), all other inserted lines must wait until the current insertion has ended.

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

<2> A non-镞 index is that the physical order of the rows is different from the order of the indexes. A non-镞 indexed leaf level contains pointers to row data pages.

There can be multiple non-镞 indexes in a table, and you can consider using non-镞 indexes in the following cases.

on many different values ​​in a column index can be considered a non-arrowhead

For example: a part_id listed in a part table

select * From employee where emp_id = 'pcm9809f'

You can consider using a 镞 index on a column with an order by clause in a query statement.

Third, the design of the query statement

SQL Server optimizer through the analysis of query statements, automatically optimize the query and determine the most effective implementation. The optimizer analyzes the query to determine which clause can be optimized and selects a useful index for the clauses that can be optimized for the query. Finally the optimizer compares all possible execution scenarios and chooses the most efficient one.

When executing a query, use a where clause to limit the number of rows that must be processed. Unless you need it, you should avoid reading and processing all rows indefinitely in a single table.

For example, the following example,

select qty from sales where stor_id=7131

is very efficient 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 a function that satisfies its purpose to limit the size of the result set.

This reduces network I/O and improves application performance for multi-user related concurrency. Because the focus of the optimizer is the query of the where clause to take advantage of the useful index. Each index in the table may become a candidate index included in the where clause. For best performance, follow the index below 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 index of the column, not in the where sub Use a query with no column1 column index in the sentence, and avoid indexing the non-first index with a multi-index in the where clause.

At this point, multiple indexes are useless.

For example, given a multicolumn index on the au_lname,

au_fname columns of the authors table in

the pubs database,

The following query statement Take advantage of the index on 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 utilize an index because it uses a multi-indexed index of a non-first index

SELECT au_id, au_lname, au_fname FROM authors

WHERE au_fname = 'Johnson'