Đề tài Tìm hiểu các chức năng hổ trợ mạng của sql_server

PHẦN II TÌM HIỂU CÁC CHỨC NĂNG HỖ TRỢ LẬP TRÌNH TRÊN MÔI TRƯỜNG MẠNG CỦA SQL SERVER & VISUAL BASIC TÌM HIỂU CÁC CHỨC NĂNG HỔ TRỢ MẠNG CỦA SQL_SERVER CHƯƠNG 1 TÌM HIỂU CÔNG CỤ TỐI ƯU HÓA TRUY VẤN CỦA MICROSOFT SQL SERVER > TỔNG QUAN VỀ TỐI ƯU HÓA HIỆU QUẢ CỦA CƠ SỞ DỮ LIỆU TRONG SQL SERVER Mục tiêu của việc điều chỉnh hiệu quả hoạt động của hệ thống là tối thiểu hóa thời gian đáp ứng cho mỗi truy vấn và tối đa hóa năng suất truyền dữ liệu của Cơ sở dữ liệu bằng cách tối thiểu tải của mạng, disk I/O, thời gian sử dụng CPU. Để đạt được mục tiêu này, ta phải hiểu rõ nhu cầu của ứng dụng, cấu trúc vật lý và luận lý của dữ liệu. Các vấn đề về hiệu quả hoạt động cần xem xét trong cả chu trình phát triển của hệ thống, chứ không phải vào cuối chu trình phát triển, lúc hệ thống được hiện thực. Ta có thể cải tiến hiệu quả hoạt động của hệ thống một cách rõ ràng bằng cách thiết kế hệ thống một cách cẩn thận ngay từ đầu. Để có thể tối ưu hóa hoạt động của SQL Server một cách hiệu quả, ta cần xác định rõ các phần mà ta có thể cải thiện hiệu quả nhiều nhất và tập trung phân tích các phần này. Mặt khác ta có thể cải thiện hiệu quả hoạt động của hệ thống bằng cách xem xét các vấn đề ở mức hệ thống như: bộ nhớ, phần cứng và v.v... Tuy nhiên, SQL Server tự động quản lý các tài nguyên hệ thống, giảm thiểu nhu cầu (do đó giảm các lợi ích) của việc điều chỉnh bằng tay ở mức hệ thống. Việc tối ưu hóa hoạt động của Cơ sở dữ liệu có thể thực hiện thông qua việc xem xét các vấn đề sau: -Thiết kế Cơ sở dữ liệu. (Database Design) Một ví dụ đơn giản thể hiện trong demo của phần 1: CREATE TABLE DONDATHANG ( MADDH CHAR(10) NOT NULL, NGAYDH DATETIME NOT NULL, NGAYDK DATETIME NULL, NGAYGIAO DATETIME NOT NULL, TRIGIA MONEY NOT NULL, MANV CHAR(10) NOT NULL, MANCC CHAR(10) NOT NULL, CONSTRAINT PK_DONDATHANG PRIMARY KEY (MADDH) ) -Điều chỉnh truy vấn. (Query Tuning) -Thiết kế ứng dụng. (Application Design) -Sử dụng các công cụ và tiện ích tối ưu hóa. (SQL Server Query Analyzer và SQL Server Profiler) -Tối ưu hóa hoạt động của SQL Server.(Setting Windows NT và SQL Server) > ĐIỀU CHỈNH TRUY VẤN: (QUERY TUNING) Có thể bạn có dự định tập trung vào vấn đề điều chỉnh ở mức hệ thống như kích thước bộ nhớ, kiểu file hệ thống, số lượng và kiểu bộ xử lý. Kinh nghiệm cho thấy hầu hết các vấn đề không thể được giải quyết theo cách này. Các vấn đề này phải được xác định bằng cách phân tích ứng dụng, các truy vấn, và các lệnh cập nhật mà ứng dụng thực hiện trên cơ sở dữ liệu cũng như cách các câu truy vấn và cập nhật này tương tác với lược đồ cơ sở dữ liệu. Các nguyên nhân làm các câu truy vấn và cập nhật tốn nhiều thời gian thực thi: Việc truyền dữ liệu trên mạng chậm Bộ nhớ không đủ trên máy chủ, hay không đủ bộ nhớ cần thiết cho SQL Server. Thiếu các thông số hữu ích. Các thông số không được cập nhật. Thiếu các chỉ mục (index) cần thiết. Khi một câu truy vấn hay lệnh cập nhật tốn nhiều thời gian hơn mình nghĩ, sử dụng danh sách sau để cải thiện hiệu quả hoạt động hệ thống: Nếu vấn đề có liên quan đến các câu truy vấn thì vấn đề bao gồm câu truy vấn nào hay tập hợp các câu truy vấn nào? Sử dụng SQL Server Profiler để xác định các câu truy vấn chậm. Hiệu quả hoạt động của câu truy vấn có thể được xác định bằng cách dùng lệnh SET để enable các tùy chọn SHOWPLAN, STATISTICS IO, STATISTICS TIME, và STATISTICS PROFILE. SHOWPLAN mô tả phương pháp được chọn bởi bộ tối ưu hóa truy vấn của SQL Server. (Xem Phụ lục Set Showplan All) STATISTICS IO báo cáo thông tin về số lần thực hiện các công việc như duyệt bảng, đọc luận lý (các trang cần truy cập trong cache), và đọc vật lý (số lần đĩa cứng được truy cập) cho mỗi bảng trong câu lệnh. (Xem Phụ lục Set Statistics IO) STATISTICS TIME trình bày khoảng thời gian (tính bằng 1/1000 giây hay milisecond) cần thiết để phân tích cú pháp, biên dịch, và thực thi câu truy vấn. (Xem Phụ lục Set Statistics Time) STATISTICS PROFILE trình bày kết quả sau mỗi lần thực thi truy vấn bằng bản mô tả sơ lược sự thực thi của câu truy vấn (profile). (Xem Phụ lục Set Statistics Profile) Trong SQL Server Query Analyzer, ta có thể chọn tùy chọn graphical execution plan để có thể xem cách SQL Server lấy dữ liệu qua sơ đồ đồ họa cụ thể. Thông tin thu được bởi các công cụ này cho phép ta quyết định cách câu truy vấn được thực thi bởi bộ tối ưu hóa truy vấn của SQL Server và các chỉ mục (index) nào được dùng. Sử dụng thông tin này, ta có thể cải thiện hiệu quả bằng cách viết lại câu truy vấn, thay đổi các chỉ mục của các bảng, hay có thể sửa đổi thiết kế của cơ sở dữ liệu. (Xem phần Phân tích câu truy vấn _ Analyzing a Query) Câu truy vấn đã được tối ưu với các thông số cần thiết? SQL Server sẽ tự động tạo các thông số liên quan đến việc phân bố các giá trị trong một cột trên các cột có sử dụng chỉ mục.( Cột nào có sử dụng chỉ mục, cột đó sẽ được SQL Server tạo các thông số phân bố các giá trị trong cột đó). Các thông số đó cũng có thể được tạo trên các cột không dùng chỉ mục hoặc được thực hiện bằng tay, dùng SQL Server Query Analyzer hay lệnh CREATE STATISTICS, hoặc tự động, bằng cách chọn tùy chọn auto create statistics. Các thông số này có thể được dùng bởi bộ xử lý câu truy vấn để xác định các chiến lược tối ưu hóa cho việc đánh giá câu truy vấn. Việc duy trì thêm các thông số trên các cột không sử dụng chỉ mục trong các tác vụ JOIN có thể cải tiến hiệu quả hoạt động của câu truy vấn. Sử dụng graphical execution plan trong SQL Server Query Analyzer để kiểm tra câu truy vấn đã có đủ thông số thống kê hay chưa? Các thông số thống kê của câu truy vấn đã được cập nhật? SQL Server tự động tạo và cập nhật các thông số thống kê của câu truy vấn hay lệnh cập nhật trên các cột có chỉ mục. Hơn nữa, các thông số còn có thể được cập nhật trên các cột không có chỉ mục hoặc bằng tay, dùng SQL Server Query Analyzer hay lệnh UPDATE STATISTICS, hoặc tự động, chọn tùy chọn auto update statistics. Các thông số up_to_date không phụ thuộc vào thời điểm hay ngày giờ của dữ liệu. Nếu không có các tác vụ cập nhật dữ liệu xảy ra, thì các thông số của câu truy vấn vẫn còn up_to_date. Nếu các thông số không được định trước là được cập nhật một cách tự động, thì ta nên chỉ định các thông số phải luôn luôn được cập nhật một cách tự động. Ví dụ: Thông số chưa được cập nhật (dùng SQL Ananlyzer) Ví dụ: Thông số sau khi được cập nhật (dùng SQL Ananlyzer) Bộ tối ưu hóa truy vấn có được cung cấp cơ hội tốt nhất để tối ưu hóa câu truy vấn phức tạp hay không? Một vài câu truy vấn vốn đã dùng nhiều tài nguyên hệ thống nên chạy chậm. Vấn đề này liên quan đến nhu cầu của ứng dụng và bản chất của Cơ sở dữ liệu. Các truy vấn này không phải không hiệu quả, bởi vì bộ tối ưu hóa truy vấn của SQL Server đã hiện thực câu truy vấn này theo cách hiệu quả nhất mà nó có thể làm được. Tuy nhiên, các truy vấn này dùng nhiều tài nguyên hệ thống, và bản chất hướng tập hợp của Transact-SQL có thể làm cho nó kém hiệu quả. ---------------------------------------------------------------------------------------- Bản chất của Transact-SQL là hướng tập hợp bởi vì dù dùng Transact-SQL hay hàm API, SQL Server mặc định trả lại một tập hợp kết quả, gọi là default result set, cho ứng dụng. default result set Kiểu mặc định mà SQL Server dùng để trả lại tập kết quả cho client. Các hàng được trả cho client theo thứ tự của chúng trong tập kết quả, và ứng dụng phải xử lý các hàng theo thứ tự này. Sau khi thực thi một câu lệnh SQL trên một conection (cầu nối), ứng dụng không thể làm gì khác ngoài việc nhận các hàng trong tập kết quả cho đến khi tất cả các hàng được lấy về. Một hoạt động khác mà ứng dụng có thể thực hiện trước khi kết thúc tập kết quả là hủy bỏ phần còn lại của tập kết quả. Đây là phương pháp nhanh nhất để lấy một hàng từ SQL Server đến client. Không một bộ tối ưu hóa nào có thể loại trừ hoàn toàn chi phí về tài nguyên cần thiết do bản chất của câu truy vấn. Thực ra, chi phí của các truy vấn này lớn là do so với các truy vấn kém phức tạp hơn. Mặc dù SQL Server dùng chiến lược thực thi truy vấn tốt nhất mà nó có thể làm được, nhưng do giới hạn này, ta vẫn không có được một chiến lược truy xuất có thể gọi là tối ưu. > PHÂN TÍCH CÂU TRUY VẤN: (ANALYZING A QUERY) Microsoft SQL Server cung cấp 3 cách thể hiện thông tin về cách nó quản lý các bảng và cách dùng chỉ mục để truy cập dữ liệu cho một câu truy vấn: Trình bày chiến lược thực thi bằng công cụ đồ họa là SQL Server Query Analyzer. Lệnh SET SHOWPLAN_TEXT ON: cho kết quả dưới dạng text. Khi thực thi lệnh này, SQL Server không thực thi các lệnh Transact_SQL mà chỉ cho kết quả là các thông tin chi tiết về cách các lệnh được thực thi. Lệnh SET SHOWPLAN_ALL ON: tương tự lệnh SET SHOWPLAN_TEXT nhưng kết quả có định dạng ngắn gọn. Khi thực thi lệnh này, SQL Server cũng không thực thi các lệnh Transact_SQL mà chỉ cho kết quả là các thông tin chi tiết về cách các lệnh được thực thi và các ước lượng về các yêu cầu tài nguyên cần thiết cho câu truy vấn. Khi hiển thị chiến lược thực thi, các câu lệnh bạn gửi cho SQL Server không được thi hành mà ngược lại, SQL Server phân tích câu truy vấn và trình bày cách các câu truy vấn sẽ được thực thi như một chuỗi các tác vụ. Chiến lược thực thi tốt nhất được dùng bởi query engine cho từng câu lệnh DML (ngôn ngữ xử lý dữ liệu đơn) hay Transact_SQL (Truy vấn giao dịch) sẽ được hiển thị, đồng thời SQL Server sẽ các thông tin có được trong thời gian dịch về stored procedure (một tập hợp tiền biên dịch của các truy vấn giao dịch được chứa chung với một tên gọi và được xử lý như một chương trình đơn vị (unit)), hay các chương trình kích hoạt (trigger) được gọi bởi chương trình gói (a batch), v.v...Ví dụ, việc thực thi một câu lệnh SQL có thể cho thấy SQL Server dùng cách duyệt bảng để lấy dữ liệu. Thay vào đó, việc duyệt chỉ mục có thể được dùng thay việc duyệt bảng nếu chỉ mục được xác định là một phương pháp nhanh hơn để lấy dữ liệu từ bảng. Kết quả được trả lại bởi lệnh Showplan_text và Showplan_all được thể hiện dưới dạng bảng (các hàng và các cột) của một cấu trúc cây. Cấu trúc cây của chiến lược thực thi dùng một hàng trong tập kết quả cho một node trong cây, mỗi node đại diện một tác vụ vật lý hay luận lý được dùng để tiến hành các thao tác trên dữ liệu nhằm đạt được kết quả mong muốn. SQL Server Query Analyzer dùng công cụ đồ họa để biểu diễn các tác vụ luận lý hay vật lý là các biểu tượng (icon). > CÁC KHÁI NIỆM TRONG VIỆC ĐIỀU CHỈNH TRUY VẤN PHỨC TẠP Microsoft SQL Server thực hiện các tác vụ như sắp xếp, giao, hợp, hiệu bằng cách dùng kỹ thuật sắp xếp trong bộ nhớ và kết bằng bảng băm (hash join). Dùng chiến lược thực thi truy vấn kiểu này, SQL Server hỗ trợ việc phân mảnh dọc các bảng, đôi khi còn gọi là lưu trữ theo cột. SQL Server cung cấp 3 kiểu cho tác vụ kết: Nested Loops Joins Merge joins Hash joins Nếu bảng ở vế này của phép kết khá nhỏ (ví dụ ít hơn 10 hàng) và bảng ở vế kia phép kết khá lớn và đã được dùng chỉ mục trên các cột tham gia phép kếtù, chỉ mục nested loops là tác vụ kết nhanh nhất bởi vì nó yêu cầu IO và các phép so sánh ít nhất. Nếu cả hai bảng ở hai vế phép kết không nhỏ nhưng đã được sắp xếp trên các cột tham gia phép kết (ví dụ: dữ liệu đã được truy cập bằng cách duyệt các chỉ mục đã được sắp xếp), merge join là tác vụ kết nhanh nhất. Nếu cả hai bảng ở cả hai vế đều lớn và có kích thước tương tự, merge join kết hợp với việc sắp xếp trước hay dùng hash join (phép kết bảng băm) đều cho hiệu quả tốt tương tự nhau. Tuy nhiên, các tác vụ kết bảng băm thường nhanh hơn nhiều nếu kích thước hai bảng tham gia phép kết khác biệt nhau khá nhiều. Hash join có thể xử lý các bảng lớn, không được sắp xếp, không dùng chỉ mục một cách rất hiệu quả. Hash join rất hữu ích cho các kết quả trung gian trong các truy vấn phức tạp bởi vì: Các kết quả trung gian không dùng chỉ mục (trừ phi các kết quả này được lưu vào đĩa cứng và dùng chỉ mục một cách rõ ràng) và thường không được sắp xếp hợp lý cho các tác vụ kế tiếp trong chiến lược thực thi truy vấn. Bộ tối ưu hóa truy vấn chỉ đánh giá các kích thước của các kết quả trung gian. Bởi vì các đánh giá có thể làm chương trình làm việc sai sót trong các câu truy vấn quan trọng, các giải thuật xử lý các kết quả trung gian không những không hiệu quả mà còn làm toàn bộ hệ thống xuống cấp nếu các kết quả trung gian trở nên lớn hơn sự dự đoán của chúng ta quá nhiều. Hash join cho phép làm giảm việc sử dụng denormalization (sự hạ độ chuẩn của Cơ sở dữ liệu). Denormalization thường được dùng để giúp ứng dụng đạt được hiệu quả tốt hơn bằng cách giảm các tác vụ kết nối, mặc dù việc này có thể gây ra sự nguy hiểm của việc dư thừa dữ liệu như sự mâu thuẫn xảy ra khi cập nhật. Hash join giảm việc denormalize. Hash join cho phép việc phân mảnh dọc (hiện thực các nhóm của các cột trong một bảng đơn trong các file hay chỉ mục riêng biệt) trở nên một tùy chọn có thể thực hiện được cho việc thiết kế cơ sở dữ liệu vật lý. Các khái niệm về nested loops join Nested Loops Join, tạm dịch là Phép kết vòng lặp lồng nhau, dùng một bảng ở một vế của phép kết như một bảng nhập ngoài (được biểu diễn là đầu vào phía trên trong bản chiến lược thực thi đồ họa) và bảng còn lại là bảng nhập trong (nằm dưới đáy). Vòng lặp phía ngoài dùng lần lượt từng hàng của bảng nhập ngoài. Vòng lặp trong, thực thi cho mỗi hàng ngoài, tìm các hàng trùng trong bảng nhập trong (tương tự như hai vòng lặp for lồng nhau). Nếu việc tìm kiếm được thực hiện trong chỉ mục thì được gọi là index nested loops join. Nếu chỉ mục được xây dựng như một phần của chiến lược truy xuất (và được hủy khi hoàn thành xong câu truy vấn), nó được gọi là kết vòng lặp lồng nhau trên chỉ mục tạm thời. Tất cả các dạng khác nhau đều được xem xét bởi bộ tối ưu hóa truy vấn. Việc sử dụng nested loops join sẽ rất hiệu quả nếu bảng ngoài khá nhỏ và bảng trong đã dùng chỉ mục và có kích thước khá lớn. Trong các giao dịch nhỏ, ví dụ như các giao dịch chỉ ảnh hưởng trên một tập nhỏ các hàng, index nested loops join hiệu quả hơn merge join và hash join rất nhiều. Tuy nhiên, trong các câu truy vấn lớn, nested loops join thường không phải là sự lựa chọn tối ưu. Các khái niệm về merge join Merge join, tạm dịch là Phép kết trộn, yêu cầu cả hai bảng phải được sắp xếp trên các cột được trộn, mà các cột này đã được xác định bởi mệnh đề WHERE trong vị từ kết. Bộ tối ưu hóa truy vấn thường duyệt chỉ mục, nếu tồn tại chỉ mục trên một tập các cột tương ứng, hay đặt tác vụ sắp xếp dưới merge join để sắp xếp trước khi thực hiện phép kết. Trong một số trường hợp đặc biệt, có thể có các mệnh đề kết bằng bội, nhưng các cột trộn chỉ được lấy từ một vài mệnh đề kết bằng mà thôi. Bởi vì mỗi dữ liệu vào được sắp xếp, tác vụ Merge Join lấy sẽ lấy một hàng từ mỗi bảng và so sánh chúng. Ví dụ, với các tác vụ kết bên trong, các hàng bằng nhau sẽ được trả lại. Nếu chúng không bằng nhau, hàng nào có giá trị thấp hơn sẽ bị loại bỏ và hàng khác sẽ được lấy vào so sánh tiếp. Quá trình này lặp lại cho đến khi tất cả các hàng được xử lý. Merge join có thể là một tác vụ bình thường hay có thể là một tác vụ nhiều-nhiều. Một merge join nhiều-nhiều dùng một bảng tạm để chứa các hàng. Nếu có các giá trị trùng nhau từ cùng một input, một trong các input sẽ phải bắt đầu xử lý lại như có các giá trị trùng từ input khác. Nếu còn lại một số vị từ, tất cả các hàng thỏa mãn vị từ merge sẽ đánh giávị từ dư này, và chỉ những hàng thỏa mãn các vị từ này mới được trả lại. Chính bản thân merge join là một tác vụ được thực thi rất nhanh, nhưng có có thể là một lựa chọn tốn kém nếu các tác vụ sắp xếp được đòi hỏi. Tuy nhiên, nếu khối lượng dữ liệu lớn và dữ liệu mong muốn cần được truy xuất có thể truy cập từ các chỉ mục B-Tree hiện có, merge join thường là thuật toán kết nhanh nhất có thể có. Các khái niệm về hash join Hash join, tạm dịch là Phép kết bảng băm, có hai bảng dữ liệu nhập là: bảng dữ liệu kiến trúc (build input) và bảng dữ liệu khảo sát (probe input). Bộ tối ưu hóa truy vấn chọn bảng dữ liệu nhỏ hơn là bảng kiến trúc, bảng lớn hơn là bảng dữ liệu khảo sát. Hash join dùng rất nhiều loại tác vụ kiểm tra sự trùng nhau: kết trong; kết ngoài trái, phải hay toàn bộ; nửa kết nối trái, phải; giao; hội; và hiệu. Ngoài ra, các biến thể của hash join có thể nhân lên việc di chuyển và nhóm (ví dụ: SELECT salary GROUPBY department). Những thay đổi này chỉ dùng một input cho cả hai bảng xây dựng vàkhảo sát. Tương tự như merge join, hash join chỉ được dùng nếu có ít nhất một điều kiện kết bằng (mệnh đề Where) trong vị từ kết. Tuy nhiên, do phép kết thường được dùng để nối các quan hệ, các phép kết này được diễn tả với mệnh đề kết giữa khóa chính và khóa ngoại, tất cả các tác vụ kết có ít nhất một mệnh đề kết. Tập hợp các cột có trong mệnh đề kết được gọi là khóa băm (hash key), bởi vì chúng là các cột tham gia hàm băm (hash function). Các vị từ khác được đánh giánhư là vị từ thêm vào, được xử lý tách biệt hẳn khỏi sự so sánh các giá trị băm (hash value). Khóa băm có thể là một biểu thức cho đến khi nó được tính toán xong hết các cột trong một hàng. Trong các tác vụ thực thi trên nhóm, các cột của nhóm được nhóm lại theo danh sách các khóa băm. Trong các tác vụ trên tập hợp như giao, cũng như di chuyển các bảng sao, khóa băm sẽ chứa tất cả các cột. Hash Join Trong Bộ Nhớ Hash join đầu tiên duyệt và tính toàn bộ bảng kiến trúc và sau đó