Question No: 108
You are the administrator of a SQL Server 2000 computer. The server is configured as shown in the
Database Server Configuration exhibit.
You need to create a new database named Inventory. Employees in your company will use the
Inventory database to track inventory data. Users will require immediate responses to queries that
help them locate where parts are stored. The tables in the database will be configured as shown in the
Database Schema exhibit.
The database will consume 14 GB of disk space. You must configure the data files and transaction log
to accelerate query response time.
Which two courses of action should you take? (Each correct answer represents part of the solution.
Choose two.)

A. On drive C, create a transaction log.
On drive D, create a data file in the PRIMARY filegroup.
On drive E, create a data file in the SECONDARY filegroup.
B. On each drive, create a transaction log.
On each drive, create a data file in the PRIMARY filegroup.
C. On drive D, create a transaction log.
On drive E, create a data file in the PRIMARY filegroup.
D. On the PRIMARY filegroup, create all tables and all indexes.
E. On the PRIMARY filegroup, create all tables. On the SECONDARY filegroup, create all
indexes.
F. On the PRIMARY filegroup, create the Parts table and its indexes.
On the SECONDARY filegroup, create all other tables and their indexes.
Answer: A, E.

Explanation:
A: The transaction log should be placed on a separate disk. A transaction log would not benefit of two or
more disks since it is sequential.
Create two data files on two separate physical disks would improve performance.
E: Seperating the tables and the indexes improve performance for in particular joins.
Note: With SQL Server 2000 it is possible to create tables or indexes on specific filegroups. This allows us
to control where the database’s tables and indexes are physically located as filegroups can be placed on
different hard drives. Placing tables in one filegroup and the table’s nonclustered indexes in another
filegroup on different physical disk can improve database performance because it will allow separate threads
to access the tables and indexes. However, a table and its clustered index cannot be separated into different
filegroups as the clustered index determines the physical order of the data in the table. Furthermore, the
transaction log should be placed on a physically separate disk. The transaction log file is written serially;
therefore, using a separate, dedicated disk allows the disk heads to stay in place for the next write operation.
This will further improve performance.

Incorrect Answers:
B: To improve performance, the transaction log should not be placed on the same physical disk that the
tables or indexes are placed on. The transaction log file is written serially; therefore, placing it on a
separate, dedicated disk allows the disk heads to stay in place for the next write operation and thus
improves performance. Furthermore, placing tables in one filegroup and the table’s nonclustered
indexes in another filegroup on different physical disk can improve database performance because it
will allow separate threads to access the tables and indexes. However, a table and its clustered index
cannot be separated into different filegroups as the clustered index determines the physical order of
the data in the table.
C: The transaction log should be placed on a physically separate disk. The transaction log file is written
serially; therefore, using a separate, dedicated disk allows the disk heads to stay in place for the next
write operation. This will further improve performance. In this solution, the tables and their indexes
are placed on the same disk. This solution thus does not take advantage of the performance
improvements that can be realised through the correct utlization of the available hardware.
D: In this solution, the tables and their indexes are placed on the same disk. This solution thus does not
take advantage of the performance improvements that can be realised through the correct utlization
of the available hardware.
F: Greater performance gains can be realized by placing tables in one filegroup and the table’s
nonclustered indexes in another filegroup on different physical disk. This will allow separate threads
to access the tables and indexes.

-----------------------
В TestKing дан ответ a, e
Но e не получится из-за кластерных индексов (PrimaryKey по умолчанию clastered)
Остается ответ a, f. Что думаете про это?