Some tips for optimizing your SQL statements

Introduction

There is a huge difference between SQL statements that write and implement functions and SQL statements that can both implement functions and ensure performance. In many cases, developers only focus on implementing the required functions, while ignoring what they write.CodePerformance and the impact on the SQL server instance (I/O, CPU, memory consumption). This may even cause the entire SQL server instance to kill. This article aims to provide some simple steps to help you optimize SQL statements.

There are many books and white papers on how to optimize SQL server performance on the market. Therefore, this article does not aim to reach the depth and breadth, but simply provides developers with a quick detection list to find the bottleneck caused by SQL statements.

A proper diagnostic tool is required before you begin to solve performance problems. Apart from the well-known SSMs and SQL profiler, SQL Server 2008 also comes with many DMV to provide key information. This articleArticleIn, I will use SSMs and some DMV to find the SQL bottleneck

 

Where do we start from?

My first step is to view the execution plan. This step can be implemented either through SMSs or SQL profiler. For convenience, I will get the execution plan in SMSs.

1) check whether you ignore the join conditions of some tables, resulting in Cartesian Product (Cross) join ). For example, there are two tables in the production system, each of which has 1000 rows of data. The vast majority of data does not need to be returned. If you apply cross join to these two tables, the returned result will be the result set of 1 million rows! The returned amount of data includes reading all the data from the physical storage medium, which occupies Io. Then the data will be imported into the memory, that is, the SQL Server Buffer. This will flush other pages in the buffer.

 

2) check whether you ignore some where clauses. If the WHERE clause is missing, additional unnecessary rows are returned. This has the same impact as step 1.

3) check whether the statistics are automatically created and updated. You can view these options in the database attributes.

By default, a new database is created. The options auto create statistics and auto update statistics are enabled, and statistics are used to help the query optimizer generate the best execution plan. This White Paper provides an excellent explanation of the importance of statistical information and the role of the execution plan. The preceding settings can be found in "options" by right-clicking database, selecting properties.

 

4) check whether the statistical information has expired. Although the statistical information is automatically created, it is equally important to update the statistical information to reflect data changes. In a large table, although the auto update statistics option has started, the statistics still cannot reflect the data distribution. By default, statistics are updated based on the random information in the extracted table as a sample. If the data is stored in order, it is likely that the data sample does not reflect the data in the table. Therefore, we recommend that you use the full scan option to regularly update statistics in frequently updated tables. This update can be performed in idle time of the database.

The DBCC show_statistics command can be used to view the last Statistics Update Time, number of rows, and number of sample rows. In this example, we can see information about the ak_address_rowguid index in the person. Address Table:

Use adventureworks; godbcc show_statistics ("Person. Address", Ak_address_rowguid); go
 

The following is the output result. Pay attention to the updated, rows, and rows sampled columns.

 

If you think the statistics have expired, you can use sp_updatestats to update all statistics in the current database:

 
Exec sp_updatestats
 

You can also use the fullscan option to update all statistics on the person. Address Table:

 
Update statistics person. Address with fullscan
 

 

5) check whether any table or index scan is performed on the Execution Plan (this is not a search). In most cases (This assumes that the statistics are the latest ), this means that the index is missing. The following DMV is helpful for searching for missing indexes:

I) SYS. dm_db_missing_index_details

Ii) SYS. dm_db_missing_index_group_stats

Iii) SYS. dm_db_missing_index_groups

The following statements use the DMV above and display the information based on the impact of missing indexes on performance:

 
Select avg_total_user_cost, avg_user_impact, user_seeks, user_scans, id. equality_columns, id. inequality_columns, id. included_columns, id. statement from sys. dm_db_missing_index_group_stats gsleft Outer Join sys. dm_db_missing_index_groups ig on (Ig. index_group_handle = Gs. group_handle) left Outer Join sys. dm_db_missing_index_details ID on (ID. index_handle = Ig. index_handle) order by avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC
 

You can also use the data engine optimization consultant to find out the missing indexes and what indexes need to be created to improve performance.

 

6) check whether there are bookmarks. Similarly, it is very easy to find bookmarks in the execution plan, and the bookmarks' search cannot be completely avoided. However, overwriting indexes can greatly reduce the books' search.

 

7) Check the sorting operation. If the sorting operation occupies a large part of the execution plan, I will consider the following solutions:

• Creating clustered indexes based on the columns to be sorted remains controversial. Because the best practice is to use a unique or Int type column as the primary key, and then let SQL Server create a clustered index on the primary key. However, it is also possible to create a clustered index using a sorting column in a specific situation.
• Create an index view and create a clustered index based on the sorting column in the index View
• Create a non-clustered index for the sorting column and include other columns to be returned.

In my other article, I will elaborate on how to select the best solution.

 

8) view the lock added to the table. If the queried table is locked due to a DML statement, the query engine needs to spend some time waiting for the lock to be released. The following are some solutions to the lock problem:

• Make the transaction as short as possible
• View the database isolation level to lower the isolation level to increase concurrency
• Use table prompts in select statements, such as readuncommitted or readpast. Although the two table prompts increase concurrency, readuncommited may cause dirty read, while readpast will only return partial result sets.

 

9) check whether any index fragmentation exists. The index fragmentation can be sys. dm_db_index_physical_statsdmv is easy to view. If the index fragmentation is greater than 30%, re-indexing is recommended. we recommend that you sort indexes when the index fragmentation is less than 30%. Index fragmentation increases I/O because the query needs to read more columns, and more pages occupy more buffers, resulting in memory pressure.

the following statement displays all indexes based on the percentage of index fragments:

Declare @ dbsysname; Set @ DB ='<DB Name>'; Select cast (object_name (S. object_id, db_id (@ dB) as varchar (20)'Table Name', Cast (index_type_desc as varchar (20)'Index type', I. Name'Index name', Avg_fragmentation_in_percent'AVG % fragmentation', Record_count'Recordcount', Page_count'Pages allocated', Avg_page_space_used_in_percent'AVG % PAGE space used'From SYS. dm_db_index_physical_stats (db_id (@ dB), null ,'Detailed') Sleft Outer Join SYS. Indexes I on (I. object_id = S. object_id and I. index_id = S. index_id) and S. index_id> 0 order by avg_fragmentation_in_percent DESC
 

The following statement re-creates all indexes of a specified table:

 
Alter index all on <Table Name> rebuild;
 

The following statement re-creates a specified index:

 
Alter index <index Name> On <Table Name> rebuild;
 

Of course, we can also sort the indexes. The following statement sorts all the indexes on the specified table:

 
Alter index all on <Table Name> reorganize;
 

The following statement specifies a specific index for sorting:

 
Alter index <index Name> On <Table Name> reorganize;
 

After rebuilding or sorting out the index, run the preceding statement again to view the index fragmentation.

 

Summary

The above nine steps are not necessary to optimize an SQL statement. However, you still need to find the step that causes the query performance bottleneck as soon as possible to solve the performance problem. As mentioned in the beginning of this article, performance problems are often caused by deeper reasons, such as CPU or memory pressure and IO bottlenecks (this list will be very long ....), Therefore, more research and reading are necessary to solve performance problems.

----------------------------------------

http://www.sqlservercentral.com/articles/Performance+Tuning/70647/