- Learn T-SQL Querying
- Pedro Lopes Pam Lahoud
- 313字
- 2021-06-24 14:38:18
Summary
We explored the internals of SQL Server query optimization and defined many important concepts that any database professional who writes T-SQL queries will keep coming back to, especially when troubleshooting query performance issues. The CE is a fundamental part of SQL Server's Query Optimizer; knowing how it uses statistics, and the importance of keeping updated and relevant statistics for the overall query optimization process, empowers database professionals to write good queries—queries that both drive and leverage good database schema designs. But also, understanding the main estimation model assumptions allows us to account for these when writing queries and to avoid pitfalls that hurt query performance. We will see these pitfalls in much more detail in Chapter 6, Easily-Identified T-SQL Anti-Patterns, and Chapter 7, Discovering T-SQL Anti-Patterns in Depth.
If, at the end of the optimization process, we still have a plan that is perceived to be inefficient, some avenues of investigation can help us determine the potential reasons for this inefficiency:
- Is it a bad CE? Analyze the execution plan to find the ratio between estimated and actual rows in costly operators. Perhaps the statistics are stalled and need to be updated?
- Is it a parameter-sensitive plan? Is it a dynamic unparameterized T-SQL statement? Or perhaps parameter-sniffing has caused a skewed query plan? The importance of parameters was discussed in Chapter 2, Understanding Query Processing, in the The importance of parameters section.
- Is it an inadequate physical database design? Are there missing indexes? Are data types for keys inadequate, thus leading to unwarranted conversions that affect estimations? Is referential integrity enforced by triggers instead of indexed foreign keys?
These are some of the aspects we must investigate as a potential source of plan inefficiency. In the next chapter, we will learn how to identify these inefficiencies by investigating the various aspects of query execution plans.
- 智能傳感器技術(shù)與應(yīng)用
- 計算機(jī)應(yīng)用復(fù)習(xí)與練習(xí)
- Blockchain Quick Start Guide
- Cloud Analytics with Microsoft Azure
- 空間傳感器網(wǎng)絡(luò)復(fù)雜區(qū)域智能監(jiān)測技術(shù)
- Hands-On Linux for Architects
- INSTANT Varnish Cache How-to
- 大數(shù)據(jù)挑戰(zhàn)與NoSQL數(shù)據(jù)庫技術(shù)
- AWS Administration Cookbook
- 基于RPA技術(shù)財務(wù)機(jī)器人的應(yīng)用與研究
- 電腦故障排除與維護(hù)終極技巧金典
- Hands-On DevOps
- C#編程兵書
- 特征工程入門與實(shí)踐
- 歐姆龍CP1H型PLC編程與應(yīng)用