Factors Affecting SQL server performance and Solutions

1> Computer Hardware
If you suspect that computer hardware is the main cause of affecting SQL server performance, you can monitor the load of the corresponding hardware through SQL server performance monitor to confirm your guesses and identify system bottlenecks.

2> Operating System

3> SQL Server ApplicationsProgram

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. Below are some usage Standardization
Table benefits:

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, the complexity of the number of referenced tables and the connection relationship between them increases when data is obtained. 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

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!
1: There is a single index in the column1 column of the table
2: 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
considering using narrow indexes on one or two columns, narrow indexes are more effective than multi-index 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 ), this will promote system performance.
for multi-column indexes, SQL Server maintains a density statistics (used for Union) on all column indexes and
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.

You can also use the following tools:
The profiler/index tuning wizard combination is very powerful in the actual database server environment involving many tables and many queries. When the database is performing a typical query, use profiler to record the. TRC file. Then load the. TRC file to index tuning Wizard to determine whether the correct index is created. Automatically generate and schedule an index creation job based on the prompts in index tuning Wizard to run during off-peak hours. Run profiler/index tuning wizard regularly (for example, weekly) to check whether there are major changes to the query executed on the database server. If yes, different indexes may be required. Regular use of profiler/index tuning wizard helps database administrators query workload changes and database size increases over time while keeping SQL server running in the optimal state.

The comparison solution is: Design related indexes as much as possible during the design, and use tools as the testing means.

3. query statement Design
(Pay attention to the index of the column in The WHERE clause .)

4. Partition View

4> customer applications

... To be continued