Job Title: Senior SQL Performance Engineer (Indexing & Tuning Expert)

Location: Remote Experience Level: Senior / Expert

About the Role

We are seeking a SQL Expert with a deep specialization in database internals, query lifecycle analysis, and high-concurrency performance tuning. In this role, you will not just "maintain" databases; you will be the primary authority on why a query is slow and how to fix it. You will work closely with our backend engineering teams to review schema design, optimize complex stored procedures, and implement advanced indexing strategies that ensure our systems scale efficiently under heavy load.

Key Responsibilities

1. Query Optimization & Analysis

• Analyze and rewrite complex, high-volume SQL queries to minimize I/O and CPU overhead.
• Deep-dive into Execution Plans to identify bottlenecks such as implicit conversions, table scans, and inefficient joins.
• Identify and resolve deadlocks, blocking, and latch contention issues in a high-transaction environment.
• Utilize Extended Events, Profiler, or Query Store to trace and troubleshoot sporadic performance dips.

2. Advanced Indexing Strategy

• Design and implement comprehensive indexing strategies (Clustered, Non-Clustered, Filtered, and Columnstore indexes).
• Conduct regular audits of unused, duplicate, or overlapping indexes to reduce write-overhead.
• Manage index fragmentation and implement automated maintenance routines (Rebuild vs. Reorganize) based on fill-factor analysis.

3. Server & Instance Tuning

• Analyze Wait Statistics (Wait Types) to determine system bottlenecks (e.g., CXPACKET, PAGEIOLATCH, SOS_SCHEDULER_YIELD).
• Configure and tune database engine parameters (Max Degree of Parallelism, Cost Threshold for Parallelism, Memory Allocation).
• Optimize TempDB configuration and file placement to reduce contention.

4. Architecture & Mentorship

• Review database schema changes and verify proper normalization/denormalization prior to deployment.
• Act as a subject matter expert (SME) for developers, conducting code reviews and teaching "set-based" thinking vs. "row-by-row" (cursor) processing.
• Establish performance baselines and alert thresholds.

Required Technical Qualifications

• Experience: 7+ years of deep experience in Database Development and Performance Tuning (specifically on [SQL Server / PostgreSQL / Oracle]).
• Mastery of Indexing: Proven expertise in B-Tree structures, Page splits, Heap tables, and Include columns.
• Tuning Tools: Expert-level proficiency with native monitoring tools (SSMS, PerfMon, DMV/DMFs) and 3rd party tools (e.g., Redgate, SolarWinds, SentryOne).
• Internals Knowledge: Strong understanding of ACID properties, Isolation Levels (and their impact on locking), and the Transaction Log architecture.
• Scripting: Ability to write robust automation scripts (T-SQL, PL/SQL, PowerShell, or Python) for health checks and maintenance.

Nice to Have (Preferred)

• Experience tuning databases in a Cloud environment (AWS RDS, Azure SQL, Google Cloud SQL).
• Knowledge of NoSQL databases for hybrid data models.
• Experience with partitioned tables and sliding window scenarios for massive datasets.

Core Competencies

• Detective Mindset: You enjoy the "hunt" of finding the root cause of a performance issue.
• Calm Under Pressure: Ability to troubleshoot critical production slowdowns in real-time.
• Communication: Ability to explain complex technical concepts (like "parameter sniffing") to non-technical stakeholders.