Monitor và tối ưu sql server
Download as PPTX, PDF4 likes2,810 views
Các nhân tố ảnh hưởng đến việc monitor và tối ưu Sql Server
More Related Content
What's hot (20)
Similar to Monitor và tối ưu sql server (20)
Monitor và tối ưu sql server
- 1. Monitor và tối ưu SQL Server
- 2. Introduction • Các yếu tố cần xem xét khi tối ưu SQL. • Tool monitor SQL. • Tối ưu SQL query dùng Database Engine Tuning Advisor.
- 3. Các yếu tố cần xem xét khi tối ưu SQL • Blocking • Deadlocks • I/O bottlenecks • Missing and Unused indexes • Statistics • Wait stats • Fragmentation • Poor query plans DBA Developer
- 4. Blocking • SQL Server dùng các lock để duy trì sự toàn vẹn data cho việc đọc và ghi • Chỉ một process có quyền kiểm soát data tại một thời điểm • Có vài kiểu lock được dùng như Shared, Update, Exclusive, Intent … • Blocking sinh ra khi có 2 process cùng muốn truy cập vào cùng một data và process thứ 2 phải chờ đến khi process thứ 1 release lock • Thông thường thì thời gian các lock được giữ là rất nhỏ • Lock được giữ lâu hơn khi update data • Lock cũng được dùng khi read data
- 5. Deadlocks • Vấn đề phổ biến của SQL Server là deadlock • Deadlock sinh ra khi 2 hoặc nhiều process cùng chờ một resource và mỗi process chờ process khác hoàn thành • Khi xảy ra deadlock và không có cách để xử lý các xung đột này, thì SQL Server sẽ chọn một process xem là một deadlock victim và rollback process đó, để các process khác có thể chạy tiếp
- 6. I/O bottlenecks • SQL Server thường là một process hoạt động I/O cao • SQL Server phải lấy data từ disk để đáp ứng cho các query • Một process khác dùng nhiều I/O là database TempDB, Transaction Log • TempDB là một khu vực làm việc tạm thời của SQL Server để làm những việc như sort và group • I/O là một phần quan trọng của SQL Server performance, cần chắc chắn việc disk không bị bottleneck • Vấn đề I/O không phải chỉ do disk, mà do cả vấn đề Index, Poor query, Fragmentation hoặc Statistics quá hạn
- 7. Index • Index là yếu tố quan trọng trong việc tối ưu query • Các kiểu index ▫ Non-Clustered ▫ Clustered ▫ Covering ▫ Filtered • Các việc làm dẫn đến giảm tối ưu query và database ▫ Không tạo Index ▫ Tạo quá nhiều Index trên một table ▫ Index sai column ▫ Không có kế hoạch bảo trì Index
- 8. Statistics • Statistic là object chứa các thông tin thống kê về sự phân tán dữ liệu trong một hoặc nhiều column của một table hoặc index view. • Statistic chứa thông tin về index • Query optimizer dùng statistic để tạo query plan giúp tăng performance của query • Một vài trường hợp, cần tạo thêm statistic hoặc thay đổi query cho kết quả tốt nhất • Statistic được create khi : ▫ Query optimizer tạo statistic cho các index của table hoặc view khi index được tạo ▫ Query optimizer tạo statistic cho các column đơn khi AUTO_CREATE_STATISTICS is on • Statistic được update khi : ▫ Query optimizer xác định khi nào statistic có thể quá hạn và sau đó update chúng khi cần ▫ AUTO_UPDATE_STATISTICS is on ▫ Dùng câu lệnh UPDATE STATISTICS hoặc store procedure sp_updatestats
- 9. Fragmentation • Sự phân mảnh là sự lưu trữ data không liên tục trên disk • Internal Fragmentation ▫ Record lưu trữ không tiên tục trong page ▫ Xuất hiện các không gian không được dùng giữa các record trong page ▫ Sinh ra khi dùng các lệnh INSERT, UPDATE, DELETE • External Fragmentation ▫ Các page được lưu trữ vật lý trong một phạm vi (Extent) ▫ Các Extent lưu trữ vật lý không liên tục trên disk • Logical Fragmentation ▫ Các page index duy trì sự sắp xếp logic của page trong extent ▫ Do Page split mà các page bị mất trật tự
- 10. Wait stats • Đưa ra thông tin về thời gian chờ việc thực thi của các thread đối với các tài nguyên của hệ thống • Có rất nhiều các thông số khác nhau về các loại tài nguyên của hệ thống ▫ PAGEIOLATCH_SH xảy ra khi một thead chờ đợi trên một chốt cho một bộ đệm đó là trong một yêu cầu I/O. Yêu cầu chốt là ở chế độ Shared. Long waits có thể chỉ ra vấn đề với disk ▫ OLEDB chờ một giao tiếp dùng OLEDB như linked server ▫ CXPACKET xảy ra với các truy vấn song song ▫ ASYNC_NETWORK_IO xảy ra khi SQL Server chờ một client lấy dữ liệu • Dựa vào các thông số trên để điều tra nguyên nhân gây ra vấn đề về hiệu năng của hệ thống
- 11. Poor query plan • Tạo câu lệnh truy vấn dữ liệu không tối ưu ▫ Lấy ra những column không cần dùng ▫ Tính toán dữ liệu dựa trên các column trong câu lệnh JOIN và WHERE ▫ Dùng sub-query ▫ Dùng con trỏ ▫ … ▫ Index không có hoặc sai hoặc không được bảo trì ▫ Statistic không được update ▫ … Developer DBA
- 12. Tool SQLProfiler • Bắt request vào database theo thời gian thực • Filter các request theo khoảng thời gian thực thi của query • Tổng hợp thông tin request trong một khoảng thời gian ra file CSV • Thống kê Index: index đang dùng, index không dùng, cách index được dùng query • Thống kê Query: query dùng nhiều, query chạy chậm, query tốn tài nguyên I/O, CPU… • Thống kê Wait: thời gian chờ trong quá trình xử lý để đáp ứng các query của instance SQL Server trên server (không áp dụng trên 1 database)
- 13. Chọn server cần theo dõi Click để khởi chạy
- 15. Số lần query dùng kiểu seek trên index Seek là kiểu nhanh nhất để access data Số lần query dùng kiểu scan trên index Scan là khi nhiều row data phải được tìm kiếm Số lần query từ một clusterd index hoặc heap Số lần index được update do sự thay đổi dữ liệu
- 16. Sự phân mảnh của index theo % Reorganize index khi sự phân mảnh nằm trong khoảng từ 5% - 30% Rebuild index khi sự phân mảnh trên 30%
- 17. Đây là những index không dùng, có thể đưa ra table của index đó cũng không được dùng => Xem xét xóa
- 18. Thời gian query plan được biên dịch Thời gian gần nhất mà query chạy Số lần chạy từ khi creation_time đến last_execuation_time Nội dung câu lệnh query
- 19. Thống kê về thời gian chờ của các hoạt động của cả hệ thống SQL Server, như I/O, Network, OLEDB…
- 20. Thông tin index trong Execution plan • Ctrl + M trong SSMS để bật tính năng execution plan và chạy query Query dữ liệu trong table không có Index Query dữ liệu trong table trên index Nonclustered Query dữ liệu trong table trên index Clustered Query dữ liệu trong table trên index Non-Clustered nhưng cần thêm dữ liệu từ một index Clusterd Query dữ liệu trong table trên index Non-Clustered nhưng cần thêm dữ liệu và table ko có index Clusterd Estimated Subtree Cost là giá trị tổng chi phí của query optimizer cho việc chạy query và tất cả các hoạt động trước nó trên cùng subtree. Giá trị nhỏ nghĩa là nó cần ít resource
- 21. Tối ưu query dùng Database Engine Tuning Advisor • Xem chi tiết tại đây.
- 22. Sự sai khác execution plan và thời gian thực thi trong store procedure • Nguyên nhân ▫ Constant value ▫ Parameter sniffing • Cách khắc phục ▫ RECOMPILE ▫ OPTIMISE FOR UNKNOWN (từ phiên bản SQL Server 2008) ▫ Parameter masking
Editor's Notes
- #4: Xem thêm https://www.mssqltips.com/sqlservertutorial/276/sql-server-performance-tuning-and-monitoring-tutorial/
- #5: Overview Khi các Lock được giữ trong khoảng thời gian thì chúng sinh ra blocking, có nghĩa 1 process phải chờ cho đến khi process khác hoàn thành và release lock Giống với deadlocking khi mà 2 process cùng chờ dùng một resource Không giống với deadlocking vì blocking được xử lý ngay khi process release resource Explanation Khi dữ liệu được update thì một Update lock được dùng và khi dữ liệu được đọc thì một Shared lock được dung Update lock sẽ tạo một Exclusive lock trên dữ liệu mà process dùng Shared lock cho phép các process khác dùng Shared lock để truy cập dữ liệu Khi mà 2 process cùng truy cập vào cùng một dữ liệu thì đó là khi locking và blocking xảy ra Xem thêm https://www.mssqltips.com/sqlservertutorial/253/troubleshooting-blocking/
- #6: Mặc định khi deadlock xảy ra, ứng dụng của bạn có thể bắt và handle error, nhưng trong SQL Server Error Log hoặc Window Event Log sẽ không bắt lỗi này Xem thêm https://www.mssqltips.com/sqlservertutorial/252/tracing-a-sql-server-deadlock/
- #7: Overview Hầu hết các trường hợp Database có dung lượng lớn hơn dung lượng của bộ nhớ máy tính được cài đặt và do đó SQL Server phải lấy data từ disk để đáp ứng cho các query Do data trong database là luôn thay đổi, các thay đổi này cần ghi vào disk TempDB cũng lưu trên disk do vậy sự phụ thuộc vào bao nhiêu object tạm được tạo trong database này có thể là hơn cả user database I/O là một phần quan trọng của SQL Server performance, cần chắc chắn việc disk không bị bottleneck, trước đây thì việc này dễ để làm do các server có cắm disk để lưu trữ tại đó. Xem thêm https://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/ https://blogs.msdn.microsoft.com/sqljourney/2013/06/03/how-to-troubleshooting-sql-server-io-bottlenecks/
- #8: Overview Vấn đề phổ biến trong Index là việc thiếu index hoặc index không đúng column do đó SQL Server phải xử lý nhiều data hơn để tìm ra bản ghi phù hợp với điều kiện của câu query Vấn đề này sinh ra 2 kiểu tìm kiếm mà trong Execution plan gọi là Index Scans và Table Scans Việc có quá nhiều index dẫn đến SQL Server phải bảo trì tất cả các index đó, điều này có thể gây chậm database …, và các index này cũng đòi hỏi không gian lưu trữ trên disk Explanation Index scan hoặc Table scan là khi SQL Server phải scan data hoặc các trang index để tìm ra bản ghi phù hợp Ngược nghĩa với scan là seek, seek dùng index để xác định các bản ghi phù hợp với query Scan yêu cầu nhiều I/O và cũng xử lý lâu hơn Key Lookup cũng là một kiểu tìm kiếm cần tránh, xảy ra khi data được tìm trong một non-clustered index mà các dữ liệu thêm (ngoài các cột được index trong non-clustered index) trong query cần phải lấy từ clustered index. Nếu table không có clusterd index thì một RID Lookup thay thế Kiểm tra trắc nghiệm trong 3 phút về SQL Index: http://use-the-index-luke.com/3-minute-test Xem thêm https://www.mssqltips.com/sqlservertutorial/277/index-scans-and-table-scans/ https://www.mssqltips.com/sqlservertutorial/258/eliminating-bookmark-keyrid-lookups/ https://www.codeproject.com/Articles/234399/Database-performance-optimization-part-Indexing https://www.codeproject.com/Articles/243320/Database-performance-optimization-part-Index-mai https://www.red-gate.com/simple-talk/sql/performance/identifying-and-solving-index-scan-problems/
- #10: Internal fragmentation Những thay đổi này không phân bố đều giữa các row của table và các index, sự đầy đủ của mỗi page có thể thay đổi theo thời gian Những không gian không dùng đến này gây ra việc sử dụng bộ nhơ kém và tốn nhiều I/O dẫn đến hiệu năng truy vấn kém External fragmentation Sự chuyển từ một Extent tới Extent khác gây ra sự xoay vòng disk cao hơn Logical Fragmentation Vì Page Split, các page bị mất sự sắp xếp. Một page bị mất sự sắp xếp là page mà page vật lý tiếp theo được chỉ định tới một index là không phải là page được trỏ tới bởi con trỏ trang tiếp theo trong lá page hiện tại Xem thêm https://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/
- #11: Là một phương pháp để điều tra sự cố về hiệu năng của hệ thống SQL Server được gọi là “waits and queues” cũng được biết đến là “wait stats” SQL Server luôn theo dõi việc thực thi
- #12: Sub-query là các query bên trong một query Kiểu query này sẽ chạy đối với từng row của query bên ngoài trả ra, do đó làm giảm hiệu năng của truy vấn SQL Ex: SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c Dùng JOIN để thay thế Ex: SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID
- #20: SQL Server’s scheduling system works Thread dùng CPU (RUNNING) cho đến khi nó cần phải chờ một resource. Sau đó di chuyển tới một unordered list chứa các thread gọi là SUSPENDED. Trong khi đó, thread tiếp theo trong FIFO queue của các thread đang chờ CPU (RUNNABLE) được CPU chạy và chuyển thành RUNNING. Nếu thread trong SUSPENDED list được thông báo là resource của nó đã có thể dùng, thì nó chuyển thành RUNNABLE và được chuyển xuống cuối queue. Các thread tiếp tục chạy từ RUNNING đến SUSPENDED đến RUNNABLE đến RUNNING và lặp lại cho đến khi task hoàn thành.
- #21: Xem thêm https://www.codeproject.com/Articles/234399/Database-performance-optimization-part-Indexing
- #22: https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor https://www.mssqltips.com/sqlservertip/1872/sql-server-database-engine-tuning-advisor-for-performance-tuning/
- #23: http://www.sommarskog.se/query-plan-mysteries.html https://dba.stackexchange.com/questions/6502/code-creates-different-plan-when-ran-ad-hoc-vs-in-a-stored-procedure