MS SQL Server — Архитектура

Для удобства понимания мы классифицировали архитектуру SQL Server на следующие части:

• Общая архитектура
• Архитектура памяти
• Архитектура файла данных
• Архитектура файла журнала

Общая архитектура

Клиент — где запрос инициирован.

Запрос — SQL-запрос на языке высокого уровня.

Логические единицы — ключевые слова, выражения и операторы и т. Д.

N / W Packets — Код, связанный с сетью.

Протоколы — в SQL Server у нас есть 4 протокола.

• Общая память (для локальных подключений и устранения неполадок).

• Именованные каналы (для соединений, которые находятся в локальной сети).

• TCP / IP (для соединений, подключенных к глобальной сети).

• Адаптер виртуального интерфейса VIA (требуется специальное оборудование для настройки поставщиком, а также устаревший из версии SQL 2012).

Общая память (для локальных подключений и устранения неполадок).

Именованные каналы (для соединений, которые находятся в локальной сети).

TCP / IP (для соединений, подключенных к глобальной сети).

Адаптер виртуального интерфейса VIA (требуется специальное оборудование для настройки поставщиком, а также устаревший из версии SQL 2012).

Сервер — где установлены службы SQL и базы данных.

Relational Engine — это то место, где будет выполнено реальное выполнение. Он содержит анализатор запросов, оптимизатор запросов и исполнитель запросов.

Query Parser (Command Parser) и Compiler (Translator) — это проверит синтаксис запроса и преобразует запрос в машинный язык.

Оптимизатор запросов — он подготавливает план выполнения в качестве вывода, принимая запрос, статистику и дерево алгебры в качестве входных данных.

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

Query Executor — здесь запрос будет выполняться шаг за шагом с помощью плана выполнения, а также будет связываться с механизмом хранения.

Storage Engine — отвечает за хранение и извлечение данных в системе хранения (диск, SAN и т. Д.), Манипулирование данными, блокировку и управление транзакциями.

ОС SQL — это находится между хост-машиной (ОС Windows) и SQL Server. Все действия, выполняемые с ядром базы данных, выполняются операционной системой SQL. Операционная система SQL предоставляет различные сервисы операционной системы, такие как управление памятью, пул буферов, буфер журналов и обнаружение взаимоблокировок с использованием структуры блокировки и блокировки.

Процесс контрольной точки — контрольная точка — это внутренний процесс, который записывает все грязные страницы (измененные страницы) из буферного кэша на физический диск. Помимо этого, он также записывает записи журнала из буфера журнала в физический файл. Запись грязных страниц из буферного кэша в файл данных также называется усилением грязных страниц.

Это выделенный процесс, который автоматически запускается SQL Server через определенные промежутки времени. SQL Server запускает процесс проверки для каждой базы данных в отдельности. Контрольная точка помогает сократить время восстановления SQL Server в случае неожиданного завершения работы или сбоя системы \ сбоя.

Контрольные точки в SQL Server

В SQL Server 2012 есть четыре типа контрольных точек —

• Автоматически — это самая распространенная контрольная точка, которая выполняется как фоновый процесс, чтобы обеспечить возможность восстановления базы данных SQL Server в течение срока, определенного параметром «Интервал восстановления — Настройка сервера».

• Косвенный — это новый компонент в SQL Server 2012. Он также работает в фоновом режиме, но соответствует заданному пользователем целевому времени восстановления для конкретной базы данных, в которой был настроен параметр. Как только Target_Recovery_Time для данной базы данных будет выбран, это переопределит Интервал Восстановления, указанный для сервера, и исключит автоматическую контрольную точку в такой БД.

• Вручную — эта команда запускается так же, как и любая другая инструкция T-SQL, после того, как вы введете команду контрольной точки, она будет выполнена до конца. Ручная контрольная точка работает только для вашей текущей базы данных. Вы также можете указать Checkpoint_Duration, который является необязательным — эта продолжительность указывает время, в которое вы хотите, чтобы ваша контрольная точка завершилась.

• Внутренний — как пользователь, вы не можете контролировать внутреннюю контрольную точку. Выдается на конкретных операциях, таких как

  • Завершение работы инициирует операцию контрольной точки во всех базах данных, кроме случаев, когда выключение не является чистым (завершение работы с nowait).

  • Если модель восстановления изменяется с Full \ Bulk-logged на Simple.

  • Принимая резервную копию базы данных.

  • Если ваша БД находится в простой модели восстановления, процесс контрольной точки выполняется автоматически либо при заполнении журнала на 70%, либо на основе параметра сервера — Интервал восстановления.

  • Команда изменения базы данных для добавления или удаления файла данных \ журнала также запускает контрольную точку.

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

  • Создание снимка БД.

• Lazy Writer Process — Lazy Writer будет выгружать грязные страницы на диск по совершенно другой причине, потому что ему нужно освободить память в пуле буферов. Это происходит, когда SQL-сервер находится под давлением памяти. Насколько я знаю, это контролируется внутренним процессом, и нет никаких настроек для этого.

Автоматически — это самая распространенная контрольная точка, которая выполняется как фоновый процесс, чтобы обеспечить возможность восстановления базы данных SQL Server в течение срока, определенного параметром «Интервал восстановления — Настройка сервера».

Косвенный — это новый компонент в SQL Server 2012. Он также работает в фоновом режиме, но соответствует заданному пользователем целевому времени восстановления для конкретной базы данных, в которой был настроен параметр. Как только Target_Recovery_Time для данной базы данных будет выбран, это переопределит Интервал Восстановления, указанный для сервера, и исключит автоматическую контрольную точку в такой БД.

Вручную — эта команда запускается так же, как и любая другая инструкция T-SQL, после того, как вы введете команду контрольной точки, она будет выполнена до конца. Ручная контрольная точка работает только для вашей текущей базы данных. Вы также можете указать Checkpoint_Duration, который является необязательным — эта продолжительность указывает время, в которое вы хотите, чтобы ваша контрольная точка завершилась.

Внутренний — как пользователь, вы не можете контролировать внутреннюю контрольную точку. Выдается на конкретных операциях, таких как

Завершение работы инициирует операцию контрольной точки во всех базах данных, кроме случаев, когда выключение не является чистым (завершение работы с nowait).

Если модель восстановления изменяется с Full \ Bulk-logged на Simple.

Принимая резервную копию базы данных.

Если ваша БД находится в простой модели восстановления, процесс контрольной точки выполняется автоматически либо при заполнении журнала на 70%, либо на основе параметра сервера — Интервал восстановления.

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

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

Создание снимка БД.

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

SQL-сервер постоянно следит за использованием памяти, чтобы оценить конфликт ресурсов (или доступность); его задача заключается в том, чтобы всегда было определенное количество свободного места. Как часть этого процесса, когда он замечает любой такой конфликт ресурсов, он запускает Lazy Writer для освобождения некоторых страниц в памяти путем записи грязных страниц на диск. Он использует алгоритм Least недавно Used (LRU), чтобы решить, какие страницы должны быть сброшены на диск.

Если Lazy Writer всегда активен, это может указывать на узкое место в памяти.

Архитектура памяти

Ниже приведены некоторые характерные особенности архитектуры памяти.

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

• Память в окнах может быть вызвана с помощью виртуального адресного пространства, совместно используемого режимом ядра (режим ОС) и режимом пользователя (приложение, такое как SQL Server).

• SQL Server «Адресное пространство пользователя» разбит на две области: MemToLeave и Buffer Pool.

• Размер MemToLeave (MTL) и пула буферов (BPool) определяется SQL Server во время запуска.

• Управление буфером является ключевым компонентом в достижении высокой эффективности ввода / вывода. Компонент управления буфером состоит из двух механизмов: диспетчера буфера для доступа и обновления страниц базы данных и пула буферов для уменьшения количества операций ввода-вывода файла базы данных.

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

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

Одной из основных целей проектирования всего программного обеспечения для баз данных является минимизация дискового ввода-вывода, поскольку чтение и запись на диск являются одними из наиболее ресурсоемких операций.

Память в окнах может быть вызвана с помощью виртуального адресного пространства, совместно используемого режимом ядра (режим ОС) и режимом пользователя (приложение, такое как SQL Server).

SQL Server «Адресное пространство пользователя» разбит на две области: MemToLeave и Buffer Pool.

Размер MemToLeave (MTL) и пула буферов (BPool) определяется SQL Server во время запуска.

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

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

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

Другие части буферного пула включают в себя —

• Структуры данных системного уровня. Содержит данные уровня экземпляра SQL Server о базах данных и блокировках.

• Кэш журнала — зарезервирован для чтения и записи страниц журнала транзакций.

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

• Пространство стека — Windows выделяет пространство стека для каждого потока, запускаемого SQL Server.

Структуры данных системного уровня. Содержит данные уровня экземпляра SQL Server о базах данных и блокировках.

Кэш журнала — зарезервирован для чтения и записи страниц журнала транзакций.

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

Пространство стека — Windows выделяет пространство стека для каждого потока, запускаемого SQL Server.

Архитектура Файла Данных

Архитектура Файла Данных имеет следующие компоненты —

Файловые группы

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

Существует два типа групп файлов в SQL Server: основной и пользовательский. Первичная файловая группа содержит первичный файл данных и любые другие файлы, специально не назначенные другой файловой группе. Все страницы для системных таблиц размещаются в основной группе файлов. Пользовательские файловые группы — это любые файловые группы, указанные с помощью ключевого слова file file group в операторе create database или alter database.

Одна файловая группа в каждой базе данных работает как файловая группа по умолчанию. Когда SQL Server выделяет страницу для таблицы или индекса, для которых при создании не было указано ни одной группы файлов, страницы выделяются из группы файлов по умолчанию. Чтобы переключить файловую группу по умолчанию из одной файловой группы в другую файловую группу, она должна иметь фиксированную роль db_owner.

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

файлы

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

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

Файлы журнала содержат всю информацию журнала, используемую для восстановления базы данных. База данных должна иметь хотя бы один файл журнала. Мы можем иметь несколько файлов журнала для одной базы данных. Рекомендуемое расширение для файла журнала .ldf .

Расположение всех файлов в базе данных записывается как в основной базе данных, так и в основном файле базы данных. В большинстве случаев ядро ​​базы данных использует местоположение файла из базы данных master.

Файлы имеют два имени — логическое и физическое. Логическое имя используется для ссылки на файл во всех операторах T-SQL. Физическое имя OS_file_name, оно должно соответствовать правилам ОС. Файлы данных и журналов могут быть размещены в файловых системах FAT или NTFS, но не могут быть размещены в сжатых файловых системах. В одной базе данных может быть до 32 767 файлов.

Extents

Экстенты являются базовой единицей, в которой пространство выделяется для таблиц и индексов. Экстент составляет 8 смежных страниц или 64 КБ. SQL Server имеет два типа экстентов — Унифицированный и Смешанный. Унифицированные экстенты состоят только из одного объекта. Смешанные экстенты совместно используются до восьми объектов.

страницы

Это фундаментальная единица хранения данных в MS SQL Server. Размер страницы составляет 8 КБ. Начало каждой страницы — 96-байтовый заголовок, используемый для хранения системной информации, такой как тип страницы, объем свободного места на странице и идентификатор объекта объекта, владеющего страницей. В SQL Server существует 9 типов страниц данных.

• Данные — строки данных со всеми данными, кроме текста, текста и данных изображения.

• Индекс — индекс записей.

• Tex \ Image — текстовые, графические и текстовые данные.

• GAM — информация о выделенных экстентах.

• SGAM — информация о выделенных экстентах на системном уровне.

• Page Free Space (PFS) — Информация о свободном пространстве, доступном на страницах.

• Карта распределения индекса (IAM) — информация об экстентах, используемых таблицей или индексом.

• Массовая измененная карта (BCM) — информация об экстентах, измененных массовыми операциями со времени последнего оператора журнала резервного копирования.

• Дифференциально измененная карта (DCM) — информация об экстентах, которые изменились со времени последнего оператора резервного копирования базы данных.

Данные — строки данных со всеми данными, кроме текста, текста и данных изображения.

Индекс — индекс записей.

Tex \ Image — текстовые, графические и текстовые данные.

GAM — информация о выделенных экстентах.

SGAM — информация о выделенных экстентах на системном уровне.

Page Free Space (PFS) — Информация о свободном пространстве, доступном на страницах.

Карта распределения индекса (IAM) — информация об экстентах, используемых таблицей или индексом.

Массовая измененная карта (BCM) — информация об экстентах, измененных массовыми операциями со времени последнего оператора журнала резервного копирования.

Дифференциально измененная карта (DCM) — информация об экстентах, которые изменились со времени последнего оператора резервного копирования базы данных.

Архитектура файла журнала

Журнал транзакций SQL Server работает логически, как если бы журнал транзакций представлял собой строку записей журнала. Каждая запись журнала идентифицируется по порядковому номеру журнала (LSN). Каждая запись журнала содержит идентификатор транзакции, которой она принадлежит.

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

Действия по восстановлению операции зависят от типа записи журнала.

• Логическая операция записана.
  • Чтобы выполнить логическую операцию вперед, операция выполняется снова.
  • Чтобы откатить логическую операцию назад, выполняется обратная логическая операция.
• До и после изображения вошли.
  • Чтобы продвинуть операцию вперед, применяется изображение после.
  • Чтобы откатить операцию назад, применяется изображение перед.

Различные типы операций записываются в журнал транзакций. Эти операции включают в себя —

• Начало и конец каждой транзакции.

• Каждое изменение данных (вставить, обновить или удалить). Это включает в себя изменения с помощью системных хранимых процедур или операторов языка определения данных (DDL) для любой таблицы, включая системные таблицы.

• Каждый экстент и распределение страниц или де-распределение.

• Создание или удаление таблицы или индекса.

Начало и конец каждой транзакции.

Каждое изменение данных (вставить, обновить или удалить). Это включает в себя изменения с помощью системных хранимых процедур или операторов языка определения данных (DDL) для любой таблицы, включая системные таблицы.

Каждый экстент и распределение страниц или де-распределение.

Создание или удаление таблицы или индекса.

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

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

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

Компонент Database Engine выбирает размер файлов виртуального журнала динамически при создании или расширении файлов журнала. Компонент Database Engine пытается поддерживать небольшое количество виртуальных файлов. Размер или количество виртуальных файлов журнала не могут быть настроены или установлены администраторами. Единственный раз, когда виртуальные файлы журнала влияют на производительность системы, это если физические файлы журнала определяются значениями small size и growth_increment.

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

Мы рекомендуем присваивать файлам журнала значение размера, близкое к требуемому окончательному размеру, а также иметь относительно большое значение growth_increment. SQL Server использует журнал предварительной записи (WAL), который гарантирует, что никакие изменения данных не будут записаны на диск до записи соответствующей записи журнала на диск. Это поддерживает свойства ACID для транзакции.