SQL Server Index Optimization Automation (Missing & Unused Index Management)

Pehle basics — Index hota kya hai?

Simple bhaasha mein — index ek kitaab ka index page hota hai. Jaise tum dictionary mein "Zebra" dhundne ke liye seedha Z section pe jaate ho, poori dictionary page by page nahi padhte — waise hi SQL Server index use karke seedha sahi data pe jump karta hai.

Index nahi hoga toh SQL Server kya karega? Full Table Scan — matlab poori table row by row padho. Agar table mein 50 lakh rows hain, toh... haan, wahin ruk jaata hai sab kuch.

🔑 Key Types

Clustered Index — Table ki physical sorting. Ek table mein sirf ek hi ho sakta hai (primary key usually).
Non-Clustered Index — Alag se bana hua pointer — jaise book ke end mein subject index. Multiple ho sakte hain.

Toh problem kya hai? Jitne zyada index utna acha?

Yahi sochta hai ek naya developer. Aur yahi galti hai.

Index ek double-edged sword hai. Read queries fast karta hai — lekin har INSERT, UPDATE, DELETE pe SQL Server ko wo index bhi update karna padta hai. Matlab — zyada indexes = write operations slow.

3 problems jo every DBA ko face karni padti hain

Problem 1 — Missing Indexes

Koi query slow chal rahi hai kyunki uske liye sahi index exist hi nahi karta. SQL Server khud ye track karta hai — DMV (Dynamic Management Views) mein suggestions store karta hai. Wo keh raha hota hai — "bhai, agar tum yahan index banao toh mere 78% kaam aasaan ho jaata."

Source: sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats

Problem 2 — Unused Indexes

Kai indexes bane hote hain ek purani query ke liye jo ab run hi nahi hoti. Wo index table par baith ke sirf write overhead badha raha hota hai. Koi use nahi karta — lekin har INSERT pe wo update hota rehta hai.

Source: sys.dm_db_index_usage_stats

Problem 3 — Duplicate Indexes

Alag alag developers ne alag alag time pe same columns pe indexes bana diye. Ab do indexes hain jo exact same kaam kar rahe hain — double overhead, zero benefit.

Sahi approach — 3 phases ka DBA workflow

Real world mein bade organizations mein — banks, fintech, enterprise payroll systems — index changes ek proper process se guzarte hain:

1. DETECT — Automated nightly job (safe)

SP sirf detect kare aur report kare. Koi DDL nahi chalana. SQL Agent se roz raat chalao. Missing indexes ki list, unused ki list, duplicates — sab ek clean result set mein DBA ke paas aaye.

2. REVIEW — DBA manually dekhe

Har suggestion pe DBA khud sooche — kya ye existing index se better hai? Table pe write load kaisi hai? Kab last restart hua? Seasonal query toh nahi? Duplicate hai kya? Iske baad SP scripts generate kare aur AuditLog mein store kare.

3. EXECUTE — Maintenance window mein, manually

Review ke baad, off-peak hours mein, DBA manually @DBAConfirm = 1 pass karke chalaye. Ek ek index. Monitor karo. Rollback plan ready rakho.

SP ka final design — 5 Modes

Mode 5 mein ek extra safety layer hai — uptime check:

-- Server restart ke baad kitne din hue?
SELECT @UptimeDays = DATEDIFF(DAY, sqlserver_start_time, GETDATE())
FROM sys.dm_os_sys_info;

-- 30 din se kam = drop karna unsafe hai
IF @UptimeDays < @MinUptimeDays
    RAISERROR('Uptime sirf %d din — usage stats reliable nahi hain abhi.', 16, 1, @UptimeDays);

✅ Best Practice

Mode 1, 2, 3 — roz raat 2 baje SQL Agent se chalao. Mode 4, 5 — kabhi bhi Agent se mat chalao. Ye sirf DBA maintenance window mein manually chalayega.

AuditLog — Sab kuch track karo

Ek acha DBA hamesha audit trail rakhta hai. Hamara ADTDB_IndexAuditLog table 3 naye columns ke saath:

DBA kabhi bhi yeh query chala ke pending scripts dekh sakta hai:

-- Wo scripts jo generate hue hain lekin abhi execute nahi hue
SELECT
    IndexName, TableName, ActionTaken,
    ScriptGenerated   AS [Review this before running],
    SafetySkipReason  AS [Why skipped?],
    LoggedBy, DateLogged
FROM dbo.ADTDB_IndexAuditLog
WHERE ActionTaken = 1 -- ScriptGenerated
  AND Executed = 0
ORDER BY DateLogged DESC;

Woh EXEC wala bug — jo sab miss karte hain

Ye ek classic SQL Server gotcha hai. Bahut log is mein fase hain:

-- ❌ WRONG — ye dynamic SQL nahi chalata
-- SQL Server is variable ko stored proc naam samjhega
EXEC @IndexScript

-- ✅ CORRECT — parentheses zaroori hain dynamic SQL ke liye
EXEC (@IndexScript)

-- Ya phir sp_executesql use karo (zyada safe, parameterized)
EXEC sp_executesql @IndexScript

ONLINE = ON — Production ka golden rule

Jab bhi production pe index create karo, ONLINE = ON use karo. Iske bina SQL Server table pe schema lock le leta hai — aur us dauran koi bhi query us table ko access nahi kar sakti.

-- ❌ Bina ONLINE — table lock ho jaayegi
CREATE INDEX [idx_Employee_Dept]
ON dbo.Employee (DepartmentId)

-- ✅ ONLINE = ON — users unaffected rehenge
CREATE INDEX [idx_Employee_Dept]
ON dbo.Employee (DepartmentId)
WITH (ONLINE = ON, FILLFACTOR = 85)

📝 Note

ONLINE = ON sirf SQL Server Enterprise ya Developer edition mein available hai. Standard edition mein ye option nahi hai — toh Standard pe maintenance window mein carefully karo.

· · ·

Quick usage reference

-- Daily nightly job (SQL Agent se safe)
EXEC dbo.dbusp_IndexMaintenance @Mode = 1;  -- Detect
EXEC dbo.dbusp_IndexMaintenance @Mode = 2;  -- CREATE scripts generate
EXEC dbo.dbusp_IndexMaintenance @Mode = 3;  -- DROP scripts generate

-- DBA manually review kare AuditLog se scripts
-- Phir maintenance window mein:
EXEC dbo.dbusp_IndexMaintenance @Mode = 4, @DBAConfirm = 1;
EXEC dbo.dbusp_IndexMaintenance @Mode = 5, @DBAConfirm = 1;

-- Custom thresholds ke saath
EXEC dbo.dbusp_IndexMaintenance
    @Mode                = 1,
    @MinImprovementScore = 500,   -- Lower = more suggestions
    @MaxIndexesPerTable  = 6,    -- Max NC indexes per table
    @MinUptimeDays       = 45;   -- Stricter uptime gate

· · ·

Summary — Key takeaways

✓

Auto-drop production mein kabhi nahi

dm_db_index_usage_stats restart pe reset hota hai. Seasonal queries saal mein ek baar chalti hain. Human review zaroori hai.

✓

EXEC (@script) — parentheses mat bhuolo

Dynamic SQL ke liye EXEC (@variable) zaroori hai. EXEC @variable silently fail karta hai ya galat SP call karta hai.

✓

Script pehle, execute baad mein

SP pehle script generate kare, AuditLog mein store kare, DBA review kare — tab execute. Ye extra step kaafi incidents bachata hai.

✓

user_updates bhi dekho unused index pe

Ek index reads mein zero ho lekin writes mein high — matlab drop karne se write overhead toh hatega lekin koi aur benefit nahi. Carefully decide karo.

✓

ONLINE = ON — production ka golden rule

Bina is option ke index create karne se table lock ho jaati hai. Enterprise edition hai toh hamesha use karo.

"Ek acha DBA woh nahi hota jo sabse zyada changes karta hai — woh hota hai jiske changes se production kabhi nahi tuta."