Permit counter OLTP spikes show high CXPACKET waits while short transactional queries run. Which action fits a careful performance response?
Select an answer to reveal the explanation.
Short Explanation
When you see high CXPACKET waits during a counter rush, short permit transactions may be spending too much time coordinating parallel work. You can review parallelism settings like MAXDOP and cost threshold—like adjusting traffic lights—so short queries don’t get a parade escort.
Full Explanation
High CXPACKET waits during permit-counter OLTP spikes, while short transactional queries run, usually mean parallel-plan coordination overhead is hurting latency-sensitive civic OLTP. A careful response is to review parallelism settings—MAXDOP and cost threshold for parallelism—so inexpensive permit transactions prefer serial plans, reserving parallelism for genuinely expensive reporting. That reduces thread-exchange chatter without treating parallelism as always wrong. Forcing every permit query to use a parallel plan with unlimited threads can amplify CXPACKET risk and starve the counter workload. Disabling all indexes pushes the optimizer toward scans and often more parallelism, worsening OLTP spikes. Storing permit fees only in client cookies abandons durable, auditable municipal fee data and does not address SQL wait statistics. Exam caveat: CXPACKET, and related CX* waits on newer engines, can also appear when parallel plans are appropriate but skewed; the fix is not always MAXDOP 1 everywhere. Azure SQL and Managed Instance defaults differ from on-premises, so set MAXDOP thoughtfully per workload class. Cost threshold changes what becomes parallel, not just MAXDOP. Practical check: capture CXPACKET share and actual plans for counter queries during a spike; note DOP and whether short OLTP queries are parallel. In a test permit database, raise the cost threshold and/or set a bounded MAXDOP, then replay the counter mix. Confirm CXPACKET and p95 latency improve without harming large overnight reports, which may need a separate MAXDOP policy.