|
发信人: reynolds(雷龙.DBA中) 整理人: reynolds(2002-07-24 11:25:24), 站内信件 |
| 136. You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. Users report that the following query responds slowly:
Select Parts.SKU AS SKU, Parts.Description as [Part Description], Locations.Description AS Location FROM PartsLocations INNER JOIN Parts ON PartsLocations.LocationID = Locations.LocationID AND Parts.LocationID = Locations.LocationID You examine the indexes in the PartsLocations table. The indexes are configured as shown below: Index_name index_description index_keys IX_PartsLocations_LocationID_PK nonclustered located on PRIMARY LocationID IX_PartsLocations_PartID_PK nonclustered located on PRIMARY PartID PK_PartsLocations clustered, unique, primary… PartID, LocationID You also examine the contiguity of the table and receive the following results: DBCC SHOWCONTIG scanning 'PartsLocations' table... Table: 'PartsLocations' (1); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 3000 - Extents Scanned..............................: 750 - Extent Switches..............................: 502 - Avg. Pages per Extent........................: 4.0 - Scan Density [Best Count:Actual Count].......: 33.33% [1:3] - Logical Scan Fragmentation...................: 33.33% - Extent Scan Fragmentation....................: 33.33% - Avg. Bytes Free per Page.....................: 1166.0 - Avg. Page Density (full).....................: 85.77% You need to improve the performance of the query. What should you do? A. Rebuild the PK_PartsLocations index. B. Rebuild the IX_PartsLocations_PartsID index. C. Rebuild the IX_PartsLocations_LocationID index. D. Rebuild the IX_PartsLocations_PartsID_LocationID index. Answer: A Reason: 前面讨论过聚集索引和数据行以同样的方式存放的，DBCC的结果中Scan Density [Best Count:Actual Count].......: 33.33% [1:3]，说明索引碎片比较大了，应该重新索引一次以提高性能。 137. You are the administrator of a 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/image23.jpg 
Analysts examine customer buying patterns and frequently use the following query: SELECT Customers.CustomerID, Invoices.InvoiceID, Invoices.InvoiceDate, OrderDetail.Quantity, Products.BKU FROM Customers INNER JOIN Invoices ON Customers.CustomerID = Invoices.CustomerID INNER JOIN OrderDetail ON Invoices.InvoiceID = OrderDetail.InvoiceID INNER JOIN Products ON OrderDetail.ProductID = Products.ProductID WHERE CustomerID = ? You analyze the indexes in the Invoices table. The indexes are configured as shown below: 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 optimize the database schema so that it responds quickly to the analysts' queries. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.) A. Re-create the PK_Invoices index as a nonclustered index B. Re-create the IX_Customers_FK index as a clustered index C. Re-create the IX_SalesPerson_FK index as a clustered index D. Create a composite index on CustomerID fields and SalesPersonID fields E. Create a composite index on InvoiceID fields and CustomerID fields Answer: A B Reason: ====================================================================== 索引优化建议 可以在不影响数据库架构和应用程序设计的情况下除去、添加和更改索引。高效的索引设计对获得好的性能极为重要。正因为如此，应该尽量试验不同的索引。索引优化向导可分析查询并建议应该创建的索引。有关更多信息，请参见索引优化向导。 Microsoft SQL Server 2000 查询优化器在多数情况下可靠地选择最高效的索引。总体索引设计策略应为查询优化器提供更多的索引选择机会，并支持其做出正确的决定。这在各种情形下可减少分析时间并取得较好的性能。 不要总是将使用索引等同于好的性能，反之亦然。如果使用索引总能产生最佳性能，查询优化器的工作就太简单了。实际上，不正确的索引检索选择会导致比最佳性能差的结果。因此，查询优化器的任务是只在索引检索能提高性能时才选择使用索引检索，在其妨害性能时则避免使用。 关于创建索引的建议如下： 〉将更新尽可能多的行的查询写入单个语句内，而不要使用多个查询更新相同的行。仅使用一个语句，就可以利用优化的索引维护。 〉使用索引优化向导分析查询并获得索引建议。有关更多信息，请参见索引优化向导。 〉对聚集索引使用整型键。另外，在唯一列、非空列或 IDENTITY 列上创建聚集索引可以获得性能收益。有关更多信息，请参见使用聚集索引。 〉在查询经常用到的所有列上创建非聚集索引。这可以最大程度地利用隐蔽查询。有关更多信息，请参见使用非聚集索引。 〉物理创建索引所需的时间在很大程度上取决于磁盘子系统。需要考虑的重要因素包括： 〉用于存储数据库和事务日志文件的 RAID（独立磁盘冗余阵列）等级。 〉磁盘阵列中的磁盘数（如果使用了 RAID）。 〉每个数据行的大小和每页的行数。这将决定为创建索引须从磁盘读取的数据页的数目。 〉索引中的列数和使用的数据类型。这将决定必须写入磁盘的索引页的数目。 检查列的唯一性。有关更多信息，请参见使用唯一索引。 〉在索引列中检查数据分布。通常情况下，为包含很少唯一值的列创建索引或在这样的列上执行联接将导致长时间运行的查询。这是数据和查询自身的基本问题，通常不识别这种情况就无法解决这类问题。例如，如果物理电话簿按姓的字母顺序排序，而城市里所有人的姓都是 Smith 或 Jones，则无法快速找到某个人。 ====================================================================== 138. You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords. Users access medical records by using the PatientID field. This field is the clustered primary key for the Patients table. When users try to access medical records, the database responds slowly. The database options 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 Unchecked Checked Unchecked Unchecked Auto create statistics Use quoted identifiers Compatibility Level Checked Unchecked 80 You want to accelerate query response time and minimize administrative overhead. How should you reconfigure the database? A. Create a SQL Server Agent job to execute the UPDATE STATISTICS statement, and schedule the job to run weekly. B. Select the ‘Auto update statistics’ check box. C. Run the Database Maintenance Plan Wizard, and accept the default settings. D. Rebuild the primary key as a nonclustered primary key. E. Clear the Auto create statistics check box. Answer: B Reason: 之所以不选择D，理由可以参看上面的解释，PatientID这类字段应该建立聚集索引来提高性能。 ====================================================================== 查询优化 完全通过系统级服务器性能优化（如内存大小、文件系统类型、处理器的数目及类型等）解决性能问题可能很诱人。但经验表明大多数性能问题不能用这种方法解决。必须通过这些方法解决性能问题：分析应用程序以及应用程序提交给数据库的查询和更新，并分析这些查询和更新如何与数据库架构交互。 持续时间意外地长的查询和更新可能由下列原因引起： 〉网络通讯速度慢。 〉服务器计算机的内存不足或 Microsoft SQL Server 2000 可用的内存不足。 〉缺少有用的统计数据。 〉统计数据过期。 〉缺少有用的索引。 〉缺少有用的数据条带化。 ====================================================================== 139. You are the administrator of a SQL Server 2000 database named Articles. A large city newspaper uses this database to store its archived newspaper articles. Journalists query the database as part of their research. You have created full-text indexes so that users can query the articles quickly. A hardware failure forces you to restore the entire database from backup. After you restore the database, the journalists report that they can no longer run queries without receiving errors. You want to ensure that the journalists can query the database. What should you do? A. Create a new full-text catalog for the Articles database B. Rebuild the full-text catalog for the Articles database C. Create a new full-text index on the table that holds the text for the articles D. Repopulate the full-text index on the table that holds the text for the articles Answer: D Reason: 全文编录是一个包含全文索引的数据库，由于重新Restore了数据库，此时全文索引和原始数据已经不同步了，为了使全文编录可用，应该重新填充全文编录，而不用重建全文编录（B）。 140. You are the administrator of a SQL Server 2000 computer. The server contains your company's investment-tracking database. Each day, 100 operators make approximately 5,000 changes to customer investments. In addition, daily and monthly reports are created from the investment data. Developers at your company need to optimize the database application. They need a sample of database query activity to discover if 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 SQL Profiler on a client computer. Configure SQL Profiler to monitor database activity, and log data to a .trc file. B. Run SQL 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. Answer: A Reason: 记录到数据库无疑加重了系统的负担。 ---- / <>< o /| / <>< (o / |/| ) ) <@ < ( O )( \ |\| <>< ) <>< () \| \ \ 每天在网易游水的鱼 |
|
[关闭][返回] |