Key factors affecting SQL server performance

1. logical database and Table Design
The logical design of databases, including the relationship between 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 includes replacing long data tables with multiple and correlated narrow tables. The following are some benefits of using standardized tables.

A: Because tables are narrow, sorting and indexing can be performed more quickly.
B: Because there are multiple tables, it is possible to have multiple indexes.
C: narrower and more compact Indexes
D: Each table can have fewer indexes, so it can improve the speed of insert update Delete and so on, because these operations will have a great impact on system performance when there are many indexes.
E: fewer null values and fewer redundant values increase the database's closeness. Due to standardization, it increases the complexity of the number of referenced tables and the connection relationships during data acquisition. Too many tables and complex connections can reduce the server performance. Therefore, you need to consider both of them comprehensively.
When defining primary keys and Foreign keys with relevant relationships, note that the primary keys used to connect multiple tables must have the same data type as the referenced keys.

2 Index Design
A: Try to avoid table scanning.
Check the WHERE clause of your query statement, because this is an important concern of the optimizer. Each column in The WHERE clause is a possible candidate index. To achieve optimal performance, consider the following example: column1 is given in the WHERE clause.
The following two conditions can improve the query performance of indexes!
First, there is a single index in the column1 column of the table.
Second, there are multiple indexes in the table, but column1 is the first index column.
Avoid defining Multiple indexes while column1 is the second or later index. Such indexes 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 created on the following columns will be useful to the optimizer.
Au_lname
Au_lname, au_fname
The indexes created in the following columns will not play a good role in the optimizer.
Au_address
Au_fname, au_lname
Consider using narrow indexes on one or two columns. Narrow indexes are more effective than multi-index and composite indexes. Use a narrow index on each page
There will be more rows and fewer index levels (relative to Multiple indexes and composite indexes), which will promote system performance.
For multi-column indexes, SQL Server maintains a density statistics (used for Union) on the indexes of all columns and
Histogram (column chart) statistics. According to the statistical results, if the first index on the composite index is rarely used, the optimizer will not use the index for many query requests.
Useful indexes can improve the performance of select statements, including insert, uodate, and delete.
However, changing the content of a table will affect the index. Each insert, update, and delete statement may degrade the performance. Experiments show that you should not use a large number of indexes on a single table, or use overlapping indexes on shared columns (reference constraints used in multiple tables.
Check the number of unique data in a column and compare it with the number of data rows in the table. This is the data selectivity. This comparison result will help you decide whether to use a column as the selected index column. If necessary, you can create an index. You can use the following query statement to return the number of different values in a column.
Select count (distinct cloumn_name) from table_name
Suppose column_name is a 10000-row table, then the returned values of column_name are used to determine whether to use and what indexes should be used.
Unique values Index

5000 nonclustered Index
20 clustered Index
3 No Index

Selection of secondary indexes and non-secondary Indexes

<1:> the physical order of rows is the same as that of indexes. Page-level and low-level indexes all contain actual data pages. A table can have only one distinct index. Because the update and delete statements require a relatively large number of read operations, secondary indexes can often accelerate such operations. In a table with at least one index, you should have a secondary index.
In the following situations, you can consider using the explain index:
For example, the number of different values in a column is limited (but not very small)
The State names in the customer table are about 50 abbreviated values of different States. You can use the region index.
For example, you can use partial indexes for columns that return values within a certain range, for example, using between,>, >=, <, <= to operate on columns.
Select * from sales where ord_date between '2014/1/93 'and '2014/3/93'
For example, you can use the suffix index for columns that return a large number of results during a query.
Select * From phonebook where last_name = 'Smith'

When a large number of rows are being inserted into the table, avoid creating a secondary index on a column with a natural growth (for example, the identity column) in the table. If you have created an explain index, the insert performance will be greatly reduced. Because each inserted row must be at the end of the table and the last data page of the table.
When a data entry is being inserted (this data page is locked), all other inserted rows must wait until the current insertion ends.
The leaf-level page of an index contains the actual data pages, and the order of the data pages on the hard disk is the same as the logic order of the secondary index.

<2:> A non-distinct index means that the physical order of rows is different from the index order. The leaf level of a non-linear index contains a pointer to the row data page.
A table can have multiple non-secondary indexes. You can consider using non-secondary indexes in the following situations.
You can consider using non-linear indexes for columns with many different values.
For example, a part_id is listed in a part table.
Select * from employee where emp_id = 'pcm9809f'
You can use the limit index for the columns of the order by clause in a query statement.

3. query statement Design

By analyzing query statements, the SQL Server optimizer automatically optimizes the query and determines the most effective execution plan. The optimizer analyzes the query statement to determine which clause can be optimized, and selects useful indexes for clauses that can be optimized. Finally, the optimizer compares all possible execution plans 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. Unless necessary, you should avoid unlimited read and processing of all rows in a table.
For example,
Select qty from sales where stor_id = 7131
It is very effective than the unrestricted query below
Select qty from sales
Avoid returning a large number of result sets for the customer's final data. It is more effective to allow SQL Server to run functions that meet its purpose to limit the size of the result set.
This reduces network I/O and improves application performance when multiple users have concurrent connections. The optimizer focuses on the query of the WHERE clause to use useful indexes. Each index in a table may become a candidate index included in the where clause. For the best performance, you can follow the following index for a given column column1.
First, there is a single index in the column1 column of the table.
Second, there are multiple indexes in the table, but column1 is the first index column. Do not use a query statement without the column1 column index in the WHERE clause, avoid using a non-first index with Multiple indexes in the WHERE clause.
In this case, 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 uses 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 use an index because it uses a non-first index with Multiple indexes.
Select au_id, au_lname, au_fname from authors
Where au_fname = 'johnson'