Создание скрипта статистики для создания базы данных, доступной только для статистики, в SQL Server

  • Статья

Из этой статьи вы узнаете, как создать скрипт статистики с помощью метаданных базы данных для создания базы данных, доступной только для статистики, в SQL Server.

Исходная версия продукта: SQL Server 2014 г., SQL Server 2012 г., SQL Server 2008 г.

Исходный номер базы знаний: 914288

Введение

DBCC CLONEDATABASE является предпочтительным методом для создания клона базы данных только схемы для исследования проблем с производительностью. Используйте процедуру, описанную в этой статье, только если вы не можете использовать DBCC CLONEDATABASE.

Оптимизатор запросов в Microsoft SQL Server использует следующие типы сведений для определения оптимального плана запросов:

  • метаданные базы данных
  • аппаратная среда
  • состояние сеанса базы данных

Как правило, для воспроизведения поведения оптимизатора запросов в тестовой системе необходимо имитировать все эти же типы информации.

Служба поддержки клиентов Майкрософт может попросить вас создать скрипт метаданных базы данных для изучения проблемы с оптимизатором запросов. В этой статье описаны шаги по созданию скрипта статистики, а также описано, как оптимизатор запросов использует эти сведения.

Примечание.

Ключи, сохраненные в этих данных, могут содержать сведения о персональных данных. Например, если таблица содержит столбец номер телефона со статистикой, значение с высоким ключом каждого шага будет находиться в созданном скрипте статистики.

Создание скриптов для всей базы данных

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

  • Вы избегаете проблем с отсутствующими зависимыми объектами, которые необходимы для воспроизведения проблемы.
  • Для выбора необходимых объектов требуется меньше действий.

Обратите внимание, что если вы создаете скрипт для базы данных, а метаданные для базы данных содержат тысячи объектов, процесс создания скриптов потребляет значительные ресурсы ЦП. Рекомендуется создавать скрипт в нерабочие часы или можно использовать второй параметр Скрипт отдельных объектов для создания скрипта для отдельных объектов.

Чтобы создать скрипт для каждой базы данных, на которую ссылается запрос, выполните следующие действия.

  1. Откройте SQL Server Management Studio.

  2. В обозреватель объектов разверните узел Базы данных, а затем найдите базу данных, для которой требуется создать скрипт.

  3. Щелкните правой кнопкой мыши базу данных, наведите указатель на пункт Задачи, а затем выберите Создать скрипты.

  4. В мастере сценариев убедитесь, что выбрана правильная база данных. Щелкните , чтобы выбрать скрипт всей базы данных и все объекты базы данных, а затем нажмите кнопку Далее.

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

    Параметр "Скрипты" Выбранное значение
    Ansi padding Верно
    Продолжить создание скриптов при ошибке Верно
    Создание скрипта для зависимых объектов Верно
    Включение имен системных ограничений Верно
    Параметры сортировки скриптов Верно
    Имена входа в скрипты Верно
    Разрешения уровня объекта скрипта Верно
    Статистика скрипта Статистика скриптов и гистограммы
    Индексы скриптов Верно
    Триггеры скрипта Верно

    Примечание.

    Обратите внимание, что параметр Script Logins и script object level Permissions (Разрешения уровня объекта скрипта ) могут не потребоваться, если схема не содержит объектов, принадлежащих именам входа, кроме dbo.

  6. Нажмите кнопку ОК , чтобы сохранить изменения, и закройте страницу Дополнительные параметры сценариев .

  7. Выберите Сохранить в файл и выберите параметр Один файл .

  8. Просмотрите выбранные параметры и нажмите кнопку Далее.

  9. Нажмите Готово.

Создание скриптов для отдельных объектов

Можно создать скрипт только для отдельных объектов, на которые ссылается конкретный запрос, а не для полной базы данных. Однако если все объекты базы данных не были созданы с помощью WITH SCHEMABINDING предложения, сведения о зависимостях в системной sys.depends таблице могут быть не всегда точными. Эта неточность может привести к одной из следующих проблем:

  • Процесс создания скриптов не содержит скрипта для зависимого объекта.

  • В процессе создания скриптов объекты могут быть написаны в неправильном порядке. Для успешного выполнения скрипта необходимо вручную изменить созданный скрипт.

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

  1. В SQL Server Management Studio разверните узел Базы данных, а затем найдите базу данных, для которой требуется создать скрипт.

  2. Щелкните правой кнопкой мыши базу данных, выберите пункт Создать базу данных, а затем выберите Файл.

  3. Введите имя файла и нажмите кнопку Сохранить.

    Контейнер основной базы данных будет написан в скрипте. Этот контейнер включает файлы, группы файлов, базу данных и свойства.

  4. Щелкните правой кнопкой мыши базу данных, наведите указатель на пункт Задачи, а затем выберите Создать скрипты.

  5. Убедитесь, что выбрана правильная база данных, а затем нажмите кнопку Далее.

  6. В диалоговом окне Выбор типов объектов выберите Выбрать определенные объекты базы данных и выберите все типы объектов базы данных, на которые ссылается проблемный запрос.

    Например, если запрос ссылается только на таблицы, выберите Таблицы. Если запрос ссылается на представление, выберите Представления и таблицы. Если проблемный запрос использует определяемую пользователем функцию, выберите Функции.

  7. Выбрав все типы объектов, на которые ссылается запрос, нажмите кнопку Далее.

  8. В диалоговом окне Задание параметров сценариев нажмите кнопку Дополнительно и измените следующие параметры со значения по умолчанию на значение, указанное в следующей таблице на странице Дополнительные параметры скрипта .

    Параметр "Скрипты" Выбранное значение
    Ansi Padding Верно
    Продолжить создание скриптов при ошибке Верно
    Включение имен системных ограничений Верно
    Создание скрипта для зависимых объектов Верно
    Параметры сортировки скриптов Верно
    Имена входа в скрипты Верно
    Разрешения уровня объекта скрипта Верно
    Статистика скрипта Статистика скриптов и гистограммы
    Скрипт USE DATABASE Верно
    Индексы скриптов Верно
    Триггеры скрипта Верно

    Примечание.

    Обратите внимание, что параметры Входа в скрипт и Разрешения уровня объектов скрипта могут не потребоваться, если схема не содержит объектов, принадлежащих именам входа, кроме dbo.

  9. Нажмите кнопку ОК , чтобы сохранить и закрыть страницу Дополнительные параметры сценариев .

    Откроется диалоговое окно для каждого типа объекта базы данных, выбранного на шаге 7.

  10. В каждом диалоговом окне выберите определенные таблицы, представления, функции или другие объекты базы данных, а затем нажмите кнопку Далее.

  11. Выберите параметр Скрипт в файл , а затем укажите то же имя файла, которое было введено на шаге 3.

  12. Нажмите кнопку Готово , чтобы запустить скрипт.

    После завершения скрипта отправьте файл скрипта инженеру служба поддержки Майкрософт. Инженер служба поддержки Майкрософт также может запросить следующие сведения:

    • Конфигурация оборудования, включая количество процессоров и объем физической памяти.

    • Параметры SET, которые были активны при выполнении запроса.

    Обратите внимание, что вы могли уже предоставить эти сведения, отправив отчет SQLDiag или трассировку SQL Profiler. Возможно, вы также использовали другой метод для предоставления этих сведений.

Как используется информация

В следующих таблицах объясняется, как оптимизатор запросов использует эти сведения для выбора плана запроса.

Метаданные

Вариант Объяснение
Ограничения Оптимизатор запросов часто использует ограничения для обнаружения противоречий между запросом и базовой схемой. Например, если запрос содержит WHERE col = 5 предложение и в базовой CHECK (col < 5) таблице существует ограничение, оптимизатор запросов знает, что строки не будут совпадать. Оптимизатор запросов делает аналогичные типы вычетов о допустимости значений NULL. Например, известно, WHERE col IS NULL что предложение имеет значение true или false в зависимости от допустимости значений NULL столбца и от того, является ли столбец из внешней таблицы внешнего соединения. Наличие ограничений FOREIGN KEY полезно для определения кратности и соответствующего порядка соединения. Оптимизатор запросов может использовать сведения о ограничениях для устранения соединений или упрощения предикатов. Эти изменения могут удалить требование доступа к базовым таблицам.
Статистика Статистические сведения содержат плотность и гистограмму, показывающую распределение ведущего столбца индекса и ключа статистики. В зависимости от характера предиката оптимизатор запросов может использовать плотность, гистограмму или и то, и другое для оценки кратности предиката. Для точных оценок кратности требуется актуальная статистика. Оценки кратности используются в качестве входных данных при оценке стоимости оператора. Поэтому для получения оптимальных планов запросов необходимо иметь хорошие оценки кратности.
Размер таблицы (количество строк и страниц) Оптимизатор запросов использует гистограммы и плотность для вычисления вероятности того, что заданный предикат имеет значение true или false. Окончательная оценка кратности вычисляется путем умножения вероятности на количество строк, возвращаемых дочерним оператором. Количество страниц в таблице или индексе является фактором при оценке стоимости операций ввода-вывода. Размер таблицы используется для расчета стоимости сканирования, и он полезен при оценке количества страниц, к которым будет обращаться во время поиска по индексу.
Параметры базы данных Несколько параметров базы данных могут повлиять на оптимизацию. Параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS влияют на то, будет ли оптимизатор запросов создавать новую статистику или обновлять устаревшие статистические данные. Уровень параметризации влияет на параметризацию входного запроса до передачи входного запроса оптимизатору запросов. Параметризация может повлиять на оценку кратности, а также предотвратить сопоставление с индексированных представлений и других типов оптимизаций. Этот DATE_CORRELATION_OPTIMIZATION параметр заставляет оптимизатора искать корреляции между столбцами. Этот параметр влияет на кратность и оценку затрат.

Среда

Вариант Объяснение
Параметры сеанса SET Этот ANSI_NULLS параметр влияет на то, принимает ли NULL = NULL выражение значение true. Оценка кратности для внешних соединений может изменяться в зависимости от текущего параметра. Кроме того, могут измениться неоднозначные выражения. Например, col = NULL выражение оценивается по-разному в зависимости от параметра. Однако col IS NULL выражение всегда оценивается одинаково.
Аппаратные ресурсы Стоимость операций сортировки и хэш-операторов зависит от относительного объема памяти, доступного SQL Server. Например, если размер данных больше кэша, оптимизатор запросов знает, что данные всегда должны быть вложены на диск. Однако если размер данных гораздо меньше кэша, операция, скорее всего, будет выполнена в памяти. SQL Server также учитывает различные оптимизации, если сервер имеет несколько процессоров и параллелизм не был отключен с помощью MAXDOP указания или параметра конфигурации max degree of parallelism.

См. также