How many temp database files ?

Đọc các bài viết về quản lý database tempdb thì nên tạo bao nhiêu data file trước giờ nhưng không chắc là việc thêm có hiệu quả hay không và lời khuyên thông thường là cứ mỗi logical CPUs thì cho 1 tempdb data file, trong khi Server SQL cho ERP đang là 2 x 8 cores mà lại có enable Hyperthread, vị chi là 32 logical CPUs chẳng lẽ lại add 32 data files cho tempdb. Thôi thì cứ add 8 files và theo dõi.

Mượn hình từ https://www.brentozar.com/blitz/tempdb-data-files/ chắc sẽ dễ hình dung.

Trong link bên dưới cũng chỉ cách để xem liệu có đúng là thiếu data files cho tempdb không.

Source : https://www.brentozar.com/blitz/tempdb-data-files/

https://www.brentozar.com/sql/tempdb-performance-and-configuration/

1 số SQL để add data files cho tempdb :

/* Re-sizing TempDB to 5 GB */

USE [master];
GO
alter database tempdb modify file (name=’tempdev’, size = 5GB);
GO

/* Adding three additional files */

USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE (NAME = N’tempdev2′, FILENAME = N’T:\MSSQL\DATA\tempdev2.ndf’ , SIZE = 5GB , FILEGROWTH = 100MB);
ALTER DATABASE [tempdb] ADD FILE (NAME = N’tempdev3′, FILENAME = N’T:\MSSQL\DATA\tempdev3.ndf’ , SIZE = 5GB , FILEGROWTH = 100MB);
ALTER DATABASE [tempdb] ADD FILE (NAME = N’tempdev4′, FILENAME = N’T:\MSSQL\DATA\tempdev4.ndf’ , SIZE = 5GB , FILEGROWTH = 100MB);
GO

Find number of logical processors

SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info

Script to Check TempDB Speed

This query hits the dynamic management function (DMF) sys.dm_io_virtual_file_stats for all of the TempDB data files and lists out how fast they’re responding to write (and read) requests:

SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = ‘ROWS’

• Are writes being evenly distributed between data files?
• Are writes finishing in 20ms or less?

If the answer is no to either of those questions, we’ve got some performance tuning work to do.

More links & sources for above SQL commands :

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2009/01/11/tempdb-monitoring-and-troubleshooting-allocation-bottleneck/

https://sqlserverzest.com/2014/06/24/sql-server-script-to-configure-tempdb-files-per-number-of-logical-processors/

Correctly adding data files to tempdb

Script to Create A TempDB File Per Processor

Index Giúp Tăng Hiệu Năng Thực Hiện Như Thế Nào

Nguồn : http://www.sqlviet.com/blog/index-giup-tang-hieu-nang-thuc-hien-nhu-the-nao

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)

Nay ta có hai câu lệnh SELECT sau để truy vấn hai bảng:

-- #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.

Tối Ưu Hóa Câu Lệnh Bằng Covering Index

Bài viết của bạn Vũ Huy Tâm từ SQLVIET.

Nguồn: http://www.sqlviet.com/blog/index-giup-tang-hieu-nang-thuc-hien-nhu-the-nao

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)

Nay ta có hai câu lệnh SELECT sau để truy vấn hai bảng:

-- #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.

Tuning your SQL Query – Generating a Proper Execution Plan

Nhân đang tìm hiểu lại về Index và Execute Plan, 1 số bài viết liên quan đọc để rõ hơn.

Source : http://www.sql-server-performance.com/2016/tuning-sql-query-execution-plan/

Recently one of my developers came to and
complained of slow performance on a particular query. During
the investigation I noticed that the primary cause was the poor query execution plan. In this article, I will
discuss the approach which I used to improve the execution plan.

For demonstration purpose, I will be creating a table named student
in the student database as follows:

createtablestudent
(
student_idint,
student_first_namevarchar(50),
student_last_namevarchar(50),
cityvarchar(50)
)

Let’s insert few set of records into it using
the below T-SQL.

declare @i int
declare @j int
declare @k int
declare @l int
declare @m int

select @i =1
select @j=100000
while (@i <= 25000)

begin
INSERT INTO student (student_id,student_first_name,student_last_name,City)

select @i,'Satnam','Singh','Mumbai'
select @i=@i+1

end

select @k = MAX(student_id)+1 from student
while (@k <= 50000)

begin

INSERT INTO student (student_id,student_first_name,student_last_name,City)
select @k,'James','Anderson','USA'
select @k=@k+1

end

select @l = MAX(student_id)+1 from student
while (@l <= 75000)

begin

INSERT INTO student (student_id,student_first_name,student_last_name,City)
select @l,'John','Henry','UK'
select @l=@l+1

end

select @m = MAX(student_id)+1 from student
while (@m <= 100000)

begin

INSERT INTO student (student_id,student_first_name,student_last_name,City)
select @m,'Brian','Murphy','Toronto'
select @m=@m+1

end

Now
let’s have a look at the query execution plan :

At
this stage, on the Table Scan it is obvious you do not have a Clustered Index to cover
the table. Let’s create it on the column named student_id which will be holding
a unique set of values in the complete table.

[Nguyen : Không có Index thì SQL sẽ scan trong table từng dòng từ đầu đến cuối và thường sẽ mất rất nhiều thời gian. Có khi may mắn dòng cần tìm nằm ngay ban đầu khi SQL scan thì nhanh hơn nhưng tất nhiên không nên trong mong vào may rủi ở đây. Do đó nên tạo Index cho column student_id]

createclusteredindexclu_student_idonstudent(student_id)

Now we can take another look at the query execution plan:

Now
you can see that the Table Scan gets converted to a Clustered Index Scan
which means that the base table is completely scanned by the Clustered Index.

[Nguyen : Lúc này SQL sẽ Scan Index thay vì table do không có tìm cụ thể student nào]

Now
let’s modify the query to have a WHERE clause in it as follows:

select student_id, student_first_name from student

 where city='USA'

Since we have a WHERE condition on the column named city we should create a NON Clustered index on to improve
performance as follows :

  create index idx_city on student(city) 

Now let’s look at the query execution plan :

Note that it’s a Clustered Index Scan. If we have a look at the
missing index recommendation as shown in the oval above, the Query optimizer is
advising us to Create an Index with an Include column. One thing to keep in mind
is that there is no need to have the column student_id in the Included column
because it is already a part of the Clustered Index. Therefore the Index
Creation logic would be as follows:

create nonclustered
index idx_key_student_first_name on student(city) include(student_first_name)

Now let’s take a final a look at the query execution plan :

The query execution plan is now greatly improved – the query
optimizer now makes a proper use of the Index to fulfill the query execution
plan.

Tình yêu của cha mẹ cho con cái và ngược lại

Tối thứ 7 ăn tối xong có thời gian xem Britain’s Got Talent 2016 và cảm thấy thật sự xúc động với tiết mục của cô Kathleen Jenkins. Xúc động không phải vì cô hát hay thôi mà khi nhìn thấy cảm xúc của người cha khi con mình được đứng trên sân khấu, cũng hồi hộp và cảm giác giống như chính ông mới là người trình diễn vậy. Chợt nghĩ làm cha mẹ thì cho dù ở bất kỳ quốc gia nào, không phân biệt chủng tộc, màu da, văn hóa, thậm chí là thời đại nào, cha mẹ đều yêu thương con cái nhưng ngược lại, ngày nay càng thấy nhiều thông tin con cái đối xử tệ bạc với cha mẹ, thậm chí nhiều trường hợp thương tâm khi chính họ là người đánh đập, giết cha mẹ chỉ vì tranh chấp đất đai, hay nhậu xỉn, hay có khi chỉ vì xin vài chục, trăm nghìn mà cha mẹ không cho. Những người như vậy thường ông bà có nói là “trời đánh” cũng đúng lắm. Cho dù sao đó họ có hối hận thì suốt cuộc sống về sau cũng sẽ luôn sống trong dày vò của bản thân. Còn không biết hối hận thì nên đào thải khỏi xã hội, nếu không thì họ sẽ dạy gì cho con của họ và như vậy sẽ hủy hoại cả thế hệ tiếp theo.

Bảng tạm (temporary table) và vấn đề khóa (lock) trong MS SQL

Nhân có dịp nghiên cứu 1 stored procedure để truy vấn dữ liệu số lượng lớn mà đang gặp vấn đề về performance chung cả hệ thống (bộ nhớ RAM Database server tăng cao), tôi tìm được bài viết giải thích khá rõ ràng về ảnh hưởng của bảng tạm trong SQL nên chia sẻ.

Bài viết Eliminate the Use of Temporary Tables For HUGE Performance Gains với link gốc của bài viết ở đây :

http://www.sql-server-performance.com/2002/derived-temp-tables/

Đại khái để tạo bảng tạm trong SQL, khi đặt tên bảng thì có dấu # phía trước nếu là bảng tạm cục bộ – local temporary table (VD : CREATE TABLE #LTMPTABLE) hoặc ## phía trước nếu là bảng tạm toàn cục – global temporary table (VD : CREATE TABLE ##GTMPTABLE). Ví dụ thông thường của việc sử dụng bảng tạm như sau :

1) CREATE the temporary table
2) INSERT data into the newly created table
3) SELECT data from the temporary table (usually by JOINing to other physical tables) while holding a lock on the entire tempdb database until the transaction has completed.
4) DROP the temporary table

Theo đó SQL khi xử lý sẽ như bên dưới, nghĩa là qua rất nhiều bước và nhiều hoạt động đọc/ghi ổ cứng được thực thi (thông qua xử lý trên tempdb). Và điều quan trọng là SQL sẽ khóa database tempdb cho đến khi truy vấn liên quan đến bảng tạm xong, nghĩa là nếu dữ liệu được truy vấn nhiều thì suốt thời gian đó đừng hòng có hoạt động nào cũng  sử dụng bảng tạm, cũng cần sử dụng tempdb xảy ra được mà phải chờ. Và về phía giao diện người dùng thì thấy hệ thống bị chậm, hay lâu quá là vì vậy.

1) Lock tempdb database
2) CREATE the temporary table (write activity)
3) SELECT data & INSERT data (read & write activity)
4) SELECT data from temporary table and permanent table(s) (read activity)
5) DROP TABLE (write activity)
4) Release the locks

Trong khi nếu 1 bảng thông thường hoặc bảng derived table thì sẽ rất ít hoạt động và thậm chí có thể không cần khóa bảng được truy vấn dữ liệu nếu dùng từ khóa with(nolock) hoặc read uncommitted trong stored procedure.

1) CREATE locks, unless isolation level of “read uncommitted” is used
2) SELECT data (read activity)
3) Release the locks

Và như vậy thì bản thân câu truy vấn sẽ ít bước hơn nhưng việc không khóa cả database tempdb trong khi truy vấn đã giúp được các hoạt động khác vẫn tiến hành được mà không phải chờ, qua đó sẽ nhanh hơn.

Tuy nhiên, trong trường hợp dữ liệu nhiều thì phần lớn chia sẻ cho rằng derived table lại sẽ chậm hơn bảng tạm mà có đánh index (derived table chỉ lưu trên bộ nhớ khi chạy và không đánh index được).

Kết luận : Khi dùng bảng tạm thì lưu ý có đánh Index và vấn đề xem xét thời gian lock tempdb sao cho ít nhất. Hoặc 1 cách khác là tạo bảng vật lý trong database tempdb (cần test thêm) hoặc database dữ liệu (cần thêm quyền, có thể tạo schema tên temp để tạo table có schema này) để giảm thời gian khóa database tempdb. Update : việc tạo bảng vật lý trong database tempdb là không khuyến cáo vì có thẻ database dữ liệu và database tempdb khác collcation.

Trong bài viết có sử dụng 1 số từ, thuật ngữ tôi tạm dịch hoặc không dịch được thì dùng luôn từ tiếng Anh, nếu có các từ, thuật ngữ  nào bạn không rõ thì hỏi thêm SQL Book Online hoặc nhanh thì Google.