| 1: | As a database implementer you are developing several new stored procedures on an existing SQL Server 2000 database that resides at your company headquarters. You are experimenting with a Query Analyzer session that contains each of the individual queries to be used in these procedures. After running a given SELECT query and using the graphical showplan feature to understand the execution plan, you want to prove that internal statistics are available for a particular column used in the query. How would you best find this information? A. Disable the graphical showplan display. B. Hold the mouse over each individual node relating to the desired column. C. Use a SET SHOWPLAN statement. D. Examine each node of the desired column without mousing over it.
|
| A1: | Answer: B. A feature of the graphical showplan display enables you to see information about each node in the display by holding the mouse over the node. A display shows the node analysis based on the contents of the entire query. Of the choices available, this would be the best mechanism to use to gain the desired information. For more information, see the section "The SQL Query Analyzer." |
| 2: | You are a database implementer of a SQL Server 2000 environment that has a single database server. The server contains all your company's databases, including an investment-tracking database. Each day more than 100 operators make approximately 5,000 changes to customer investments. In addition, daily and monthly reports are created from the investment data. Another development team at your company needs to optimize a database application. The team members need a sample of database query activity to discover whether they can speed up the transactions. The developers also want to replay the sample on another SQL Server computer. You need to capture the sample, but you want to minimize any increase to the workload of the server. What should you do? A. Run Profiler on a client computer. Configure SQL Profiler to monitor database activity, and log data to a .trc file. B. Run Profiler on the server. Configure SQL Profiler to monitor database activity, and log data to a database table. C. Run System Monitor on a client computer. Configure System Monitor to monitor database activity, and log data to a .blg file. D. Start SQL Server from a command prompt. Specify trace flag 1204 to enable verbose logging.
|
| A2: | Answer: A. Although it would be most desirable to run the query on another SQL Server, it is always best to select a machine other than the production machine to absorb the overhead of the Profiler itself. For more information, see the section "Monitor Activity with the Profiler." |
| 3: | You are the administrator of a SQL Server computer. Users report that the database times out when they attempt to modify data. You use the Current Activity window to examine locks held in the database, as shown in Figure 7.5. You need to discover why users cannot modify data in the database, but you do not want to disrupt normal database activities. What should you do? A. Use the Current Activity data to discover which resources are locked. B. Use the sp_who stored procedure to discover who is logged in as spid 52. C. Use SQL Profiler to capture the activity of the user logged in as spid 52. D. Use System Monitor to log the locks that are granted in the database.
Figure 7.5. The Current Activity window. 
|
| A3: | Answer: A. Although knowing who has the login may be useful later, the first thing to look for is what is executing that is causing the block. For more details, see "Current Activity" in SQL Server Books Online. |
| 4: | You are working on a SQL Server 2000 computer that contains a database named Sales. The company's web-based application uses the Sales database to store sales transactions. The company website has grown in popularity, and database utilization has increased. You need to collect data about the utilization of server resources so that you can provide capacity planning. You want to automate the collection process so that information is gathered as quickly as possible. What should you do? A. Configure System Monitor to collect data and store it in a SQL Server table. B. Create a job that executes the sp_statistics stored procedure daily. C. Use SQL Profiler to trace server activity and store the results in tables. D. Configure alerts to store information in the Windows application event log.
|
| A4: | Answer: C. To monitor detailed activity of SQL Server, you should use the SQL Server Profiler and configure a trace to take in the necessary data and filter for the desired objects. For more details, see the section "Monitor Activity with the Profiler." |
| 5: | You create two transactions. One transaction inserts employee personal information into the database. The other transaction inserts employee demographics. The second query is less important than the first. How could you ensure that when locking problems exits the second query is always the one terminated? A. Set DEADLOCK_PRIORITY to LOW for the transaction that inserts the employee personal information. B. Set DEADLOCK_PRIORITY to LOW for the transaction that inserts the employee demographics. C. Add code that checks for the inserting of personal information. If an error is encountered, restart the transaction. D. Add the ROWLOCK optimizer hint to all UPDATE queries. E. Set the isolation level to SERIALIZABLE when inserting the personal information.
|
| A5: | Answer: B. SET DEADLOCK_PRIORITY LOW specifies that the current transaction is the preferred deadlock victim. If this were performed on the transaction that contained the personal information, it would actually be the opposite of what we wanted to accomplish. Adding ROWLOCK hints or altering the isolation levels would not be appropriate in most instances in which there are deadlock possibilities between two known transactions and does nothing to guarantee the server selection for termination. |
| 6: | You have a mission-critical application that runs within the context of the company's SQL Server. Database activity is high during the day as sales transactions are entered through network point-of-sale terminals. Sales clerks are reporting that transactions are taking too much time and customers are annoyed with the waiting period for updates to information. What would you do to diagnose the situation? A. Increase the amount of memory on the server. B. Upgrade the processor on the server or move to a multiple processor machine. C. Use sp_configure to increase the number of locks available to the server. D. Run SQL Profiler in the day, and use the output for the Index Tuning Wizard. E. Run SQL Profiler in off-peak times, and use the output for the Tuning Wizard.
|
| A6: | Answer: D. You need to perform some diagnosis during the period in which the issues exist. There is no sense in changing the physical configuration of the server until some diagnosis has been performed. Although adding memory and/or upgrading the processor may help the situation, it is impossible to know whether that is even an issue before performing some tests. SQL Profiler tests should be performed during activity; to get an accurate reading of the database usage, a profiler trace should be performed during the day when the system is being used. |
| 7: | Your company database contains a table listing the items and quantities available for sale. You have a stored procedure that updates the table. The procedure first checks to see whether there is sufficient quantity for the sale before performing the update. The check is performed with a HOLDLOCK so that the quantity is held for the duration of the transaction. Sometimes this procedure is chosen as a deadlock victim when others are reading data from the same table. You would like to prevent this while maintaining data integrity. What should you do? A. Remove the table hint. B. Change the table hint to UPDLOCK. C. Change the table hint to REPEATABLEREAD. D. Set the transaction isolation level to SERIALIZABLE. E. Set the transaction isolation level to REPEATABLE READ.
|
| A7: | Answer: B. You should alter the lock to an UPDLOCK in this circumstance because you want the data to be in the same state as the read when you actually perform the update. Using this lock will allow other users to still access the data as long as they do not attempt to make an update while the lock is in place. A HOLDLOCK will hold the shared lock until the end of the transaction but not prevent updates. Removing this option without replacing it with something else serves no purpose. SERIALIZABLE does nothing to change the situation because it provides the same functionality as the HOLDLOCK. REPEATABLE READ options do not provide for protection against phantom deletions of the record being updated. |
