35. Using SQL Query Analyzer and SQL PROFILER
Use SQL Query Analyzer
Use SQL PROFILER
Best T-SQL statement
Summary of this chapter
This chapter we will continue in Chapter 21 for instructions on pre-deposit procedures. This chapter will learn how to use SQL Query Analyzer and SQL PROFILE to analyze pre-departments and other statements. From the analysis, you can determine if the T-SQL statement is efficient. A valid SQL Server query uses the appropriate operational order and the appropriate index to reduce the number of rows of processes and minimize I / O operands.
Use Query Analyzer to see SQL Server Query Optimizer for the execution plan for T-SQL statement. Query Optimizer is a module used to find the best implementation plan for each T-SQL statement. Query Optimizer analyzes each T-SQL statement, consolidates a large number of possible execution plans, and calculates the resource consumption of each plan according to the resources and procedures required. Choose a plan that best consumes resources. Each plan's resource consumption is determined to collect statistics on the system, but these materials are not necessarily the latest. Because you may know more about the database and data more than Query Optimizer, you can decide better than Query Optimizer. Using Query Analyzer, you can determine whether Query Optimizer is provided to the statement of the plan. If it is determined that it is not good enough, try to modify the T-SQL statement or use SQL tips to optimize the statement. This chapter will learn how to use Query Analyzer, and how to optimize T-SQL statements.
Use PROFILER to analyze internal activities in SQL Server system to determine which SQL statements and pre-depreciation use cause system resources burden. With this information, you can first adjust these statements and pre-departments. In addition to learning how to use PROFILER, this chapter will also explain how to use the information provided by Profiler.
Use the Query Analyzer tool provided by SQL Query Analyzer Microsoft SQL Server 2000 to replace Interactive SQL in Windows (ISQL / W) to become a SQL graphics interface. However, you may notice that Query Analyzer is displayed in the task administrator is isqlw.exe. You can use Query Analyzer to process T-SQL statements and view these statements. Query Analyzer can also be treated as an inner tool to evaluate the execution plan generated by Query Optimizer for the T-SQL Query Optimizer statement. Performing T-SQL State Query Analyzer The most basic function is to perform T-SQL statements and display the statement results. According to the following steps, use Query Analyzer to perform T-SQL statement:
Click Start / Program Set / Microsoft SQL Server / Query Analyzer, display the connection to the SQL Server dialog, as shown in Figure 35-1. Use this dialogue and SQL Server system.
Figure 35-1 Connection to SQL Server dialog
Type the server name in the SQL Server text block. It can be the name of the local server or remote server. Choose a local server you want to connect to display the black point as shown in Figure 35-1. The verification block under SQL Server can specify whether the server is to open the server when the system is not operating. Select the verification method used to connect SQL Server in the wiring area. If you select an account verification using Windows, you don't have to specify the user name or password because the account of Windows 2000 will be used to verify accessing SQL Server. If you choose to use SQL Server account verification, you must specify the SQL Server user name and password to access SQL Server. Press OK to connect to the specified SQL Server and start Query Analyzer. When the Query Analyzer window appears, you can only see the query and visit pane, but once the T-SQL statement is started, the window will change. Maximizing the Query pane fills the entire Query Analyzer window, as shown in Figure 35-2. Select the database you want to perform in the drop-down list of tool columns. Figure 35-2 Display Selecting the Master Database, this example will be selected in the drop-down list. Figure 35-2 SQL Query Analyzer Window
After selecting the database, type the T-SQL statement in the right pane. In this example, type "Select * from Customers". Now there are multiple options available, you can check the syntax of the T-SQL statement, or press the execution query button (the green triangle of the right) to perform the query button (the green triangle of the right) to perform the statement. formula. Press Cancel Query Execution Button (square) to stop the execution of the query. Figure 35-3 shows a complete query for the Customers data sheet for the Northwind repository. Once the T-SQL statement is transmitted, Query Analyzer will create a pane to view the results vertically and horizontally, as shown in Figure 35-3. Query Analyzer can also be used to help you adjust your T-SQL statement, you will see in the Figure 35-3 Complete Query Analyzer inquiry View execution plan and modify T-SQL statement Query Analyzer can also be used to review the execution plan, this execution plan is the choice made by Query Optimizer for your T-SQL statement. This feature helps you determine if the T-SQL statement is efficient and determines which execution path and data access path should be selected. Then you can modify the T-SQL statement and the database architecture, check whether the effectiveness is improved. To use Query Analyzer to view the T-SQL statement, follow these steps: After the Query Analyzer window is typed, the T-SQL statement that requires Query Analyzer evaluation, press the execution plan button to display the evaluation (this button selects the right side of the Database selection drop-down list), or press Ctrl L to display the execution plan of the evaluation The pane, as shown in Figure 35-4. In this pane, the query is described through the graphic, and the consumption of each operation is displayed here. In the pane of Figure 35-4, an index name Customers.pk_customers appears, indicating that the cluster index Customers.pk_customers is used to access the information. Figure 35-4 Evaluation Implementation Plan Other additional information is also available in the implementation plan pane of the assessment. To view the additional information of the operation, move the mouse cursor to the operation image. A pop-up window will appear, including additional information, as shown in Figure 35-5. Figure 35-5 Additional information about operations This pop-up window contains the following information: Operations performed by physical job queries, such as index scanning, coupling, and total, and more. If the entity operation is displayed in red, it is a warning issued by Query Optimizer, then you should fix the T-SQL statement. The estimated data list is estimated by the number of columns retrieved by the operation. The estimated data column size requires the estimated size of the column. Estimated I / O consumption / estimated CPU consumption is taken by this operational estimation I / O resource and CPU time. The lower the value means that the T-SQL statement is more efficient. The estimated execution number is performed in the T-SQL statement. The estimated consumption consumption is consumed by the operation determined by Query Optimizer. This consumption is displayed as the total cost of total consumption of T-SQL. The estimated sub-tree consumption time performs the front portion and this part of the T-SQL statement. If there are multiple subtals, this option allows you to view the consumption of each sub-tree. Quotes T-SQL statement uses the quotes used. Description Execution Plan describes how Query Optimizer selects the execution of T-SQL statements and includes the order of the steps and the type used. Data Access Method is an object that describes how the data library object (data sheet, index, etc.) is accessed. Both are associated, because in some, the data access method is considered to be part of the implementation plan. Of course, you can also take a separate consideration. Next, you will see an example of using Query Analyzer. Example shows the impact of low-efficiency T-SQL statement on performance: slow down response time, and occupies system resources for other programs. Now look at an example of using Query Analyzer to review and modify T-SQL statement. Modify T-SQL statement can achieve better performance. In many cases, a more effective and better T-SQL statement can be established. Next, several T-SQL statements will be explained, which belongs to a more complex estimate execution plan. These examples use the ORDERS data sheet in the Northwind database. Now to view the organization of this information sheet. This information will help determine if Query Optimizer has selected the appropriate implementation plan. The ORDERS data sheet has a cluster index called PK_ORDERS in the ORDERID column, and there are eight other indexes, as shown in the Manage Indexes dialog box in Figure 35-6. (To access the dialogue, in Enterprise Manager, expand a server group, expand a server, expand the database folder, expand the Northwind Database, then select the information table data clip. In the right pane, Orders The data table is pressed on the right button and select all the job from the quick function table, then select the management index. Or just select the management index directly from the Query Analyzer tool function table, then select the Orders data sheet from the drop-down function table. .) Figure 35-6 Managing Index Dialogue Viewing the plan and modify the SELECT statement In this section, this query is required to obtain information about orders, and these orders are stored by staff of staff IDs 4. Please enter the following query in Qery Analyzer and perform the execution plan of the display evaluation: Select OrderID, Customerid, Employeeid, OrderDate From Orders WHERE EMPLOYEEID = 4 In the organization of the staff, each staff handles a small part of the order, so SQL Server may need to use the EmployeeID index when the query is inquiry. However, Query Analyzer is displayed by SQL Server uses the PK_ORDERS cluster index, as shown in the execution plan pane of the evaluation in Figure 35-7. Figure 35-7 The execution plan pane of the assessment shows the use of the PK_ORDERS cluster index. To use the EmployeeID index, you can use the SELECT statement prompt (HINT), the program code is as follows (prompt will be discussed in the Select OrderID, Customerid, Employeeid, OrderDate From Orders with (INDEX (EmployeeID)) WHERE EMPLOYEEID = 4 Description In SQL Server 7, the better use index prompt is index = index_name. In SQL Server 2000, the preferred index prompt is index (index_name). Through this information, you can indicate that Query Optimizer uses the required execution plan instead of the Query Optimizer selected by the Query Optimizer. The adjusted evaluation execution plan pane is shown in Figure 35-8. You can see from the displayed data access method, use the EMPLOYEEID index, then return a bookmark search (Bookmark Lookup), which will get information from the database. (Bookmark Search Search for the internal identification code of a list.) Figure 35-8 Plan pane for adjustment after evaluation Query Optimizer is an efficient tool that provides the best implementation plan for constant update statistics. Since different corporate line numbers may be more clear their organization and information, in some cases, the company can choose the best implementation plan than Query Optimizer. note Use the prompt instead of the Query Optimizer selection, you must bear the risk that the change may bring. Although the loss or damage to the data is not large, it may be negative impact on the performance of the system. View joint operation Performing a junction operation is more executable than performing a selection operation, it will be seen in the plan pane of the evaluation execution. The coupling operation will be accessed by several data sheets and the information searched after access. (Discussion in Chapter 14). Please enter the following Joint Operation Examples in Query Analyzer: Select ORDERID, Customerid, Employees.employeid, FirstName, Lastname, OrderDate From Orders Join Employees On ORDERS.EMPLOYEID = Employees. Employeeid Previous statements include SQL-92 JOIN Operations. It is recommended to use this action to perform a link in SQL Server 2000. The following statement uses a more traditional linkage: Select ORDERID, Customerid, Employees.employeid, FirstName, Lastname, OrderDate From Orders, Employees Where orders.employeid = Employees.employeeID The T-SQL statement is coupled in the EmployeeID Data Line to join the Orders and Employees data sheets. The results of the evaluation execution plan are shown in Figure 35-9. Figure 35-9 Displaying the coupling operation of the plan pane executed in the evaluation In the pane, you can see which of the two subtots is large, or you can see the type of planning connection operation. SQL Server supports a variety of different coupling operations, including Hash Join, Nested Loops Join and Merge Join. Complex coupling operations, the implementation plan is relatively complicated. Since your purpose is to reduce the total amount of CPU time, and reduce the number of execution I / O operations, you must judge whether or not to use a good execution plan. Sometimes you can use a prompt to specify a special index you need to use, thereby reducing CPU and I / O behavior. In this query, this execution plan should be the most suitable because the coupling is the only operation specified in the WHERE clause. Viewing Total Operation The T-SQL statement of the T-SQL shown below performs link operations and total operations, please enter in Query Analyzer: Set quoted_identifier on Go Select Customerid, SUM ("Order Details" .unitprice) From Orders Join "ORDER DETAILS" ORDERS.ORDERID = "Order Details ".orderid GROUP BY CUSTOMERID Note Because the data sheet name ORDER DETAILS contains a keyword and a space, you must use the option set quoted_identifier on. To get more information about this option, see the set quoted_identifier in the "Online Series" index. The plan pane of the evaluation execution of this composite operation is shown in Figure 35-10. Figure 35-10 shows the total operation in the plan pane in the evaluation execution Check the pre-depreciation program to display the execution plan of the pre-depressed program, you can simply call the pre-stored program in Query Analyzer. Query Analyzer displays the evaluation execution plan of the calling pre-preserved program, as shown in Figure 35-11, SP_WHO. (It is to be noted that the execution plan of the pre-stored program is quite complicated). Even if you do not confirm the T-SQL statement that makes up the pre-preserved program, the execution plan of the pre-depicting program can also be displayed. Figure 35-11 Preparation in the plan pane of the evaluation execution Using the object browser item browser (Object Browser) is included in SQL Server 2000 to enhance the Query Analyzer function. When you start Query Analyzer, you will see the item browser on the window. The object browser is divided into two parties of the database object area and the universal object area. In the database object area, you can browse objects of information sheets and view tables; system objects and functional tables can be accessed in the general-purpose object area. You can use the item browser to find out the information provided and then decide. Database object object browsing the top part contains a database object, and immediately see the preset database and the established database, and displays the SQL Server. Want to know which information is available in the object browsing, as long as the object is expanded. Expand the Northwind Database first, then expand the user data sheet. You can now see the data sheet available in the Northwind database, as shown in Figure 35-12. Figure 35-12 Viewing the information sheet in viewing object browsing Then expand a user-defined data sheet and then expand the folder containing the field, index, conditional constraints, resource dependencies, trigger program information. Figure 35-13 shows the expanded Orders data sheet. Or you can expand the data clip, see information about the system data sheet, seek watch, pre-deployment, function, and user definition data. In Query Analyzer, it is quite convenient, so it does not need to check item information other than Query Analyzer when establishing SQL statements and pre-departments. Not only can you view information in item browsing, you can also edit objects, drag objects, and even create instruction codes and modify the object, and add functions in a very practical tool. Figure 35-13 Expanding the information sheet in the object browsing Universal objects browsing more than part of the part of the Common Objects, which contains data clips such as setting functions, cursor functions, date, and time functions, and mathematical functions such as setting functions. So you can quickly access the features to use without querying syntax. If you expand the folder in this area, the setting option will be displayed, as shown in Figure 35-14. Drag these features into the Query pane, or use a mouse to select this function to display a brief description. This is quite convenient for the AD-HOC query process. Figure 35-14 Expanding a data clip in the general object browsing in the object In addition to accessing the whole country variable, you can access many other useful quick-see, such as mathematical and string functions. Continue to expand the object, you can use these functional information to get information on parameter requirements. For example, Figure 35-15 shows the parameter folder in the unfolded mathematical function. Figure 35-15 Viewing the parameter folder in the object browsing SQL PROFILER tools can also help identify low-efficiency T-SQL statements in addition to Query Analyzer. PROFILER can display T-SQL statements performed in all systems and display them with graphics. You can also use multiple sorting and filter options to find T-SQL statements that use the most CPU and I / O resources. With these information, you can determine which T-SQL statements are needed when adjusting the system. For T-SQL statements transmitted through the application, you can view T-SQL statements and determine how efficiency when utilizing access application sources. The Profiler tool in SQL Server 2000 is similar to the Profiler tool in SQL Server 7, and of course some strengthened features. One of the new features is to reference tracking models to establish tracking files. (After confirming that you can use this feature to track SQL Server, tracking can be established). In SQL Server 7, tracking features can only be created manually. To call the PROFILER tool and perform track, follow these steps: Press the start / program set / Microsoft SQL Server / Profiles. When PROFILER is just appearing, you will see an empty PROFILER window. If you do not open any panes in Profiler, you will not analyze any statements. To start analysting, you must select an existing tracking mode, or create a new tracking mode. (The startup process will be described in step 4). SQL PROFILER provides several tracking modes. These features are not required to be tracked from Scratch, so they save a lot of time. To check the tracking list, click on the old file in the file function table, select tracking template, display the open old gear dialogue, as shown in Figure 35-16. Figure 35-16 Opens the available tracking in the old file dialogue The tracking description in SQL Server is as follows: SQLSERVERPROFILERSP_COUNTS.TDF Statistics The number of pre-depreciation has been executed. This result will group according to the name of the pre-depreciation, including the number of times executed by the program. SQLSERVERPROFILERSTANDARD.TDF collects information about the connection point, executes the pre-depreciation, and executes SQL batch files sequentially. SQLSERVERPROFILERTSQL.TDF collects all T-SQL statements to transmit to SQL Server and classify according to user groups. This tracking contains T-SQL statements and the time it executes. SQLSERVERPROFILERTSQL_DURATION.TDF Displays the T-SQL statement executed, and the time used in these T-SQL statements (one thousand seconds). SQLSERVERPROFILERTSQL_GROUPED.TDF collects similar information to SQLServerProfilertsql, but the data is packetized according to the transmitted user. SQLSERVERPROFILERTSQL_REPLAY.TDF provides detailed information about the T-SQL statement that has already been executed. This tracking provides information that can be used to re-execute the T-SQL statement in Query Analyzer. SQLSERVERPROFILERTSQL_SPS.TDF Displays the pre-depicting program and the T-SQL instruction in the pre-queer. This result is sequentially displayed according to the time executed. SQLSERVERPROFILERPROFILERTUNING.TDF collects information about the pre-depreciation and executing SQL batch files. These tracking is quite useful. For example, SQLSERVERPROFILERTSQL_DURATION tracking can find which T-SQL statement spends a lot of execution time. This information allows you to start an optimization query. A statement is implemented very slow, it may be because its workload is large, or the efficiency is very low. In the next step, you must use the preset definition of each tracking. To start tracking, select the file / new increase, then select tracking. Display the connection to the SQL Server dialog, as shown in Figure 35-17. In this dialogue, select the SQL Server system you want to track and press OK. Figure 35-17 Wire to SQL Server dialogue block Enter the tracking properties window, as shown in Figure 35-18. At the general tag, you can name and select the starting point of the tracking. This example is to select SQLSERVERPROFILERTSQLDURATION tracking. On the part below the page, you can specify whether you want to enter the tracking content to the file or SQL Server tab. If these options are not selected, the tracking will only appear on the screen. Alternatively, you can specify the time of tracking completion, which is quite useful when performing long tracking. Figure 35-18 Tracking the general page signs of the properties window Then select the event page sign, as shown in Figure 35-19. Here you can select an event of one or more tracking records. A large number of types and special events can be tracked, and the information listed in the available event category can be tracked. Figure 35-19 Tracking the event tab of the properties window After selecting the event you want to track, click the data line tab as shown in Figure 35-20. In this tab, specify which information to collect during the tracking process. This information includes termination time, object ID, and more. Figure 35-20 Data line tab tracking properties window Select the filter tab, as shown in Figure 35-21. You can specify whether you want to capture the conditions you need. For example, exclude tracking Profiler. Through the exclusion of the SQL Server process, it is possible to avoid confusion of the Profiler window and add reading. Figure 35-21 Filter tab tracking properties window When the setting option is complete, press the execution start tracking. If you want to modify the tracking, it is recommended to use the Save Save Options in the File Menu to save the tracking modification (with different names). Once you start tracking, the event will appear in the Profiler window. Using the tracks selected in this example, the event will be sorted by Duration (one million seconds). Figure 35-22 Displaying the tracking in the PROFILER window execution. Note that in a busy environment, Profiler may use important system resources. The more events, the more system resources are taken up. The optimization T-SQL statement learned the T-SQL statement in the PROFILER inspection system in the previous chapter, and using Query Analyzer to determine execution planning and data access. It is also possible to improve the effectiveness using modified T-SQL statements. With these tools, you have the ability to modify T-SQL statements and improve statements. In this section, it will see a variety of ways of optimization T-SQL statements, allowing it to provide better efficiency or take less system resources. Figure 35-22 Tracking Best implementation plan Modifying the implementation plan is quite difficult, and it is not easy to create a better implementation plan than Query Optimizer. Some operations are more likely to benefit in the modification of the program, they are Join, Group by, Order By, and Union. For modifications of these operations, it can be easily achieved by using prompts, which will be mentioned in later However, there is no specific mode for optimization T-SQL statements. Because each database is unique, and the application is not the same, the modification is performed according to different situations. Select data access method As mentioned earlier in this chapter, the data access method is actually a set of objects, and SQL Server uses these objects to obtain information from the database. The data access method can be optimized by analyzing the information contained in the analysis database and the database, and the number of I / O operations can be reduced. Like the modified implementation plan, there is no formula for modifying the best data access method. The following guidelines help to choose the best data access method: Use the best index to use the best index, which is necessary when obtaining possible optimal performance. The best index for a particular operation is the fastest found, and a method of minimum I / O operation. You can use the in-depth understanding of the database and data or use Query Analyzer to confirm the best index. Query Analyzer lets you try different analog conditions to determine which index can pass the minimum number of columns. (Remember, Query Analyzer simply estimates the number of columns to be returned; to determine the exact column number, you must use Profiler.) Note As mentioned in Chapter 17, the index has a great advantage for SQL Server, but if the use is incorrect, it may be in turn affecting performance. Monitor the number of indexes of each data sheet, especially when performing a lot of INSERT, UPDATE, and DELETE statements. Too many indexs may result in decrease in the performance of this type of operation, which is due to the reduction in efficacy caused by the additional system resource occupancy of the index. Using Covering Indexes As mentioned in Chapter 17, using overwriting indexes can help you avoid additional I / O steps. You don't have to use the underlying information sheet, you can get the required information from the index. Reduce the reported data column decides whether you need to pass the actual needs from the query. Modify T-SQ L statements to facilitate access to information that needs to be accessed, do not pass the information columns that will be discarded. Reduce the information obtained from the database, which can be achieved by adding the selectivity of the query. Use the prompt to modify the T-SQL statement to change the data access method and execution plan, but if it is not unprotected, it will change the T-SQL statement. Safe optimization T-SQL statement method is to use a prompt. Tips You can specify which objects you want to do with Query Optimizer and which items need to be used. In this section, many different SQL Server prompts will be learned, as well as their usage. Join Hints Join Hint is used to specify which types of coupling operations should be performed. (If there is no specified type in the query, Query Optimizer will choose one yourself.) In SQL Server, you can perform Nested loops Joins, Hash Joins, Merge Jointers and Remote Joins. Use the following prompts to specify the connection method: LOOP specifies the nest ring connection. In the neoplastic ring joint, each column in the external data sheet and the internal data sheet will be checked, and the check value is equal. Hash specifies the mixture. Among the mixed-ends, a data sheet will be reorganized into a mix of data sheet. Other data sheets are scanned each time, the mixed function is used to search for the same content. MERGE specifies a sorted merge connection. In the sorting combined coupling, each data sheet is sorted, and then a column is compared according to the Power. Remote specifies the remote connection. The distal coupling is at least one coupled data sheet at the distal end. Look at the example of the link tips, use the examples of our previous example (see Select ORDERID, Customerid, Employees.employeid, FirstName, Lastname, OrderDate From Orders, Employees Where orders.employeid = Employees.employeeID Option (haveh Join) Description Joint tips are independent of each other - only one of them can be used each time. If you choose to use SQL-92 syntax as a link, you can also specify link types with a prompt. Using SQL-92 syntax, you can rewrite the previous query, as follows: Select ORDERID, Customerid, Employees.employeid, FirstName, Lastname, OrderDate From Orders Inner Hash Join Employees ORDERS.EMPLOYEID = EMPLOYEES.EMPLOYEEID) Joint Tips is the topic of advanced, and we do not provide experience methods for use. There are many reasons for different choices of special junctions, such as the number of other operators, and how many data sheets are in the coupling table. There is an optimal way to determine if the linkage will provide additional performance, which is to try each type of connection in Query Analyzer to see which of the minimum consumption. Of course, Query Optimize R usually helps to help select the best connection operation. Query prompt Query Hints is used to specify how to perform a specific query operation. The available query operations are divided into three categories: Group BY, Union and Miscellaneous. Packet prompts the following prompts to specify how to perform Group BY or Compute operations: Hash group by specifies the use of a mixed function to perform the Group BY operation. Order Group By Specifies the use of sort operations to perform Group BY operations. Using the previous group by example (see Select Customerid, SUM (OrderDetails.Unitprice) From Orders, ORDERDETAILS Hash group by customerid Option (haveh group) Description Group By Tips are independent of each other - only one of them can be used each time. Joint Tips The following prompt is to specify how to perform UNION operations: Merge Union uses a merge operation to perform UNITION. Hash Union uses a mixed function to perform UNITION. Concat Union performs Union using a series function. This is an example of using the Concat Union Tip: Select OrderID, Customerid, Employeeid, OrderDate From Orders Where customerid = 'Tomsp' Union Select OrderID, Customerid, Employeeid, OrderDate From Orders WHERE EMPLOYEEID = '4' Option (Concat Union) NOTE The UNION prompt is also independent of each other. Unfortunately, there is no certain formula to determine which Union operation is 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 minimum consumption. Usually SQL Server Query Optimizer can determine the best policies for UNION prompts. Miscellaneous Tips The following prompts can be used to perform a variety of query operations: Force ORDER enforces the order in the order in the query. In the preset state, SQL Server can reorder the data sheet 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.employeid, FirstName, Lastname, OrderDate From Orders, Employees Where orders.employeid = Employees.employeeID Option (Robust Plan) Data Sheet Tips Table Hints Use to control data sheet access, and the two data sheets are prompted as follows: Fast N replaces Fastfirstrows to keep backhand compatibility. Optimal queries to get the data of the most N ray. INDEX = index_name Forces Query Optimizer to use the specified index when possible. One of the previous examples of this chapter demonstrates how to use Index Tips: Select OrderID, Customerid, Employeeid, OrderDate From Orders with (INDEX = EmployeeID) WHERE EMPLOYEEID = 5 Option (FAST 10) With is selective. The index = EmployeeID prompts here will specify the use of the EmployeeID index. Through the specified FAST 10, SQL Server will optimize the acquisition of the previous 10 column (if possible), then return to the remaining columns. This chapter summarizes this chapter, you have learned how to use Query Analyzer to determine the best implementation plan and data access method for query. In addition, you have learned how to use PROFILER to view the T-SQL statement executed in the system, and how to perform tracking to determine if some of these T-SQL statements may result in performance problems. Also discussed how to optimize the implementation plan and data inventory in accordance with your database and information. Finally, you have learned how to use prompts to specify a specific execution plan or data access method. In Chapter 36, the theme of your study will develop into performance issues and how to solve efficacy.
