TAILIEUCHUNG - Microsoft SQL Server 2008 R2 Unleashed- P134

Microsoft SQL Server 2008 R2 Unleashed- P134:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1274 CHAPTER 35 Understanding Query Optimization FIGURE A graphical execution plan of a query using parallel query techniques. Common Query Optimization Problems So you ve written a query and examined the query plan and performance isn t what you expected. It might appear that SQL Server isn t choosing the appropriate query plan that you expect. Is something wrong with the query or with the Query Optimizer Before delving into a detailed discussion about how to debug and analyze query plans covered in detail in Chapter 36 the following sections look at some of the most common problems and SQL coding issues that can lead to poor query plan selection. Out-of-Date or Insufficient Statistics Admittedly having out-of-date or unavailable statistics is not as big a problem as it was in SQL Server releases prior to . Back in those days the first question asked when someone was complaining of poor performance was When did you last update statistics If the answer was Huh we usually found the culprit. With the Auto-Update Statistics and Auto-Create Statistics features in SQL Server 2008 this problem is not as prevalent as it used to be. If a query detects that statistics are out of date or missing it causes them to be updated or created and then optimizes the query plan based on the new statistics. Download from Common Query Optimization Problems 1275 NOTE If statistics are missing or out of date the first running query that detects this condition might run a bit more slowly as it updates or creates the statistics first especially if the table is relatively large and also if it has been configured for FULLSCAN when indexes are updated. However SQL Server 2008 provides the AUTO_UPDATE_STATISTICS_ASYNC database option. When this option is set to ON queries do not wait for the statistics to be updated before compiling. Instead the out-of-date statistics are put on a queue for updating by a worker thread in a background process and the query and any other

TỪ KHÓA LIÊN QUAN
Đã phát hiện trình chặn quảng cáo AdBlock
Trang web này phụ thuộc vào doanh thu từ số lần hiển thị quảng cáo để tồn tại. Vui lòng tắt trình chặn quảng cáo của bạn hoặc tạm dừng tính năng chặn quảng cáo cho trang web này.