| 1: | You are working for a small manufacturing company that has been operating well for some time. Lately the end users have been reporting that when performing queries against information on customers, the system is growing increasingly slow. After examining the system, you determine that the table definition has recently been altered. You want the response time to improve and be similar to what it was before the change. How would you fix the problem? A. Run a DBCC DBREINDEX. B. Drop and re-create the table's clustered index. C. Drop and re-create all indexes. D. Update the index statistics. E. Stop and restart the server.
|
| A1: | Answer: D. Because the table structure has recently been altered, it is a good possibility that this change has caused the indexing information to become unstable or that statistics affecting the index have not been updated. If you restart the service, SQL Server should then update the statistical information accordingly, but this may affect use of the server and may not be a possibility. After the restart, you may want to ensure that all statistics are intact. Also consider index fragmentation as a possible source to the problem. For more information, see the section "Pointing an Index in the Right Direction." |
| 2: | Your company has a table named Products that is dedicated to its goods. A month ago, you added three nonclustered indexes to the table named NC1_Pro, NC2_Pro, and NC3_Pro. You also added a clustered index named C1_Pro on the primary key named Prod_ID. You monitor the performance on the indexes and notice that the indexes are not as efficient as before. You decide to rebuild each index in the table. Which method should you use to rebuild all indexes in the fastest and most efficient way? A. Use DBCC DBREINDEX (Products). B. Create a clustered index with Drop-Existing; create a nonclustered index with Drop-Existing. C. Delete all indexes and then re-create them. D. Use DBCC DBREINDEX (NC1_Pro, NC2_Pro, NC3_Pro, C1_Pro). E. Update the index statistics.
|
| A2: | Answer: A. Use DBCC DBREINDEX. Answer B is wrong because this would be more time-consuming than DBCC DBREINDEX. You would have to individually rebuild all indexes. Answer C is not correct because deleting a clustered index and then re-creating it means deleting and re-creating all nonclustered indexes. Also, the process would have to involve two separate steps. For more details, refer to the section "Index Maintenance over Time." |
| 3: | You have a table named Products that holds information pertaining to the products your company trades. You need to perform quality searches on the Description column so that you can find products needed using diverse methods. You decide using full-text searches is the best method and so enable full-text indexing in your table. You want to keep performance consistent with the full-text indexes so that they do not degrade, and you want to minimize overhead associated with their maintenance. What should you do? A. Expand the database to accommodate future growth of the full-text indexes. B. Use the Index Tuning Wizard. C. Repopulate using the Full-Text Indexing Wizard and specify Keep Performance Consistency. D. Schedule regular repopulates of the full-text indexes.
|
| A3: | Answer: D. Unlike regular SQL indexes, full-text indexes are not automatically kept up-to-date as data is modified in the associated tables. Full-text indexes should be frequently updated to maintain performance. For more information, see "Full-Text Indexing and Searches." |
| 4: | Which statements show the maximum number of clustered and nonclustered indexes allowed in a single table? A. Clustered 249 and nonclustered 149 B. Clustered 249 and nonclustered 249 C. Clustered 1 and nonclustered 249 D. Clustered 1 and nonclustered unlimited
|
| A4: | Answer: C. There can be only one clustered index per table and as many as 249 nonclustered indexes. For more details, refer to SQL Server Books Online. |
| 5: | Jauna is a DBA who has received complaints from many users concerning the data retrieval and modification times on the Sales table. She knows that you are a developer and asks you to figure out a way to resolve the problem she is facing. Because you are a new developer, you happen to know that indexes increase performance, and that's just about all you know about indexes. You imprudently index all 12 columns in the Sales table. Which of the following statements outlines the consequence of using the numerous indexes you have just done? A. Numerous indexes make modifying data slower. B. Numerous indexes are not allowed to be built; indexes can be used only in smaller quantities. C. Numerous indexes result in a very short index life span. D. Numerous indexes decrease performance on queries that select data with the SELECT clause.
|
| A5: | Answer: A. Indexes used in larger quantities often degrade the rate at which insertions, deletions, and some modifications to data occur. Nevertheless, indexes generally speed up data access in cases in which the data in the table is sufficient to warrant indexing. For more information, see "Using the Index Tuning Wizard." |
| 6: | You are evaluating the database design given to you by another developer. This database was to be designed with an emphasis on query performance, and an attempt has been made to meet the design goal. Replying to this directive, the developer has sketched out a design for several indexes for the tables. These indexes have been put together with the highest expected query usage kept in mind. As you review his design, you notice that the new indexes provide varying degrees of benefit to various queries. Which of his indexes is likely to be the most effective? A. An index on gender for 134,000 registered voters. B. An index on the sales agent's last initial for 25,000 orders. C. An index for the StateCode primary key column in a US_States table. D. An index for the State column in a PacificTime_ZIP_Codes table.
|
| A6: | Answer: C. Gender is never a good column to supply an index against because it has only two possible values. In general, a column with a high percentage of unique values is the best choice for indexing. An agent's last initial has 26 possibilities in a table of 25,000, which is still a rather poor choice. An index created on the state value would be a good choice in either C or D, but C provides the best ratio. |
| 7: | You have several procedures that will access existing indexed tables. Before you put the procedures into production, you want to ensure optimal performance. You also want to ensure that daily operations in the database are not adversely affected. What should you do? A. Create a covering index for each query in the procedures. B. Create an index that includes each column contained in every WHERE clause. C. Use the Index Tuning Wizard to identify whether indexes should be added. D. Create statistics on all columns in all the SELECT and WHERE clauses.
|
| A7: | Answer: C. There are already indexes in place and you don't want to create additional indexes that are not needed and may adversely affect the current system. The first and second answers therefore are not suitable, at least until testing determines that the indexes will improve performance. Creating additional statistics may not be warranted. What is needed is an analysis of the system to determine the indexes needed. |
| 8: | Selects, inserts, and updates are taking too long against tables that are already indexed. Additional tables and stored procedures have been added since the original indexes were created, and many of the procedures are no longer used. What would you do to improve the response times as quickly as possible? A. Execute the DBCC UPDATEUSAGE statement. B. Execute the DBCC SHOW_STATISTICS to find high-density indexes to drop. C. Run the Index Tuning Wizard to find any missing or extra indexes. D. Use SQL profiler to find table problems.
|
| A8: | Answer: C. Although the profiler and DBCC operations are very good diagnostic tools, they will not provide the necessary improvements and overall quick analysis that the Index Tuning Wizard gives you. In this instance running the wizard will, in a single operation, provide the necessary information to both eliminate unwanted indexes and inform you of where additional indexes may be warranted. |
