В каждом файле данных базы данных (MDF) SQL Server хранит ваши данные на страницах размером 8 КБ. Это килобайты - не мегабайты, не гигабайты, а всего лишь килобайты.

К примеру, мы создаем таблицу:

CREATE TABLE dbo.Employees

(EmployeeID INT IDENTITY(1,1),

 EmployeeName VARCHAR(200))

Во-первых, да, я понимаю, я не должен делать EmployeeName как одно поле - у меня должны быть FirstName, LastName, MiddleName, Suffix, Prefix и т.д., но я стараюсь сократить это письмо максимально.

Тем не менее, в этой таблице, каждая запись занимает немного места. EmployeeID - это INTeger, который занимает 4 байта. Это те же 4 байта, независимо от того, это число равно 1 или 1 000 000. EmployeeName  - это VARCHAR (200), что означает, мы можем хранить до 200 символов здесь, и каждый символ занимает байт. Если мы вставим 'BRENT OZAR', это 10 символов, поэтому нам нужно 10 байтов, чтобы сохранить это.

Если все наши сотрудники в среднем содержат около 10 символов, это означает, что мы можем разместить около 500-600 записей на странице базы данных размером 8 КБ.

Brent Ozar Unlimited - небольшая компания, поэтому мы можем хранить всех наших сотрудников на одной странице размером 8 КБ. Когда мы добавляем, редактируем и удаляем сотрудников, SQL Server забирает эту 8KB-страницу с диска, вносит ее в память, вносит необходимые изменения, а затем записывает эту страницу данных обратно на диск. Сама страница размером 8 КБ - это самый маленький блок, который будет кэшироваться SQL Server - он не кэширует отдельные строки / записи - и каждая страница принадлежит исключительно одному объекту.

Немного про объекты

Вы заметите, что я избегаю использования слова «таблица». Таблицы классные, но когда мы начинаем разбираться с тем, что SQL Server делает, мы хотим начать думать об этих трех типах объектов:

Куча - это специализированная структура данных типа дерево. В моей таблице dbo.Employees я не указывал, в каком порядке SQL Server должен хранить мои данные, поэтому данные на моей странице размером 8 Кбайт будут удалены в любом порядке.

Кластеризованный индекс - то, что мы обычно считаем таблицей. Если бы я создал свою таблицу следующим образом:

CREATE TABLE dbo.Employees

(EmployeeID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

 EmployeeName VARCHAR(200))

Тогда SQL Server будет хранить мои данные в порядке EmployeeID. Таким образом, когда я ищу EmployeeID # 42, он может перейти непосредственно к этому номеру без сканирования всех моих сотрудников. Индекс сортируется в порядке поля EmployeeID, но на самом деле это полная копия нашей таблицы, включая все наши поля - в данном случае это просто EmployeeName.

Некластеризованный индекс - Если я скажу SQL Server:

CREATE NONCLUSTERED INDEX IX_Name

ON dbo.Employees(EmployeeName)

Затем SQL Server создаст вторую копию моей таблицы, отсортированную по EmployeeName. Эта копия моей таблицы будет включать только поля, указанные в индексе (EmployeeName), а также все поля, необходимые для возврата к кластеризованному индексу (в данном случае, ключ кластеризации, EmployeeID).

Все три этих объекта - кучи, кластеризованные индексы и некластеризованные индексы - будут храниться на отдельных наборах страниц. У нас не будет кластеризованного индекса и некластеризованного индекса для той же таблицы на той же странице - они разделены. Поэтому, когда мы занимаемся анализом, мы должны думать не о таблицах, а об индексах. Чтобы узнать больше, прочитайте Jes Borland's SQL Server Index Terms.

Страницы и строки на диске

The sys.dm_db_index_physical stats

Функция динамического управления (DMF) возвращает количество строк и страниц, хранящихся в каждом объекте базы данных. Он принимает параметры за идентификатор базы данных и идентификатор объекта, также вы можете передавать NULL для получения информации по всем вашим объектам базы данных. Прокрутите страницу до ссылки примеров на странице «Books Online», и вы получите запросы для изучения одной таблицы. Я настоятельно рекомендую начать с небольшой таблицы, потому что некоторые параметры DMF заставят SQL Server фактически искать на каждой странице объекта. Это означает, что, если все страницы этого объекта не кэшируются в памяти, работа вашего SQL Server может замедлиться.

DMF также включает в себя средний размер записи и максимальный размер записи. Просто потому, что мы делаем все VARCHAR (8000), не означает, что мы фактически сохраняем 8000 символов в каждом поле. Но пока не меняйте структуру базы данных. Давай оставим это на потом.

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

Подробнее о страницах

На моей 90-минутной сессии «How to Think Like the Engine» я объясняю страницы, индексы, объединения и т. Д. Я использую реальные страницы базы данных из базы данных StackOverflow.com для демонстрации, и вы фактически получаете PDF-файлы, которые вы можете распечатать и следовать им по ходу дела.

Ваше домашнее задание

Давайте начнем думать о том, какие объекты занимают место в наших базах данных.  Является ли ваша база данных сокровищницей, хватающейся за кучу некластеризованных индексов, которые не используются, а просто занимают место? Насколько велик каждый индекс, и будет ли он использоваться или нет?

Это важно, потому что чем больше индексов вы имеете:

• тем дольше происходит резервное копирование
• тем больше памяти вам нужно для кэширования
• И что самое главное, тем медленнее будут ваши вставки / обновления / удаления, так как SQL Server должен будет хранить больше копий вашей таблицы

No Comments