لطفا منتظر باشید ...
Objective 4.3: Troubleshoot and optimize programming objects.
A number of tools and methods are available to troubleshoot and optimize programming objects, such as stored procedures, triggers, user-defined functions, and views. These include coding statements, such as the PRINT statement and RAISERROR, to track and monitor the values of variables, as well as query and table hints to optimize the data access in a Transact-SQL statement. One of the most valuable tools for troubleshooting stored procedures is the Transact-SQL Debugger. This tool is available in the SQL Query Analyzer and includes the most common debugging options, such as using watch expressions, stepping through code, and setting breakpoints in code.
Objective 4.3 Questions
1. | 70-229.04.03.001 You have a Web-based Online Transaction Processing (OLTP) orders application that has a few problematic SELECT queries that join six different tables and request the retrieved results in different orders. These queries are taking too long to return the results to the user. Which of the following would be the easiest and most effective way to get better performance from these few queries? (Choose all that apply.) Use the OPTION (FAST n) query hint. Use the ROBUST PLAN query hint. Create indexed views for the queries. Rewrite the statement as a view. | |
2. | 70-229.04.03.002 You need to troubleshoot a stored procedure, determining what the results of a particular local variable are during the execution of the store procedure. Which of the following can help to troubleshoot this procedure? (Choose all that apply.) Use the SET statement to set the local variable to another value. Use the PRINT statement inside the procedure to print out the local variable contents. Use the RAISERROR statement. Use the SQL Query Analyzer Transact-SQL Debugger. | |
3. | 70-229.04.03.003 You have a database application that uses dynamic SQL. The only changes to the query are the values that are used in the WHERE clause. You would like to ensure that the execution plan generated by the optimizer at compile time will be reused on subsequent executions. Which of the following approaches can help ensure cached execution plan reuse? (Choose all that apply.) Rewrite the statement using the sp_executesql system stored procedure and fully qualified names. Rewrite the statement as a stored procedure, using input parameters for the WHERE clause substitution. Rewrite the statement using fully qualified object names. Rewrite the statement as a view. | |
4. | 70-229.04.03.004 You have designed a database application that will be used by a large number of users, and you want to ensure that SELECT queries against the Products table do not block transactions that might be inserting data into other tables in the database. The Products table does not change except during a nightly batch job that will adjust the Products table. Which of the following SELECT statement locking hints can be used to minimize locking in this scenario? HOLDLOCK NOLOCK PAGLOCK TABLOCK | |
5. | 70-229.04.03.005 You are evaluating the performance of your database. Your organization allows access to the database only through stored procedures, but you notice that some of the stored procedures are recompiling. Which of the following situations can cause a stored procedure to recompile? (Choose all that apply.) Changes to a large percentage of the data in the underlying objects Interleaving of data manipulation language with data definition language in the stored procedure Cursors created in the stored procedure that reference a temporary table in a SELECT statement Statements that reference temporary tables that were created outside the stored procedure |
Answers
1. | A. Selecting this option will cause the query to be optimized for the first number of rows that is supplied in the hint. This returns the first group of information to users as fast as possible, allowing the time that the user looks at the first group to be used to retrieve the rest of the results. This is especially helpful if you supply the approximate number of rows to the query hint that fills the first page on the user interface. C. Creating an indexed view by creating a view for the problematic queries and then adding a clustered unique index on these views will cause the creation of a table that is maintained by the server. There is a modification cost for the extra maintenance that needs to be performed by the server for these tables, but the performance gains can be great. A number of requirements must be met for an indexed view to be used. Only the Enterprise Edition of SQL Server supports indexed views, and there are a number of other prerequisites. |
2. | B. The PRINT statement can be used to output the value of a local variable. Using the PRINT statement at critical points in the procedure can help to troubleshoot the procedure. The PRINT statement can accept only character or Unicode values, so variables with other data types will need to be converted before they can be used with the PRINT statement. C. Although RAISERROR is generally used to output error messages to clients, it can also be used to troubleshoot Transact-SQL by using the statement at critical points in the code, outputting the value of the local variable at those points in the execution of the code. D. This is an excellent tool to debug a stored procedure. It can be used only within the SQL Query Analyzer, so it cannot be used during actual procedure execution from a client. However, it is very powerful and can show the value of local variables (through the watch functionality) throughout the execution of the code. It also supports stepping through code and setting breakpoints. The Transact-SQL Debugger should not be used on a production server. |
3. | A. Using the sp_executesql system stored procedure and passing the changed parameter values can help to ensure that a cached execution plan can be reused. The optimizer will give an internal name to the query and treat it as a pseudo-stored procedure, reusing the plan when possible if only the parameter values change. All object names must be fully qualified for the optimizer to reuse the plan. B. Using a stored procedure will provide the best chance of an execution plan being reused. Fully qualified names are not required because the procedure name by itself will cause the optimizer to recognize the plan. However, using a stored procedure does not ensure that a cached execution plan is reused every time because there are conditions that will cause a stored procedure to recompile, such as the use of temp tables and cursors. |
4. | B. Using the NOLOCK hint in this scenario would be the best approach. This hint will hold the minimal locks on the Products table, and because this table does not change during the transaction period of the day, it would not be a problem as far as uncommitted data being read by users. This will provide the best concurrency and ensure that minimal locking is held during the transaction. |
5. | A. If a sufficient number of data modifications have been made to the underlying objects (such as large insertions or deletions), the stored procedure might be recompiled. This could be considered a good thing because the optimizer might select a different execution plan based on the data changes. B. Using statements that create or drop objects interleaved with SELECT statements or other data manipulation statements can cause recompilation. This occurs because the objects might not exist during the first compile, so the optimizer is not able to create an execution plan. If objects, such as temporary tables and indexes, need to be created in a stored procedure, it is best to try to create them at the beginning, before they are referenced. C. Creating a cursor with the DECLARE CURSOR statement that uses a temporary table in the SELECT clause of the cursor can cause a stored procedure to recompile. D. A reference to a temporary table that was created outside the stored procedure can cause a recompile. It is best to create the required temporary table in the stored procedure, ensuring that the data definition statements, such as the CREATE TABLE statements, do not interleave with any data manipulation statements, such as SELECT statements. |
