Troubleshooting SQL Database Performance Issues

The operation of a project, always accompanied by performance problems, system queries too slow, how to quickly query and so on

Here is a brief explanation of how to troubleshoot and solve these problems.

During the development process:

1: Do not absolute three-paradigm, appropriate build redundancy can improve query speed, without multiple table association

2: Do not appear circular SQL if you can associate with a table

Example: In this case, each of the t_t1 data is searched, the data in the T_T2 is looped, and the index associated with the table is invalidated, resulting in a slow response.

--Error Demonstration Select a, B, (select C from T_t2 where id=t_t1.id) from t_t1--correct procedure select A,b,c from T_t1,t_t2 WHERE t_t1.id=t_t2.id

3: Configure LOG4JDBC, easy to find the problem of SQL

4: Create a reasonable index

Indexes are useful for finding small ranges of data in a wide range of databases, and not for creating indexes if the column data is mostly the same, or if the amount of data fetched is significant.

During the maintenance process:

1: Change database parameters: When a database is installed, you should change the parameters to make them more useful for your project

Modify data Buffer parameters: The data cache is to load the disk information into memory, read the memory faster than the disk speed, should be appropriate to improve the data buffer.

Modify the personal sort area parameter: When the data needs to be sorted, more than the provided memory space, part of the data in the sorting area, and part of the disk area, after multiple memory and disk exchange, to complete the sorting, affecting the sorting speed.

2: Periodic Table analysis, so that the database table some information re-entry into force

3: Rebuilding the index, for a table often added delete, the data change is not small, but the index is constantly expanding, so, periodically rebuild the index, conducive to query.

4: Regular collation of table fragments.

5: View the execution plan for slow SQL: Sybase as an example

View execution plan for statement: set  SHOWPLAN  on  go  SQL statement go  set  SHOWPLAN  OFF  Go  View stored procedure execution plan: set  SHOWPLAN  On  go  EXEC  pr_ stored procedure go  SET  SHOWPLAN  OFF  Go   

Troubleshooting SQL Database Performance Issues