Using Query Analyzer to adjust SQL Server

Source: Internet
Author: User
Tags join
I like to think of the tools bundled with SQL Server as inverted pyramid, the tools for diagnosing and checking general problems at the top, looking for and diagnosing specific areas of problems at the bottom. In addition to providing a convenient way to write SQL scripts, Query Analyzer is the resource that you need to use when you need to resolve specific problems in a particular SQL script. If you need to indicate which query is holding the lock on a particular table, you will also want to use the Query Analyzer.
The key diagnostic feature of Query Analyzer is its ability to display a query's execution plan. This execution plan will provide you with various types of useful information, such as how and when to use or not to use the index during the execution of the query. It also provides many other details, such as sorting, parallelism (parallelism), nested loops, and other things that SQL Server must do when executing a specified query.
 
Benefits of the Plan
With the Query Analyzer, you can view the projected plan without having to run the query itself, or view the actual plan after the query is executed. Obviously, the real plan is more accurate because it is physically running the query on the database. However, this approach may not be the best choice for a large and/or long-term system load query. In a typical case, I run this tool when I think a particular query is having a problem, or if I expect a query to be called frequently enough in the application so that it can cause performance problems.
You can view the expected execution plan by typing the SQL expression into the Query Analyzer and then pressing [ctrl]l]. The execution plan is then displayed in the tab of the results (Results) panel. The execution plan may be difficult to read at the beginning because it is not read from left to right as in English, but is read from right to left.
Scheduled in operation
Here's an example of what you can do with it, and from this example you can see how to use Query Analyzer to view an execution plan. Consider the following query, which returns the location and related area 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 BY
Territorydescription, RegionDescription

The results of this query indicate that there are two EmployeeID in the east (eastern) of the first number, as shown in Figure A.

Figure A


Query results

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.