Jakie są korzyści z włączenia Query Store na msdb?

Z bazy danych systemu SQL (master, model, msdb, tempdb) magazyn zapytań może być używany tylko na msdb. Szukałem i nie znalazłem żadnej dokumentacji dotyczącej magazynu zapytań na msdb.

Chociaż nie widać tego w interfejsie GUI, można to sprawdzić w instancji SQL 2016

Sprawdzanie poprawności sklepu z zapytaniami jest wyłączone

USE msdb
SELECT * FROM sys.database_query_store_options; 

Włącz Query Store

USE [master]
GO
ALTER DATABASE msdb SET QUERY_STORE = ON
GO
ALTER DATABASE msdb SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO)
GO

Sprawdzanie poprawności magazynu zapytań jest włączone

USE msdb
SELECT * FROM sys.database_query_store_options; 

Spośród całej systemowej bazy danych dlaczego msdb jest jedyną z opcją korzystania z Query Store i jaką wartość dodaje?

-- Stop Query Store
USE [master]
GO
ALTER DATABASE msdb SET QUERY_STORE = OFF
GO

Włączenie funkcji przez Microsoft nie oznacza, że ​​będzie ona przydatna dla wszystkich. W przypadku systemów korzystających z niektórych funkcji może oznaczać poleganie na informacjach przechowywanych w MSDB. W takich przypadkach sklep z zapytaniami może być przydatny.

Oto kilka artykułów na temat używania i dostrajania obiektów bazy danych MSDB.

msdb Baza danych z książek online.

MSDB Performance Tuning autorstwa Geoffa N. Hitena

Znaczenie konserwacji na MSDB przez Tim Radney, gdzie wspomniał:

Optymalizacja indeksów w msdb jest tak samo ważna jak bazy danych użytkowników. Wiele razy znalazłem klientów, którzy optymalizują bazy danych użytkowników, ale nie baz danych systemu. Ponieważ baza danych msdb jest intensywnie używana przez agenta SQL Server, wysyłanie dziennika, brokera usług, SSIS, tworzenie kopii zapasowych i przywracanie oraz inne procesy, indeksy mogą ulec znacznej fragmentacji. Upewnij się, że zadania optymalizacji indeksu obejmują również systemowe bazy danych lub przynajmniej msdb. Widziałem, jak optymalizacja indeksów uwalnia kilka gigabajtów miejsca od wysoce pofragmentowanych indeksów w msdb.

Widzę, w jaki sposób magazyn zapytań może pomóc w optymalizacji strategii indeksowania i optymalnym wyszukiwaniu / gromadzeniu / usuwaniu niektórych informacji przechowywanych w MSDB.

@SqlWorldWide odpowiedział na [msdb]część pytania „dlaczego ”, więc nie powielę tego tutaj. Ale odpowiedzi „dlaczego nie [master], [model], [tempdb]” część pytania:

• [tempdb]jest tymczasowym magazynowaniem i ze swej natury wydaje się, że nigdy nie skorzystałby ani z automatycznej optymalizacji, ani z możliwości przeprowadzenia analizy historycznej. Jeśli Query Store śledzi statystyki wykonania procedur przechowywanych, nie pomoże to tutaj, gdy procedury przechowywane są gdzie indziej. I chociaż możliwe jest tworzenie tymczasowych procedur przechowywanych, lokalne tymczasowe procedury przechowywane prawdopodobnie nie skorzystałyby na tym, biorąc pod uwagę, że ich nazwa zawiera unikalny kod skrótu do oddzielania podobnych nazw w wielu sesjach. I chociaż globalne tymczasowe przechowywane procesy mają jednolitą nazwę między sesjami, biorąc pod uwagę tymczasowy charakter, nie ma sposobu, aby założyć, że globalne tymczasowe przechowywane procesy o tej samej nazwie między sesjami (zakładając, że nie w tym samym czasie) będą miały nawet ten sam kod, i dlatego nie może mieć znaczącego /korelowalne statystyki.

• [model]jest szablonem do tworzenia nowych baz danych (w tym [tempdb], które są tworzone ponownie przy każdym uruchomieniu / ponownym uruchomieniu instancji SQL Server). Zapytania nie są wykonywane z tego miejsca. Jednak przypuszczam, że sensowne może być włączenie tutaj Magazynu zapytań, aby domyślnie był włączony podczas tworzenia nowych baz danych. Ale jednak na to jednak, że oznaczałoby Zapytanie Store będzie włączony [tempdb], a to jest po prostu głupie (patrz punkt bezpośrednio powyżej).

  AKTUALIZACJA:
  Woah, Nelly! Właśnie przeczytałem wstępne pytanie, które doprowadziło do tego pytania , i zauważyłem coś dziwnego: pojawił się tylko komunikat o błędzie dla [master]i [tempdb]; nie zgłoszono błędu [model]. Możliwe, że OP po prostu pominął ten komunikat o błędzie podczas kopiowania do pytania, więc uruchomiłem następujące polecenie na SQL Server 2016 SP1-CU7-GDR (13.0.4466.4), aby zobaczyć na własne oczy:

  ALTER DATABASE [model] SET QUERY_STORE = ON; -- completes successfully!
  
  -- Restart instance to force recreation of [tempdb];
  
  CREATE DATABASE [IsQueryStoreEnabledByDefault];
  
  SELECT * FROM sys.databases WHERE [is_query_store_on] = 1;
  
  DROP DATABASE [IsQueryStoreEnabledByDefault];

  A wyniki? [model]i [IsQueryStoreEnabledByDefault]są zwracane, ale nie[tempdb] ma ich w wynikach! Tak, dodatkowy jednak do dwóch pierwszych „Jednakże” s, wydaje się, że [model] mogą mieć włączony Zapytanie Store, który a) niedokonanie Query zapisanego optymalizacyjne (tak, to słowo, nawet sprawdzone ;-) na nowo tworzone DB, oraz b) jest ignorowany przy odtwarzaniu [tempdb]po rozpoczęciu usługi (stąd nie jest to tylna furtka do włączenia [tempdb]).  

• [master]jest główną bazą danych systemu i nie powinieneś mieć uruchomionego kodu. Również Procedury przechowywane, które istnieją tutaj i są często używane, nie skorzystałyby z optymalizacji lub zostałyby wykonane w kontekście bazy danych użytkowników, gdzie są wywoływane (tzn. Procedury przechowywane w systemie zaczynające się od sp_są szczególnym przypadkiem, w którym „pojawiają się” we wszystkich Bazy danych - nie muszą być w pełni kwalifikowane [master]..- i wykonywać tak, jakby faktycznie istniały w każdej bazie danych) i prawdopodobnie są zarządzane przez magazyn zapytań w bazie danych użytkowników, w której są wywoływane.