EngineeringSenior DeveloperSQL Deep Dive

Hướng dẫn tối ưu hiệu năng SQL Server

3 min read6 lessons in this module

Mục tiêu

Cung cấp các kỹ thuật và best‑practice để tối ưu hiệu năng cho các workload .NET Enterprise trên SQL Server (on‑prem và Azure SQL), giúp giảm latency, tăng throughput và tối đa hoá tài nguyên.

1. Thu thập số liệu (Metrics)

MetricMô tảCông cụĐề xuất ngưỡng
CPU %Sử dụng CPU của instancesys.dm_os_performance_counters< 70 % (trên 8 core)
Wait StatsCác loại wait (CXPACKET, LCK_M_X, PAGEIOLATCH)sys.dm_os_wait_statsXem top 5, giảm CXPACKET bằng parallelism
IORead/Write latency, MB/ssys.dm_io_virtual_file_stats< 5 ms avg latency
TempDB contentionPage latch, allocation bottlenecksys.dm_db_file_space_usage< 5 % allocation rate

2. Indexing

  • Clustered Index: Chọn cột độc nhất, tăng dần (ví dụ Id). Tránh clustered index trên GUID.
  • Non‑clustered Index: Tạo covering index cho các query thường (SELECT … WHERE CustomerId = @id).
  • Include Columns: Thêm các cột không cần filter vào INCLUDE để tránh lookup.
  • Filtered Index: Dùng cho các bảng lớn với phần dữ liệu ít được truy vấn (WHERE IsActive = 1).
  • Rebuild vs Reorganize: Khi fragmentation > 30 % → ALTER INDEX … REBUILD; 10‑30 % → REORGANIZE.

3. Statistics & Query Plans

  • Auto‑update statistics: Bật AUTO_UPDATE_STATISTICS_ASYNC để tránh block.
  • Manual update: UPDATE STATISTICS dbo.Orders WITH FULLSCAN; sau bulk load.
  • Plan Guides: Khi optimizer chọn plan kém, dùng sp_create_plan_guide.
  • Force‑Seek: Thêm OPTION (FORCESEEK) nếu cần.

4. Partitioning & Archiving

  • Table Partitioning: Partition theo OrderDate (monthly) để giảm scan trên bảng lớn.
  • Sliding Window: Di chuyển dữ liệu cũ vào Archive table hoặc Data Lake.
  • Switch Partition: ALTER TABLE dbo.Orders SWITCH PARTITION … TO dbo.OrdersArchive; nhanh và không lock.

5. TempDB tối ưu

  • Multiple Data Files: Số file = CPU cores * 1‑2 (ví dụ 8 core → 8‑16 files).
  • File Placement: Đặt trên SSD nhanh, không cùng drive với data files.
  • Trace Flag 1117 & 1118: Kích hoạt để giảm allocation contention (trên SQL 2019+ mặc định).

6. Cấu hình Parallelism

  • MAXDOP: Đối với OLTP, đặt MAXDOP = 1 hoặc CPU_COUNT/2. Đối với DW, để cao hơn.
  • Cost Threshold for Parallelism: Tăng lên 25‑50 để tránh parallelism cho query ngắn.

7. Memory & Buffer Pool

  • Max Server Memory: Đặt max server memory để để lại RAM cho OS (không quá 80 % tổng RAM).
  • Buffer Pool Extension: Khi cần, bật sp_configure 'buffer pool extension', 1 và chỉ định SSD.

8. Monitoring & Alerting

  • Extended Events: sp_server_diagnostics để capture deadlocks, long‑running queries.
  • SQL Audits: Giám sát thay đổi schema.
  • Azure Monitor: Đặt alert khi cpu_percent > 80 hoặc log_write_wait_time > 5s.

9. Thực hành CI/CD cho DB

  • SQLPackage (/Action:Publish) để deploy migration từ CI.
  • Database Projects trong Visual Studio / Azure DevOps để version‑control schema.
  • Rollback Scripts: Luôn có script DROP hoặc UNDO cho mỗi migration.

File thuộc mục SQL Deep‑Dive của senior‑dotnet‑wiki.