Part - 7 MCSE Interview
QUESTION 41
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01.
Certkiller -DB01 contains a database that consists of two files:
1. The primary file belongs to the Primary filegroup.
The primary file is located on drive C.
2. The secondary file belongs to a user-defined filegroup named Filegroup1.
Filegroup1 is defined as the default filegroup.
The secondary file is located on drive D.
The operating system files are also located on drive C. Drive D is running out of space. To this end you decide to add a new disk to Certkiller -DB01 to enable you
to add more data to the database.
What should you do?
A. The maximum allowed size for the secondary file should be reduced on the Properties sheet for the database.
B. A new file within the Primary filegroup should be created on the new disk.
C. A new file within Filegroup1 should be created on the new disk.
D. A new file in a new filegroup should be created on the new disk.
Answer: C
Explanation: Files that belong to the same filegroup fill with data in a way that is
proportionate to the amount of free space that exists in each file. The new file within Filegroup1 will take the largest portion of new data because it is empty. Thus the existing file will take less data. Overtime both the files on Filegroup1 will fill approximately the same time.
Incorrect answers:
A: You cannot reduce the maximum allowed size for the secondary file on the
Properties sheet for the database.
B: All system tables reside in the Primary filegroup which also serves as the default
filegroup after the installation of SQL Server. If files in Primary filegroup run out
of space, then the database will become unmanageable. In this case however, the
disk that hosts the Primary filegroup is not running out of space, thus it is not
necessary to create a new file within Primary and place it on a new disk.
D: Creating a new filegroup will not result in the automatic filling of your data into the new filegroup until new objects on that filegroup are created or until existing objects are moved to it
QUESTION 42
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
The Certkiller .com network has started making use of a database that has a
SalesPersons table storing business information. The Certkiller .com SalesPersons
table is shown below:
QUESTION 43
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
You are responsible for administering the databases on the Windows 2000 server
which has tables that are enabled for full-text indexing. The Certkiller .com network
users can successfully run full-text queries. You additionally started regularly
backing up all databases on the Windows 2000 server.
During the course of the business day the Windows 2000 Server suffered a
hardware failure and the entire computer is lost. Certkiller .com has decided to
acquire a new computer with identical hardware and you install the appropriate
software and restore the databases from the latest backups. You later decide to test
The Certkiller .com network users use the SalesPersons table to record transactions
and generate various reports. The Certkiller .com management has recently noticed
that performance has degraded noticeably. You are required to speed up the
transaction recording and report generating.
What should you do?
A. Join hints should be added to all queries for the SalesPersons table.
B. A DROP INDEX statement should be executed.
C. The Auto update statistics database option should be disabled.
D. The structure of the data should be normalized and create the appropriate indexes.
Answer: D
Explanation: In the scenario you should remember that to efficiently manage online
transaction processing, you require normalizing the database. The normalization of
a database is used to minimize redundancy in a database resulting in fewer tables
with columns to manage.
Incorrect Answers:
A:You should not consider making use of joint hints in the scenario because the statementis used in FROM clauses of DELETE, SELECT and UPDATE statements.
B: This option should not be considered in the scenario as you would be dropping the
specified indexes. Properly configured indexes are sure to reduce the amount of time for reports to be generated.
C: This option should not be used in the scenario because the administrator would then be required to manually update statistics when new data is added to the database.
QUESTION 44
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com makes use of a SQL Server 2000 database to record the business
transactions. Certkiller .com has configured the database with the default options
and is depicted below:
The recent reports of Certkiller .com should be generated monthly which indicate the
amount of sales produced by each employee. Below is a sample query shown to
retrieve the data:
SELECT LastName, FirstName
Total = SUM (p.UnitPrice * (1 - od.Discount) * od.Quantity)
FROM Employees e JOIN Orders o ON e.EmplopyeeID = o.EmployeeID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
WHERE DATEPART (mm, OrderDate) = 7 AND DATEPART (yy, OrderDate) = 2000
GROUP BY LastName, FirstName
ORDER BY LastName, FirstName
The result of the query leaves the network users reporting that their queries are
slow. You are required to improve the performance of the queries.
What should you do?
A. Nonclustered indexes should be created on all foreign key columns.
B. The transaction log file and the data files should be placed on the same physical drive.
C. The UPDATE STATISTICS statement should be executed against the Employee and
Orders tables.
D. A view should be created that is required to generate the monthly sales report.
Answer: A
Explanation: In the scenario you should remember that Foreign keys are columns
that define relationships between tables. By adding indexes on foreign key columns
the performance of the queries can be substantially improved.
Incorrect Answers:
B: You should not consider making this move in the scenario as this would not improve
the performance of the query.
C: You should remember in the scenario that by default the update statistical information option is enabled.
D: In the scenario you should not consider creating a view because it is a predefined
query which does not accept any parameters.
QUESTION 45
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
You have recently received instruction from the Certkiller .com CIO to monitor the
database performance and usage as he is concerned that the transaction log may
become full. The results of the monitoring show that the number of transactions per
time period is not increasing and you performed frequent transaction log backups
using the default backup settings. The amount of data contained in the log file still
continues to increase. You are required to select which of the following is the most
likely reason for this?
A. The trunk.log on chkpt database option has been disabled.
B. The DBCC SHRINKFILE statement was not run.
C. The DBCC SHRINKDATABASE statement was not run.
D. A long-running active transaction is contained in the log.
Answer: D
Explanation: In the scenario you should remember that when backing up the
transaction log using the default options only the inactive part of the log will be
truncated and the active part of the log can not be truncated.
Incorrect Answers:
A: You should not use this option in the scenario because this has the same effect as
using the Simple Recovery model.
B, C: This option may be used to shrink the size of the database files providing that
enough space within the files for shrinking exists.
QUESTION 46
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com network currently uses a SQL Server 2000 database to store detailed
information about sales transactions. The Certkiller .com sales director frequently
has to run ad hoc queries against the database to determine the total amount of
orders placed by customers and the region from which the orders are placed in a
given month.
The Certkiller .com sales director reports that the query performance is slow and he
does not want to write complex queries that specify joins in order to retrieve the
data. You decided that you do not need to build a data warehouse but you want to
improve the response times of the queries.
What should you do?
A. The aggregated data should be calculated and maintain with the help of triggers.
B. The query governor cost limit option should be disabled.
C. The SET SHOWPLAN_TEXT option should be enabled.
D. An application should be created in which the queries have been properly optimized.
Answer: A
Explanation: In the scenario you should remember the Aggregate function can be
used to calculate aggregated values from detailed information. You should also
remember that additional columns can be added for the prorogated data, to ensure
data is accurate you can use triggers that will detect changes made to the tables.
Incorrect Answers:
B: You should not consider this option in the scenario as no query will be prevented from being executed no matter how much resources will be consumed.
C: This option should not be used in the scenario because it is used to force SQL to show you each step in the execution of a query without actually executing the query.
D: This option should not be considered in the scenario because the application will not meet the requirements of the Certkiller .com sales director.
QUESTION 47
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com has recently implemented a SQL Server 2000 database which is used
to store business information and manage daily activities. You have received
instruction to implement a new application. When you test the application error
1205 is generated.
This error is sometimes returned by the new application at other times the network
users report that they receive it. You are required to ensure that the new application works properly and that users are not adversely affected.
What should you do?
A. The transaction log should be backed up.
B. The new application should be configured to use autocommit mode.
C. The number of system locks should be increased.
D. The value that is specified in the min memory per query option should be decreased.
Answer: B
Explanation: In the scenario you should always remember that a deadlock occurs
when two connections to SQL Server block each other by holding locks on
information that is required. This is the reason why the error 1205 is received at
times by the application and reported by the users.
Incorrect Answers:
A: This option should not be used in the scenario although it is a good practice when
administrating databases.
C: This option should not be used in the scenario it should only be considered when the error 1204 was received which was not so in the scenario.
D: This option should not be used in the scenario as it will not guarantee that the query will execute properly in the scenario.
QUESTION 48
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com currently makes use of a SQL 2000 database for storing business
information. The server hosting the database is configured with the default settings.
The network users of Certkiller .com make use of a client application programmed to
call three stored procedures. You have recently received instruction from the
network CIO to bulk-load a vast amount of new data into the database.
In order to start the process you are required to first drop any existing indexes on
the table involved in the bulk-load. You later should bulk-load the new data and
recreate the indexes. The Certkiller .com network users started reporting that the
results of the bulk-load have caused the client application used to query the
database to become unacceptably slow. You are required to improve the
performance of the application.
What should you do?
A. The stored procedure used by the application should be recompiled.
B. Additional indexes should be added to the queried tables.
C. Statistics should be manually created in the database.
D. Update statistics should be manually created in the database.
Answer: A
Explanation: In the scenario you should remember that the stored procedure
worked fine before the bulk-load there for the stored procedure should be
recompiled in order to make a new execution plan for the application.
Incorrect Answers:
B: You should not consider taking this action in the scenario because the action taken will not ensure improved performance using the application.
C, D: In the scenario you should always remember that there is no need to manually
force statistics to be update as you will still require recompiling the stored procedures.
QUESTION 49
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com has recently decided to expand its business to the Internet by
implementing a Web-site which users can use to purchase the products.
Certkiller .com has enabled the customers to be able to order products in stock and
place orders for products not currently in stock. The network users using the
Web-site search for merchandise by scrolling through a list in their Web browsers.
The Internet community has recently complained that the application is very slow.
The SQL Server 2000 database that Certkiller .com uses to store product information
is extremely large. You decide to use SQL Profiler to capture a trace and you
discover the application issues the following statement:
SELECT * FROM Products
You are required to improve the performance of the application.
What should you do?
A. The application should be configured to use a WHERE clause in the query.
B. The application should be configured to use a GROUP BY clause in the query.
C. Separate views should be created for the most popular products.
D. The Products table should be specified as read-only.
Answer: A
Explanation: In the scenario you should remember shat using the SELECT *
FROM Products statement does not specify any search criteria limiting its output.
By using the WHERE clause in the query you would be limiting the query by
imposing a boundary which will reduce the output and improve the performance of
the application.
Incorrect Answers:
B: This option should not be used in the scenario because it is used to specify the
columns by which rows must be grouped when aggregate functions are included in the
select list.
C: This option should not be used in the scenario because this will not improve the
performance of the application as required by the customers.
D: This option should not be used in the scenario because this will not allow the
modification of the data and can only be specified at the filegroup or database level.
QUESTION 50
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com currently uses a SQL Server 2000 database to store business
information. The database used consists of two filegroups: CK_FGR01 and
CK_FGR02 and no tables span the filegroups. Recently Certkiller .com network
users report that he made an incorrect change to the Bill table residing on
CK_FGR02.
You are required to restore the database from the backups to the state it was before
the wrong transaction was made. The Certkiller .com network have configured their
databases to use Full Recovery Model be default. The full database backup is made
nightly and transaction log backups are made every hour.
What should you do? (Choose all that apply.)
A. The transaction log should be truncated.
B. The database should be restored from the latest full database backup with recovery.
C. The active log backup should be restored with recovery.
The log backups that were made since the last database backup without recovery should
be restored.
D. CK_FGR02 should be restored from the latest full database backup without recovery.
E. The active transaction log should be backed up.
Answer: C, D, E
Explanation: In the scenario you should immediately backup the active transaction
log and then restore CK_FGR02 from the latest full database backup and specify
WITH NORECOVERY to apply transaction log backups after the database is
restored.
Incorrect Answers:
A: There is no need to perform this action in the scenario because this is done
automatically in the scenario.
B: There is no need in the scenario to have the entire database restored from the latest full database backup as this will not help achieve the scenario objective.
Post a Comment