Tinh chỉnh hiệu suất thực thi (Performance tuning) là một vấn đề khó và không có một quy tắc vàng nào cả. Tuy nhiên bạn có thể bước một bước dài một cách ngạc nhiên trong vấn đề này nếu theo một số nguyên tắc cơ bản trong bài này.
Về mặt lý thuyết, việc điều chỉnh hiệu năng được thực hiện bởi các DBA. Tuy nhiên họ sẽ không có đủ thời gian để rà soát lại tất cả mọi thay đổi của các procedure, do đó học một số kiến thức căn bản về điều chỉnh thực thi sẽ giúp bạn tiết kiệm thời gian cho việc phải làm việc lại với các mã nguồn.
Sau đây là danh sách 15 gợi ý cơ bản nhất mà bất kỳ một develope nào cũng cần phải thực hiện trong khóa học về điều chỉnh hiệu năng, thực hiện những điều này không đảm bảo hiệu năng sẽ được cải thiện một cách chắc chắn, nhưng ít ra nó cũng không làm cho hệ thống chậm đi.
- Tạo Primary key trên mỗi bảng (trừ khi bạn đủ thông thạo để có thể cấu hình một kế hoạch tốt hơn), thiết lập cho PK này là Clustered Index (Chú ý nếu bạn tạo PK trong SSMS thì điều này là mặc định)
- Tạo index cho tất cả các cột được xác định là Foreign key, nếu bạn biết chắc cột khóa ngoại này có giá trị là duy nhất thì cần tạo thuộc tính cho index của các FK này là Unique.
- Không tạo Index cho các cột ngòai hai loại column trên.
- Trừ khi bạn mong muốn có các hành vi ứng xử theo ngữ cảnh khác nhau, còn không thì luôn tham chiếu đến các đối tượng bằng tên sở hữu đầy đủ trong four-part name, viết dbo.sysdatabases thay vì chỉ viết sysdatabases.
- Sử dụng tùy chọn set nocount on ở phần đầu của mỗi procedure và set nocount off ở cuối mỗi procedure.
- Nghĩ kỹ về LOCK. Nếu bạn không viết phần mềm về ngân hàng, thì vấn đề là có thể bạn sẽ nắm lấy cơ hội để thực hiện việc đọc dữ liệu một cách tự do thoải mái. Gợi ý là bạn có thể dùng với từ khóa NOLOCK, nhưng sẽ đơn giản hơn nếu sử dụng tùy chọn SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ở đầu mỗi procedure và thiết lập về READ COMMITTED ở cuối.
- Điều này chắc bạn đã nghe nói đi nói lại hàng nghìn lần, nhưng một lần nữa xin nhắc lại là chỉ select trả về những colum cần thiết và chỉ rõ các cột, hạn chế sử dụng selec * from hết mức có thể.
- Sử dụng transaction khi thích hợp, và không cho bất kỳ người dùng nào tương tác vào khi transaction đang thực hiện.
- Bỏ qua việc sử dụng bảng tạm hết mức có thể, nếu cùng quá bắt buộc phải tạo bảng tạm, bạn nên tạo bảng bằng khai báo tường minh bằng mệnh đề tạo bảng CREATE TABLE #Temp.
- Bỏ qua sử dụng NOT IN, thay vào đó hãy sử dụng LEFT JOIN, mặc dù về tính dễ đọc và dễ viết mã thì NOT IN trông có vẻ rõ ràng hơn.
- Nếu bạn bắt buộc phải sử dụng SQL động, hãy sử dụng sp_executesql với tham số được đặt tên (hơn là sử dụng EXEC hoặ EXECUTE), vì với thủ tục này, bạn có cơ hội sử dụng lại kế hoạch thực thi (query plan), tiết kiệm đáng kể thời gian cho trình biên dịch lập lại kế hoạch thực thi khi bạn chạy lại thủ tục tương ứng.
- Tạo lập thói quen lập tài liệu về thủ tục của bạn trước và sau mỗi lần thay đổi. Luôn giữ ý niệm rằng, nếu sau mỗi lần thay đổi, CPU phải tăng thêm 10 - 15% năng lực cho việc đọc, ghi thì rất có thể thủ tục của bạn cần phải xem xét lại.
- Tránh việc cứ phải "đi qua đi lại" (round trips) giữa server và client, thay vào đó, hãy gửi các yêu cầu cần thiết một lần tới máy chủ thực thi và trả về một tập hợp nhiều kết quả là một trong những cách để giải quyết vấn đề này.
- Bỏ qua Index hints và JOIN hints.
- Sau khi đã thực hiện xong việc coding. Bạn hãy sử dụng Profiler và thực hiện thủ tục của bạn thông qua ứng dụng từ đầu đến cuối, để theo dõi số lượng đọc, ghi, số lượng lệnh gọi đến server ngay trên máy của bạn. Theo dõi xem có gì bất thường không? Sẽ là không bình thường nếu thấy việc gọi một thủ tục mà không còn được dùng đến, hoặc các lệnh gọi trùng lắp. Hãy kết hợp với DBA để làm việc này sẽ hiệu quả hơn.
Nếu bạn thực hiện được 15 gợi ý như trên, sẽ là bước khởi đầu rất tốt để tiếp tục học thêm những vấn đề mới về tinh chỉnh hiệu năng thực thi trong SQL Server.
Sẽ còn nhiều thứ phải tiếp tục nghiên cứu khi chúng ta xây dựng một mô hình ứng dụng, mạng, và SQL Server cũng như các vấn đề khác tiềm tàng gây ra hiện tượng thắt cổ chai trong SQL Server (bottlenecks). Chúng ta cũng sẽ nghiên cứu về các khả năng có thể cải thiện hiệu năng và một số bước mà bạn có thể làm mà không cần đi quá sâu vào "địa hạt" của một DBA chính hiệu.
(Nguồn: http://www.simple-talk.com)