Key three aspects that affect SQL Server performance

The logical design of logical database and table design database, including the relationship between table and table, 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 empty values and fewer redundant values, increasing the compactness of the database

Because 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.

Second, the design of the index

A: Avoid table scans to 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: Multiple indexes in the table, but Column1 is the first indexed column avoids 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 index established on the following columns will be useful for the optimizer

au_lname

au_lname, au_fname The indexes 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 federation) on all the columns ' indexes, and on the first index

Histogram (columnar chart) statistics. 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 assumes that COLUMN_NAME is a 10000-row table, see the column_name return value to determine whether it should be used, and what indexes should be used.

Unique values Index
5000 Nonclustered index
20 Clustered index
3 No index