DBA Fundamentals-SQL Server Performance Tuning
2016/9/21 PASS online event
DBA Fundamentals: SQL Server Performance Tuning
Presented by Pinal Dave
1.資料庫相容性層級影響基數估計(Cardinality Estimation)
提升資料庫相容性層級就可以改善效能
The New and Improved Cardinality Estimator in SQL Server 2014
https://blogs.technet.microsoft.com/dataplatforminsider/2014/03/17/the-new-and-improved-cardinality-estimator-in-sql-server-2014/
Cardinality Estimation (SQL Server)
https://msdn.microsoft.com/en-us/library/dn600374.aspx
CE(Cardinality Estimation)會預測您的查詢可能傳回的資料列數目。 查詢最佳化工具使用基數預測，來產生最佳查詢計劃。 CE 愈精確，查詢計劃通常愈理想。
In 1998, a major update of the CE was part of Microsoft SQL Server 7.0, for which the compatibility level was 70. Subsequent updates came with SQL Server 2014 and SQL Server 2016, meaning compatibility levels 120 and 130. The CE updates for levels 120 and 130 incorporate assumptions and algorithms that work well on modern data warehousing workloads and on OLTP (online transaction processing).
新版(層級 120 和 130 )的基數估計更新了合併假設與演算法，增強並適用於新式資料倉儲工作負載和 OLTP (線上交易處理)。
改善了以下的問題
Your application system could possibly have an important query whose plan is changed to a slower plan due to the new CE. Such a query might be like one of the following:
- An OLTP query that runs so frequently that multiple instance of it often run concurrently.
- A SELECT with substantial aggregation that runs during your OLTP business hours.
Troubleshooting Poor Query Performance: Cardinality Estimation
https://technet.microsoft.com/en-us/library/ms181034(v=sql.105).aspx
2.自動更新與建立統計資訊
Statistics
https://msdn.microsoft.com/en-us/library/ms190397.aspx
3.Missing index (script from Pinal Dave blog)
https://blog.sqlauthority.com
https://go.sqlauthority.com
https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/
4.Wait Stats and Queues (script from Pinal Dave blog)