Introduction

SQL Server me ek aisa database hai jo har query, har process aur almost har operation me involve hota hai.

Ye ek temporary workspace hai jahan SQL Server aur users dono apna temporary data store karte hain.

Agar TempDB slow ho gaya poora SQL Server slow ho jayega

Is blog me hum deep me samjhenge:

• TempDB kya hota hai
• Kya-kya store hota hai
• Performance tuning kaise kare
• Real DBA best practices

TempDB Kya Hai:- TempDB ek system database hai jo temporary data store karta hai

TempDB Me Kya Store Hota Hai

TempDB me 3 major types ka data store hota hai:

1️⃣ User Created Temporary Objects

Ye wo objects hain jo user khud create karta hai.

Examples:

• Temporary tables
• Table variables
• Temporary stored procedures
• Cursors

Example

CREATE TABLE #TempTable
(
    ID INT,
    Name VARCHAR(50)
)

✔ Ye table TempDB me store hota hai
✔ Session end → table delete

2️⃣ Internal Objects (SQL Server Created)

SQL Server khud bhi temporary objects create karta hai.

Examples:

• Sorting (ORDER BY)
• GROUP BY operations
• Hash joins
• Index rebuild
• Intermediate results

Example

SELECT Name, COUNT(*)
FROM Employees
GROUP BY Name

✔ SQL Server internally TempDB use karega

3️⃣ Version Store

Ye advanced concept hai.

Use hota hai:

• Row versioning
• Snapshot isolation
• Read committed snapshot

Simple Meaning

TempDB me old versions of data store hote hain taaki:

✔ Blocking kam ho
✔ Concurrency improve ho

TempDB Files Structure

TempDB me bhi 2 types ke files hote hain:

1️⃣ Data File

tempdb.mdf

2️⃣ Log File

templog.ldf

Default Settings

• Initial size → 8 MB
• Auto growth → 64 MB

⚠ Reality me ye values bahut choti hoti hain (production ke liye)

Multiple Data Files Concept

TempDB me multiple data files ho sakte hain.

Rule Based on CPU

Case 1: CPU ≤ 8

👉 Files = CPU cores

Example:

4 cores → 4 files
8 cores → 8 files

Case 2: CPU > 8

👉 Minimum 8 files se start karo

Agar contention aaye:

👉 4-4 karke increase karo

Example:

8 → 12 → 16 → 20

Important Concept – Contention

Contention = jab multiple queries same resource ke liye fight karte hain

Symptoms:

• Queries slow
• Wait types increase
• TempDB bottleneck

TempDB Restrictions ❌

TempDB par kuch limitations hoti hain:

• Backup nahi le sakte
• Restore nahi kar sakte
• Rename nahi kar sakte
• Offline nahi kar sakte
• Mirroring enable nahi
• CDC enable nahi
• Filegroup add nahi

Important

✔ TempDB automatically recreate hota hai restart par

TempDB Performance Optimization 🔥

Ab sabse important part 👇

1️⃣ Instant File Initialization (IFI)

IFI enable karne se:

✔ File growth fast hoti hai
✔ Performance improve hoti hai

Simple Explanation

Without IFI:

Disk pe pehle zeros likhe jaate hain → slow

With IFI:

Direct data write hota hai → fast 🚀

2️⃣ Pre-Allocate Size

Bad practice:

8 MB se start → baar baar auto grow

Good practice:

Initial size = 2GB / 4GB (workload ke hisab se)

3️⃣ Equal Size Files

Galti:

File1 = 100GB
File2 = 10GB

SQL Server sirf bada file use karega ❌

Best:

All files = equal size ✔

4️⃣ Auto Growth Proper Set Karo

Bad:

Growth = 64 MB

Good:

Growth = 1GB (1024 MB)

5️⃣ Separate Disk Use Karo

Best practice:

✔ TempDB ko SSD par rakho
✔ User DB se alag drive par rakho

Reason

TempDB heavy IO use karta hai

👉 Same disk → bottleneck
👉 Separate disk → fast performance

TempDB Move Kaise Kare

Kab use hota hai:

• Disk full ho
• Faster disk use karna ho

Steps

Step 1: Current location check

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')

Step 2: Location change

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\TempDB\tempdb.mdf')

Step 3: SQL Server restart

✔ Changes apply honge

Step 4: Old files delete karo

Real DBA Scenario

Problem:

SQL Server slow

Check:

• TempDB contention
• Disk IO high
• Auto growth frequent

Solution:

✔ Files increase karo
✔ Disk change karo
✔ Query optimize karo

Common Mistakes

❌ TempDB size chhota rakhna
❌ Single file use karna
❌ Auto growth chhota rakhna
❌ Same disk par sab rakhna

Key Points

• Temporary data store karta hai
• User + system dono use karte hain
• Restart par reset ho jata hai

Important Usage

• Temp tables
• Sorting
• Joins
• Index operations

Best Practices

✔ Multiple files use karo
✔ Equal size rakho
✔ SSD par rakho
✔ Auto growth optimize karo
✔ IFI enable karo

Final Advice

TempDB tuning = SQL Server performance tuning ka major part

Agar aap DBA banna chahte ho:

👉 TempDB deeply samajhna must hai