Z optymalizacja zapytań jest pewien problem… programista wykonuje testy na swoich małych zestawach przykładowych danych, specjalnie nie przykładając się do optymalizacji. Ma działać – to jest jego cel. Wydajność jest bolesna dla użytkownika i administratora, z którym w pierwszej kolejności kontaktuje się użytkownik, gdy mu coś nie działa. Niestety administrator nie ma wpływu na treść zapytań. Jedyne co możne zrobić to zaproponować pewne ulepszenia w zapytaniach programiście…
Od wersji 2016 administrator i programista dostają do dyspozycji nowe narzędzie – query store.
Z założenia jest to mechanizm, który śledzi zapytania wykonywane na systemie, bada ich wydajność (konsumpcje CPU, pamięci, IO) i przechowuje na temat zapytania historyczne informacje pozwalające śledzić co działo się z zapytaniem przez pewien czas. Znajdziemy tu statystyczne informacje o czasie wykonania zapytań wraz z wykorzystywanym w danej chwili planem zapytania.
Mechanizm domyślnie jest wyłączony i trzeba go włączyć. Mozna to zrobić we właściwościach bazy danych, jak na ilustracji powyżej, ale oczywiście można tez TSQL:
ALTER DATABASE [AdventureWorks] SET QUERY_STORE = ON;
Mozna tu określić jak długo historia ma być przechowywana (np 31 dni) oraz co zrobić, gdy query store się przepełni (domyślnie stare informacje będą zastępowane nowymi).
Po odświeżeniu drzewka obiektów zobaczysz folder Query Store, a w nim kilka przykładowych raportów. Tutaj został wyświetlony raport „Regressed queries”, który prezentuje najcięższe zapytania (wykres po lewej), historyczne dane statystyczne dla wybranego zapytania (wykres po prawej) i zastosowany wtedy plan zapytania (na dole).
I teraz dość straszna sprawa. Po wybraniu zapytania po lewej stronie, zobaczysz porozrzucane kropeczki na wykresie po prawej. Klikając na owych kropeczkach, możesz zapoznawać się z tym, jaki plan zapytania był stosowany za każdym razem. Zobaczysz też ile czasu trwało średnio wykonanie zapytania z wykorzystaniem tego planu, jaki by ł minimalny i maksymalny czas trwania zapytania itp. Oczywiście wolelibyśmy, żeby czas był jak najkrótszy (zazwyczaj), więc szczególnie interesujące będą punkty położone najniżej. Kiedy już zidentyfikujemy plan zapytania, który jest naszym zdaniem najlepszy wystarczy kliknąć „Force plan”. Gotowe. Optymalizacja przez klikanie…
Kilka przydatnych widoków:
- metadane o query store – sys.database_query_store_options
- wykaz zapytań – sys.query_store_query
- wykaz planów – sys.query_store_plan
- tekst zapytań – sys.query_store_query_text
Wiecej na ten temat: https://msdn.microsoft.com/en-us/library/mt614796.aspx
