Home » Microsoft » MB6-886 » Which action should you take?
You are a database developer on an instance of SQL Server 2008. You receive input from several business partners as XML documents. You process the incoming
XML documents and store the data in the InData table in an xml data type column.
You have a stored procedure named ProcessUpdate that includes a transaction that performs the following tasks:
Updates several rows in a lookup table
Updates rows in the Master table based on the xml column value in the InData table
Deletes rows from the InData table for rows successfully processed. You expect a high volume of incoming XML files and are concerned about performance of the ProcessUpdate stored procedure. You want to ensure that queries do not time out or cause lock escalation if possible. Which action should you take?
A. Store the incoming XML documents on the file system instead of in the database.
B. Modify the ProcessUpdate stored procedure to execute multiple transactions, with each transaction processing a smaller batch of incoming XML documents.
C. Set the transaction isolation level to SERIALIZABLE.
D. Include the TABLOCK table hint on the query that updates the Master table.
Correct Answer: B
Explanation/Reference:
You should modify the ProcessUpdate stored procedure to execute multiple transactions, with each transaction processing a smaller batch of incoming XML documents. When a transaction is processing a large volume of rows and performing a large number of DML operations, it often adversely affects performance.
Transactions should be as short as possible to minimize locking issues for other users. When the volume is high, you can split the work of the transaction into smaller batches and commit each of the smaller batches as they are processed. This will reduce the memory required for processing and minimize blocking and contention issues for other database users.
You should not store the incoming XML documents on the file system instead of in the database. Using FILESTREAM storage would reduce the database size, but the transaction would still process all of the data, and would likely cause concurrency issues when performing the updates and deletes.
You should not set the transaction isolation level to SERIALIZABLE because this would likely cause concurrency issues. The SERIALIZABLE transaction isolation level is the most restrictive and completely isolates transactions from one another. Because you may be processing a large number of rows, this could adversely affect other users accessing the Master table.
You should not include the TABLOCK table hint on the query that updates the Master table. The TABLOCK table hint can be specified within a query to lock a specific table while the query executes. In this scenario, you have a transaction that is performing multiple DML tasks, and locking the table during one of the transaction’s activities would not improve overall performance for the transaction.
