- CPU Bottlenecks
- High CPU usage may indicate inefficient queries or parallelism issues.
Memory Pressure
-
Insufficient memory leads to excessive disk I/O.
-
-
Disk I/O Bottlenecks
-
Check db data and log disk having very good speed I/O
Auto-Grow Settings
-
Default auto-growth may cause frequent interruptions. Use manual sizing where possible.
-
-
TempDB Configuration
-
Too few data files, contention on metadata, or slow disks can all cause issues.
Long-Running Transactions
-
Hold locks too long, blocking others.
-
-
Inappropriate Isolation Levels
-
SERIALIZABLE or REPEATABLE READ can increase blocking unnecessarily.
-
-
Use of Hints (e.g., NOLOCK)
-
May reduce blocking but introduce dirty reads.
CPU: Look for sustained high usage. Check for long-running queries or parallelism issues.
-
Memory: CRM is memory intensive. Ensure SQL Server has sufficient RAM and isn't competing with other services.
-
Disk: Place
TempDB, logs, and data files on separate high-speed drives. - SQL Server Log file having enough space
- SQL server data file having enough space
