Tips for database developers for Performance Tuning

Performance Tuning is not a simple task. There is no omnipotent solution at all, but you can use some basic principles to implement it.

In general, performance tuning is the role of DBA. however, DBA does not have time to carefully check every change made to the stored procedure. so learning some basic tuning may help you save the effort of rewriting.CodeTime.
I have listed 15 items here. When writing code, developers should naturally consider these items to achieve performance tuning. these are some tips on sqlserver performance-they can be implemented simply, and the results are usually obvious after implementation. in addition, the completion of the following items does not necessarily mean a lot of load reduction, but at least the database will not become slower.

1. Create a primary key on each table and set it as a clustered index, unless you can choose a better design method.
2. Create an index on the field of the foreign key. If you are sure that the values in this field are unique, you must add a unique identifier.
3. Do not create indexes on other columns.
4. except in rare cases, we recommend that you specify the object owner in tsql, for example, using DBO. sysdatabases instead of sysdatabases.
5. Add set nocount on at the beginning of each stored procedure and use set nocount off at the end.
6. The lock level needs to be carefully considered. If it is not a banking system, can it accept dirty reads? If you accept dirty reads, you can use the nolock prompt. The simpler method is to use set transaction isolation level read uncommited at the beginning of the stored procedure and reset it to read commited at the end.
7. You may have heard it thousands of times, but you still need to remind me that you only need to return the required data (rows and columns ).
8. Use transactions when appropriate, but allow zero user interaction in the transaction process. I usually try to place transactions in the stored procedure as much as possible.
9. Avoid using temporary tables as much as possible. However, if you can only use temporary tables, use the create table # temp command to create temporary tables explicitly.
10. Avoid using not in. Use left Outer Join instead, although not in is more readable.
11. if dynamic SQL is used (to execute some concatenated SQL statements), use the naming parameters and sp_executesql (instead of exec) to ensure the opportunity to reuse the execution plan. although stored procedures are the right option in most cases, do not use them incorrectly.
12. develop the habit of analyzing code before and after the change. After you find that the CPU or read/write costs are 10 ~ 15% improvement means you need to check your code.
13. Try to find a way to reduce the number of requests to the server. Returning a large number of result sets at a time is one of the methods.
14. Avoid indexing or join prompts
15. When you are writing code, please only monitor statements from your machine and then apply Program Run these statements once. pay attention to the number of reads and writes and the number of requests sent to the server. focus on all exceptions. it is not uncommon for a stored procedure that is no longer in use or repeated calls to the stored procedure. it is quite impressive for DBAs to check these monitoring results.

If you take the above 15 steps, you will have a good start.