Use the query analyzer to adjust the SQL Server script _ MySQL

I like to think of the tools bundled with SQL servers as inverted pyramid. tools for diagnosing and checking general problems are at the top, and tools for finding and diagnosing specific areas of problems are at the bottom. In addition to providing a convenient way to write SQL scripts, the QueryAnalyzer still needs to be used when you need to solve specific SQL scripts to identify problems. For example, I like to regard the tools bundled with SQL Server as inverted pyramid. tools for diagnosing and checking general problems are at the top, and tools for finding and diagnosing specific areas of problems are at the bottom. In addition to providing a convenient way to write SQL scripts, the Query Analyzer still needs to use resources when you need to solve specific SQL scripts to identify problems. If you need to specify which query is holding the lock status of a specific table, you also want to use the query analyzer.

The key diagnostic feature of the query analyzer is that it can display the execution plan of a query. This execution plan will provide you with various types of useful information, such as how and when to use or not use indexes during query execution. It also provides many other details, such as sorting, parallelism, nested loops, and other things that the SQL Server must do when executing a specified query.

Benefits of the plan

With the query analyzer, you can view the expected plan without running the query itself, or view the actual plan after the query is executed. Obviously, the real plan is more accurate because it runs queries on the database physically. However, this method may not be the best choice for queries with huge system loads and/or long-term queries. In typical cases, when I think a specific query has a problem, or if I expect a query to be called frequently enough in the application to cause performance problems, I will run this tool.

You can enter the SQL expression in the query analyzer and press [Ctrl] L to view the expected execution plan. The execution plan is then displayed on the Results tab. The execution plan may be difficult to read at the beginning, because it does not read from left to right like English, but from right to left.

Running plan

Here is an example that you can follow. From this example, you can see how to use the query analyzer to view an execution plan. Consider the following query in the region. it returns the location and region of an employee from the Northwind database:

SELECT
TerritoryDescription, RegionDescription
FROM
Employees e
JOIN EmployeeTerritories et ON e. employeeid = et. employeeid
JOIN Territories t ON et. territoryid = t. territoryid
JOIN Region r ON t. regionid = r. regionid
WHERE
E. employeeid = 1
ORDER
TerritoryDescription, RegionDescription

The result of this query shows that employee ID 1 has two regions in the East (Eastern), as shown in.

Figure

Query result

Figure BDisplays the expected execution generated by the SQL server from the sample query. Each node in this icon represents the steps to perform the sample query during the SQL service period. Note that each node includes an expected relative consumption and the name of each index used by the SQL Server when the execution plan is displayed.

Figure B