|
发信人: reynolds(雷龙.DBA中) 整理人: reynolds(2002-07-24 14:35:31), 站内信件 |
| 131. You are the administrator of an SQL Server 2000 computer. The server contains a database named Sales. The database is configured as shown below:
See http://www.cheet-sheets.com/228/image20.jpg 
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 configured as follows: Restrict Access Read-only Recovery Model ANSI NULL default Recursive triggers Unchecked Unchecked Full Unchecked Unchecked Auto update statistics Torn page detection Auto close Auto shrink Checked Checked Unchecked Unchecked Auto create statistics Use quoted identifiers Compatibility Level Checked Unchecked 80 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 Answer: B Reason: 查询加速就要用好索引。 132. You are the administrator of a SQL Server 2000 computer. The server contains a database that is heavily indexed and that the company users query extensively. The database has grown and query response time has slowed. The database is stored in a single data file. You want to accelerate query response time. What should you do? A. On a new hard disk, create a new filegroup. Drop the existing nonclustered indexes, and then re-create them on the new filegroup. B. On a new hard disk, add a new file to the PRIMARY filegroup. Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup. C. On the existing hard disk, create a new filegroup. Drop the existing nonclustered indexes, and then re-create them on the new filegroup. D. On the existing hard disk, add a new file to the PRIMARY filegroup. Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup. Answer: A Reason: 单独的磁盘和单独的文件组才能真正解决问题。 133. You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The database has a group of tables that analysts use to examine sales trends. The database options are set to their default values, and the tables are configured as shown below: See http://www.cheet-sheets.com/228/image21.jpg 
Analysts who use the database report that query performance has become slow. You analyze the clustered primary key on the Invoices table and receive the following results: DBCC SHOWCONTIG scanning 'Invoices' table... Table: 'Invoices' (21575115); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 200 - Extents Scanned..............................: 50 - Extent Switches..............................: 40 - Avg. Pages per Extent........................: 4.0 - Scan Density [Best Count:Actual Count].......: 60.00% [3:5] - Logical Scan Fragmentation...................: 0.00% - Extent Scan Fragmentation....................: 40.00% - Avg. Bytes Free per Page.....................: 146.5 - Avg. Page Density (full).....................: 98.19% - DBCC execution completed. If DBCC printed error messages, contact your system administrator. You want to improve performance of queries that join tables to the Invoices table. What are three possible Transact-SQL statements you can execute to achieve this goal? (Each correct answer represents a complete solution. Choose three.) A. DBCC UPDATEUSEAGE ('Sales','Invoices','PK_Invoices') B. CREATE UNIQUE CLUSTERED INDEX PK_Invoices On Invoices(InvoiceID) WITH DROP_EXISTING C. DBCC INDEXDEFRAG ('Sales','Invoices','PK_Invoices') D. DBCC DBREINDEX (Sales.dbo.Invoices, 'PK_Invoices') E. UPDATE STATISTICS 'Invoices' F. DBCC CHECKALLOC (Sales, REPAIR_FAST) Answer: B C D Reason: ====================================================================== DBCC INDEXDEFRAG 可以对表或视图上的索引和非聚集索引进行碎片整理。DBCC INDEXDEFRAG 对索引的叶级进行碎片整理，以便页的物理顺序与叶节点从左到右的逻辑顺序相匹配，从而提高索引扫描性能。 重建索引 在数据库中创建索引时，查询所使用的索引信息存储在索引页中。连续索引页由从一个页到下一个页的指针链接在一起。当对数据的更改影响到索引时，索引中的信息可能会在数据库中分散开来。重建索引可以重新组织索引数据（对于聚集索引还包括表数据)的存储，清除碎片。这可通过减少获得请求数据所需的页读取数来提高磁盘性能。 SQL Server 索引的两种类型为： 聚集 聚集索引基于数据行的键值在表内排序和存储这些数据行。由于数据行按基于聚集索引键的排序次序存储，因此聚集索引对查找行很有效。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。数据行本身构成聚集索引的最低级别。 只有当表包含聚集索引时，表内的数据行才按排序次序存储。如果表没有聚集索引，则其数据行按堆集方式存储。 非聚集 非聚集索引具有完全独立于数据行的结构。非聚集索引的最低行包含非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。数据行不按基于非聚集键的次序存储。 在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键。 ====================================================================== 134. You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The Sales database is configured as shown below: See http://www.cheet-sheets.com/228/image22.jpg 
The Sales database stores point-of-sale transaction data. The data is later imported into another database for analysis. Recently, users have reported slow response times whenever they enter point-of-sale information. You examine the indexes on the Invoices table that are configured as shown in the Index Description exhibit. index_name index_description index_keys IX_Customers_PK nonclustered located on PRIMARY CustomerID IX_SalesPerson_PK nonclustered located on PRIMARY SalesPersonID PK_Invoices clustered, unique, primary on PRIMARY InvoiceID You want to accelerate database response time when users insert new point-of-sale information. What should you do? A. Drop IX_Customers_FK and IX_SalesPerson_FK. B. Change IX_Customers_FK to a clustered index. C. Change IX_SalesPerson_FK to a clustered index. D. Change PK_Invoices to a nonclustered index. Answer: A Reason: 135. You are the administrator of a SQL Server 2000 database. The database was configured with all default options. You drop indexes to facilitate a large import of data, and rebuild them after the import is complete. Users report that response times from a stored procedure have become slower. In the stored procedure, you examine the following code. CREATE PROCEDURE GetCustomer ( @CustomerID Int ) AS SELECT FirstName, LastName, Address1, Address2, City, State, Zip, MAX(PurchaseDate) AS LastPurchase FROM Customers C JOIN Addresses A ON A.AddressID = C.AddressID JOIN Purchases P ON P.CustomerID = C.CustomerID WHERE C.CustomerID = @CustomerID GROUP BY FirstName, LastName, Address1, Address2, City, State, Zip You want to improve the performance of the procedure by using the least amount of administrative effort. What should you do? A. Recompile the stored procedure. B. Update the database statistics. C. Check the consistency of the database and repair errors. D. Create a view and modify the stored procedure to use the view. Answer: A Reason: 题目已经暗示了用缺省配置来设置的数据库，因此统计更新是被选中的。而索引出现过重大调整的，因此需要重新编译过程来优化执行计划。 ====================================================================== 更新统计 随着列中数据的更改，索引和列的统计信息可能会过时，导致查询优化器选择的查询处理方法不是最佳的。例如，如果创建一个包含一个索引列和 1,000 行数据的表，每一行在索引列中的值都是唯一的，则查询优化器将把该索引列视为搜集查询数据的好方法。如果更新列中的数据，因而有很多重复值，则该列对于查询不再是理想的候选列。然而，查询优化器仍然根据索引的过时分发统计信息（这些信息基于的是更新前的数据），将该列看作是一个好的候选列。 重新编译存储过程 在用诸如添加索引或更改索引列中的数据等操作更改数据库时，应通过重新编译访问数据库表的原始查询计划使其得到重新优化。在重新启动 Microsoft® SQL Server™ 2000 后第一次运行存储过程时自动发生该优化。当存储过程使用的基础表更改时也会发生优化。但是如果添加了存储过程可能从中受益的新索引，将不发生自动优化，直到下一次 SQL Server 重新启动后再运行该存储过程时为止。 ====================================================================== ---- / <>< o /| / <>< (o / |/| ) ) <@ < ( O )( \ |\| <>< ) <>< () \| \ \ 每天在网易游水的鱼 |
|
[关闭][返回] |