2. 计算机
  3. You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The database is configured

You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The database is configured

admin2009-05-19  34
问题 You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The database is configured as shown in the exhibit.


In the last six months, the database has grown by 4 GB. Users report that query response time has slowed. The options set up on the database are shown in the Database Options exhibit.


You want to accelerate query response time. What should you do?
选项 A、Update the database statistics.
B、Add indexes to the foreign key fields.
C、Truncate the transaction log.
D、Run the database maintenance plan wizard.
E、Drop primary keys from all tables.
答案B
解析 Explanation: A FOREIGN KEY is a column or combination of columns used to establish and enforce a link between the data in two tables and should be indexed because changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables and FOREIGN KEY columns are often used in join criteria when the data from related tables is combined in queries by matching the column(s) in the FOREIGN KEY constraint of one table with the primary or unique key column(s) in the other table.

These indexes allow SQL Server 2000 to find related data in the foreign key table quicker. Although creating this index is not a requirement, a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.

Incorrect Answers:
A: SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index would be the best index to use for a particular query process. As the data in a column changes, index and column statistics can become out-of-date.

This can hinder the query optimizer’s ability to make optimal decisions on how to process a query. As a result query performance will deteriorate. When statistics become out-of-date they can be updated automatically if the auto update statistics check box on the tables property dialog box is checked. The exhibit shows that this check box is checked; therefore it is not out-of-date statistics that is hampering query performance.

C: Old transaction log records that are no longer necessary for recovering or restoring a database should be deleted to make way for new log records. The process of deleting these old log records to reduce the size of the logical log is called truncating the log. However, the active portion of the transaction log can never be truncated.

The active portion of the log is the part of the log needed to recover the database at any time, so must have the log images needed to roll back all incomplete transactions. It must always be present in the database in case the server fails because it will be required to recover the database when the server is restarted.

This truncation occurs automatically at the completion of any BACKUP LOG statement or every time a checkpoint is processed, provided the database is using the Simple Recovery model. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system.

D: The Database Maintenance Plan Wizard can be used to set up the core maintenance tasks necessary to ensure that a database performs well, is regularly backed up, and is checked for inconsistencies.

The Database Maintenance Plan Wizard creates a SQL Server 2000 job that performs these maintenance tasks automatically at scheduled intervals and can be used to reorganize the data on the data and index pages by rebuilding indexes with a new fill factor; compress data files by removing empty database pages; update index statistics to ensure the query optimizer has up-to-date information about the distribution of data values in the tables; perform internal consistency checks of the data and data pages within the database to ensure that a system or software problem has not damaged data; backup the database and transaction log files; and to set up log shipping.

E: Indexes are used to speed up the retrieval of rows for SELECT statements. Because indexes control how the data rows of the table are ordered and have pointers to locate specific data rows they can also increase the speed of updates and deletes. This is because SQL Server must first find a row before it can update or delete the row. Removing the indexes would thus hamper query performance.
转载请注明原文地址:https://kaotiyun.com/show/xuhZ777K
本试题收录于： 微软70228题库微软认证分类
0

微软70228

微软认证

相关试题推荐
随机试题
最新回复(0)