Using SQL Query Analyzer
3 4
The Query Analyzer tool, supplied with Microsoft SQL Server 2000, has replaced Interactive SQL for Windows (ISQL/W) as an SQL GUI, however you may have noticed that the Query Analyzer shows up as isqlw.exe in the task manager. You can use Query Analyzer to process T-SQL statements and view the results of those statements. Query Analyzer can also be used as a debugging tool to evaluate the execution plan that is generated by the query optimizer for your T-SQL statement.
Running T-SQL Statements
Running T-SQL statements and displaying the results of those statements are the most basic capabilities of Query Analyzer. To use Query Analyzer to run a T-SQL statement, follow these steps:
- Click Start, point to Programs, point to Microsoft SQL Server, and then choose Query Analyzer. The Connect To SQL Server dialog box appears, as shown in Figure 35-1. You use this dialog box to make a connection to a SQL Server system.
Figure 35-1. The Connect To SQL Server dialog box.
- Enter the server name in the SQL Server combo box. This can be the name of a local server or a remote server. Figure 35-1 shows a period (.) in the box. Entering a period indicates that you want to connect to the local server. The check box immediately below the SQL Server box lets you specify whether you want to start the server if it is not already running. In the Connect Using area, choose the authentication method you want to use to connect to SQL Server. If you choose to use Microsoft Windows NT Authentication, you don't have to specify a user name or password because the Microsoft Windows 2000 account will be used to authenticate access to SQL Server. If you choose to use SQL Server Authentication, you must then specify a SQL Server user name and password in order to access SQL Server.
- Click OK to connect to the specified SQL server and to start Query Analyzer. When the Query Analyzer window first appears, only the Query pane and the navigation panes are visible, but this will change once you start submitting T-SQL statements. Maximize the Query pane to fill the entire right-hand side of the Query Analyzer window, as shown in Figure 35-2. In the drop-down list on the toolbar, select the database you want to run queries against. Figure 35-2 shows the master database selected. For our example, click the down arrow and choose Northwind.
Figure 35-2. The Query Analyzer window.
- After selecting a database, type a T-SQL statement in the right-hand pane—in this case, SELECT * FROM Customers. You now have several options. You can check the syntax of the T-SQL statement by clicking the Parse Query button on the toolbar (the blue check mark), or you can run the statement by clicking the Execute Query button (the green triangle pointing to the right). You can stop a query's execution by clicking the Cancel Executing Query button (the square). Figure 35-3 shows a completed query against the Customers table in the Northwind database.
Once you have submitted a T-SQL statement, Query Analyzer creates a pane that lets you scroll vertically and horizontally to view the results, as shown in Figure 35-3. Query Analyzer can also be used to help you tune your T-SQL statements, as we'll see in the section "Optimizing TSQL Statements" later in this chapter.
Figure 35-3. A completed Query Analyzer query.
Viewing Execution Plans and Modifying T-SQL Statements
As mentioned, you can also use Query Analyzer to display the execution plan that Query Optimizer chooses for a T-SQL statement. This feature can help you determine whether your T-SQL statement is efficient and what execution path and data access paths have been chosen. You can then make changes to the T-SQL statement and the database schema and see whether performance improves. To use Query Analyzer to view the estimated execution plan of a T-SQL statement, follow these steps:
- In the Query Analyzer window, type a T-SQL statement for Query Analyzer to evaluate, as described earlier, and then click the Display Estimated Execution Plan button (the button to the right of the database selection drop-down list) or press Ctrl+L. The Estimated Execution Plan pane is displayed, as shown in Figure 35-4. In this pane, the query is graphically depicted, and the cost of each operation is shown. The data access method is also shown here. In the pane shown in Figure 35-4, the index name Customers.PK_Customers appears, which means that the clustered index Customers.PK_Customers is used to access the data.
Figure 35-4. The Estimated Execution Plan.
- The Estimated Execution Plan pane provides access to additional data about the operations shown in it. To view this additional data for any operation, hold the mouse pointer over the operation's icon. A pop-up window containing additional data appears, as shown in Figure 35-5.
Figure 35-5. Viewing additional data about an operation.
This pop-up window contains the following information:
- Physical Operation/Logical Operation The operations performed by the query, such as index scans, joins, aggregations, and so on. If the physical operator is displayed in red, the query optimizer has issued a warning, and you should fix your T-SQL statement.
- Estimated Row Count The number of rows that the query optimizer estimates will be retrieved by the operation.
- Estimated Row Size The estimated size of the rows being retrieved in bytes.
- Estimated I/OCost/Estimated CPU Cost The estimated I/O resources and CPU time that will be used by this operation. A lower value means a more efficient T-SQL statement.
- Estimated Number Of Executes The estimated number of times that this operation will be executed during the execution of the T-SQL statement.
- Estimated Cost The cost of the operation as estimated by the query optimizer. This cost is shown as a percentage of the total cost of the TSQL statement.
- Estimated Subtree Cost The estimated cost of executing the preceding parts and this particular part of the T-SQL statement. If there are several subtrees, this option allows you to see the costs of executing each subtree.
- Argument The parameters used by the T-SQL statement.
NOTE
The execution plan describes how the query optimizer will execute a T-SQL statement. This plan shows the types of operations that will be used and the order in which they will be performed. The data access method describes how the database objects (tables, indexes, and so on) will be accessed. These two are related—the data access method is sometimes considered part of the execution plan, but it can also be considered separately.
Next we'll look at some more complex examples of using Query Analyzer. These examples also demonstrate how inefficient T-SQL statements can affect performance, both by slowing response times and by using system resources that could have been used by other processes. We'll first look at an example of using Query Analyzer to view and modify the execution plan for a T-SQL statement. As mentioned, by modifying your T-SQL statements, you might be able to achieve better performance for them. In many cases, you can create a more efficient, functionally equivalent T-SQL statement. Then we'll look at progressively more complex estimated execution plans for several types of T-SQL statements.
The examples in the remainder of this section use the Orders table from the Northwind database. Let's review the organization of this table. When we look at the examples, this information will help you determine whether the query optimizer is choosing the appropriate execution plan. The Orders table has a clustered index named PK_Orders on the OrderID column and has eight other indexes, as shown in the Manage Indexes dialog box in Figure 35-6. (To access this dialog box by using Enterprise Manager, expand a server group, expand a server, expand Databases, expand the Northwind database, and click the Tables folder. Right-click the Orders table in the right-hand pane, point to All Tasks in the shortcut menu, and then choose Manage Indexes. Or simply choose Manage Indexes from the Query Analyzer Tools menu, and choose the Orders table from the drop-down menu.)
Viewing the Plan for and Modifying a SELECT Statement
In this section, we'll look at a query that requests information about orders that have been placed by an employee whose employee ID is 4. The query is shown here:
SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE EmployeeID = 4
In this employee's organization, each employee handles a small portion of the total orders, so you might expect SQL Server to use the EmployeeID index when processing the query. Instead, Query Analyzer informs you that the access method SQL Server will use is the PK_Orders clustered index, as shown in Figure 35-7.
To direct the query optimizer to use the EmployeeID index instead, you can use a hint with the SELECT statement, as shown in the following code. (Hints are discussed in the section "Using Hints" later in this chapter.)
SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WITH (INDEX(EmployeeID)) WHERE EmployeeID = 5
NOTE
In Microsoft SQL Server 7, the preferred index hint was INDEX=index_name. With SQL Server 2000, the index hint INDEX(index_name) is preferred.
By providing this extra information in the command, you instruct the query optimizer to use the execution plan that you want, rather than the one that the query optimizer has chosen for you. The adjusted Estimated Execution Plan pane is shown in Figure 35-8. As you can see from the data access method displayed, the EmployeeID index will be used as the input to a bookmark lookup, which will then retrieve the data from the database. (A bookmark lookup searches for an internal identifier for a row of data.)
The query optimizer is an efficient tool and constantly updates statistics to enable it to choose the best execution plan. But because you understand your organization and your data, in some cases, you might be better equipped than the query optimizer to choose the best execution plan.
CAUTION
When you use a hint to override the query optimizer, you do so at your own risk. Although there is little or no danger of data loss or corruption, you can adversely affect your system's performance.
Viewing the Plan for a Join Operation
Performing a join operation involves many more processes than does performing a select operation, as you will see in the Estimated Execution Plan pane shown later in this section. A join operation involves several table accesses followed by the combining of the retrieved data. (Joins are discussed in Chapter 14.) An example of a statement specifying a join operation is shown here:
SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName, LastName, OrderDate FROM Orders JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
The preceding statement includes the SQL-92 JOIN operator. Using this operator is the recommended way to perform joins in SQL Server 2000. The following statement uses the more traditional join syntax, which SQL Server still supports:
SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName, LastName, OrderDate FROM Orders, Employees WHERE Orders.EmployeeID = Employees.EmployeeID
Either of these T-SQL statements joins the Orders and Employees tables on the EmployeeID column. The resulting estimated execution plan is shown in Figure 35-9.
In this pane, you can see which of the two subtrees has a greater cost. You can also see the type of join operation planned. SQL Server supports several join operations, including hash joins, nested loops joins, and merge joins. With a complex join operation, the execution plan can be quite complicated. (Query Analyzer will adjust the size of the Estimated Execution Plan pane to accommodate the number of branches needed.) Because your goal is to reduce the amount of CPU time as well as the number of I/O operations, you should try to determine whether a better execution plan can be used. In some cases, you can use a hint to specify that you want to use a particular index, thus reducing both CPU and I/O activity. You can also use hints with table joins. For the query shown in Figure 35-9, because the join is the only operation specified in the FROM clause, the execution plan shown is probably the best.
Viewing the Plan for an Aggregate Operation
The T-SQL statement shown here performs not only a join operation but also an aggregate operation:
SET QUOTED_IDENTIFIER ON GO SELECT CustomerID, SUM("Order Details".UnitPrice) FROM Orders JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID GROUP BY CustomerID NOTE
Because the table name Order Details contains a keyword and a space, the option SET QUOTED_IDENTIFIER ON must be used. This allows the table name Order Details to be specified in double quotes. For more information about this option, look up "SET QUOTED_IDENTIFIER" in the Books Online index.
The Estimated Execution Plan pane for this complex operation is shown in Figure 35-10.
Viewing the Plan for a Stored Procedure
To view the execution plan of a stored procedure, you simply invoke that stored procedure from Query Analyzer. Query Analyzer will display the estimated execution plan for the stored procedure you are calling. Figure 35-11 shows the plan for sp_who. (Notice that the execution plan for this commonly used stored procedure is quite complex.) You can view the execution plan for a stored procedure without having to know which T-SQL statements make up the stored procedure.
Using Object Browser
Object Browser is an enhancement to Query Analyzer included in SQL Server 2000. When you start up Query Analyzer, you will see Object Browser in the left-hand side of the window. Object Browser is split into two sections, the database objects section and the common objects section. In the database objects section, you can browse objects such as tables and views. In the common objects section, you can conveniently access system objects and functions. You should explore Object Browser to find out what information it provides and then determine what you can use.
Database Objects
The top portion of Object Browser contains the database objects. You can immediately see the default databases and any databases that you have created, shown under the SQL Server system that they belong to. In order to see what information is available within Object Browser, simply expand objects. Let's expand the Northwind database and then expand User Tables. Now you can see the tables available within the Northwind database. This is shown in Figure 35-12.
You can next expand a user-defined table and then expand folders containing information about columns, indexes, constraints, dependencies, and triggers. Figure 35-13 shows the Orders table expanded. Or you can expand the appropriate folders to view information about system tables, views, stored procedures, functions, and user-defined data types.
Having database object information available within Query Analyzer is quite convenient because it allows you to create SQL statements and stored procedures without having to look up object information outside Query Analyzer. Not only can you view information in Object Browser, but you can also edit objects, drag and drop objects, and even script object creation and object modification. These features add functionality to an already useful tool.
Common Objects
In the lower part of Object Browser is a folder named Common Objects (shown in Figure 35-14). Within this folder are folders containing information about objects such as configuration functions, cursor functions, date and time functions, and mathematical functions. You can thus quickly access a function without having to look up its syntax.
If you expand a folder in this area, you will be presented with configuration functions, as shown in Figure 35-14. You can drag these functions to the Query pane, or, by placing the mouse pointer over the function, you can see a brief description. This is convenient for ad-hoc query processing.
In addition to accessing the global variables, you can access many other useful shortcuts to functions such as mathematical and string functions. By continuing to expand objects, you can get information about the parameters required by these functions, for example. Figure 35-15 shows the Parameters folder of a mathematical function expanded.
EAN: N/A
Pages: 264