You are a database developer. You plan to design a database solution by using SQL Server
2008. The database contains a table that has two partitions. The first partition contains the
current data, and the second partition contains historical data. There is a high frequency of
data manipulation on the current data. The testers report that they are frequently
unsuccessful in querying the historical data only. You discover that locks are being
escalated to the table-level lock. You need to ensure that without modifying the SELECT
statements, you enable users to query the historical data. You want to achieve this goal by
using the minimum amount of administrative effort and minimally affecting other queries.
What should you do?
A.
Move the historical partition to a read-only filegroup.
B.
Set the Server Startup Parameters to include -T1211.
C.
Use the ALTER TABLE … SET (LOCK_ESCALATION = AUTO) command.
D.
Set DEADLOCK PRIORITY to HIGH.