Index là phương tiện rất mạnh để tăng hiệu năng thực hiện của câu lệnh. Bài post này sẽ cung cấp một ví dụ cho bạn thấy bên trong SQL Server sử dụng index để  tăng hiệu năng như thế nào. Ở đây tôi dùng database AdventureWork là database mẫu đi kèm với SQL Server (bạn có thể dowload database này về và cài vào nếu chưa có sẵn).

Trước hết ta hãy dùng bảng Sale.Customer để tạo ra hai bảng mới là Sale.Customer_noIndex và Sale.Customer_Index, đồng thời tạo 1 index trên trường CustomerID cho bảng Sale.Customer_Index:

SELECT *
INTO Sales.Customer_NoIndex
FROM Sales.Customer

SELECT *
INTO Sales.Customer_Index
FROM Sales.Customer
GO
CREATE INDEX Idx_Customer_Index_CustomerID ON Sales.Customer_Index(CustomerID)

-- #1
SELECT CustomerID, CustomerType
FROM Sales.Customer_NoIndex
WHERE CustomerID = 11001
-- #2
SELECT CustomerID, CustomerType
FROM Sales.Customer_Index
WHERE CustomerID = 11001

Hai câu lệnh này sẽ cho cùng kết quả, khác biệt duy nhất là câu lệnh thứ hai truy vấn bảng Sales.Customer_Index có index trên trường cần tìm (CustomerID). Ta sẽ xem hai câu lệnh trên được thực hiện như thế nào bằng cách nhìn vào kế hoạch thực thi (execution plan) của chúng. Khi bắt đầu thực hiện một câu lệnh, SQL Server lên một kế hoạch gồm các bước sẽ tiến hành để thực thi câu lệnh đó, gọi là kế hoạch thực thi.Trên hàng công cụ bạn hãy bấm vào nút “Include Actual Execution Plan”. Khi đó, mỗi lần bạn chạy câu lệnh hệ thống sẽ vừa thực hiện câu lệnh vừa đồng thời trả lại kế hoạch thực thi mà nó đã dùng để thực hiện câu lệnh đó.

Bạn hãy bôi đen câu lệnh thứ nhất và thực hiện nó, ở tab “Execution plan” hiện ra kế hoạch thực thi như thế này:

Như vậy ta thấy hệ thống sẽ thực thi câu lệnh bằng cách duyệt qua cả bảng (table scan) và tìm ra các bản ghi thỏa mãn yêu cầu tìm kiếm. Thao tác duyệt bảng có nghĩa là hệ thống cần phải đọc tuần tự từng bản ghi từ đầu đến cuối để tìm ra kết quả. Trong trường hợp này, nó phải đọc toàn bộ 19 185 bản ghi và tìm ra bản ghi có CustomerID=11011. Đây là một thao tác rất chậm vì nó phải xử lý tất cả các bản ghi trong bảng. Nên nhớ hệ thống sẽ không dừng lại khi nó tìm được bản ghi đầu tiên có CustomerID=11011, vì nó không biết liệu còn bản ghi nào khác có giá trị CustomerID tương tự hay không, cho nên để chắc chắn trả lại kết quả đầy đủ hệ thống vẫn phải tiếp tục đọc các bản ghi còn lại. Ta có thể nhận xét thấy chi phí của thao tác duyệt bảng tăng tuyến tính cùng với số lượng bản ghi trong bảng (độ phức tạp là O(n)).

Giờ ta hãy thực hiện câu lệnh thứ hai, lần này kế hoạch thực thi sẽ như sau:

Lần này ta không thấy thao tác table scan nữa, mà thay vào đó là index seek và RID lookup. Index seek là khi hệ thống có thể nhảy đến được node trên cây index chứa khóa thỏa mãn yêu cầu tìm kiếm. Index là một cấu trúc dữ liệu có dạng B-tree, nên nó rất thích hợp với các thao tác tìm kiếm theo kiểu key=value, chỉ cần vài phép so sánh là hệ thống định vị được node chứa khóa cần tìm. Node này chứa khóa (trường được index, ở đây là giá trị của CustomerID) và RID là ID của bản ghi tương ứng trong bảng (đây là giá trị nội bộ chỉ dùng bên trong hệ thống, ta không truy cập được giá trị này). Vì thế bước tiếp theo là dùng RID này để nhảy đến bản ghi tương ứng trong bảng (RID lookup) để lấy các trường dữ liệu cần thiết. Với index seek, độ phức tạp giảm xuống thành O(logn), một bước tiến vượt bậc so với table scan.

Ta có thể so sánh chi phí của hai câu lệnh trên bằng cách thực hiện cả hai cùng nhau:

Ta thấy câu lệnh thứ nhất chiếm tới 95% tổng chi phí, trong khi câu lệnh thứ hai chỉ chiếm có 5%. Nói cách khác, index trên trường CustomerID đã giúp cho câu lệnh thực hiện nhanh lên đến 19 lần. Index đã giúp cho lượng dữ liệu hệ thống cần xử lý để tìm ra kết quả giảm xuống đến mức tối thiểu, và điều đó đã tạo ra bước nhảy về tốc độ. Từ đây ta rút ra một bài học quan trọng: Các trường thường được dùng trong mệnh đề WHERE là các ứng cử viên đầu tiên cần được tạo index.

Khi một non-clustered index được dùng để thực thi một câu lệnh, ta thường thấy trong kế hoạch thực thi thao tác Key Lookup (hoặc Bookmark Lookup ở các phiên bản trước), là thao tác mà hệ thống sau khi tìm kiếm trên cây index nhảy tới bản ghi tương ứng trong bảng để lấy các trường dữ liệu cần trả về:

USE AdventureWorks
GO
SELECT ContactID, FirstName, LastName
FROM  Person.Contact
WHERE EmailAddress = 'kristina1@adventure-works.com'

Ta thấy index IX_Contact_EmailAddress trên trường EmailAddress đã được sử dụng (thao tác Index Seek), và câu lệnh đạt được hiệu năng tốt hơn rất nhiều so với quét bảng khi không có index. Tuy nhiên thao tác Key Lookup ở đó vẫn chiếm tới một nửa chi phí câu lệnh. Một kỹ thuật có thể giúp tối ưu hơn nữa cho câu lệnh này, là loại bỏ Key Lookup bằng cách đưa các trường dữ liệu cần thiết vào cây index. Khi đó hệ thống chỉ cần tìm trên index và trả kết quả về cho câu lệnh mà không cần phải truy nhập vào bảng. Index lúc đó được gọi là covering index cho câu lệnh.

Ở phiên bản SQL Server 2000 trở về trước, cách làm duy nhất để đạt được điều này là tạo index trên tất cả các cột cần tìm, như ở ví dụ trên là tạo một index phức hợp gồm ba trường EmailAddress, FirstName và LastName. Tuy nhiên cách làm này không phải luôn khả thi, ví dụ khi index trên một mình trường EmailAddress là cần thiết để duy trì ràng buộc duy nhất trên trường này; đưa thêm các trường khác vào index làm phá vỡ ràng buộc này. Hoặc khi trường cần đưa vào có kiểu VARCHAR(MAX) – kiểu dữ liệu này không thể tạo được index.

Phiên bản SQL Server 2005 bắt đầu bổ sung thêm lựa chọn INCLUDE trong lệnh CREATE INDEX để tăng khả năng covering của index:

CREATE NONCLUSTERED INDEX IX_Contact_EmailAddress
ON Person.Contact(EmailAddress)
INCLUDE (FirstName,LastName)
WITH (DROP_EXISTING  = ON) -- xóa index nếu đã được tạo trước đó

Lệnh trên vẫn tạo index trên trường EmailAddress, nhưng đồng thời “ký gửi” hai trường FirstName và LastName vào đó. Cần lưu ý là hai trường này không thuộc về khóa index. Ví dụ nếu đây là unique index thì trường EmailAddress vẫn phải duy nhất. Khi thực hiện lại câu lệnh SELECT ở trên, kế hoạch thực thi đã thay đổi:

Như vậy Key Lookup đã biến mất, tức là bước truy nhập vào bảng đã bị loại bỏ, vì tất cả các trường dữ liệu mà câu lệnh yêu cầu đã được tìm thấy ngay tại index. Điều này cũng có nghĩa là chi phí câu lệnh được giảm đi một nửa. Thực tế đây là mức tối ưu nhất mà một câu lệnh có thể đạt được khi dùng non-clustered index. Một ưu điểm nữa khi chỉ cần đọc index là nó làm giảm tranh chấp trên bảng với các câu lệnh khác đang đồng thời truy nhập vào bảng đó.

Cũng từ kế hoạch thực thi trên ta có thêm một nhận xét là trường ContactID, vốn là khóa chính trong bảng, cũng được đọc từ cây index mà không cần quay sang bảng. Sở dĩ như vậy là vì, trường khóa chính luôn được lưu trên cây index để dùng làm con trỏ đến bản ghi tương ứng trong bảng (khi bảng không có khóa chính thì một giá trị định danh bản ghi, RowID, được dùng).

Lưu ý là kỹ thuật trên cũng làm tăng chi phí cho việc UPDATE và INSERT dữ liệu. Ví dụ khi bạn cập nhật FirstName và LastName, không những bản ghi trong bảng mà cả node index tương ứng cũng cần được cập nhật theo. Bạn cần thử nghiệm kỹ càng xem hiệu năng của cả hệ thống có bị ảnh hưởng không trước khi áp dụng kỹ thuật này.


Phiên bản áp dụng: SQL Server 2005 trở lên

Nếu bạn là developer thì chắc chắn rằng bạn phải biết SQLServer ! (Hiện tại SQLServer đã có đến phiên bản SQLServer 2008). Tuy nhiên ứng với phiên bản SQLServer nào Microsoft đều đưa ra các store procedure hệ thống (System stored procedures)

Bài viết này mình đề cập đến 5 store procedure hệ thống theo mình nghĩ là mỗi developer khi làm việc với SQLServer đều phải biết.
1. sp_help
a. Mục đích: Đây là store procedure hệ thống giúp bạn tra cứu nhanh về thông tin của các đối tượng (objects) có trong database. Như là: xem cấu trúc của 1 bảng, xem bảng này có phụ thuộc bảng nào, xem bảng này có Primary Key là Foregin Key của table nào, …..
b. Cú pháp
    * sp_help : để lấy tất cả các thông tin về tất cả các đối tượng trong Database
    * sp_help <tên của đối tượng> : để lấy tất cả các thông tin cụ thể của đối tượng (thông số tên đối tượng truyền vào) trong Database

c. Ví dụ
    * sp_help —> Khi đó bạn sẽ thấy giống màn hình sau:

    * sp_help ‘TB_TacGia’—> Khi cần lấy thông tin của table TB_TacGia trong Database: QuanLyThuVien.
      

2. sp_helptext
a. Mục đích: Đây là store procedure hệ thống giúp bạn tra cứu định nghĩa các đối tượng: store procedure hệ thống, store procedure do người dùng định nghĩa, hàm do người dùng định nghĩa, trigger, ….
b. Cú pháp
    * sp_helptext  <tên của đối tượng> : để lấy định nghĩa của đối tượng (thông số tên đối tượng truyền vào) trong Database
c. Ví dụ
    * sp_helptext ‘dbo.spTB_TacGia_TruyVan’: để lấy định nghĩa của store procedure do người dùng định nghĩa: spTB_TacGia_TruyVan trong Database: QuanLyThuVien. Khi đó bạn sẽ thấy màn hình như sau

3. sp_MSforeachtable
a. Mục đích: Đây là store procedure hệ thống giúp bạn duyệt qua tất cả các table trong Database. Khi đó bạn dùng kí tự ? để làm kí tự đại diện cho table. Ví dụ cơ bản nhất khi dùng store procedure hệ thống này là đếm số dòng của tất cả các bảng trong Database
b. Cú pháp
    *  sp_MSforeachtable <Câu lệnh SQL> —> Câu lệnh SQL bắt buộc phải chứ kí tự ? làm kí tự đại diện cho tên table
c. Ví dụ
    *  sp_MSforeachtable ‘SELECT ”?”, COUNT(*) FROM ?’ : Đếm số dòng của tất cả các table trong Database: QuanLyThuVien. Khi đó bạn sẽ thấy màn hình như sau

4. sp_depends
a. Mục đích: Đây là store procedure hệ thống giúp bạn liệt kê các Views, store procedure do người dùng định nghĩa, hàm do người dùng định nghĩa, trigger, … có phụ thuộc vào tên đối tượng truyền vào
b. Cú pháp
    *  sp_depends <tên của đối tượng>  –> tìm các đối tượng khác có phụ thuộc với tên đối tượng được của thông số truyền vào
c. Ví dụ
    *  sp_depends ‘Person.Address’  -> tìm các đối tượng khác phụ thuộc vào table: TB_TacGia trong Database: QuanLyThuVien. Khi đó ta sẽ thấy như màn hình sau

5. sp_spaceused
a. Mục đích: Đây là store procedure hệ thống giúp bạn lấy kích thước của Database hoặc kích thước của một đối tượng trong Database.
b. Cú pháp
    *  sp_spaceused —> lấy kích thước của Database (là Database mà ta đang thực thi store procedure hệ thống này)
    * sp_spaceused <tên của đối tượng> —> lấy kích thước của 1 đối tượng cụ thể được truyền vào từ thông số tên đối tượng
c. Ví dụ
    *  sp_spaceused —> lấy kích thước của Database: QuanLyThuVien . Khi đó bạn sẽ thấy màn hình như sau

sp_spaceused ‘Person.Address’ —> lấy kích thước của table: TB_TacGia trong Database: QuanLyThuVien Khi đó bạn sẽ thấy màn hình như sau

Và cuối cùng, bạn hãy thực hiện lệnh sau

sp_MSforeachtable ‘execute sp_spaceused @objname = ”?” ‘

Nó là sự kết hợp của 2 lệnh: sp_MSforeachtable, sp_spaceused mình vừa giới thiệu ở trên và cho biết nó làm mục đích gì ?

Tempdb là một database hệ thống của SQL Server. Nó dùng để chứa các kết quả trung gian khi câu lệnh đang thực hiện, thực hiện sắp xếp (với câu lệnh có ORDER BY), chứa các bảng tạm, biến bảng…

Khi server bận bịu với nhiều yêu cầu truy vấn đồng thời thì kích thước của tempdb có thể trở nên rất lớn. Vị trí mặc định của các file database của tempdb là ở thư mục chứa các file chương trình SQL Server. Bạn nên chuyển chúng sang một ổ đĩa riêng để tránh hiện tượng các file này lớn lên chiếm hết không gian đĩa. Để chuyển các file sang vị trí mới bạn hãy làm theo các bước sau:
Bước 1: Kiểm tra tên file và vị trí hiện thời

USE TempDB
GO
EXEC SP_HELPFILE
GO

kết quả sẽ tương tự như sau:

name     fileid  filename
tempdev  1       C:\Program Files\Microsoft...\tempdb.mdf
templog  2       C:\Program Files\Microsoft...\tempdb.ldf

Tên mặc định cho database file là tempdev và cho log file là templog (đây là tên logic, không phải là tên file trên đĩa). Ghi lại thư mục hiện tại cho hai file này.
Bước 2: Chuyển các file này sang thư mục mới

USE master
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'd:\data\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = templog, FILENAME = 'd:\log\templog.ldf')
GO

Khi làm việc với dữ liệu, có thể bạn gặp những tình huống trong đó, dữ liệu trong bảng có những bản ghi trùng nhau ở những trường đáng nhẽ ra không được trùng ...

Ví dụ bạn có 1 bảng chứa danh sách các địa chỉ email tên là DiaChiEmail gồm có các trường: ID, Email, và Ten; dữ liệu này được dùng cho công tác marketing (nôm na là spam ). Theo logic của dữ liệu thì trường Email xác định duy nhất một địa chỉ email, và bạn chỉ muốn gửi 1 email tới mỗi địa chỉ. Vì những lý do nào đó (có thể do lỗi nhập dữ liệu, hoặc khi import dữ liệu từ nhiều nguồn khác nhau…), bảng DiaChiEmail lại xuất hiện các bản ghi có trường Email giống nhau, mặc dù các trường còn lại thì khác nhau. Nay bạn muốn loại bỏ các bản ghi trùng và chỉ giữ lại một bản ghi duy nhất cho mỗi Email, và giả sử bạn muốn giữ lại bản ghi có trường ID lớn nhất (vì bản ghi này có dữ liệu mới nhất). Ta hãy xem xét cách làm như thế nào qua một ví dụ cụ thể:

-- tạo bảng
CREATE TABLE dbo.DiaChiEmail(
ID INT IDENTITY PRIMARY KEY,
Email VARCHAR(100),
Ten NVARCHAR(100)
)
GO
-- insert một vài bản ghi
INSERT dbo.DiaChiEmail(
Email,
Ten
)
SELECT 'abc@gmail.com', 'abc' UNION ALL -- #1
SELECT 'def@yahoo.com', 'def' UNION ALL -- #2
SELECT 'ghi@hotmail.com', 'ghi' UNION ALL -- #3
SELECT 'jkl@ymail.com', 'jkl' UNION ALL -- #4
SELECT 'abc@gmail.com', 'abc 2' UNION ALL -- #5, trùng với bản ghi #1
SELECT 'abc@gmail.com', 'abc 3' UNION ALL -- #6, trùng với bản ghi #1
SELECT 'ghi@hotmail.com', 'ghi 2' -- #7, trùng với bản ghi #3

Như vậy địa chỉ email abc@gmail xuất hiện 3 lần với tên khác nhau; còn ghi@hotmail.com xuất hiện 2 lần. Vì ta chỉ muốn giữ lại bản ghi sau cùng cho mỗi địa chỉ email, kết quả mà ta mong đợi từ quá trình loại bỏ bản ghi trùng sẽ là:

‘def@yahoo.com’, ‘def’, ’2010/01/13′ — bản ghi #2
‘jkl@ymail.com’, ‘jkl’, ’2010/01/14′ — bản ghi 4
‘abc@gmail.com’, ‘abc 3′, ’2010/01/15′ — bản ghi #6
‘ghi@hotmail.com’, ‘ghi 2′, ’2010/01/16′ — bản ghi #7

Câu lệnh sau giúp ta làm điều đó:

DELETE a
FROM DiaChiEmail a
JOIN
(
SELECT Email, MAX(ID) AS ID
FROM DiaChiEmail
GROUP BY Email
HAVING COUNT(*)>1
) b
ON a.Email = b.Email
AND a.ID < b.ID

CREATE TABLE #BanGhiTrung(
Email VARCHAR(100) PRIMARY KEY,
ID INT
)
GO
INSERT #BanGhiTrung(Email, ID)
SELECT Email, MAX(ID) AS ID
FROM dbo.DiaChiEmail
GROUP BY Email
HAVING COUNT(*) > 1
GO
DELETE a
FROM DiaChiEmail a
JOIN #BanGhiTrung b
ON a.Email = b.Email
AND a.ID < b.ID

WITH [TBL_DUPLICATE] AS
(
SELECT
  STT = ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID DESC), Email
FROM DiaChiEmail
)
DELETE FROM [TBL_DUPLICATE] WHERE STT > 1