You are in the beta testing process of the application for Bilco. You then discover
that the Web service takes several seconds to return. You also notice that a large
number of transactions are held on the Bilco-DB01 when a large payroll is
processed. A script that iterates through a company's employees and inserts a paycheck for
each employee into the VCP-310paychecks
table is currently used to process the paychecks.The script is initiated when the customer clicks
on the Submit Payroll button on the
Web site. A trigger on the Paychecks table retrieves deduction information. The
trigger has a single transaction to ensure that if any deduction is incalculable, an
error is logged and the transaction is rolled back.A SQL Profiler trace reveals that Bilco-DB01
waits for the GetFederalTax procedure to return before calling the GetStateTax procedure. Bilco want the
payroll processing performance to be optimized.

What should you do?

A. Change the trigger to send messages to Service Broker queues.
B. Change the 640-802trigger to use distributed transactions.
C. Change the isolation level to serializable.
D. Make use of implicit transactions and not explicit transactions.

Answer: A
Explanation: The trigger should be changed to send messages to Service Broker
Queues. By creating a queue for handling the GetFederalTax and a queue for
handling the GetStateTax procedures, you will allow for asynchronous handling of
messages thus reducing the transaction locks that are held on the data.

1. Only a single computer will be available to run SQL Server 2005. You must
accomplish 350-001the tasks using the fewest possible instances of SQL Server. Performance
should always be optimized.

Incorrect answers:
B:
Since only a single server namely BIlco-DB01 is involved in the transaction, you should
not change the trigger to use distributed transactions. Although the Web services might
be implemented by a SQL Server, you are not using distributed queries to access it.
C: A Serializable isolation level will result in isolating each transaction, thus preventing
data from being accessed until the transaction commits.
D: Implicit transactions do not require one to begin a transaction, but you must still either
commit it or roll it back. It 70-649also will not reduce the number of transaction locks or make
provision for asynchronous messaging.