索引sql server
Understanding indexing needs allows us to ensure that important processes run efficiently and that our server hardware is not being over-taxed by poorly performing queries.
sqlserver如何加索引， 了解索引需求使我們能夠確保重要的流程高效運行，并且不會因查詢性能不佳而使服務器硬件負擔過重。
Collecting metrics on SQL Server index usage and missing index needs is critical to making smart decisions. To be truly proactive, though, we need to create a framework that allows us to quickly, efficiently, and regularly report on changes in indexing needs. With a system in place that can let us know when changes are needed, we can stay ahead of the optimization game and keep our customers happy!
收集有關SQL Server索引使用情況和缺少索引需求的指標對于做出明智的決定至關重要。 但是，要真正主動，我們需要創建一個框架，使我們能夠快速，有效地并定期報告索引需求的變化。 有了一個可以讓我們知道何時需要更改的系統，我們可以領先于優化游戲并保持客戶滿意！
sql索引有什么用， In previous articles, we built processes that regularly collect and aggregate data about missing SQL Server indexes and index usage statistics. Using this information, we can take additional steps to analyze this data and begin to make decisions based on it. Our goal is to build a reporting framework that can be run as often as desired where the results are stored in a table for easy retrieval.
在以前的文章中，我們構建了一些流程來定期收集和聚合有關缺失SQL Server索引和索引使用情況統計信息的數據。 使用此信息，我們可以采取其他步驟來分析此數據并開始根據該數據做出決策。 我們的目標是建立一個報告框架，該框架可以根據需要運行，并將結果存儲在表格中以便于檢索。
Once we report on index usage or missing SQL Server indexes, we can generate recommendations. Each time the process is run, we can check those recommendations and determine what has changed and if they have become more or less relevant over time. If we implemented a suggestion, we can then track the results and determine if it was a good idea, or perhaps not worth the cost.
sql server和mysql。 一旦我們報告了索引使用情況或缺少SQL Server索引，便可以生成建議。 每次運行該流程時，我們都可以檢查這些建議并確定已更改的內容以及它們是否隨著時間的推移變得越來越相關。 如果我們實施了建議，則可以跟蹤結果并確定這是一個好主意，還是不值得付出的代價。
The goal is to allow us to fully understand indexing on our important databases and to be able to proactively act on this information. Things change all the time, and being able to quantify that change before and after any database or application changes will allow us to stay on top of indexing. Our reaction times to change will be far better and, hopefully, we’ll avoid the pain of performance troubles that could have been prevented had we had more information available up-front.
目的是使我們能夠充分理解重要數據庫上的索引，并能夠主動對這些信息采取行動。 事情一直在變化，并且能夠在任何數據庫或應用程序更改之前和之后量化該更改，這將使我們能夠始終處于索引編制之上。 我們對變更的React時間將大大縮短，并且希望，如果我們能預先獲得更多信息，我們將避免可能避免的性能問題。
sqlserver安裝、 In our previous work, we developed processes that collected data about index usage. First, we work with index usage statistics, which allow us to understand how often indexes are read or written to. Using this data, we can determine if an index is unused, minimally used, or misused. Once we understand those results, we can make a decision to drop the index (if unneeded) or alter it (if it could benefit from different columns).
在以前的工作中，我們開發了收集有關索引使用情況數據的流程。 首先，我們使用索引使用情況統計信息，該統計信息使我們能夠了解讀取或寫入索引的頻率。 使用此數據，我們可以確定索引是否未使用，使用最少或濫用。 一旦了解了這些結果，就可以決定刪除索引（如果不需要）或更改索引（如果可以從不同的列中受益）。
Second, we collected data about missing indexes. As queries executed that could have benefitted from an index that does not exist, we report on this data so that we can later make decisions based on it. Many index recommendations are not worth the effort or overlap with each other, but all of this data allows us to better understand the workload that executes against our SQL Servers and decide what new indexes could be worth the resources.
sql server2012， 其次，我們收集了有關缺失索引的數據。 由于執行的查詢可能會從不存在的索引中受益，因此我們將報告此數據，以便以后可以基于該數據做出決策。 許多索引建議不值得付出努力或彼此重疊，但是所有這些數據使我們能夠更好地了解針對我們SQL Server執行的工作負載，并確定哪些新索引可能值得這些資源。
Our first process populated tables called Index_Utilization_Details and Index_Utilization_Summary. The first table provided a snapshot of usage metrics each time the process was run. The second table was an aggregate that maintained overall usage counts over time, allowing for a better long-term view of index use. A brief snapshot of data in these tables looks like this:
我們的第一個過程填充了稱為Index_Utilization_Details和Index_Utilization_Summary的表。 第一個表在每次運行過程時提供使用情況指標的快照。 第二張表是一個匯總表，可以隨著時間的推移保持總體使用情況計數，從而可以更好地長期查看索引使用情況。 這些表中的數據簡要快照如下所示：
Our second process also populated 2 tables: Missing_Index_Details and Missing_Index_Summary. These tables also offered both a point-in-time view of missing index metrics, as well as a longer-term aggregated view of overall use. Two views were created in order to provide additional metrics that can be derived from this data: v_Missing_Index_Details and v_Missing_Index_Summary. Here is a screenshot of some sample data returned from these views:
我們的第二個過程還填充了2個表： Missing_Index_Details和Missing_Index_Summary 。 這些表還提供缺少索引指標的時間點視圖以及整體使用情況的長期匯總視圖。 為了提供可以從該數據派生的其他指標，創建了兩個視圖： v_Missing_Index_Details和v_Missing_Index_Summary 。 這是從這些視圖返回的一些示例數據的屏幕截圖：
We can see via this data that we now have a wealth of data that is regularly collected and aggregated. We can choose to periodically review this data and decide if any actions need to be taken, but we can remove the manual element to that process and automate a process that inspects this data for us, putting the results into a central location. From here, we can report on it using an email, SSRS, or any other tool of our choosing.
通過這些數據我們可以看到，我們現在擁有大量定期收集和匯總的數據。 我們可以選擇定期查看此數據并決定是否需要采取任何措施，但是我們可以刪除該流程中的手動元素，并為我們自動檢查該數據的流程，從而將結果放置在中心位置。 在這里，我們可以使用電子郵件，SSRS或我們選擇的任何其他工具對此進行報告。
The remainder of this article will be concerned with making the best possible use of this data in order to easily report on potential indexing needs. At the same time, we will make an effort to go overboard and over-engineer a solution that is too complex to maintain or use.
本文的其余部分將關注如何最好地利用此數據，以便輕松報告潛在的索引需求。 同時，我們將努力解決過于復雜而難以維護或使用的解決方案。
Before diving into our data, let’s put together a plan of attack. There are enough ways in which this process could be implemented that we should endeavor to stay focused on our ultimate goal, which is to report on important index needs. Here are is the basic structure of the stored procedure that we will be building:
在深入研究數據之前，讓我們制定一個攻擊計劃。 有足夠的方法可以實施此過程，我們應該努力保持專注于最終目標，即報告重要的索引需求。 這是我們將要構建的存儲過程的基本結構：
Phew! That’s quite a bit of work we have to do! It sounds like a lot, but in reality, it’s a variety of checks against data that we have already collected in order to determine if it is significant enough to report back to us. With that (slightly) reassuring thought in mind, let’s begin building this thing!
！ 這是我們要做的很多工作！ 聽起來很多，但實際上，它是對我們已經收集的數據進行的各種檢查，以確定它是否足夠重要以向我們報告。 帶著（稍微）令人放心的想法，讓我們開始構建這個東西！
The easiest metric to check for are indexes that are unused. The validation is easy, and the most important piece of data we would want to know is for how long they have been unused. We can view unused indexes like this:
最容易檢查的指標是未使用的索引。 驗證很容易，我們想知道的最重要的數據是未使用多長時間。 我們可以這樣查看未使用的索引：
?
SELECTIndex_Utilization_Summary.Database_Name,Index_Utilization_Summary.[Schema_Name],Index_Utilization_Summary.Table_Name,Index_Utilization_Summary.Index_Name,Index_Utilization_Summary.Index_Utilization_Summary_Create_Datetime,Index_Utilization_Summary.Index_Utilization_Summary_Last_Update_Datetime,Index_Utilization_Summary.User_Update_Count_Total,Index_Utilization_Summary.Last_User_Update,DATEDIFF(DAY, Index_Utilization_Summary.Index_Utilization_Summary_Create_Datetime, Index_Utilization_Summary.Index_Utilization_Summary_Last_Update_Datetime) AS Days_Without_Reads
FROM IndexMetrics.Index_Utilization_Summary
WHERE Index_Utilization_Summary.User_Seek_Count_Total = 0
AND Index_Utilization_Summary.User_Scan_Count_Total = 0
AND Index_Utilization_Summary.User_Lookup_Count_Total = 0;In addition to knowing that the index has not been read, the last column provides how many days have passed without any reads. While an index that has been unused for 6 months is a great candidate for removal, one that has only been unused for a day would need some more time to be sure that it is definitely not used. The results of this query are as follows:
除了知道尚未讀取索引之外，最后一列還提供了多少天沒有讀取任何數據。 雖然已使用6個月的索引很適合刪除，但僅使用1天的索引將需要更多時間來確保絕對不會使用。 該查詢的結果如下：
Since the last seek/scan/lookup columns are NULL for unused indexes, we have to guess as to how long the index has been unused for. To be safe, we will only go back as far as our index metrics have been collected. Our results above show 5 indexes without any reads, 3 of which have been unused for almost a month and might be good candidates for removal. As always, an index unused for 2 months might be critical to some quarterly or yearly report that will bomb without it, so some level of verification of non-use is a good idea before making changes.
由于未使用的索引的最后一個搜索/掃描/查找列為NULL，因此我們必須猜測該索引已被使用了多長時間。 為了安全起見，我們只會回溯到我們收集到的指標指標。 我們上面的結果顯示5個索引沒有任何讀取，其中3個已經使用了將近一個月，并且很可能會被刪除。 與往常一樣，未使用2個月的索引對于某些季度或年度報告可能至關重要，這些報告會在沒有該索引的情況下爆炸，因此在進行更改之前一定程度的驗證未使用是個好主意。
Next, we can look for indexes that are underused. These are indexes that are frequently written but rarely read. There is no universally accepted definition for what underused is, so for the sake of our demonstration, we will consider any index that has less than 2% reads. You may adjust this based on your comfort level with the schema and data that is being analyzed to be more conservative or return more data.
接下來，我們可以查找未充分使用的索引。 這些是經常寫入但很少讀取的索引。 對于未充分利用的定義沒有普遍接受的定義，因此為了便于演示，我們將考慮讀取次數少于2％的任何索引。 您可以根據您的舒適程度使用正在分析的方案和數據進行調整，以使其更加保守或返回更多數據。
The following query will return any indexes that have 2% or less reads, but greater than zero (separating this data from our previous query):
以下查詢將返回讀取次數小于或等于2％但大于零的所有索引（將這些數據與之前的查詢分開）：
?
WITH CTE_UNDERUSED_INDEXES AS (SELECT*,CAST(Index_Utilization_Summary.User_Seek_Count_Total + Index_Utilization_Summary.User_Scan_Count_Total + Index_Utilization_Summary.User_Lookup_Count_Total AS DECIMAL(19,2)) AS Read_Count,CAST(Index_Utilization_Summary.User_Seek_Count_Total + Index_Utilization_Summary.User_Scan_Count_Total + Index_Utilization_Summary.User_Lookup_Count_Total + User_Update_Count_Total AS DECIMAL(19,2)) AS Operation_CountFROM IndexMetrics.Index_Utilization_Summary )
SELECTCTE_UNDERUSED_INDEXES.Read_Count / CTE_UNDERUSED_INDEXES.Operation_Count AS Percent_Read,CTE_UNDERUSED_INDEXES.Index_Utilization_Summary_Create_Datetime,CTE_UNDERUSED_INDEXES.Index_Utilization_Summary_Last_Update_Datetime,CTE_UNDERUSED_INDEXES.Database_Name,CTE_UNDERUSED_INDEXES.[Schema_Name],CTE_UNDERUSED_INDEXES.Table_Name,CTE_UNDERUSED_INDEXES.Index_Name,CTE_UNDERUSED_INDEXES.User_Seek_Count_Total,CTE_UNDERUSED_INDEXES.User_Scan_Count_Total,CTE_UNDERUSED_INDEXES.User_Lookup_Count_Total,CTE_UNDERUSED_INDEXES.User_Update_Count_Total,CTE_UNDERUSED_INDEXES.Last_User_Seek,CTE_UNDERUSED_INDEXES.Last_User_Scan,CTE_UNDERUSED_INDEXES.Last_User_Lookup,CTE_UNDERUSED_INDEXES.Last_User_Update
FROM CTE_UNDERUSED_INDEXES
WHERE CTE_UNDERUSED_INDEXES.Read_Count / CASE WHEN CTE_UNDERUSED_INDEXES.Operation_Count = 0 THEN 1 ELSE CTE_UNDERUSED_INDEXES.Operation_Count END > 0
AND CTE_UNDERUSED_INDEXES.Read_Count / CASE WHEN CTE_UNDERUSED_INDEXES.Operation_Count = 0 THEN 1 ELSE CTE_UNDERUSED_INDEXES.Operation_Count END <= 0.02;The CTE helps us to avoid divide-by-zero errors, and perform our calculations without too much repeated TSQL. The results look like this:
CTE幫助我們避免被零除的錯誤，并執行我們的計算而無需太多重復的TSQL。 結果如下：
A single index is returned against one of our index utilization tables. Never fear—I intentionally performed a ton of writes on the table to make this appear in our filters, but this illustrates how we can separate between unused indexes and underused indexes, allowing us to broaden the scope of analysis to include indexes that are used but might still be worth some scrutiny.
針對我們的索引利用率表之一返回單個索引。 不用擔心-我故意在表上進行大量寫操作以使它出現在我們的過濾器中，但這說明了如何區分未使用的索引和未充分使用的索引，從而使我們能夠擴大分析范圍，以包括已使用的索引，但也許仍然值得仔細研究。
The index returned above has only 1.2% of all operations as reads, meaning that we pay quite a bit for maintenance, and may not get a very good value for it. It’s possible those 1.2 % are extremely important, or perhaps they are not. These are the questions that we should consider prior to making any changes.
上面返回的索引僅占讀取的所有操作的1.2％，這意味著我們為維護付出了很多，并且可能無法獲得很好的價值。 這1.2％可能非常重要，或者可能并非如此。 這些是我們進行任何更改之前應考慮的問題。
Our last category of the index to consider is one that is ineffectively used. These would be indexes that are scanned frequently and rarely or never the target of seek operations. While it may be by design, I would be concerned if an index on a large/busy production table were being scanned often. If we are not intentionally looking to return a large portion (or all) of the index, then a great deal of reads is being wasted.
我們要考慮的索引的最后一類是使用無效的索引。 這些索引是經常掃描的索引，很少或永遠不會是搜索操作的目標。 盡管可能是設計使然，但我會擔心是否經常掃描大型/繁忙生產表上的索引。 如果我們不是故意要返回很大一部分（或全部）索引，那么大量的讀取就被浪費了。
Similar to earlier, we will set an arbitrary break point for determining if an index is effective or not. If 98% or more of operations are scans, then we will flag it as a potential problem. We can use the following query to check for any indexes that happen to meet this criterion:
與前面類似，我們將設置一個任意斷點來確定索引是否有效。 如果98％或更多的操作是掃描，那么我們會將其標記為潛在問題。 我們可以使用以下查詢來檢查碰巧符合此條件的所有索引：
?
WITH CTE_UNDERUSED_INDEXES AS (SELECT*,CAST(Index_Utilization_Summary.User_Seek_Count_Total + Index_Utilization_Summary.User_Scan_Count_Total + Index_Utilization_Summary.User_Lookup_Count_Total AS DECIMAL(19,2)) AS Read_CountFROM IndexMetrics.Index_Utilization_Summary )
SELECTCTE_UNDERUSED_INDEXES.Index_Utilization_Summary_Create_Datetime,CTE_UNDERUSED_INDEXES.Index_Utilization_Summary_Last_Update_Datetime,CTE_UNDERUSED_INDEXES.Database_Name,CTE_UNDERUSED_INDEXES.[Schema_Name],CTE_UNDERUSED_INDEXES.Table_Name,CTE_UNDERUSED_INDEXES.Index_Name,CTE_UNDERUSED_INDEXES.User_Seek_Count_Total,CTE_UNDERUSED_INDEXES.User_Scan_Count_Total,CTE_UNDERUSED_INDEXES.User_Lookup_Count_Total,CTE_UNDERUSED_INDEXES.User_Update_Count_Total,CTE_UNDERUSED_INDEXES.Last_User_Seek,CTE_UNDERUSED_INDEXES.Last_User_Scan,CTE_UNDERUSED_INDEXES.Last_User_Lookup,CTE_UNDERUSED_INDEXES.Last_User_Update
FROM CTE_UNDERUSED_INDEXES
WHERE CTE_UNDERUSED_INDEXES.User_Scan_Count_Total / CASE WHEN CTE_UNDERUSED_INDEXES.Read_Count = 0 THEN 1 ELSE CTE_UNDERUSED_INDEXES.Read_Count END >= 0.98;Reviewing the results, we can see that there are 9 indexes that happen to meet this criteria:
查看結果，我們發現有9個索引恰好滿足此條件：
As we look at these indexes, it becomes clear that the solution may not be straightforward. Dropping these indexes is not going to solve our scan problems (if they are actually problematic). Some are clustered primary keys, which are important to our underlying data.
當我們查看這些索引時，很明顯，解決方案可能并不簡單。 刪除這些索引并不能解決我們的掃描問題（如果它們確實有問題）。 一些是集群主鍵，對我們的基礎數據很重要。
To investigate further, we’d want to check and see if these scans are a performance concern. If they are, then there are likely missing indexes that could address them or a known problem-query that could be responsible for them. In other words, this data is useful as it provides an additional metric when analyzing an overall indexing strategy. The correct action may be to do nothing, or it may involve further research. We may be able to use this data to corroborate other performance concerns in order to make a solid case in favor of other schema or application changes.
為了進一步調查，我們想檢查一下這些掃描是否與性能有關。 如果是這樣，則可能會缺少可以解決這些問題的索引，或者可能是對它們負責的已知問題查詢。 換句話說，此數據很有用，因為它在分析整體索引策略時提供了額外的指標。 正確的措施可能是什么都不做，或者可能需要進一步的研究。 我們可能能夠使用此數據來證實其他性能問題，從而為支持其他架構或應用程序更改提供可靠的理由。
Now that we have reviewed our existing indexes, we can begin to look at what indexes are missing and could benefit performance on a given database. As always, not all missing indexes are good indexes, so we need to evaluate each carefully before making any changes in a production environment. We can query our existing missing index data in order to determine which are important to us:
現在，我們已經檢查了現有索引，我們可以開始查看缺少哪些索引，這些索引可以改善給定數據庫的性能。 與往常一樣，并非所有丟失的索引都是好的索引，因此我們需要在生產環境中進行任何更改之前仔細評估每個索引。 我們可以查詢現有的缺失索引數據，以確定哪些對我們很重要：
?
SELECT'CREATE MISSING INDEX' AS Recommended_Action,v_Missing_Index_Summary.First_Index_Suggestion_Time AS Index_Metric_Creation_Datetime,v_Missing_Index_Summary.Last_User_Seek AS Last_Recommended_Datetime,v_Missing_Index_Summary.[Database_Name],v_Missing_Index_Summary.[Schema_Name],v_Missing_Index_Summary.Table_Name,'Index_' + CONVERT (VARCHAR, ROW_NUMBER() OVER (ORDER BY v_Missing_Index_Summary.Index_Creation_Statement)) + '_' + v_Missing_Index_Summary.Table_Name AS Index_Name,v_Missing_Index_Summary.Equality_Columns,v_Missing_Index_Summary.Inequality_Columns,v_Missing_Index_Summary.Include_Columns,v_Missing_Index_Summary.User_Seeks AS User_Seek_Count,NULL AS User_Scan_Count,NULL AS User_Lookup_Count,NULL AS User_Update_Count,NULL AS Last_User_Seek,NULL AS Last_User_Scan,NULL AS Last_User_Lookup,NULL AS Last_User_Update,NULL AS Is_Primary_Key,NULL AS Is_Clustered_Index,v_Missing_Index_Summary.Improvement_Measure,NULL AS Is_Unused,NULL AS Is_Dropped
FROM IndexMetrics.v_Missing_Index_Summary
WHERE v_Missing_Index_Summary.Improvement_Measure > 5The filters on this query will ignore any missing index that has already been implemented but more importantly will only return missing indexes with an improvement measure greater than 5. This number is completely arbitrary and you are free to adjust up or down based on your sensitivity towards recommendations. Adjust it higher, get fewer results—adjust it lower and get more results.
此查詢中的過濾器將忽略任何已實施的缺失索引，但更重要的是，只會返回改善程度大于5的缺失索引。此數字完全是任意的，您可以根據自己的敏感度隨意向上或向下調整建議。 對其進行更高的調整，可獲得更少的結果，而對其進行較低的調整，可獲得更多的結果。
Here are the suggested missing indexes on my local SQL Server:
這是我的本地SQL Server上建議的缺少索引：
Included are all metrics on the missing index, including the number of benefitting seeks that would have occurred, as well as the improvement measure (off the right side of the screen). The index name is also completely arbitrary, but I find it useful to fill in the blank with something meaningful. It can easily be adjusted to fit whatever index naming convention you follow in order to make this more convenient.
其中包括遺失索引上的所有指標，包括可能發生的獲利尋求的數量以及改進措施（位于屏幕右側）。 索引名稱也完全是任意的，但我發現用有意義的內容填充空白很有用。 可以輕松調整它以適應您遵循的任何索引命名約定，以使其更加方便。
Clustered indexes are special in that they form the logical basis for storage of a table. Unused clustered indexes strongly imply that the table as a whole is not used. More research is required—for example, to determine if all non-clustered indexes are also unused, but this scenario is worth noting:
聚集索引的特殊之處在于它們構成了存儲表的邏輯基礎。 未使用的聚集索引強烈暗示著整個表未使用。 需要做更多的研究，例如，確定是否還沒有使用所有非聚集索引，但是這種情況值得注意：
?
SELECTIndex_Utilization_Summary.Database_Name,Index_Utilization_Summary.[Schema_Name],Index_Utilization_Summary.Table_Name,Index_Utilization_Summary.Index_Name,Index_Utilization_Summary.Index_Utilization_Summary_Create_Datetime,Index_Utilization_Summary.Index_Utilization_Summary_Last_Update_Datetime,Index_Utilization_Summary.User_Update_Count_Total,Index_Utilization_Summary.Last_User_Update,DATEDIFF(DAY, Index_Utilization_Summary.Index_Utilization_Summary_Create_Datetime, Index_Utilization_Summary.Index_Utilization_Summary_Last_Update_Datetime) AS Days_Without_Reads
FROM IndexMetrics.Index_Utilization_Summary
WHERE Index_Utilization_Summary.User_Seek_Count_Total = 0
AND Index_Utilization_Summary.User_Scan_Count_Total = 0
AND Index_Utilization_Summary.User_Lookup_Count_Total = 0
AND Index_Utilization_Summary.User_Update_Count_Total = 0
AND Index_Utilization_Summary.Is_Clustered_Index = 1;This query is similar to our search for unused indexes, except that it also validates if it is a clustered index with absolutely no use at all (reads or writes). The results can allow us to ask the question: “Is this table unused?”. From that question, we can determine if it is unused, and if so, is this a good thing? Are all other non-clustered indexes on the table unused? If truly unused, can it be dropped? Unused tables can result from failed or nonexistent cleanup projects, but could also be a sign that an application is failing to use a table correctly.
該查詢類似于我們對未使用索引的搜索，不同之處在于它還會驗證它是否是完全沒有使用（讀或寫）的聚集索引。 結果可以使我們提出一個問題：“此表是否未使用？”。 從這個問題，我們可以確定它是否未使用，如果是，這是件好事嗎？ 表上的所有其他非聚集索引是否未使用？ 如果確實未使用，可以將其丟棄嗎？ 未使用的表可能是由于清理項目失敗或不存在導致的，但也可能表示應用程序無法正確使用表。
An alternate way to view unused index data is to constrain usage by the last time the table was used. Perhaps it is not unused for all-time, but it has been unused in the past month. The following query will determine if any indexes have not received a read or write in the past month, but were not picked up by our unused index check from earlier:
查看未使用的索引數據的另一種方法是在上次使用該表之前限制使用情況。 也許它并非一直未被使用，但在過去一個月中一直未被使用。 以下查詢將確定在過去一個月中是否沒有任何索引接受過讀取或寫入，但是之前的未使用索引檢查未將其選中：
?
SELECT*
FROM IndexMetrics.Index_Utilization_Summary
WHERE (Index_Utilization_Summary.Last_User_Seek IS NULL OR Index_Utilization_Summary.Last_User_Seek <= DATEADD(MONTH, -1, CURRENT_TIMESTAMP))
AND (Index_Utilization_Summary.Last_User_Scan IS NULL OR Index_Utilization_Summary.Last_User_Scan <= DATEADD(MONTH, -1, CURRENT_TIMESTAMP))
AND (Index_Utilization_Summary.Last_User_Lookup IS NULL OR Index_Utilization_Summary.Last_User_Lookup <= DATEADD(MONTH, -1, CURRENT_TIMESTAMP))
AND (Index_Utilization_Summary.Last_User_Update IS NULL OR Index_Utilization_Summary.Last_User_Update <= DATEADD(MONTH, -1, CURRENT_TIMESTAMP))
AND (Index_Utilization_Summary.Last_User_Seek + Index_Utilization_Summary.Last_User_Scan + Index_Utilization_Summary.Last_User_Lookup + Index_Utilization_Summary.Last_User_Update <> 0)One final check we can make it to look for clustered index scans. Earlier, we identified misused indexes: Those with far more scans than seeks. When the index with a high scan count happens to be the clustered index, then we know that there may be some queries out there that are not covered by indexes but should be. We can filter and get info on these like this:
我們可以進行最后一項檢查，以查找聚簇索引掃描。 之前，我們確定了誤用的索引：掃描次數遠多于查找次數的索引。 當具有較高掃描計數的索引恰好是聚集索引時，那么我們知道那里可能存在一些未被索引覆蓋但應該包含的查詢。 我們可以像這樣過濾并獲取有關信息：
?
WITH CTE_UNDERUSED_INDEXES AS (SELECT*,CAST(Index_Utilization_Summary.User_Seek_Count_Total + Index_Utilization_Summary.User_Scan_Count_Total + Index_Utilization_Summary.User_Lookup_Count_Total AS DECIMAL(19,2)) AS Read_CountFROM IndexMetrics.Index_Utilization_SummaryWHERE Index_Utilization_Summary.Is_Clustered_Index = 1 )
SELECTCTE_UNDERUSED_INDEXES.Index_Utilization_Summary_Create_Datetime,CTE_UNDERUSED_INDEXES.Index_Utilization_Summary_Last_Update_Datetime,CTE_UNDERUSED_INDEXES.Database_Name,CTE_UNDERUSED_INDEXES.[Schema_Name],CTE_UNDERUSED_INDEXES.Table_Name,CTE_UNDERUSED_INDEXES.Index_Name,CTE_UNDERUSED_INDEXES.User_Seek_Count_Total,CTE_UNDERUSED_INDEXES.User_Scan_Count_Total,CTE_UNDERUSED_INDEXES.User_Lookup_Count_Total,CTE_UNDERUSED_INDEXES.User_Update_Count_Total,CTE_UNDERUSED_INDEXES.Last_User_Seek,CTE_UNDERUSED_INDEXES.Last_User_Scan,CTE_UNDERUSED_INDEXES.Last_User_Lookup,CTE_UNDERUSED_INDEXES.Last_User_Update
FROM CTE_UNDERUSED_INDEXES
WHERE CTE_UNDERUSED_INDEXES.User_Scan_Count_Total / CASE WHEN CTE_UNDERUSED_INDEXES.Read_Count = 0 THEN 1 ELSE CTE_UNDERUSED_INDEXES.Read_Count END >= 0.98;The results of this tell us where more research is required. Odds are very good that one of two scenarios apply to this situation:
結果告訴我們需要更多的研究。 兩種情況之一適用于這種情況的可能性很好：
For either scenario, we have identified a potential performance problem and can address whether it is one that requires fixing or if it is indicative of normal behavior.
對于這兩種情況，我們都確定了潛在的性能問題，可以解決是需要修復的問題還是表明其行為正常的問題。
With all of the data collected above, it would be beneficial to collect, store, and reference it as needed. We can run queries like those shown here whenever needed, but there will be some time savings if we turn the various queries into a stored procedure and were able to reference the collected data anytime.
使用上面收集的所有數據，根據需要收集，存儲和引用它會很有用。 我們可以在需要時運行類似此處所示的查詢，但是如果我們將各種查詢轉換為存儲過程并能夠隨時引用所收集的數據，則會節省一些時間。
This process will collect the data above and then crunch it to determine if any previous recommendations were implemented. The goal is for the process to be as simple as possible:
此過程將收集上面的數據，然后對其進行處理，以確定是否實施了先前的任何建議。 目的是使過程盡可能簡單：
From that point, the data is available for review anytime. Attached to this article are updated versions of the missing index collection process, index utilization process, and a new process that will collect and analyze this data for you. This is optional but could be a nice way to codify how indexes should be managed, reducing the chance for mistaken decisions or inconsistent filters.
從那時起，可以隨時查看數據。 本文附帶的是缺少索引收集過程，索引使用過程以及將為您收集和分析此數據的新過程的更新版本。 這是可選的，但可能是一種很好的方式來整理應如何管理索引，從而減少了決策錯誤或過濾器不一致的機會。
The missing index and index utilization collection processes can be used independently of the recommendation proc successfully. They can be scheduled to run daily and allowed to collect data that can, over time, be used to make smart indexing decisions.
可以成功使用丟失的索引和索引利用率收集過程，而與推薦過程無關。 可以將它們安排為每天運行，并允許它們收集可隨時間推移用于制定智能索引決策的數據。
With any script that analyzes data and returns recommendations, there is the threat of building a black box so complex that it becomes uncertain how it works or why. Such a script also becomes error-prone and more likely to break under various unforeseen circumstances. Our goal in this effort is to develop insight into index metrics without going overboard.
使用任何分析數據并返回建議的腳本，都存在構建黑匣子的威脅，以至于無法確定黑匣子的工作方式或原因。 這樣的腳本也容易出錯，并且在各種不可預見的情況下更容易中斷。 我們努力的目標是在不過度投入的情況下深入了解索引指標。
By persisting index data, we can generate very accurate data over time without the need for guesswork. In addition, we avoid the potential pitfall that arises if recommendations are given with too little historical data to build on. On the whole, the collection process requires 4 tables, 2 stored procedures, 2 views, and 2 jobs. The recommendation process requires a single stored procedure, table, and a calling job. The intention of this is to ensure that each object is short and sweet so that no component of this process becomes excessively complex.
通過持久保留索引數據，我們可以隨時間生成非常準確的數據，而無需進行猜測。 此外，如果推薦的歷史數據太少，我們將避免潛在的陷阱。 總體而言，收集過程需要4個表，2個存儲過程，2個視圖和2個作業。 推薦過程需要單個存儲過程，表和調用作業。 這樣做的目的是確保每個對象簡短而甜美，以使該過程的任何組成部分都不會變得過于復雜。
If it helps, you may put all objects in this process into their own schema, segregating them from other objects and making them easy to find and work with. I used IndexMetrics for the demos here, but any can be used.
如果有幫助，您可以將此過程中的所有對象放入它們自己的模式中，將它們與其他對象隔離開來，使它們易于查找和使用。 我在這里的演示中使用了IndexMetrics ，但是任何都可以使用。
I am a huge proponent of understanding how SQL Server works and using that knowledge to make great decisions. At the same time, an attempt has been made here to keep things as simple as possible. Feel free to download, test, and use the attached scripts to make smart decisions. Once understood, these scripts can be built on and made bigger, badder, and more custom-tailored to your needs.
我強烈理解SQL Server的工作原理，并利用這些知識來做出明智的決定。 同時，這里嘗試使事情盡可能簡單。 隨時下載，測試和使用附帶的腳本來做出明智的決定。 一旦理解，就可以構建這些腳本，并使它們變得更大，更糟糕，并根據您的需求進行定制化。
With that in mind, please enjoy 🙂
考慮到這一點，請享受🙂
Attached to this article are copies of the scripts needed to implement index usage stats tracking, missing index stats tracking, and the reporting proc discussed here. Some enhancements have been made to the underlying processes, so the new versions should be used with preference over any older ones.
本文的附件是實現索引使用情況統計跟蹤，缺少索引統計跟蹤以及此處討論的報告過程所需的腳本副本 。 已對基礎流程進行了一些增強，因此應優先使用新版本，而不是任何舊版本。
All objects reside in the IndexMetrics schema now and have been given the ability to aggregate data across all databases, ignoring database name. This is useful in environments with many databases that all share a similar design and usage patterns.
現在，所有對象都位于IndexMetrics模式中，并且可以忽略數據庫名稱而跨所有數據庫聚合數據。 這在具有許多共享相同設計和使用模式的數據庫的環境中很有用。
Smart indexing decisions require us to collect and maintain indexing metrics. Without weeks, months (or more) of data to work with, we run the risk of making short-sighted decisions that could hamstring performance in the future.
明智的索引決策要求我們收集和維護索引指標。 如果沒有數周，數月（或更多）的數據需要處理，我們就有可能做出近視的決策，從而可能會阻礙未來的績效。
Once collected and aggregated over time, we can run queries or processes against this data in order to return a set of indexes that warrant further research. Once investigated, we can generate a list of changes that make sense and are supported by our historical data.
一旦收集并隨時間匯總，我們就可以針對這些數據運行查詢或流程，以返回一組值得進一步研究的索引。 一旦進行了調查，我們可以生成有意義的更改列表，并得到我們的歷史數據的支持。
A process like this allows us to make smarter decisions and to track those decisions over time. If a new SQL Server index is needed, we can catch that need before it becomes a significant problem. If we remove an index, we can track performance in the wake of that decision and determine if that decision had any unintended side-effects. The result is a process that provides positive and useful feedback unto itself. Long-term, we can monitor all indexing changes, their effect, and new recommendations to determine what an application’s natural response is to our changes and be able to make smart decisions immediately without waiting for any long term repercussions to manifest.
這樣的過程使我們可以做出更明智的決策，并隨著時間的推移跟蹤這些決策。 如果需要新SQL Server索引，我們可以在它成為嚴重問題之前就抓住它。 如果刪除索引，我們可以跟蹤該決策之后的性能，并確定該決策是否有任何意外的副作用。 結果是一個過程，可以為其自身提供積極而有用的反饋。 長期而言，我們可以監視所有索引更改，它們的影響以及新的建議，以確定應用程序對我們的更改的自然響應，并能夠立即做出明智的決策，而無需等待任何長期影響。
Previous articles in this series:
本系列以前的文章：
翻譯自: https://www.sqlshack.com/sql-server-reporting-sql-server-index-utilization/
索引sql server
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
![[译]编写高性能对垃圾收集友好的代码](/upload/rand_pic/2-481.jpg)
