sql server 入門
mysql和sql server？ This article will cover some essential techniques for SQL query tuning. Query tuning is a very wide topic to talk about, but some essential techniques never change in order to tune queries in SQL Server. Particularly, it is a difficult issue for those who are a newbie to SQL query tuning or who are thinking about starting it. So, this article will be a good starting point for them. Also, other readers can refresh their knowledge with this article. In the next parts of this article, we will mention these techniques that help to tune queries.
本文將介紹一些用于SQL查詢優化的基本技術。 查詢調優是一個非常廣泛的話題，但是為了調優SQL Server中的查詢，某些基本技術從未改變。 尤其對于那些剛開始使用SQL查詢優化或正在考慮啟動它的人來說，這是一個難題。 因此，本文將是他們的一個很好的起點。 另外，其他讀者可以通過本文來刷新自己的知識。 在本文的下一部分中，我們將提及有助于調優查詢的這些技術。
We should use the asterisk (star) sign solely when we need to return all columns of the table. However, this usage type becomes a bad habit by the programmers, and they start to type their queries with the “SELECT * “ statement. At various times in my database administrator career, I have experienced that the “SELECT * “ statement used to retrieve only one column of the multicolumn tables. Worse than this experience, the developer is not aware of this problem. This usage approach causes more network and I/O activity, so it affects the query performance negatively because of the more resource consumption. Now, we will make a pretty simple test to find out the performance difference between the “SELECT *” statement against “SELECT column_name1, column_name2, column_nameN” statement. Assume that, on a web application, we only need to show two columns, but we used the asterisk sign in our query.
僅在需要返回表的所有列時，才應僅使用星號（星號）。 但是，這種用法類型已成為程序員的壞習慣，并且他們開始使用“ SELECT * ”語句鍵入其查詢。 在數據庫管理員的職業生涯中的很多時候，我都經歷過“ SELECT * ”語句僅用于檢索多列表的一列。 比這種情況更糟糕的是，開發人員沒有意識到這一問題。 這種使用方法會導致更多的網絡和I / O活動，因此由于更多的資源消耗，會對查詢性能產生負面影響。 現在，我們將進行一個非常簡單的測試，以找出“ SELECT * ”語句與“ SELECT column_name1，column_name2，column_nameN ”語句之間的性能差異。 假設在Web應用程序上，我們只需要顯示兩列，但是在查詢中使用星號。
SQL Server Management Studio (SSMS) offers a very helpful tool that helps to analyze and compare the executed queries performance metrics. This tool name is Client Statistics, and we will activate this option before executing the following two queries.
SQL Server Management Studio（SSMS）提供了非常有用的工具，可以幫助分析和比較執行的查詢的性能指標。 該工具名稱是客戶統計 ， 并且我們將在執行以下兩個查詢之前激活此選項。
Now we will execute the sample queries respectively and open the Client Statistics tab.
現在，我們將分別執行示例查詢并打開“ 客戶端統計信息”選項卡。
---Query-1 first execute this query---
SELECT *
FROM Sales.SalesOrderDetail;
---Query-2 ---SELECT SalesOrderId , CarrierTrackingNumber
FROM Sales.SalesOrderDetail;
As we can see obviously, there is a dramatic difference between the received network measurements of these two select statements in the Network Statistics section. At the same time, the Time Statistics measurement comparison result does not change for the “SELECT *” statement. It shows poor performance than the “SELECT column_name1, column_name2, column_nameN” statement. In light of this information, we can come to this outcome “As possible as we should not use the asterisk signs in the SELECT statements.
顯而易見，在“ 網絡統計”部分中，這兩個選擇語句的接收到的網絡度量之間存在巨大差異。 同時，“ SELECT * ”語句的“ 時間統計”測量比較結果不會更改。 它顯示的性能比“ SELECT column_name1，column_name2，column_nameN”語句差。 根據這些信息，我們可以得出以下結果：“盡可能避免在SELECT語句中使用星號。
A scalar-valued function takes some parameters and returns a single value after a certain calculation. The main reason for why the scalar-valued functions affect performance negatively, the indexes cannot be used on this usage approach. When we analyze the following query execution plan, we will see an index scan operator because of the SUBSTRING function usage in the WHERE clause.
標量值函數接受一些參數，并在進行特定計算后返回單個值。 標量值函數對性能產生負面影響的主要原因是，不能在這種使用方法上使用索引。 當我們分析以下查詢執行計劃時，我們將看到索引掃描 運算符，因為WHERE子句中使用了SUBSTRING函數。
SELECT ProductNumber FROM Production.Product
WHERE SUBSTRING(ProductNumber,1,2) = 'AR'
The index scan operator reads all index pages in order to find the proper records. However, this operator consumes more I/O and takes more time. As possible, we should avoid the index scan operator when we see it in our execution plans.
索引掃描操作員讀取所有索引頁以找到正確的記錄。 但是，該操作員消耗更多的I / O并花費更多的時間。 當在執行計劃中看到索引掃描運算符時，應盡可能避免使用它。
On the other hand, particularly for this query, we can improve their performance with a little touch. Now we will change this query as below so the query optimizer will decide to use another operator.
另一方面，特別是對于此查詢，我們可以稍加改進即可提高其性能。 現在，我們將按以下方式更改此查詢，以便查詢優化器決定使用其他運算符。
SELECT ProductNumber FROM Production.Product
WHERE ProductNumber LIKE 'AR%'
The index seeks operator only reads the qualified rows because this operator effectively uses the indexes to retrieve the selective rows. At the same time, it shows great performance when against the index scan operator.
索引查找運算符僅讀取合格的行，因為該運算符有效地使用索引來檢索選擇的行。 同時，與索引掃描運算符相比，它顯示出出色的性能。
Tip: Scalar-valued functions are executed for each row of the resultset, so we should consider the rows number of the resultset when we use them. They can damage the query performance when trying to use for the queries which will return a huge number of rows. However, Microsoft has broken this traditional chain with SQL Server 2019 and made some performance improvements in the query optimizer for the scalar-valued functions, and it has been generating more accurate execution plans if any query contains the scalar-valued function. You can see the following article for more details about this improvement:
提示：標量值函數是針對結果集的每一行執行的，因此使用它們時，應考慮結果集的行數。 當試圖用于查詢時，它們會損壞查詢性能，這將返回大量的行。 但是，Microsoft打破了SQL Server 2019的傳統鏈條，并在針對標量值函數的查詢優化器中進行了一些性能改進，并且如果任何查詢包含標量值函數，它都會生成更準確的執行計劃。 您可以查看以下文章，以獲取有關此改進的更多詳細信息：
Improvements of Scalar User-defined function performance in SQL Server 2019
SQL Server 2019中標量用戶定義函數性能的改進
Covering indexes contains all referenced columns of the query, so they improve the selectivity of the index, and if any query uses this index, it accesses the data more efficiently. However, before creating any covered index, we need to figure out cost-benefit analyses because any new index directly affects the performance of the insert statement. Now we will analyze the execution plan of the following query.
覆蓋索引包含查詢的所有引用列，因此它們提高了索引的選擇性，并且如果有任何查詢使用此索引，它將更有效地訪問數據。 但是，在創建任何覆蓋索引之前，我們需要弄清楚成本效益分析，因為任何新索引都會直接影響insert語句的性能。 現在，我們將分析以下查詢的執行計劃。
SELECT CarrierTrackingNumber,UnitPrice FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 10 AND 10000
As we learned, the index scan operation is not shown good performance during the execution of the query. To overcome this problem, we will create the following index. The main characteristic of this index is that it covers all the columns of the query through the index key or included columns.
據我們了解，在執行查詢期間，索引掃描操作未顯示出良好的性能。 為了克服這個問題，我們將創建以下索引。 該索引的主要特征是它通過索引鍵或包含的列覆蓋了查詢的所有列。
CREATE NONCLUSTERED INDEX IX_00Cover
ON [Sales].[SalesOrderDetail] ([UnitPrice])
INCLUDE ([CarrierTrackingNumber])
After the creation of the index, we will re-analyze the actual execution plan of the same query.
創建索引后，我們將重新分析同一查詢的實際執行計劃。
The execution plan of the query has started to use an index seek operator, and this operator shows better performance than the index scan operator.
查詢的執行計劃已開始使用索引查找運算符，并且該運算符顯示出比索引掃描運算符更好的性能。
UNION ALL and UNION operators are used to combine two or more than two result sets of the select statements. However, the main difference between these two operators is the UNION operator eliminates the duplicate rows from the result set. In terms of the query tuning, UNION ALL operator perform better performance than UNION operator. As a result, if we don’t consider the duplicate rows in the result set, we should use UNION ALL operator in our select statements. When we compare execution plans of the following queries, we will see a noticeable difference between these two execution plans. At first, we will compare the execution plans.
UNION ALL和UNION運算符用于組合select語句的兩個或兩個以上結果集。 但是，這兩個運算符之間的主要區別是UNION運算符從結果集中消除了重復的行。 在查詢調優方面， UNION ALL運算符的性能優于UNION運算符。 因此，如果我們不考慮結果集中的重復行，則應在select語句中使用UNION ALL運算符。 當我們比較以下查詢的執行計劃時，我們將看到這兩個執行計劃之間的明顯差異。 首先，我們將比較執行計劃。
---Query-1---
SELECT Vendor.AccountNumber, Vendor.Name
FROM Purchasing.Vendor AS Vendor
UNION ALL
SELECT Vendor.AccountNumber, Vendor.Name
FROM Purchasing.Vendor AS Vendor---Query-2---
SELECT Vendor.AccountNumber, Vendor.Name
FROM Purchasing.Vendor AS Vendor
UNION
SELECT Vendor.AccountNumber, Vendor.Name
FROM Purchasing.Vendor AS Vendor
When we analyze the comparison of the execution plans, we can see that the Sort?operator adds extra cost to the select statement, which is using the UNION operator. As a final word about these two operators, if we don’t consider the duplicate records, we should use UNION ALL operator to combine the result sets.
在分析執行計劃的比較時，我們可以看到Sort運算符為select語句增加了額外的成本， 使用UNION運算符。 最后，關于這兩個運算符，如果我們不考慮重復的記錄，則應使用UNION ALL運算符組合結果集。
The execution plan offers us to the visual presentation?of the query processing steps. When we analyze any execution plan, we can clearly understand the road map of the query, and it is also a significant beginning point to tune a query. The estimated and actual execution plans are the two types of execution plans that can be used by us to analyze the queries. During the creation of the estimated execution plan, the query does not execute but generated by the query optimizer. Despite that, it does not contain any runtime metrics and warnings.
執行計劃為我們提供了查詢處理步驟的可視化表示。 在分析任何執行計劃時，我們可以清楚地了解查詢的路線圖，這也是調整查詢的重要起點。 估計執行計劃和實際執行計劃是我們可以用來分析查詢的兩種執行計劃。 在創建估計的執行計劃期間，查詢不會執行，而是由查詢優化器生成。 盡管如此，它不包含任何運行時指標和警告。
On the other hand, the actual execution plan contains more reliable information and measurements about the query. Actual Execution plans provide an advantage to tune the query performance.
另一方面，實際執行計劃包含有關查詢的更可靠的信息和度量。 實際執行計劃為調整查詢性能提供了一個優勢。
Another approach can be combo usage for the execution plans for the queries which have longer execution times. At first, we can check the estimated execution plan and then re-analyze the actual execution plan.
另一種方法可以是對執行時間較長的查詢的執行計劃進行組合使用。 首先，我們可以檢查估計的執行計劃，然后重新分析實際的執行計劃。
Tip: If a query includes any temp table, the estimated execution plan can not be generated. It will be given an error when we try to generate it. The following query will return an error when trying to generate an estimated execution plan.
提示：如果查詢中包含任何臨時表，則無法生成估計的執行計劃。 當我們嘗試生成它時，將給出一個錯誤。 嘗試生成估計的執行計劃時，以下查詢將返回錯誤。
SELECT Name,ProductNumber
INTO #TempTbl
FROM Production.ProductSELECT * FROM #TempTbl
In this article, we learned essential techniques to tune SQL queries. Performance tuning is a very complicated and struggling task to accomplish, but we can learn this concept from easy to difficult, and this article can be a good beginning point to start. Also, the following topics are vital to improving our query tuning skills; therefore, you can look at the following articles for the next steps of your learnings.
在本文中，我們學習了調優SQL查詢的基本技術。 性能調優是一項非常復雜且艱巨的任務，但是我們可以從容易到艱難地學習這一概念，并且本文可以作為一個很好的起點。 另外，以下主題對于提高我們的查詢調優技能至關重要。 因此，您可以查看以下文章，以了解接下來的學習步驟。
翻譯自: https://www.sqlshack.com/getting-started-with-sql-server-query-tuning/
sql server 入門
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
