Key to affecting SQL Server database performance design

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 optimize databases and applicationsProgramLay a good foundation. 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 the 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. With narrow indexes, there will be more rows and fewer index levels on each page (relative to multi-index and composite index), which will promote system performance. For multi-column indexes, SQL Server maintains the density statistics (used for Union) on the indexes of all columns and the histogram (column chart) Statistics on the first index. 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'