Simple SQL Server Performance Tuning

The purpose is to view the system (ApplicationProgram+ Database) Performance of calling SQL stored procedure stored procedures and SQL statements. How slow is it? Which Stored Procedure consumes the most time?

Sometimes when you set several indexes (clustered/non-clustered index) in a table, the result is still very slow (full table scan), because your index is not hit at all, it does not appear in where or order by at all!

1. Open profiler:

"Start-programs-SQL server2005-Performance Tools-SQL Server Profiler"

2. Click the first button in the toolbar-"New trace ..."

3. In the first tab: general, tick "Save to table" and output it to the table.

Select the master database in the pop-up window. The table name is random, for example, JQ

4. In the second tab: Events selection, do not select the Security and session sections. Only the stored procedure and SQL statements are selected.

5. Click Run.

If an error occurs: "Only TrueType fonts are supported. there id not a TrueType font ", select" choose font... "select another font.

6. After the monitoring starts, start the application. You can monitor the peak hours as long as you want. After monitoring, click STOP.

 

 

7. Open SQL Server Management studio, link to the master database, and enter the SQL statement:

Select top 500 * From JQ order by duration DESC;

8. You can see the most time-consuming stored procedure. Next, we will analyze the SQL statements of each stored procedure.

You can check the execution plan.

Menu: "query"-"display estimated execution plan ."

 

9. Modify and optimize the SQL statement. Repeat the preceding steps.