Explain how parallel execution works and when it provides benefits.

Parallel execution divides work across multiple worker processes. The coordinator process spawns workers, each processing a partition of data. Workers perform partial aggregations or sorts, then the coordinator merges results. Benefits: Large table scans (>8MB), aggregations, sorts, hash joins. Overhead: Worker startup (~10ms each), coordination, memory per worker. Sweet spot: 2-4 workers. Diminishing returns beyond 8 workers. Not beneficial for: Small result sets, index seeks, memory-constrained systems.

What is parameter sniffing and how do you handle it?

Parameter sniffing occurs when the optimizer generates a plan based on the first parameter values it sees, then caches that plan. If those values are atypical, the cached plan may be suboptimal for other values. Example: First call with status=pending (1% of rows) generates index scan plan. Second call with status=completed (99% of rows) uses same plan but should use table scan. Solutions: 1) OPTION (RECOMPILE) to regenerate plan each time, 2) OPTIMIZE FOR hint with typical values, 3) Plan guides to force specific plans, 4) Local variables to prevent sniffing.

How do you tune the cost model for your hardware?

Cost model tuning adjusts optimizer parameters to match hardware characteristics. Key parameters: random_page_cost (default 4.0 for HDD, set 1.1 for SSD, 1.0 for NVMe), seq_page_cost (baseline 1.0), effective_cache_size (available RAM for caching), work_mem (per-operation memory). Impact: Changing random_page_cost from 4.0 to 1.1 makes optimizer prefer index scans on SSDs. Test changes with EXPLAIN and validate with production workload. Document tuning decisions.

Given an execution plan showing parallel execution with poor efficiency, how would you optimize it?

Step 1: Check Workers Planned vs Launched - if mismatch, investigate resource constraints. Step 2: Calculate parallel efficiency: total_time / (num_workers + 1). Target >70%. Step 3: Check if workers have similar execution times - imbalance indicates poor partitioning. Step 4: Verify table size justifies parallelism (>8MB). Step 5: Check work_mem - each worker needs memory. Step 6: Consider if operation benefits from parallelism (scans yes, seeks no). Step 7: Adjust max_parallel_workers_per_gather or use MAXDOP hint. Step 8: For poor partitioning, consider manual partitioning or different parallel strategy.

Parallel execution efficiency depends on even work distribution, sufficient resources, and appropriate operations. Poor efficiency often indicates resource contention, inappropriate parallelism, or data skew.