SQL-Ex blog

В большинстве случаев оптимизатор запросов заботится о создании и обновлении статистики распределения. SQL Server имеет три варианта установки поведения статистики. Рассмотрим вкратце все три.

Правый щелчок на базе данных открывает свойства базы данных.

• Auto Create Statistics: Автоматическое создание статистики. Принимается по умолчанию (на картинке Auto Create Statistics = True). Объекты статистики создаются на столбце, требуемом в предикате запроса. Вся созданная статистика, использующая эту опцию, имеет префикс _WA. SQL Server также генерирует статистику для объекта, как только вы создаете индекс или ключ, например, первичный ключ.
  
  

  Select
  
  top 10
  
      object_name(s.object_id) as table_name,
  
      s.name as stat_name,
  
      s.is_temporary,
  
      ds.last_updated,
  
      ds.modification_counter,
  
      ds.rows,
  
      ds.rows_sampled,
  
         ds.steps
  
  from sys.stats as s (nolock)
  
  cross apply sys.dm_db_stats_properties(s.object_id, s.stats_id) as ds
  
  WHERE s.name like '_WA%'  and object_name(s.object_id) not like '%sys%'
  
  ORDER BY s.name;

  
  
  


• Auto Update Statistics (автоматическое обновление статистики): Оптимизатор выполняет внутренние вычисления на базе нескольких операций вставки, обновления, удаления, и автоматически обновляет статистику, если Auto Update Statistics = true.


• Auto Update Statistics Asynchronously (асинхронное автоматическое обновление статистики): Если эта опция включена (по умолчанию отключена), SQL Server проверяет наличие синхронных или асинхронных обновлений статистики.

Поведение статистики в группе доступности Always On

Группы доступности SQL Server Always On конфигурируют первичную и вторичную реплики для обеспечения высокой надежности и решения для аварийного восстановления. Начиная с SQL Server 2016 мы можем выполнять маршрутизацию нагрузки только на чтение (Read-Only Routing) на вторичную реплику. Мы можем выполнять транзакции чтения-записи только в первичной реплике. Более подробно об этом можно прочитать в следующей статье.

В группах доступности SQL Server создает и поддерживает статистику в первичной реплике баз данных. Эта статистика из первичной реплики посылается во вторичную реплику, подобно другим записям журнала транзакций. Если вы используете вторичную реплику для отчетов, и все запросы для отчетов получают данные из неё, это может потребовать другой статистики, отличной от статистики, которая реплицируется из первичной реплики. Оптимизатор запросов не может создавать статистику на вторичной реплике, поскольку база данных находится в режиме "только на чтение".

Тестовая среда:

• Реплика в синхронном режиме с двумя узлами SQL Always On

Демо для статистики в группах доступности Always On

Давайте создадим таблицу в первичной реплике:

CREATE TABLE tbltest 

  ( 

     id      INT IDENTITY PRIMARY KEY, 

     NAME    NVARCHAR(100), 

     country NVARCHAR(100) 

  )

В таблице пока нет никаких данных. Проверим статистику на обеих, первичной и вторичной, репликах с помощью следующего запроса:

SELECT Object_name(s.object_id) AS table_name, 

       s.NAME                   AS stat_name, 

       s.is_temporary, 

       ds.last_updated, 

       ds.modification_counter, 

       ds.rows, 

       ds.rows_sampled 

FROM   sys.stats AS s (nolock) 

       CROSS apply sys.Dm_db_stats_properties(s.object_id, s.stats_id) AS ds 

WHERE  Object_name(s.object_id) = 'tbltest'

• Вывод статистики первичной реплики SQL Server:
  
  


• Вывод статистики вторичной реплики SQL Server:
  
  

Как объяснялось выше, SQL Servcer автоматически создает статистику в первичной реплике по запросу оптимизатора. Давайте вставим несколько записей в таблицу.

DECLARE @Id INT 

SET @Id = 1 

WHILE @Id <= 10000 

  BEGIN 

      INSERT INTO tbltest 

      VALUES      ('Rajendra - ' + Cast(@Id AS NVARCHAR(20)), 

                   'SQLShack - ' + Cast(@Id AS NVARCHAR(10)) 

                   + 'Article') 

 

      SET @Id = @Id + 1 

  END

Выполним оператор SELECT для создания статистики:

SELECT remarks 

FROM   [adventureworks2014].[dbo].[tbltest] 

WHERE  remarks BETWEEN 'SQLShack - 5674Article' AND 'SQLShack - 9994Article'

Теперь выполним запрос, чтобы проверить статистику SQL Server на обеих репликах. Мы можем увидеть новую статистику под именем, начинающегося с _WA_sys. Первичная реплика реплицирует эту статистику на вторичную реплику, и вы можете видеть одну и ту же статистику.

Вывод первичной реплики:



Вывод вторичной реплики:

Статистика SQL Server в реплике на чтение Always On

Представим, что вы используете вторичную реплику для рабочей нагрузки только на чтение. Если мы не выполняем эти запросы на первичной реплике, она не может создавать новую статистику в первичной реплике и реплицировать её на вторичную реплику.

Давайте посмотрим, как это работает на вторичной реплике на чтение. Выполните команду в базе данных вторичной реплики:

SELECT name 

FROM   [adventureworks2014].[dbo].[tbltest] 

WHERE  name BETWEEN 'SQLShack - 5674Article' AND 'SQLShack - 9994Article'

Теперь снова проверим статистику на обеих репликах, и посмотрим разницу.

• Первичная реплика - нет изменения в статистике SQL Server:
  
  
  


• Вторичная реплика - добавлена новая статистика (временно) в SQL Server:
  
  
  

Мы можем увидеть новую статистику _WA_Sys_00000002_405A880E_readonly_database_statistics для таблицы tbltest. Посмотрите внимательно эту строку; там установлен флажок is_temporary в значение 1 для этой статистики. Во вторичной реплике оптимизатор запросов создает временную статистику в базе данных tempdb. Это добавляет суффикс ‘_readonly_database_statistics’ для таких временных статистик. Здесь нужно заметить, что оптимизатор запросов сам создает статистику в базе данных в первичной реплике, и она только реплицируется во вторичную базу данных. В этом случае статистика не может генерироваться во вторичной базе только на чтение, поэтому используется tempdb для создания временной статистики. Оптимизатор запросов достаточно умен, чтобы использовать эту статистику из tempdb и оптимизировать рабочую нагрузку. SQL Server Always On работает в направлении от первичной к вторичной реплике, поэтому эта временная статистика не может перемещаться в первичную реплику. Если перезапустить вторичную реплику по каким-либо причинам, эта временная статистика пропадает. Она занимает 8 Кбайт (1 страница) в tempdb и не зависит от размера таблицы.

Мы можем использовать команду DBCC Show_Statistics, чтобы проверить статистику конкретного столбца. В следующем примере мы хотим проверить статистику столбца name в таблице tbltest:

DBCC show_statistics('tbltest', 'name')

Мы можем видеть временную статистику для этого столбца, а также его диапазон и плотность.

Устаревание статистики в группах доступности Always On

Рассмотрим сценарий, в котором вы не выполняете никаких запросов в первичной реплике. Это не создает и не обновляет никакой статистики в первичной реплике из-за отсутствия активности в первичной реплике. В то же время мы выполняем работу во вторичной реплике, и она создает, обновляет статистику согласно требованиям.

Здесь мы имеем три статистики для вторичной реплики:

• Статистика первичного ключа


• Автоматическая статистика _WA


• Временная статистика

Вставим больше записей в первичную реплику. Это сделает статистику устаревшей для первичной реплики. Теперь выполним следующий запрос на выборку на вторичной реплике для получения записей при использованием первичного ключа в условии WHERE.

SELECT remarks 

FROM   [adventureworks2014].[dbo].[tbltest] 

WHERE  id BETWEEN 2000 AND 3000000 

Проверьте статистику на вторичной реплике, и вы сможете увидеть странное поведение. На следующем скриншоте видно, что отображается статистика первичного ключа, а также временная статистика.



Это показывает, что SQL Server создал временную статистику для него. Т.к. мы не выполнили оператор SELECT на первичной реплике, оптимизатор запросов не обновляет статистику на первичной реплике.

Это подразумевает, что SQL Server создал временную статистику. Это не привело к потере постоянной статистики в базе данных вторичной реплики. Временная статистика по-прежнему доступна в tempdb, но оптимизатор запросов умный и знает, что временная статистика более свежая, чем постоянная статистика. Он использует её для подготовки плана выполнения и получения данных.

Если мы посмотрим план выполнения оператора SELECT, то увидим, что оптимизатор запросов использует статистику PK__tbltest__3214EC07B0E08F59 для выполнения этого запроса.



Давайте вручную обновим статистику на первичной реплике, используя команду Update Statistics:

Update Statistics tbltest

Это обновит статистику на первичной реплике и реплицирует её на вторичную реплику. Проверьте статус статистики на вторичной реплике.

У нас нет временной статистики для первичного ключа.



Мы по-прежнему имеем временную статистику для вторичной реплики с суффиксом _readonly_secondary_status. Как вы теперь знаете, временная статистика создается в tempdb, следовательно, если вы перезапустите службу SQL Server на вторичной реплике, будет создана новая копия tempdb, и вся временная статистика пропадет.

Обзор поведения статистики SQL Server в базах данных групп доступности Always On

Вот сводка действий.

Заключение

В этой статье исследовалось поведение статистики SQL Server в группах доступности Always On. Это очень важный аспект для настройки производительности. Вы можете оставлять вопросы и замечания в комментариях к статье (оригинала).