SQL Performance Optimization Preparation-clear cache, enable IO statistics

Tag: Execute SQL Server Query Analyzer query database src step drop effect

If you need to perform SQL performance tuning under SQL Server, you need to prepare the following:

One, SQL Query Analyzer settings:

1, open the actual execution plan tracking.

2. Set SQL to clear the cache before each execution needs to optimize SQL.

In the usual case of SQL Server performance tuning, to ensure real-world restore performance problems, we need to close the SQL Server's own execution plan and cache. You can clear the cache with the following settings.

1 DBCC dropcleanbuffers  --purge buffer 2 DBCC FREEPROCCACHE  --delete elements from the plan cache

3, open the query IO read statistics, query time statistics.

Set STATISTICS time on-execution times set STATISTICS IO on--io read

After you turn on settings, execute the SQL effect as follows:

For each of these icon nodes, when the mouse slide up, you can see the specific execution information. Such as:

You can view predicates, objects, output lists, analyze problem points, or create optimized indexes.

Of course, you can also change the viewing mode, right-click to select Show Execution Plan XML.

It is also important to note that when your SQL is very long logical relationship is very complex, the execution plan will be a large network diagram, you will find in the lower right corner there is a plus button, click on a thumbnail image. Through the thumbnail you can easily locate the execution node, it is also more useful to use.

Second, for the SQL Server profile,sql query tracker for analysis.

1. Open with: SQL Server Query Analyzer tools, SQL profile. Open in the way:

2. Connection & Special settings:

Open the Post interface such as:

After setting the correct connection information, click Connect and the following screen appears. Follow the steps in the diagram to set it up.

where DatabaseID, hostname can query in the Query Analyzer, the script is as follows:

1 Select db_id () 2 Select Db_name () 3 Select HOST_ID () 4 Select HOST_NAME ()

Actually, hostname is your native computer name.

Click Run after the final setup is finished. Effects of normal tracking

Focus on the duration, writes, Reads, CPU, the Analysis object is TextData, and the execution of the statement. Where duration is the number of milliseconds and 1000 is 1 seconds.

——————————————————————————————————————————

Application Summary & Recommendations :

The above applies the Fit method is:

1, first through the SQL query tracker, tracking out the SQL you executed, and then locate the SQL in which duration compares or exceed the performance standard of SQL (such as page access 3s, 5s, 8s), report 30s and so on.

2, the problem of SQL in the Query Analyzer analysis, mainly through the execution plan and IO statistics location time-consuming high and IO read large place, and then gradually adjust the SQL logic relationship (such as adding business conditions filter to reduce the set, index, adjust like match, etc.), Optimize and then re-track to see if there is any effect, and finally achieve SQL optimization purposes.

Write here, basically I commonly used SQL performance optimization of the way has been finished, I hope to provide you with help.

Absolute dry Goods, reproduced please indicate the source. Original address

SQL Performance Optimization Preparation-clear cache, enable IO statistics