Query Tunning

                                                 Query Tunning

Let consider table design.

CREATE TABLE Employee

(

    EmployeeID        INT IDENTITY(1,1) PRIMARY KEY,

    EmployeeCode      VARCHAR(20) NOT NULL UNIQUE,

    FirstName         VARCHAR(100) NOT NULL,

    LastName          VARCHAR(100) NULL,

    FullName          AS (FirstName + ' ' + ISNULL(LastName,'')) PERSISTED,

    Email             VARCHAR(150) NULL,

    MobileNo          VARCHAR(15) NULL,

    Gender            CHAR(1) NULL,      -- M/F/O

    DateOfBirth       DATE NULL,

    JoinDate          DATE NOT NULL,

    DepartmentID      INT NOT NULL,

    DesignationID     INT NOT NULL,

    ManagerID         INT NULL,

    EmploymentType    VARCHAR(20) NULL,  -- Permanent/Contract

    IsActive          BIT NOT NULL DEFAULT 1,

    CreatedDate       DATETIME2 DEFAULT GETDATE(),

    CreatedBy         VARCHAR(50),

    ModifiedDate      DATETIME2 NULL,

    ModifiedBy        VARCHAR(50)

);

✔️ Check Actual Execution Plan

Always start with:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Look for:

• Table scans

• Key lookups

• High logical reads

• Parallelism operators

Rule: High logical reads = performance problem.

---

✔️ Avoid SELECT *

Bad:

SELECT * FROM Employee;

Better:

SELECT EmpID, Name, Dept FROM Employee;

Why:

• Reduces I/O

• Uses covering indexes better.

---

🔎 2️⃣ Index Tuning

Clustered vs Non-Clustered

• Clustered → data storage order

• Nonclustered → lookup structure

Good Index Design

CREATE INDEX IX_Employee_Dept_Date
ON Employee(DeptID, JoinDate);

Tips:

• Put highly filtered columns first

• Include columns to avoid lookup:

CREATE INDEX IX_Sales
ON Sales(OrderDate)
INCLUDE (Amount, CustomerID);

---

❌ Over-Indexing Problems

Too many indexes cause:

• Slow INSERT/UPDATE

• Extra maintenance cost

Check unused indexes:

SELECT * 
FROM sys.dm_db_index_usage_stats;

---

🧠 3️⃣ WHERE Clause Optimization

Bad (non-SARGable)

WHERE YEAR(OrderDate) = 2025

Good (SARGable)

WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01'

Reason:

• Allows index seek.

---

🚀 4️⃣ JOIN Optimization

Always Join on Indexed Columns

Bad:

ON A.Name = B.Name

Good:

ON A.ID = B.ID

---

Check Join Type

• Nested Loop → small data

• Hash Match → large sets

• Merge Join → sorted data

If hash joins appear too often → missing indexes.

---

🧱 5️⃣ Temp Table vs Table Variable

✔ Use Temp tables for large data:

CREATE TABLE #Temp (...);

Why:

• Supports statistics

• Better execution plans

Table variables are slower for large datasets.

---

🔥 6️⃣ Parameter Sniffing Fixes

Problem:
Bad execution plan reused.

Solutions:

OPTION (RECOMPILE)

OR

DECLARE @LocalVar INT = @Param;

OR optimize with proper indexes.

---

⚙️ 7️⃣ Parallelism (Important for You)

You already asked about CXPACKET before 👍

Check:

SELECT * FROM sys.dm_os_wait_stats;

Guidelines:

• Cost Threshold for Parallelism → usually 40–50

• MAXDOP → based on CPU cores.

Too much parallelism = CPU 100%.

---

📊 8️⃣ IO & Wait Analysis

Find Top Expensive Queries

SELECT TOP 10
    total_worker_time,
    execution_count,
    query_hash
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;

---

Common Wait Types

Wait	Meaning	Action
CXPACKET	Parallelism	Tune MAXDOP
PAGEIOLATCH	Disk slow	Improve I/O
SOS_SCHEDULER_YIELD	CPU pressure	Query tuning
LCK_M_*	Blocking	Index/Query fix

---

🧹 9️⃣ Data Type Optimization

Bad:

NVARCHAR(MAX)

Better:

NVARCHAR(100)

Large datatypes = more memory + tempdb usage.