SQL Server Performance Optimization-improving performance through systematic methods

Original http://www.cnblogs.com/BoyceYang/archive/2013/06/15/3138142.html

Read navigation

1. Overview

2.RulesLogic Database Design

3.EnableDesign with efficient Indexes

4.EnableEfficient Query Design

5.EnableLow performance analysis with technology

6. Summary

 

1. Overview

This is the most effective way to optimize SQL server performance by finding areas that can significantly improve performance and focusing on the analysis area. Otherwise, a large amount of time and energy may be wasted in areas where performance cannot be improved. The performance issues caused by multi-user concurrency are not discussed here.

The areas for maximum performance improvement are generally logical database design, index design, and query design. However, the biggest performance problem is often caused by the lack of such research. If performance is listed as a concern, the smart approach is to focus on these aspects first, because a substantial increase in performance is often done with a relatively small amount of time.

Start to enter the subject.

2. standardized logical database design

Reasonable and standardized logical database design can produce optimal performance. A large number of narrow tables are a feature of the standard database. A small number of wide tables are non-standard data features. Highly standard databases are usually associated with complex table joint queries, which may damage the database performance. In any case, SQL Server optimization is very effective in fast query, efficient connection, and effective indexing. The following are the benefits of standardization:

• For narrow tables, sort and create indexes faster.
• For wide tables, it is best to use clustered indexes.
• The narrower the index is, the more accurate the table should be.
• Better use segments to control the physical space of tables
• The fewer indexes for each table, the more helpful the update operation performance is.
• The fewer nulls columns, the less redundant data, the more compact the database.

For SQL Server, standardization will help improve rather than damage performance. As standardization increases, a certain number of complex table connections are required to retrieve data. As long as standardization does not cause connections to more than four tables in many queries, the standardization process should be implemented.

If the logical database design is fixed and it is impossible to re-design it as a whole, and research shows that a large table has a performance bottleneck, in this case, you can selectively Standardize this large table. If data is accessed through the stored procedure, the architecture change will not affect the application.Program. If this is not the case, you can create a view to hide this change, because the view can create a single table.

3.EnableDesign with efficient Indexes

Unlike many non-relational systems, relational indexes are not considered as part of the logical database design. Indexes can be deleted, added, and updated without affecting the database architecture or application design. It is very important to achieve good SQL server performance and efficient index design. For these reasons, do not hesitate to show the performance changes caused by different indexes.

In most cases, the optimizer reliably selects the most efficient index. All policies should provide good index optimization options. I believe this is the correct decision. This can reduce the analysis time and provide good performance in multiple situations.

Next we will introduce the index. Check the WHERE clause of SQL queries because it is the main focus of optimization. The columns listed in the WHERE clause may be used as index alternatives. If there are too many statements that need to be checked, select a representative group or a group that is only slow.

Narrow indexes are recommended. Narrow indexes are more efficient than hybrid and composite indexes. The more rows on each page of a narrow index, the lower the index level, in order to improve performance. SQL Server optimization only maintains statistics on the most important columns of a composite index. Therefore, if the first column of the composite index is poorly selective, the index will not be optimized.

The optimizer can quickly and efficiently analyze the possibility of hundreds of indexes and table connections. With more narrow indexes available to the optimizer, the optimizer will have more options, which is helpful for performance. A small number of wide indexes and composite indexes are provided to the optimizer. The optimizer has only a few options, which affects performance.

Too many indexes may reduce performance because of the overhead of updating these indexes. However, a large number of update-oriented operations require more read operations, rather than write operations. If the performance is improved when you try a new index, do not hesitate to use it.

Use clustered indexes. Using clustered indexes appropriately can greatly improve performance. Even clustered indexes can accelerate the update and delete operations because these operations require a lot of read operations. Each table may only have a single clustered index. Therefore, you must use this index flexibly. Query of the number of returned rows or query involving a range value is a candidate that may be clustered index to improve performance.

Example:

 
1:Select*FromPhonebook
 

 
2: 
 

3:WhereName = 'lily'
 

 
4: 
 

 
5:Select*FromMemertable
 

 
6: 
 

 
7:WhereMember_no> 5000AndMember_no <1, 6000
 

Through constraints, the name and member_no columns mentioned above may not be a suitable candidate for Non-clustered indexes. Try to use non-clustered indexes on the columns that return a small number of rows of data.

Check the uniqueness of column data. This will help determine what columns are used as clustered indexes, non-clustered indexes, and no indexes required.

The query statement checks data uniqueness. For example:

 
1:Select Count(DistinctColname)FromTablename
 

This statement returns the number of unique values in a column. Compare the quantity and total number of rows in the table. In a 10 thousand-row table, 5000 columns with no repeated values may be a good alternative for non-clustered indexes. 20 columns with no repeated values may be the most suitable for clustered indexes, indexes are not required for the three columns without repeated values. These are just examples, not static rules.Remember to create an index on each column listed in the where query clause.

When selecting indexes, the number of rows returned by the query statement is also an important factor. The optimizer will consider the cost of non-clustered indexes at least one page of I/O per returned row. At this speed, it does not take a long time to scan the entire table more efficiently. To treat a result set rationally, either limit the size of the result set or use a clustered index to locate a huge result set.

4.EnableEfficient Query Design

Some query statements are resource-intensive. This is related to the common problems of basic data and indexes in most RDBMSs (relational database management systems), rather than in specific SQL servers. They are not inefficient, and the optimizer will try its best to implement efficient query statements. However, they are resource-intensive, and SQL's result-oriented nature may make them inefficient. It is impossible for the optimizer to eliminate the inherent resource costs of these structures. Compared with simpler statements, they consume more internally. Although SQL server uses the optimal access plan, there are still restrictions.

For example:

• Large result set
• In And or statements
• Highly unique WHERE clause
• ! = (Not equal)
• Some column functions, such as sum
• Expression or data conversion in the WHERE clause
• Local variables in the WHERE clause

Some factors may require the structure of these query statements. If the optimizer can limit the result set and then apply resource-intensive queries, their impact will be reduced.

For example:

 
1:Inefficiency:Select Sum(Salary)From Table
 

 
2: 
 

 
3:Efficient:Select Sum(Salary)From Table WhereZip ='123'
 

4: 
 

 
5:Inefficiency:Select*From Table WhereLname = @ VaR
 

 
6: 
 

 
7:Efficient:Select*From Table WhereLname = @ VaRAndZip ='123'
 

In the first example, the sum operation does not accelerate indexing. Each row needs to be read and summed. Suppose there is an index in the zip column. The Optimizer may use this to initially limit the result set and then apply the sum function. This may be faster.

In the second example, the local variable is assigned a value until it is run. However, the optimizer cannot select an access plan until it is running. You must select an access plan during compilation. However, during compilation, when an access plan is generated, the value of @ var cannot be determined. Therefore, the input @ var cannot be used as the index selection. You can use the and clause to limit the result set. Using Stored procedures is an optional technique, so that you can pass the parameter and assign the parameter value to the @ VaR value in the stored procedure.

A large result set of most RDBMSs is very performance-consuming. You can choose not to return a large result set to the client as the final data. It is highly efficient to allow the database to execute predefined functions in the background and limit the size of the result set.

5.EnableLow performance analysis with technology

Separate queries first or separate slow queries. When a few SQL queries are slow, it often shows that the whole application is slow. Use the diagnostic or debug mode to record the generated SQL statements for a tool that displays the generated SQL statements. It is easier to use embedded SQL tools. Before splitting slow queries, perform the following steps:

• Run a statement that is suspected to be slow. use tools (such as iSQL and SAF) to verify whether the statement is actually slow.
• Use set statistics Io on to check the I/O consumption of the statement and the selected access plan. The optimizer is designed for the smallest I/O. Record logic I/O. The improvement results are measured on this basis.
• If the query involves views or stored procedures, extract these statements and run them separately. When you try to use different indexes, the access plan can be changed.
• Some tables can generate I/O and run as triggers. Pay attention to the triggers and views that may be related to these tables.
• Check the indexes of slow statement tables. Use the technology listed earlier to check whether there is a better index and modify it if necessary.
• After changing the index, run the query again and observe the changes in the I/O and access plan.
• After the improvement is completed, run the main program to see if all the performance has been improved.

Check the I/O or CPU limits of the program. This is usually useful for determining whether the query statement is in an I/O or CPU critical state. We need to focus on improving the real performance bottleneck. For example, if a query is in the CPU critical state, even if more memory is added to the SQL Server, the performance can be improved too much, of course, more memory can improve the cache hit rate. The following steps check the I/O and CPU critical states of SQL Server:

• Use the OS/2 CPU monitoring program.
• When a query is run, if the CPU usage remains high (> 70%), this indicates a CPU critical state.
• When a query is run, if the CPU usage remains low (<50%), this indicates a CPU critical state.
• Use Statistics Io to compare CPU utilization information

6. Summary

SQL Server can improve the performance of large databases. To tap into the potential of this performance, efficient database design, indexing, and query statements are required. These areas are the most likely alternative areas to capture significant performance improvements. It is a special recommendation to try to use indexes. In general, the systematic method not only invests less time in analyzing performance issues, but also produces a huge performance improvement.

 

I would like to thank @ watching dreamstar for reading this article.Article.

Read navigation

1. Overview

2.RulesLogic Database Design

3.EnableDesign with efficient Indexes

4.EnableEfficient Query Design

5.EnableLow performance analysis with technology

6. Summary

 

1. Overview

This is the most effective way to optimize SQL server performance by finding areas that can significantly improve performance and focusing on the analysis area. Otherwise, a large amount of time and energy may be wasted in areas where performance cannot be improved. The performance issues caused by multi-user concurrency are not discussed here.

The areas for maximum performance improvement are generally logical database design, index design, and query design. However, the biggest performance problem is often caused by the lack of such research. If performance is listed as a concern, the smart approach is to focus on these aspects first, because a substantial increase in performance is often done with a relatively small amount of time.

Start to enter the subject.

2. standardized logical database design

Reasonable and standardized logical database design can produce optimal performance. A large number of narrow tables are a feature of the standard database. A small number of wide tables are non-standard data features. Highly standard databases are usually associated with complex table joint queries, which may damage the database performance. In any case, SQL Server optimization is very effective in fast query, efficient connection, and effective indexing. The following are the benefits of standardization:

• For narrow tables, sort and create indexes faster.
• For wide tables, it is best to use clustered indexes.
• The narrower the index is, the more accurate the table should be.
• Better use segments to control the physical space of tables
• The fewer indexes for each table, the more helpful the update operation performance is.
• The fewer nulls columns, the less redundant data, the more compact the database.

For SQL Server, standardization will help improve rather than damage performance. As standardization increases, a certain number of complex table connections are required to retrieve data. As long as standardization does not cause connections to more than four tables in many queries, the standardization process should be implemented.

If the logical database design is fixed and it is impossible to re-design it as a whole, and research shows that a large table has a performance bottleneck, in this case, you can selectively Standardize this large table. If data is accessed through the stored procedure, the architecture change will not affect the application. If this is not the case, you can create a view to hide this change, because the view can create a single table.

3.EnableDesign with efficient Indexes

Unlike many non-relational systems, relational indexes are not considered as part of the logical database design. Indexes can be deleted, added, and updated without affecting the database architecture or application design. It is very important to achieve good SQL server performance and efficient index design. For these reasons, do not hesitate to show the performance changes caused by different indexes.

In most cases, the optimizer reliably selects the most efficient index. All policies should provide good index optimization options. I believe this is the correct decision. This can reduce the analysis time and provide good performance in multiple situations.

Next we will introduce the index. Check the WHERE clause of SQL queries because it is the main focus of optimization. The columns listed in the WHERE clause may be used as index alternatives. If there are too many statements that need to be checked, select a representative group or a group that is only slow.

Narrow indexes are recommended. Narrow indexes are more efficient than hybrid and composite indexes. The more rows on each page of a narrow index, the lower the index level, in order to improve performance. SQL Server optimization only maintains statistics on the most important columns of a composite index. Therefore, if the first column of the composite index is poorly selective, the index will not be optimized.

The optimizer can quickly and efficiently analyze the possibility of hundreds of indexes and table connections. With more narrow indexes available to the optimizer, the optimizer will have more options, which is helpful for performance. A small number of wide indexes and composite indexes are provided to the optimizer. The optimizer has only a few options, which affects performance.

Too many indexes may reduce performance because of the overhead of updating these indexes. However, a large number of update-oriented operations require more read operations, rather than write operations. If the performance is improved when you try a new index, do not hesitate to use it.

Use clustered indexes. Using clustered indexes appropriately can greatly improve performance. Even clustered indexes can accelerate the update and delete operations because these operations require a lot of read operations. Each table may only have a single clustered index. Therefore, you must use this index flexibly. Query of the number of returned rows or query involving a range value is a candidate that may be clustered index to improve performance.

Example:

 
1:Select*FromPhonebook
 

 
2: 
 

 
3:WhereName = 'lily'
 

 
4: 
 

 
5:Select*FromMemertable
 

 
6: 
 

7:WhereMember_no> 5000AndMember_no <1, 6000
 

Through constraints, the name and member_no columns mentioned above may not be a suitable candidate for Non-clustered indexes. Try to use non-clustered indexes on the columns that return a small number of rows of data.

Check the uniqueness of column data. This will help determine what columns are used as clustered indexes, non-clustered indexes, and no indexes required.

The query statement checks data uniqueness. For example:

 
1:Select Count(DistinctColname)FromTablename
 

This statement returns the number of unique values in a column. Compare the quantity and total number of rows in the table. In a 10 thousand-row table, 5000 columns with no repeated values may be a good alternative for non-clustered indexes. 20 columns with no repeated values may be the most suitable for clustered indexes, indexes are not required for the three columns without repeated values. These are just examples, not static rules.Remember to create an index on each column listed in the where query clause.

When selecting indexes, the number of rows returned by the query statement is also an important factor. The optimizer will consider the cost of non-clustered indexes at least one page of I/O per returned row. At this speed, it does not take a long time to scan the entire table more efficiently. To treat a result set rationally, either limit the size of the result set or use a clustered index to locate a huge result set.

4.EnableEfficient Query Design

Some query statements are resource-intensive. This is related to the common problems of basic data and indexes in most RDBMSs (relational database management systems), rather than in specific SQL servers. They are not inefficient, and the optimizer will try its best to implement efficient query statements. However, they are resource-intensive, and SQL's result-oriented nature may make them inefficient. It is impossible for the optimizer to eliminate the inherent resource costs of these structures. Compared with simpler statements, they consume more internally. Although SQL server uses the optimal access plan, there are still restrictions.

For example:

• Large result set
• In And or statements
• Highly unique WHERE clause
• ! = (Not equal)
• Some column functions, such as sum
• Expression or data conversion in the WHERE clause
• Local variables in the WHERE clause

Some factors may require the structure of these query statements. If the optimizer can limit the result set and then apply resource-intensive queries, their impact will be reduced.

For example:

 
1:Inefficiency:Select Sum(Salary)From Table
 

2: 
 

 
3:Efficient:Select Sum(Salary)From Table WhereZip ='123'
 

 
4: 
 

 
5:Inefficiency:Select*From Table WhereLname = @ VaR
 

 
6: 
 

 
7:Efficient:Select*From Table WhereLname = @ VaRAndZip ='123'
 

In the first example, the sum operation does not accelerate indexing. Each row needs to be read and summed. Suppose there is an index in the zip column. The Optimizer may use this to initially limit the result set and then apply the sum function. This may be faster.

In the second example, the local variable is assigned a value until it is run. However, the optimizer cannot select an access plan until it is running. You must select an access plan during compilation. However, during compilation, when an access plan is generated, the value of @ var cannot be determined. Therefore, the input @ var cannot be used as the index selection. You can use the and clause to limit the result set. Using Stored procedures is an optional technique, so that you can pass the parameter and assign the parameter value to the @ VaR value in the stored procedure.

A large result set of most RDBMSs is very performance-consuming. You can choose not to return a large result set to the client as the final data. It is highly efficient to allow the database to execute predefined functions in the background and limit the size of the result set.

5.EnableLow performance analysis with technology

Separate queries first or separate slow queries. When a few SQL queries are slow, it often shows that the whole application is slow. Use the diagnostic or debug mode to record the generated SQL statements for a tool that displays the generated SQL statements. It is easier to use embedded SQL tools. Before splitting slow queries, perform the following steps:

• Run a statement that is suspected to be slow. use tools (such as iSQL and SAF) to verify whether the statement is actually slow.
• Use set statistics Io on to check the I/O consumption of the statement and the selected access plan. The optimizer is designed for the smallest I/O. Record logic I/O. The improvement results are measured on this basis.
• If the query involves views or stored procedures, extract these statements and run them separately. When you try to use different indexes, the access plan can be changed.
• Some tables can generate I/O and run as triggers. Pay attention to the triggers and views that may be related to these tables.
• Check the indexes of slow statement tables. Use the technology listed earlier to check whether there is a better index and modify it if necessary.
• After changing the index, run the query again and observe the changes in the I/O and access plan.
• After the improvement is completed, run the main program to see if all the performance has been improved.

Check the I/O or CPU limits of the program. This is usually useful for determining whether the query statement is in an I/O or CPU critical state. We need to focus on improving the real performance bottleneck. For example, if a query is in the CPU critical state, even if more memory is added to the SQL Server, the performance can be improved too much, of course, more memory can improve the cache hit rate. The following steps check the I/O and CPU critical states of SQL Server:

• Use the OS/2 CPU monitoring program.
• When a query is run, if the CPU usage remains high (> 70%), this indicates a CPU critical state.
• When a query is run, if the CPU usage remains low (<50%), this indicates a CPU critical state.
• Use Statistics Io to compare CPU utilization information

6. Summary

SQL Server can improve the performance of large databases. To tap into the potential of this performance, efficient database design, indexing, and query statements are required. These areas are the most likely alternative areas to capture significant performance improvements. It is a special recommendation to try to use indexes. In general, the systematic method not only invests less time in analyzing performance issues, but also produces a huge performance improvement.

 

I would like to thank @ watching dreamstar for his support for this article.