Znajdowanie i dostrajania podobnych kwerend za pomocą kwerendy i kwerendy Plan mieszania
Podczas wyszukiwania zasób-intensywnego kwerend, należy rozważyć jak znaleźć i strojenia podobnych kwerend, które wspólnie zużywać znaczne systemu zasóbs.Sys.dm_exec_query_stats i sys.dm_exec_requests widoki dynamiczne zarządzanie zapewniają mieszania kwerendy i wartości mieszania, że plan kwerend można używać określić obciążenie zasób wartość zagregowana dla podobnych kwerend i planów wykonywanie podobnych kwerend.
W tym temacie definiuje mieszanie kwerend i planu kwerend, wartość mieszania zawiera przykłady zastosowania wartości mieszania, aby znaleźć koszt skumulowany podobnych kwerend i wykonanie planów i sposoby zwiększenia wydajności dla podobnych kwerend i wykonanie planów.
Opis mieszania kwerendy i mieszania planu kwerend
Wartość mieszania kwerendy jest wartość mieszania binarne obliczany na tej kwerendzie i służy do identyfikowania kwerend w logice podobne.optymalizator kwerendy oblicza wartość mieszania kwerendy podczas kompilacji kwerendy.Kwerendy, które różnią się tylko wartości literału hasz tej samej kwerendy.Na przykład następujące dwie kwerendy hasz tej samej kwerendy, ponieważ różnią się one tylko wartości literału, przypisane do FirstName i LastName.
USE AdventureWorks2008R2;
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = 'Amanda' AND P.LastName = 'Allen';
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = 'Logan' AND P.LastName = 'Jones';
GO
--Show the query hash for both queries.
SELECT st.text AS "Query Text", qs.query_hash AS "Query Hash"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
WHERE st.text = 'SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = ''Amanda'' AND P.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = ''Logan'' AND P.LastName = ''Jones'';
';
GO
Następujące dwie kwerendy mają mieszań innej kwerendy, ponieważ ich różnice są logiczne (I względem lub) i nie są ograniczone do tylko literałów.
USE AdventureWorks2008R2;
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = 'Amanda' AND P.LastName = 'Allen';
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = 'Logan' OR P.LastName = 'Jones';
GO
--Show the query hash for both queries.
SELECT st.text AS "Query Text", qs.query_hash AS "Query Hash"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
WHERE st.text = 'SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = ''Amanda'' AND P.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = ''Logan'' OR P.LastName = ''Jones'';
';
GO
Mieszania plan kwerend jest wartością mieszania binarne obliczone na plan wykonania kwerend i służy do identyfikowania planów wykonywanie podobnych kwerend.optymalizator kwerendy oblicza wartość mieszania planu kwerend podczas kompilacji kwerendy przy użyciu wartości planu wykonania, takie jak operator logicznych i fizycznych i podzbiór atrybutów ważne operator.Wykonanie planów kwerend, których samą strukturę drzewa fizycznej i logicznej operatora, jak również wartości atrybut identyczne podzbiór atrybut ważne operator ma taką samą wartość mieszania planu kwerend.
Gdy kwerend z mieszania identyczne kwerendy są wykonywane w różnych danych, różnice w Kardynalność wyniki kwerendy może spowodować optymalizator kwerendy o wybranie innej kwerendy wykonanie planów, powodując mieszań plan innej kwerendy.
Poniższy przykład pokazuje jak dwie podobne kwerendy mogą mieć taką samą wartość mieszania kwerendy, ale może nie mieć ten sam plan wykonania kwerend.Aby wyświetlić wartości mieszania na dwa sposoby: końcowe instrukcja SELECT i XML Showplan, gdzie są one wymienione w StmtSimple elementu jako wartości atrybut QueryHash i QueryPlanHash.
USE AdventureWorks2008R2;
GO
SET STATISTICS XML ON;
GO
SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 1;
GO
SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 3;
GO
SET STATISTICS XML OFF;
GO
--Show the query_hash and query plan hash
SELECT ST.text AS "Query Text", QS.query_hash AS "Query Hash",
QS.query_plan_hash AS "Query Plan Hash"
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text (QS.sql_handle) ST
WHERE ST.text = 'SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 1;
' OR ST.text = 'SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 3;
';
GO
Jeśli kardynalność szacunków dla ProductID = 3 są wysokie, optymalizator kwerendy może użyć operator skanowanie indeksu w planu kwerend.Jeśli kardynalność szacunków dla IDProduktu = 1 są niskie optymalizator kwerendy może używać indeksu wyszukiwania operator.
Unikatowooci wartości mieszania
Jest możliwość kolizji mieszania, w których nierównych kwerend i kwerendy planów mają taką samą wartość mieszania.Chociaż prawdopodobieństwo kolizji mieszania jest bardzo mała i mało prawdopodobne, aplikacje zależne od unikatowości kwerendy mieszania i mieszania może mieć błędów z powodu wartości mieszania duplikat planu kwerend.Na przykład kwerenda mieszania i mieszania planu kwerend należy traktować jako klucz podstawowy lub unikatowe kolumna.
Znajdowanie koszt skumulowany kwerend
Poniższy przykład zwraca informacje o górnej kwerend pięciu zgodnie z średni czas Procesora.W tym przykładzie agregatów kwerend zgodnie z ich mieszania kwerendę tak, aby równoznaczna kwerend są pogrupowane według ich zużycia zasób zbiorcza.
USE AdventureWorks2008R2;
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO
Poniższy przykład zwraca informacje o najwyższym planów kwerend pięciu zgodnie z średni czas Procesora.W tym przykładzie agregatów kwerend zgodnie z ich mieszania planu kwerend, tak kwerend z taką samą wartość mieszania planu kwerendy są pogrupowane według ich zużycia zasób zbiorcza.
USE AdventureWorks2008R2;
GO
SELECT TOP 5 query_plan_hash AS "Query Plan Hash",
SUM(total_worker_time)/SUM(execution_count) AS "Avg CPU Time",
MIN(CAST(query_plan as varchar(max))) AS "ShowPlan XML"
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_query_plan(QS.plan_handle)
GROUP BY query_plan_hash
ORDER BY 2 DESC;
GO
Za pomocą mieszania kwerendy i mieszania planu kwerend, aby poprawić wydajność kwerendy
Śledzenie i zbadaj wykonanie planu zmian
Degradations wydajności lub ulepszenia może wystąpić, jeśli kwerenda jest ponownie kompilowana i optymalizator kwerendy generuje plan wykonania innej kwerendy.Przy użyciu skrótu planu kwerend, można przechwycić, przechowywania i porównywanie planów wykonanie kwerendy czas.Wiedząc, których wykonanie planów zmieniły może pomóc w diagnozowanie wydajności wpływu zmian danych i konfiguracja.
Na przykład po zmianie konfiguracja systemu, można porównać wartości mieszania plan kwerend dla kwerend krytyczne pierwotne wartości mieszania planu kwerend.Różnice w wartości mieszania planu kwerendy można stwierdzić, możesz zmiany konfiguracja systemu spowodowało zaktualizowane kwerendy wykonanie planów kwerend ważne.Może również podjąć decyzję o zatrzymać wykonywanie dla bieżącego długo działającą kwerendę, jeśli jego kwerendy mieszania w sys.dm_exec_requests różni się od jego linii bazowej kwerendy plan mieszania, znanej mają dobrej wydajności.
Definiowanie parametrów kwerend podobne do ponownego buforowanego planu poprawy przez
Jeśli zestaw kwerend ma taką samą wartość mieszania kwerendy i mieszania planu kwerend, tworząc jeden sparametryzowanych kwerend może zwiększyć wydajność.Wywołanie jednej kwerendy z parametrami zamiast wielu kwerend za pomocą literałów umożliwia ponowne użycie plan wykonania kwerend buforowaną.Więcej informacji na temat korzyści wynikających z ponownego użycia planów kwerend buforowaną, zobacz Wykonanie planu buforowania i ponownego użycia.
Jeśli nie można zmodyfikować aplikację, umożliwia szablon plan guides z wymuszoną parametryzacja osiągnąć podobny wynik.Aby uzyskać więcej informacji, zobacz Określanie zachowania parametryzacji kwerendy przy użyciu prowadnic Plan.