Ms SQL Server 2000 administrator manual series-35. use SQL query analyzer and SQL profiler

35. use SQL query analyzer and SQL profiler
Use SQL query Analyzer
Use SQL profiler
Optimize T-SQL statement
Summary
In this chapter, we will continue with the description of the pre-stored procedures in Chapter 21st. This chapter describes how to use SQL query analyzer and SQL profile to analyze pre-stored programs and other statements. From the analysis, you can determine whether the T-SQL declarative is efficient. An effective SQL Server query uses the appropriate operation order and appropriate indexes to reduce the number of rows and columns in the process and minimize the number of I/O operations.
Query analyzer allows you to view the execution plan of SQL Server query optimizer for the T-SQL declarative selection. Query Optimizer is a module used to find the best execution plan for each T-SQL statement. Query Optimizer analyzes each T-SQL statement, considers a large number of possible execution plans, and calculates the resource consumption for each plan based on the required resources and program time. Select the plan that does not consume resources. The resource consumption of each plan depends on the statistical results collected by the system, but these data are not necessarily the latest. Because you may know more about the database and data content than query optimizer, you can decide a better plan than query optimizer. Using the information provided by query analyzer, you can determine whether the plan provided by query optimizer to the declarative plan is efficient. If you are sure it is not good enough, try modifying the T-SQL statement or using the SQL prompt to optimize the statement. This chapter will learn how to use query analyzer and how to optimize T-SQL statements.
Using profiler can analyze the internal activities of the SQL server system to determine which SQL statements and pre-stored programs are used to burden system resources. With this information, you can first adjust these statements and pre-stored procedures. In addition to learning how to use profiler, this chapter also describes how to use the information provided by Profiler most effectively.
Use SQL query Analyzer
 
The query analyzer tool provided by Microsoft SQL Server 2000 replaces interactive SQL in Windows (iSQL/W) as an SQL graphical interface. However, you may notice that the query analyzer is isqlw.exe in the task administrator. You can use query analyzer to process T-SQL statements and view the results of these statements. Query analyzer can also be used as a debugging tool to evaluate the execution plan produced by the query optimizer statement for the T-SQL query optimizer.
Execute T-SQL statement
 
The most basic function of query analyzer is to execute T-SQL statements and display declarative results. Follow these steps to execute the T-SQL statement with query Analyzer:
1. Click Start/ASSEMBLY/Microsoft SQL Server/query analyzer. The online SQL Server dialog box is displayed, as shown in Figure 35-1. Use this dialog box to connect to the SQL server system.
 
 
Figure 35-1 online to SQL Server dialog box
2. type the server name in the SQL Server text box. It can be the name of the local server or remote server. Select a local server to be connected. The black spots in 35-1 are displayed. Check box under SQL Server to specify whether to enable the server when the system is not running. In the online use area, select the verification method used for online SQL Server. If you choose to use Windows Account Verification, you do not need to specify the user name or password, because Windows 2000 account will be used to verify access to SQL Server. If you choose to use SQL Server account authentication, you must specify the SQL Server user name and password to access SQL Server.
3. Click OK to connect to the specified SQL Server and start query analyzer. When the query analyzer window appears, only the query and view Pane appears, but the window changes once the T-SQL statement is transferred. The maximum query pane fills up the entire query analyzer window, as shown in 35-2. Select the database to be queried from the drop-down list in the tool column. Figure 35-2 shows that the master database is selected. In this example, the northwind database is selected in the drop-down list.
 
 
Figure 35-2 SQL query analyzer window
4. After selecting the database, type the T-SQL statement in the right pane, in this example, type select * from MERs 』. Now there are multiple options available, you can check the T-SQL declarative syntax by clicking the profiling query button on the toolbar (blue checkmark, you can also click the query button (the green triangle to the right) to execute the statement. Click the cancel query execution button (square) to stop query execution. Figure 35-3 shows a complete query of the northwind database's MERs data table.
Once the T-SQL statement is passed, query analyzer creates a pane that allows you to scroll vertically and horizontally to view results, as shown in 35-3. Query analyzer can also be used to help you adjust your T-SQL statement, which you will see in the <optimize T-SQL Statement> section of this chapter.

 
 
Figure 35-3 complete query analyzer Query
View execution plans and modify T-SQL statements
 
Query analyzer can also be used to view an execution plan, which is the choice that query optimizer makes for your T-SQL statement. This feature helps you determine whether the T-SQL declarative is efficient and determine which execution path and data access path should be selected. You can then modify the T-SQL declarative and database architecture to check for improved performance. To use query analyzer to view T-SQL declarative evaluation execution plans, follow these steps:
1. after you type the T-SQL statement that requires query analyzer evaluation in the query analyzer window, press the show evaluation execution plan button (this button is on the right of the database select drop-down list), or press Ctrl + L, display the execution plan pane of the evaluation, 35-4. In this pane, the query is graphically described. The consumption and data access methods of each operation are displayed here. In the pane of figure 35-4, the index name customers. pk_customers is displayed, indicating that the Cluster Index customers. pk_customers is used to access data.
 
 
Figure 35-4 Evaluation execution plan pane
2. Additional data is also provided in the execution plan pane of the evaluation. To view additional data for an operation, move the cursor over the operation icon. A pop-up window appears, containing additional data, as shown in Figure 35-5.
 
 
Figure 35-5 view additional operation data
This pop-up window contains the following information:
O operations performed by an object job query, such as index scan, join, and total. If the entity action is displayed in red, it is a warning from query optimizer, And you should fix the T-SQL statement.
 
O estimate the number of columns retrieved by the operation.
 
O estimate the size of the data column to be retrieved.
 
O estimated I/O consumption/estimated CPU consumption is the estimated I/O resource and CPU time occupied by this operation. The lower the value means the more efficient the T-SQL statement.
 
O estimated executions the number of executions performed in the T-SQL statement.
 
O estimated consumption is determined by query optimizer. This consumption shows the total consumption Percentage of the T-SQL statement.
 
O The estimated subtree consumed time to execute the consumption of the previous part and this part of the T-SQL declarative. If multiple subtree exist, this option allows you to view the consumption of each subtree.
 
The independent variable used by the T-SQL statement.
 
________________________________________
Description
The execution plan describes how query optimizer selects the execution T-SQL statement and includes the sequence of steps and the type of operation in use. Data access method is an object that describes how database objects (such as data tables and indexes) are accessed. These two are related because sometimes data access methods are considered part of the Execution Plan. Of course, you can also consider it separately.
________________________________________
The following describes a complex example of using query analyzer. Example shows the impact of inefficient T-SQL declarative statements on performance: slowing down response time and consuming system resources for other programs. Now let's look at an example of using query analyzer to view and modify a T-SQL declarative execution plan. Modifying the T-SQL statement can achieve better performance. In many cases, a more effective and functional T-SQL statement can be established. The following describes several T-SQL statements that fall into a more complex type of prediction execution plan.
These examples use the orders data table in the northwind database. View the organization of the data table. This information helps determine whether the query optimizer selects an appropriate execution plan. The orders data table has a cluster index named pk_orders in the orderid column. There are also eight other indexes, as shown in the manage indexes dialog box in 35-6. (To access this dialog box, expand a server group in Enterprise Manager, expand a server, expand the database data folder, expand the northwind database, and select the data folder of the data table. Click the right button on the orders data table in the right pane, select all jobs from the shortcut menu, and then choose manage indexes. Alternatively, you can choose manage indexes directly from the query analyzer tool menu, and then select the orders data table from the drop-down menu .)

 
 
Figure 35-6 index management dialog box
View plans and modify select statements
 
In this section, the example of this query requires information about orders, which are stored by employees with employee ID 4. Enter the following query in qery analyzer and execute the execution plan showing the evaluation:
Select orderid, customerid, employeeid, orderdate
From orders
Where employeeid = 4
In the organization of the employee, each employee processes a small part of the order. Therefore, SQL Server may need to use the employeeid index for query. However, query Analyzer displays the pk_orders cluster index used by SQL Server, as shown in the execution plan pane of the evaluation in 35-7.

 
 
Figure 35-7 The evaluation execution plan pane shows the use of the pk_orders Cluster Index.
To query optimizer using the employeeid index, you can use the SELECT statement prompt (hint). The program code is as follows (tips will be discussed in the <usage tips> section in this chapter .) :
Select orderid, customerid, employeeid, orderdate
From orders with (index (employeeid ))
Where employeeid = 4
________________________________________
Description
In SQL Server 7, Index = index_name is a good index prompt.
In SQL Server 2000, index (index_name) is a good index prompt ).
________________________________________
This information instructs query optimizer to use the desired execution plan, rather than the plan selected by query optimizer. The adjusted evaluation execution plan pane is shown in Figure 35-8. You can see from the displayed data access method that the employeeid index is used, and then a bookmark lookup is returned. This query will  from the database. (Bookmarks are used to search for internal identifiers of a column of data .)

 
 
Figure 35-8 adjusted plan pane for evaluation execution
Query Optimizer is an efficient tool to continuously update statistics and provide the best execution plan for selection. Because different company lines may be more clear about their organization and data, in some cases, companies can choose the best execution plan that is more efficient than query optimizer.
________________________________________
Note:
When you use the prompt to replace the query optimizer selection, you must bear the risks that may arise from the change. Although data loss or damage is unlikely, it may have a negative impact on the system's execution efficiency.
________________________________________
View Join Operations
 
The join operation is performed more than the select operation. You will see it later in the Plan pane of the evaluation execution. The Join Operation accesses several data tables and combines the searched data after access. (Link is discussed in Chapter 14th ). Enter the following Join Operation example in query Analyzer:
Select orderid, customerid, employees. employeeid, firstname,
Lastname, orderdate
From orders join employees on orders. employeeid = employees.
Employeeid
The previous statement includes the SQL-92 join operator element. We recommend that you use this operation element to execute connections in SQL Server 2000. The following statement uses a more traditional link Syntax:
Select orderid, customerid, employees. employeeid, firstname,
Lastname, orderdate
From orders, employees
Where orders. employeeid = employees. employeeid
The T-SQL statement Concatenates the orders and employees tables in the employeeid row. The results of the evaluation execution plan are shown in Figure 35-9.

 
 
Figure 35-9 shows join operations in the Plan pane for evaluation execution
In the pane, You can see which of the two subtree consumes a lot, or the type of the planned join operation. SQL Server supports a variety of join operations, including hash join, nested loops join, and merge join ). Complex join operations make execution plans relatively complex. Because you want to reduce the total CPU usage and the number of I/O operations, you must determine whether a good execution plan is used. Sometimes you can use prompts to specify the Special Indexes to be used to reduce CPU and I/O behavior. In this query, because join is the only operation specified in the WHERE clause, this execution plan should be the most suitable.
View total operations
 
The T-SQL statements shown below perform join and total operations, enter in query Analyzer:
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
________________________________________
Description
Because the table name order details contains a keyword and a space, you must use the set quoted_identifier on option. For more information about this option, see set quoted_identifier in the online books index.
________________________________________
The plan pane for the evaluation execution of this composite operation is shown in Figure 35-10.

 
 
Figure 35-10 shows the total actions in the evaluation execution plan pane
View stored programs
 
To display the execution plan of the pre-stored program, you can simply call the pre-stored program in query analyzer. Query Analyzer displays the evaluation execution plan for the called stored program, sp_who shown in 35-11. (It should be noted that the execution plan of the pre-stored program is quite complex ). Even if you're not sure about the T-SQL statement that makes up a stored program, you can display the execution plan for that stored program.

 
 
Figure 35-11 pre-stored programs in the evaluation execution plan pane
Use Object Browser
 
The Object Browser is included in SQL Server 2000 to improve the query analyzer function. When you start query analyzer, the Object Browser is displayed in the left window. The Object Browser is divided into two parts: the database object area and the common object area. In the database object area, you can browse objects such as data tables and views. In the common object area, you can access system objects and menus. You can use the Object Browser to find the provided information and then decide the information to use.
Database objects
 
The top part of the Object Browser contains database objects. You can view the preset database and the created database immediately and display the SQL Server to which it belongs. To know available information about object browsing, just expand the object. Expand the northwind database, and then expand the user data table. The available data tables in the northwind database are displayed, as shown in Figure 35-12.

 
 
Figure 35-12 View data tables in object browsing
Expand a user-defined data table and a data folder containing fields, indexes, condition constraints, resource dependency, and trigger program information. Figure 35-13 shows the expanded orders data table. Alternatively, you can expand the data folder to view information about system data tables, views, pre-stored programs, functions, and user-defined data types.
It is quite convenient to provide object information in query analyzer. In this way, when creating SQL statements and pre-stored programs, you do not need to check object information other than query analyzer. You can not only view information in object browsing, but also edit objects, drag objects, and even set up script codes and modify objects. You can add functions to a useful tool.

 
 
Figure 35-13 expand data tables in object browsing
Common Object
 
The lower part of object browsing is the data folder named common objects, this data folder contains object information such as the setting function, cursor function, date and time function, and mathematical function. Therefore, you can quickly access a feature to be used without the need to query the syntax. If you expand the data folder in this area, the setting options are displayed, as shown in Figure 35-14. Drag these functions to the query pane, or click this function to display a brief description. This is quite convenient for the ad-hoc query process.

 
 
Figure 35-14 expand a data folder in a common object browsed by an object
In addition to accessing global variables, you can also access many other useful shortcut menus, such as mathematical and string functions. Continue to expand the object. You can use these menus to obtain the parameter requirements. For example, figure 35-15 shows the parameter data folder in the expanded mathematical function.

 
 
Figure 35-15 view the parameter data folder in the object browsing
Use SQL profiler
 
In addition to query analyzer, the SQL profiler tool can also help identify inefficient T-SQL statements. Profiler displays the T-SQL statements executed in all systems and graphically displays them. You can also use multiple sorting and filtering options to find the T-SQL statement that uses up to CPU and I/O resources. With this information, you can determine which T-SQL statements focus on when tuning the system. For T-SQL statements that call through applications, you can view T-SQL statements and determine how efficient you are when using access to application source code.
The profiler tool in SQL Server 2000 is similar to the profiler tool in SQL Server 7. Of course, it has some enhanced functions. One of the new features is to reference a tracing template to create a Tracing file. (Tracing can be established only after you confirm that you can use this function to track SQL Server ). In SQL Server 7, the tracing function can only be created manually.
To call the profiler tool and perform tracing, follow these steps:
1. Click Start/ASSEMBLY/Microsoft SQL Server/profiles. When profiler appears, an empty profiler window is displayed. If no pane is opened in profiler, no statement is parsed.
2. To start analysis, You must select an existing tracing mode or create a new tracing mode. (The startup process is described in Step 4 ). SQL profiler provides several trace modes. Using these features saves a lot of time because you do not have to create tracing from scratch. To check the tracking list, click "enable old file" in the File menu and select a Tracking template. The "enable old file" dialog box is displayed, as shown in Figure 35-16.
 
 
Figure 35-16 available tracing is displayed in the "enable old file" dialog box
The tracing description in SQL Server is as follows:
O sqlserverprofilersp_counts.tdf: count the number of stored programs that have been executed. This result will be grouped based on the name of the pre-stored program, including the number of times the program is executed.
 
O sqlserverprofilerstandard. TDF collects connection node information, executes the pre-stored program, and executes SQL batch commit in sequence.
 
O sqlserverprofilertsql. TDF collects the order in which all T-SQL statements are delivered to SQL Server and are transmitted by user group classification. This trace contains the T-SQL statement and the time it was executed.
 
O sqlserverprofilertsql_duration.tdf displays the T-SQL statement that is being executed, and the time (in 1/1000 s) that these T-SQL statements are executed ).
 
O sqlserverprofilertsql_grouped.tdf collects data similar to sqlserverprofilertsql tracing, but groups data based on declarative users.
 
O sqlserverprofilertsql_replay.tdf provides detailed information about the statements of the executed T-SQL. The trail provides data that can be used to re-Execute T-SQL statements in query analyzer.
 
O sqlserverprofilertsql_sps.tdf displays the T-SQL commands in the pre-stored program and the pre-stored program. The result is displayed in sequence based on the declarative execution time.
 
O sqlserverprofilerprofilertuning. TDF collects data about pre-stored programs and executed SQL batch files.
 
These traces are quite useful. For example, sqlserverprofilertsql_duration tracing can find out which T-SQL statements take a lot of execution time. This information allows you to start optimizing queries. A declarative statement is executed slowly, probably because it is heavy or inefficient. The default definition for each trace must be used as described in the next step.
3. To start tracing, select a file/Add, and then select tracing. The "online to SQL Server" dialog box is displayed, as shown in Figure 35-17. In this dialog box, select the SQL Server System to be tracked, and then press OK.
 
 
Figure 35-17 online to SQL Server dialog box
4. Enter the tracking Properties window, as shown in Figure 35-18. On the General tab, you can name and select the tracing start point. In this example, select sqlserverprofilertsqlduration tracing. In the tab, you can specify whether to input the obtained tracing content to the file or SQL server data table. If these options are not selected, the tracing will only appear on the screen. In addition, you can specify the tracking completion time, which is quite useful for long-time tracking.
 
 
Figure 35-18 General tab of the tracing Properties window
5. Select the event tab, as shown in Figure 35-19. You can select one or more events that have been tracked. A large number of types and special events can be tracked, and data listed in the list of available event categories can be tracked.
 
 
Figure 35-19 event tab in the tracking Properties window
6. Select the event to be tracked and click the data line tab, as shown in Figure 35-20. On this tab, specify the data to be collected during the tracing process. This data includes the end time and Object ID.
 
 
Figure 35-20 Data row tab in the tracking Properties window
7. Select the Filter tab, as shown in Figure 35-21. In this tab, you can specify whether to capture the conditions required by the event. For example, exclude tracing profiler (default ). By eliminating the SQL server process, you can avoid confusion in the profiler window and increase readability.
 
 
Figure 35-21 Filter tab of the tracking Properties window
8. After setting options are completed, follow the instructions to start tracing. If you want to modify tracing, we recommend that you use the option of saving new files in the archive menu to save tracing modification (with different names ). Once tracking starts, the event will appear in the profiler window. Using the trail selected in this example, events are sorted by duration (one thousandth of a second ). Figure 35-22 shows the trace in execution in the profiler window.
________________________________________
Note:
In a busy environment, profiler may use important system resources. The more events are tracked, the more system resources are occupied.
________________________________________
Optimize T-SQL statement
 
In the previous section, we learned the T-SQL statements executed in the profiler view system and the use of query analyzer to determine execution plans and data access. You can also improve performance by modifying the T-SQL statement. With these tools, you have the ability to modify the T-SQL statement to improve the effectiveness of statement execution. In this section, you will see multiple ways to optimize the T-SQL declarative so that it can provide better performance or consume less system resources.

 
 
Figure 35-22 trace in execution
Optimize execution plan
 
It is quite difficult to modify the execution plan, and it is more difficult to create an execution plan that is better than the query optimizer plan. Some operations are more likely to benefit from modifications to the execution plan. They are join, group by, order by, and union. Modifications to these operations can be easily achieved through prompts, which will be mentioned later in the <tips> section. By changing the prompt and displaying the output of query analyzer, you can see a more effective operation.
However, there is no particular pattern for an optimized T-SQL statement. Because each database is unique and applications are different, modifications are made based on different situations.
Select a data access method
 
As described earlier in this chapter, the data access method is actually a set of objects that SQL server uses to  from the database. By analyzing the data contained in databases and databases, You can optimize the data access methods to reduce the number of I/O operations.
Like modifying an execution plan, there is no formula for modifying the best data access method. The following guidelines help you select the best data access method:
• Use the best index to use the best index for an operation, which is required to achieve the best possible performance. The best index for a specific operation is the fastest way to find data and at least I/O operations. You can use your in-depth understanding of the database and data or use query analyzer to confirm the best index. Query analyzer allows you to try different simulated conditions to determine which index can return the least number of columns. (Remember, query analyzer simply estimates the number of columns to be returned. to determine the exact number of columns, you must use profiler .)
 
________________________________________
Description
As mentioned in chapter 17th, indexing has great benefits for SQL Server, but if it is used incorrectly, it may in turn affect the efficiency. Monitors the number of indexes for each data table, especially when many insert, update, and delete statements are performed. Too many indexes may reduce the efficiency of such operations. This is caused by the occupation of additional system resources to modify the index.
________________________________________
• Using covered indexes, as described in chapter 17th, helps you avoid additional I/O steps. You do not have to access the underlying data table. You can obtain the required data from the index.
 
• Reduce the number of returned data columns to determine whether to return all actually needed data from the query. Modify the T-SQ l declarative so that only the data to be accessed is accessed, and do not return the discarded data column. Reduce the data columns obtained from the database, which can be achieved by increasing the query selectivity.
 
Usage tips
 
You can modify the T-SQL statement to change the data access method and execution plan, but if the execution is not careful enough, it will change the T-SQL statement function. The safer way to optimize the T-SQL declarative is to use the prompt. The prompt specifies the operations to be performed and the objects to be used by query optimizer. In this section, we will learn many different SQL Server prompts and their usage methods.
Connection prompt
 
Join hints is used to specify the types of Join Operations that query optimizer should perform. (If NO type is specified in the query, query optimizer selects one .) In SQL Server, You can execute nested loops joins, hash joins, merge joins, and remote joins ). Use the following prompt to specify the join method:
• Loop specifies the nested loop link. In nested loop join, each column in the external data table is checked and each column in the internal data table is checked, and the value is equal.
 
• Hash specifies the collection and join. In a collection join, a data table is reorganized into a collection data table. Each time other data tables are scanned for a column, the aggregate function is used to search for the same content.
 
• Merge specifies a sort merge join. In the sort and merge joins, each data table is sorted and then a column is compared in descending order.
 
• Remote specifies remote connection. The remote connection has at least one joined data table remotely.
 
Let's take a look at the example of the link prompt and use our previous example (see the <Join Operation> section in this chapter) to specify a collection join as prompted in the following statement:
Select orderid, customerid, employees. employeeid, firstname,
Lastname, orderdate
From orders, employees
Where orders. employeeid = employees. employeeid
Option (hash join)
________________________________________
Description
The join prompt is independent of each other-only one of them can be used at a time.
________________________________________
If you choose to use the SQL-92 syntax as a link, you can also specify the link type with a prompt. With the SQL-92 syntax, You can override the previous query as follows:
Select orderid, customerid, employees. employeeid, firstname,
Lastname, orderdate
From orders inner hash join employees
On (orders. employeeid = employees. employeeid)
The connection prompt is an advanced topic, and we cannot provide practical experience. There are many different reasons for selecting special join operations, such as the number of equal operators, the size of each data table in the join, and the number of joined data tables. There is an optimal way to determine whether changing the join operation will provide additional performance, that is, to try each type of join in query analyzer to see which one can provide minimum consumption. Of course, query optimize R usually selects the best join operation for help.
Query prompt
 
Query hints is used to specify how to perform specific query operations. Available query operations include group by, union, and miscellaneous ).
The following prompt in the group prompt specifies how to perform the group by or compute operation:
• The hash group by function is used to execute the group by operation.
 
• Order Group by specifies to use the sort operation to perform the group by operation.
 
Use the preceding group by example (see <view total operations> in this Chapter). Follow the instructions below to specify how to perform the hash group by operation:
Select customerid, sum (orderdetails. unitprice)
From orders, orderdetails
Hash group by customerid
Option (hash Group)
________________________________________
Description
Group by prompts independent of each other-only one of them can be used at a time.
________________________________________
The following prompt is used to specify how to perform the union operation:
• Merge Union uses the merge operation to execute union.
 
• Hash Union uses the aggregate function to execute union.
 
• Concat Union uses the concatenation function to execute union.
 
This is an example of the Concat Union prompt:
Select orderid, customerid, employeeid, orderdate
From orders
Where customerid = 'tomsp'
Union
Select orderid, customerid, employeeid, orderdate
From orders
Where employeeid = '4'
Option (Concat Union)
________________________________________
Description
The Union prompt is independent of each other.
________________________________________
Unfortunately, there is no certain formula to determine which union operation is best performed in your environment. Once again, the best way is to use query analyzer to try different Union prompts to see which one can provide the minimum consumption. Generally, SQL Server query optimizer can determine the optimal policy for the Union prompt.
Miscellaneous prompts the following prompts can be used to perform a variety of query operations:
• Force Order: The query is executed in the order that the data table appears in the query. In the preset state, SQL Server can re-Sort data tables for access.
 
• Robust plan forces query optimizer to prepare the most likely maximum number of data columns.
 
The following is an example of using this prompt:
Select orderid, customerid, employees. employeeid, firstname,
Lastname, orderdate
From orders, employees
Where orders. employeeid = employees. employeeid
Option (robust plan)
Data Table prompt
 
Table hints is used to control data table access. The two data tables are shown as follows:
• Fast n replaces fastfirstrows to maintain backward compatibility. Optimize the query to obtain the data in the top n columns.
 
• Index = index_name force query optimizer to use the specified index when possible. One of the previous examples in this chapter demonstrates how to use the index prompt:
 
• Select orderid, customerid, employeeid, orderdate
• From orders with (Index = employeeid)
• Where employeeid = 5
Option (fast 10)
With is selective.
The Index = employeeid prompt here indicates that the employeeid index will be specified. By specifying fast 10, SQL Server optimizes the acquisition of the first 10 columns (if possible) and returns the remaining columns.
Summary
 
In this chapter, you learned how to use query analyzer to determine the best execution plan and data access method for queries. You also learned how to use profiler to view the T-SQL statements executed in the system, and how to execute tracing to determine if some of the T-SQL statements may cause performance problems. We also discussed how to optimize execution plans and database access methods based on your database and data. Finally, you learned how to use tips to specify a specific execution plan or data access method. In Chapter 36th, the theme you learn will evolve into performance problems and how to solve performance problems.