Волк В.К. Базы данных. Практикум по проектированию, программированию и администрированию баз данных, включающий примеры и практические задания для самостоятельного выполнения
 Скачать 3.17 Mb.
|
|
ГЛАВА 14. ПРАКТИКУМ ПО АДМИНИСТРИРОВАНИЮ 14.1. Общие методические указания Структура и содержание. Практикум содержит четыре практические ра- боты, отражающие следующие аспекты администрирования: – управление физической моделью базы данных (работы № 1 и 2); – управление индексными структурами данных (работа № 3); – анализ процедурных планов выполнения SQL-запросов (работа № 4). Каждая работа содержит несколько взаимосвязанных заданий, выполне- ние которых направлено на решение поставленных в работе задач и требует освоения и применения соответствующих инструментальных средств админи- стрирования баз данных. Программное обеспечение. Все работы выполняются в системе SQL-Ser- ver Management Studio, версия сервера баз данных — не старше 2008R2. Отчет по работе должен содержать: − цели и задачи, описание методики проведения работы, используемых структур данных и инструментальных программных средств; − иллюстративный материал (листинги программных компонентов, выво- димые на экран результаты их работы, графический материал и пр.); − анализ полученных результатов с собственными выводами; − ответы на контрольные вопросы (при их наличии). Защита. Работа выполняется индивидуально, защита работы проводится в форме собеседования по материалу представленного отчета. В процессе за- щиты оценивается полнота и качество выполнения практических заданий, гра- мотность использования инструментальных средств, правильность и обосно- ванность выводов по результатам работы, качество оформления отчета. 14.2. РАБОТА № 1. Анализ файловой структуры баз данных Цель работы: ознакомление с программной архитектурой сервера баз данных и приобретение практических навыков применения инструментальных программных средств, используемых разработчиками и администраторами для управления файловой структурой баз данных. Задачи: – изучить пользовательский интерфейс программной среды SQL-Server Management Studio; – исследовать файловую структуру системной базы данных «Model», ис- пользуемую в качестве шаблона для создания пользовательских баз данных; – освоить технику создания пользовательских баз данных средствами MS SQL-Server Management Studio исоответствующими средствами языка Transact SQL (операторы Create Database, Create Table, Alter Table); 7 / 19 179 – освоить технику модификации параметров файловой структуры пользо- вательских баз данных средствами MS SQL-Server Management Studio, сред- ствами языка Transact SQL (оператор Alter Database); – исследовать объекты системного каталога базы данных, ответственные за хранение параметров ее файловой структуры. Задание 1. Анализ файловой структуры базы данных «Model» 1.1. Активизируйте системную БД «Model». Определите свойства файло- вой структуры (состав и имена файлов и файловых групп, размеры и прочие параметры файлов) этой базы данных. 1.2. Просмотрите и проанализируйте схемы и содержимое системных таблиц SysFileGroups и SysFiles этой базы данных (через соответствующие им одноименные системные представления (Sys.SysFileGroups и Sys.SysFiles) и пря- мым доступом к этим таблицам SQL-оператором Select). 1.3. На базе таблиц SysFileGroups и SysFiles создайте хранимое представ- ление (Create View) для визуализации информации о технических параметрах файлов базы данных и их распределения по группам. 1.4. Сохраните результаты выполнения задания в отчете. Задание 2. Создание пользовательских баз данных 2.1. Создайте пользовательскую БД и сформируйте ее схему (2–3 связан- ные таблицы) средствами SQL-Server Management Studio. 2.2. Активизируйте созданную БД и, не заполняя таблиц данными, вы- полните в контексте этой базы данных задание 1.2. 2.3. В контексте этой БД выполните хранимое представление, созданное при выполнении задания 1.3. Проанализируйте результаты. 2.4. Создайте еще одну пользовательскую БД средствами Transact SQL (оператор Create Database). Создайте в этой БД две вторичные файловые груп- пы, одной из которых установите свойство «по умолчанию». Создайте по два вторичных файла в каждой из вторичных файловых групп. Создайте в этой БД 4–5 простых таблиц, определите для этих таблиц файловые группы. 2.5. Повторите задания 2.2 и 2.3 в контексте новой базы данных. 2.6. Сохраните результаты выполнения задания в отчете. Задание 3. Модификация файловой структуры баз данных 3.1. Используя средства SQL-Server Management Studio, измените пара- метры файловой структуры одной из пользовательских БД, созданных при вы- полнении предыдущего задания: – увеличите в 2 раза начальный размер первичного файла БД; – уменьшите в 2 раза шаг приращения размера этого файла; – создайте две дополнительные (вторичные) файловые группы; – создайте во вторичных файловых группах по два (вторичных) файла ба- зы данных. 3.2. В контексте этой (модифицированной) БД выполните запрос, создан- ный при выполнении задания 1.3. Проанализируйте результат. 8 / 19 180 3.3. Измените по своему усмотрению параметры файловой структуры си- стемной базы данных «Model» (размер первичного файла БД, количество вто- ричных файловых групп и вторичных файлов). 3.4. Создайте новую пользовательскую БД, в контексте этой БД выполни- те задания 2.2 и 2.3, результаты сохраните в отчете. 14.3. РАБОТА № 2. Анализ алгоритмов резервирования памяти Цель работы: исследование типовых алгоритмов управления процессами распределения файловых страниц между логическими объектами базы данных и приобретение практических навыков использования инструментальных средств администратора для анализа и управления физической моделью данных. Задачи: – изучить внутреннюю организацию файлов БД в системе MS SQL-Server и внутреннюю организацию файловых страниц; – изучить структуру объектов системного каталога БД, ответственных за хранение параметров ее физической модели; – освоить языковые (TransactSQL) средства создания и модификации ло- гических объектов БД; – освоить технику анализа физической модели БД с использованием ко- манд системной утилиты DBCC и системных хранимых процедур; – исследовать алгоритмы выделения дисковой памяти для хранения логи- ческих объектов базы данных (таблиц и индексов), реализованные в MS SQL-Server. Методические указания Лабораторная работа содержит четыре задания, каждое из которых предполагает постановку несложного эксперимента с привлечением програм- мных компонентов базы данных и последующий анализ полученных результа- тов. В процессе выполнения заданий продолжается знакомство с компонента- ми системного каталога базы данных (таблицами SysObjects, SysIndexes и соот- ветствующими им системными представлениями), а также приобретается опыт использования встроенных программных средств, предназначенных для созда- ния и анализа компонентов файловой структуры БД: – SQL-операторы подмножества DDL языка TransactSQL: CreateDatabase, CreateTable, Create Proc; – команды системной утилиты DBCC (DataBase Console Command): DBCC TraceON, DBCC Page, DBCC ExtentINFO, DBCC ShowContig; – системные хранимые процедуры и функции: sp_helptext, sp_helpfile, sp_spaceused, Object_ID(). 9 / 19 181 Задание 1. Анализ системного каталога пользовательской БД 1.1. Используя SQL-команду CREATE DATABASE, создайте пользователь- скую базу данных с простейшей файловой структурой: один файл данных и од- на файловая группа (листинг 4.13). Use master; CREATE DATABASE MyDB-1 ON (NAME = MyDB-1_Dat, FILENAME = 'C:\...\...\MyDB-1.mdf', SIZE = 4MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1MB) LOG ON (NAME = MyDB-1_log, FILENAME = 'D:\...\...\MyDB-1.ldf', SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 15%); Листинг 4.13 Пример создания БД 1.2. Проконтролируйте и сохраните в отчете параметры БД, созданной в результате выполнения приведенного выше SQL-кода: а) прямым доступом оператором Select к таблицам SysFiles и SysFileGroups системного каталога базы данных MyDB-1; б) с использованием хранимой процедуры sp_helpfile (листинг 4.14). Use MyDB-1; EXEC sp_helpfile; Листинг 4.14 Пример выполнения хранимой процедуры 1.3. Используя SQL-команду CREATE TABLE, создайте в этой базе данных простую унарную таблицу MyTable_1, каждая строка которой будет занимать ровно одну дисковую страницу (листинг 4.15): Use MyDB-1; CREATE TABLE MyTable_1 (column1 char(8000) NOT NULL default 'One row in one page'); Листинг 4.15 Пример создания унарной таблицы 10 / 19 182 1.4. Прямым доступом оператором к таблице SysObjects системного ката- лога БД MyDB-1 определите и сохраните в отчете: а) параметры name, Id, xtype и crdate для вновь созданного объекта (таб- лицы MyTable_1); б) общее количество объектов такого же типа (xtype) в этой БД; в) общее количество объектов с такой же датой создания (crdate) в этой БД; г) общее количество пользовательских таблиц (xtype = 'U'), системных таблиц (xtype = 'S'), хранимых представлений (xtype = 'V') и хранимых процедур (xtype = 'P') в этой БД. 1.5. Используя встроенную функцию Object_ID(), определите идентифи- катор объекта (таблицы MyTable_1) по его имени. Use MyDB-1; Select Object_ID('MyTable_1'); Листинг 4.16 Пример использования функции Object_ID() 1.6. Прямым доступом к таблице SysIndexes системного каталога БД MyDB-1 определите и сохраните в отчете: а) количество строк в этой таблице, соответствующих объекту MyTable1 (для ограничения выборки используйте конструкцию: where ID = Object_ID('MyTable_1')); б) значения полей Id, IndId, First, Root, FirstIAM для таблицы MyTable_1; в) количество и номера (адреса PageNum) дисковых страниц, владельцем которых является таблица MyTable_1 (в текущей ситуации эта таблица еще не содержит ни одной строки данных). 1.7. Сформулируйте и сохраните в отчете ответы на следующие вопросы. 1. Каковы результаты трансляции SQL-запроса CREATE TABLE … и в каких таблицах системного каталога MS SQL-Server сохраняет эти результаты? 2. Каково назначение системной таблицы SysObjects? 3. Какую информацию несут значения полей name, Id, xtype и crdate в таблице SysObjects? 4. Каково назначение системной таблицы SysIndexes? 5. Какую информацию несут значения полей Id и IndId в таблице SysIndexes? 6. Какую информацию несут значения полей First, Root и FirstIAM и в ка- ком формате представлена эта информация в таблице SysIndexes? 7. Сколько страниц занимает в файле данных «пустая» таблица? 8. Для чего можно использовать встроенные хранимые процедуры sp_helpfile и sp_helptext и встроенную функцию Object_ID()? Приведите примеры. 11 / 19 183 Задание 2. Исследование алгоритма резервирования памяти в базах данных с простой файловой структурой Продолжим эксплуатировать БД MyDB-1, созданную при выполнении предыдущего задания. Эта база содержит единственный (primary) файл данных, ассоциированный с единственной (primary) файловой группой. В соответствии с результатами выполнения задания 1.4, часть страниц типа Data этого файла занята объектами системного каталога, а единственная пользовательская таблица MyTable_1 пока пуста и не владеет ни одной из фай- ловых страниц этого типа. Проведя несложный эксперимент, исследуйте процесс резервирования и заполнения файловых страниц и распределения их по экстентам соответствую- щих типов (смешанных — mixed или однородных — uniform) при последова- тельной вставке строк в таблицы. 2.1. Создайте в БД MyDB-1 еще одну таблицу, например MyTable-2, с та- кой же схемой, как у таблицы MyTable-1 (используйте SQL-код, подобный при- веденному в листинге 4.15). 2.2. Вставьте по одной строке в обе эти таблицы (листинг 4.17) и затем повторно выполните задание 1.6 для двух таблиц (теперь обе таблицы не пусты и каждая из них является владельцем как минимум двух страниц: одной стра- ницы типа Data и одной IAM-страницы). Use MyDB-1; INSERT INTO MyTable_1 DEFAULT VALUES; INSERT INTO MyTable_2 DEFAULT VALUES; Листинг 4.17 Вставка одной строки Определите номера экстентов, содержащих страницы, выделенные двум таблицам при вставке в них строк данных. К какому типу относятся эти экстен- ты — uniform или mixed? Ответ обоснуйте и сохраните в отчете. 2.3. С помощью команды PAGE системной утилиты DBCC просмотри- те заголовки и основное содержимое этих страниц (для каждой из двух таб- лиц). DBCC TRACEON (3604) DBCC PAGE ('MyDB-1', Листинг 4.18 Пример выполнения команды PAGE С четвертым параметром команды PAGE придется поэспериментировать: значение этого параметра (0, 1, 2 или 3) влияет (по-разному для различных ти- пов страниц) на объем и формат выводимой на экран информации. 2.4. Используя хранимую процедуру sp_spaceused, определите количе- ство страниц, занятых каждой из этих таблиц. 12 / 19 184 Use MyDB-1; EXEC sp_spaceused MyTable_1; EXEC sp_spaceused MyTable_2; Листинг 4.19 Пример выполнения процедуры sp_spaceused Прокомментируйте результаты работы этой процедуры, представленные на экране в табличном виде, как это показано на рисунке 4.16. NAME ROWS RESERVED DATA INDEX_SIZE UNUSED MyTable_1 1 – – – – NAME ROWS RESERVED DATA INDEX_SIZE UNUSED MyTable_2 1 – – – – Рис. 4.16 Форма представления результатов выполнения процедуры sp_spaceused Подтвердите или скорректируйте свой ответ на вопрос, сформулирован- ный в задании 2.2. 2.5. Последовательно добавляя в обе таблицы еще по 4 строки (листинг 4.20), определите после каждой вставки общее количество зарезервированных страниц (поле RESERVED), количество страниц, занятых строками таблиц (поле DATA), и количество зарезервированных, но еще не использованных страниц (поле UNUSED). Use MyDB-1; INSERT INTO MyTable_1 DEFAULT VALUES; EXEC sp_spaceused MyTable_1; Go 4; INSERT INTO MyTable_1 DEFAULT VALUES; EXEC sp_spaceused MyTable_2; Go 4; Листинг 4.20 Вставка 4-х строк с контролем занятого пространства Обратите внимание на динамику изменения трех указанных выше пара- метров при изменении количества вставленных в таблицы строк. Теперь в каж- дой таблице по 5 строк и, соответственно, каждая таблица является владельцем пяти файловых страниц типа DATA и одной IAM-страницы. В экстентах какого типа (uniform или mixed) размещены страницы, выде- ленные двум этим таблицам? 2.6. Повторите предыдущий опыт — вставьте еще по 5 строк в обе эти таблицы (Go 5) и проанализируйте полученный результат. 2.7. Вставьте в эти таблицы еще по 50 строк, проанализируйте получен- ный результат с помощью процедуры sp_spaceused и дополнительно с помо- щью команды DBCC EXTENTINFO (листинг 4.21), которая отобразит на экране 13 / 19 185 информацию об идентификаторах занятых страниц (поля file_id и page_id), ко- личестве выделенных (ext_size) и фактически заполненных (pg_alloc) страниц. 2.8. Если SQL-Server все еще выделяет таблицам страницы в смешанных экстентах, продолжайте вставлять строки в таблицы до тех пор, пока сервер не начнет резервировать однородные экстенты для страниц каждой из таблиц. DBCC EXTENTINFO(MyDB-1,MyTable_1,-1 8 ) Листинг 4.21 Пример выполнения команды EXTENTINFO 2.9. С помощью команды DBCC PAGE просмотрите содержимое страниц типа GAM и SGAM (позиции этих страниц в файле фиксированы: № 2 — для GAM и № 3 — для SGAM). Какие свойства (двухбитовые коды) получили экс- тенты, зарезервированные для хранения строк таблиц MyTable_1 и MyTable_2? 2.10. С помощью команды DBCC PAGE просмотрите содержимое страницы типа PFS (фиксированная позиция № 1 в файле данных). Определите степень заполнения нескольких страниц, выделенных таблицам MyTable_1 и MyTable_2. 2.11. Сформулируйте и сохраните в отчете ответы на следующие вопросы. 1. В каком формате хранятся номера страниц в таблице SysIndeses? 2. Может ли логический объект базы данных (например, таблица) быть владельцем единственной файловой страницы? 3. Может ли логический объект базы данных быть владельцем несколь- ких файловых страниц типа DATA? 4. Может ли одна файловая страница типа DATA иметь более чем одного владельца? 5. Может ли одна файловая страница типа DATA входить в состав более чем одного экстента? 6. В каких случаях MS SQL-Server резервирует смешанные экстенты? 7. В каких случаях MS SQL-Server резервирует однородные экстенты? 8. В каких структурах данных и в каком формате SQL-Server хранит ин- формацию о свободных экстентах, типах зарезервированных экстентов и сво- бодном пространстве внутри файловых страниц? 9. Какие эксплуатационные показатели использовались в качестве крите- риев при реализации стратегии резервирования экстентов? Задание 3. Исследование алгоритма распределения памяти в базах данных со сложной файловой структурой Если в предыдущем задании анализировался процесс резервирования экс- тентов и страниц единственного файла данных, то теперь ставится задача экс- периментального исследования алгоритма распределения страниц одного логи- ческого объекта (таблицы) между несколькими файлами данных. 8 Последним параметром команды EXTENTINFO можно указывать либо имя индекса табли- цы (для вывода информации о страницах этого индекса), либо число 0 (для вывода информа- ции о страницах, занятых строками таблицы), либо число –1 (для вывода информации о страницах, занятых строками таблицы и всеми ее индексами). 14 / 19 186 Рабочая гипотеза, которую следует подтвердить, опровергнуть или уточ- нить в результате выполнения этого задания, может быть сформулирована сле- дующим образом: «Если файловая группа содержит более одного файла типа DATA, то при вставке строк в таблицу, ассоциированную с этой файловой группой, количество файловых страниц, выделяемых сервером в каждом из файлов, будет пропорционально их размерам». Для выполнения задания потребуется создать несколько баз данных, имеющих более сложную (по сравнению с MyDB-1)файловую структуру: не- сколько файловых групп и несколько файлов разных размеров в каждой группе. 3.1. Создайте новую пользовательскую базу данных (например, MyDB-2) со следующей файловой структурой: – две файловые группы (группа Primary со свойством по умолчанию и до- полнительная группа Group2); – четыре файла типа DATA (первичный файл и три вторичных файла (File1, File2 и File3), принадлежащих группе Group2); – установите начальные размеры вторичных файлов: Size = 5, 10 и 15 Mb соответственно; – установите остальные размерные параметры, одинаковые для всех этих трех файлов: MaxSize = Unlimited; Grows = 1 Mb. 3.2. Проконтролируйте и сохраните в отчете полученный результат с ис- пользованием прямого доступа к системной таблице sysfiles. 3.3. Создайте в базе данных MyDB-2 новую таблицу MyTable_3 (листинг 4.22) — каждая строка этой таблицы будет занимать ровно одну файловую страницу, и все эти страницы (в случае заполнения строк таблицы) будут раз- мещены во вторичных файлах, включенных в группу Group2. Use MyDB-2; CREATE TABLE MyTable_3 (column1 char(8000) NOT NULL default 'One row in one page') ON Group2; Листинг 4.22 Пример создания таблицы, связанной с файловой группой 3.4. Учитывая тот факт, что все системные объекты БД MyDB-2 будут размещены в ее первичном файле, а вторичные файлы будут заняты исключи- тельно пользовательскими данными таблицы MyTable_3, рассчитайте (прибли- зительно) максимальное количество страниц этой таблицы, соответствующее начальному размеру каждого из трех файлов группы Group2. 3.5. Используя листинг 4.23, напишите пользовательскую хранимую про- цедуру AddRows, при выполнении которой в таблицу будет вставлено заданное количество строк со значениями полей по умолчанию, указанными при созда- нии таблицы. 15 / 19 187 3.6. Используя процедуру AddRows, вставьте в таблицу MyTable_3 рас- четное количество строк так, чтобы меньший из трех вторичных файлов ока- зался заполненным примерно наполовину. Проконтролируйте и сохраните в от- чете полученный результат. CREATE PROC AddRows @Tablename char(12),@maxrows int AS SET nocount off DECLARE @count INT SET @count = 0 WHILE @count < @maxrows BEGIN INSERT INTO @Tablename DEFAULT VALUES SET @count = @count + 1 END Листинг 4.23 Пример SQL-кода для создания хранимой процедуры 3.7. Многократно повторяя п. 3.6, добейтесь ситуации, когда наибольший (по начальному размеру) из вторичных файлов увеличится по размеру примерно вдвое. 3.8. По результатам проведенного эксперимента опишите и сохраните в отчете алгоритм распределения страниц типа DATA между файлами одной файловой группы в случае, когда начальные размеры файлов различны, но их предельные размеры не ограничены. 3.9. Создайте новую пользовательскую базу данных (например, MyDB-3) с файловой структурой, аналогичной MyDB-2, но с различными параметрами MaxSize для вторичных файлов. 3.10. Повторите п. 3.2–3.7 этого задания. 3.11. По результатам проведенного эксперимента постройте графики за- висимостей размеров файлов от количества строк таблицы, опишите и сохрани- те в отчете алгоритм распределения страниц типа DATA между файлами одной файловой группы в случае, когда различны и начальные, и предельные размеры файлов. Задание 4. Исследование структуры файловой страницы типа DATA При выполнении предыдущих заданий исследовался процесс резервиро- вания файловых страниц при вставке строк в таблицы, и для этого было удобно использовать унарные таблицы с полями типа char(8000), чтобы каждая строка таблицы занимала целую страницу. В реальной ситуации страница может со- держать несколько строк таблицы и при этом иметь свободное пространство для вставки в таблицу последующих строк. Объектом исследования в этом задании является внутренняя структура файловой страницы и процесс ее заполнения строками таблицы. Для выполнения задания рекомендуется создать новую базу данных (например, MyDB-4) с простой файловой структурой. 16 / 19 188 4.1. Создайте бинарную таблицу MyTable_4(Key1 INT, Data CHAR(10)) с длиной строки 14 байт. 4.2. Вставьте в таблицу 10 строк, заполнив поля случайными данными. USE MyDB-4 DECLARE @key1 INT, @data CHAR(10) SET @key1=1000*RAND(), @data=STR(@key1) INSERT into MyTable_4 values(@Key1,@data) Go 10 Листинг 4.24 Вставка 10-ти строк в таблицу Примечание. Функция RAND() возвращает псевдослучайное число в диапазоне (0–1), а функция STR() преобразует число в соответствующую цифровую строку. 4.3. Определите номер первой файловой страницы (SysIndexes.First), вы- деленной этой таблице, и просмотрите страницу командой DBCC PAGE(). Опре- делите и сохраните в отчете: – количество слотов страницы, занятых строками таблицы; – смещения (в байтах) каждого слота; – длину (в байтах) каждого слота. 4.4. Вставьте еще 100 строк в таблицу и повторно выполните п. 4.3. 4.5. Просмотрите PFS-страницу командой DBCC PAGE, определите процент заполнения первой страницы, выделенной таблице. 4.6. Выполняйте п. 4.4 и 4.5 до 100%-ного заполнения первой страницы выделенной таблицы. Определите и сохраните в отчете: – количество слотов первой страницы, занятых строками таблицы; – суммарный объем страницы, занятый заполненными слотами; – суммарный объем страницы (в байтах), занятый областью обратных ссылок (offset table). 4.7. Просматривая первую страницу таблицы, выберите по своему усмот- рению один из заполненных слотов, запомните его номер и значения полей со- ответствующей строки таблицы. Затем удалите из таблицы эту строку (Delete MyTable_4 Where Key1=…). 4.8. Повторно просмотрите страницу командой DBCC PAGE, обращая вни- мание на слот с удаленной строкой. Прокомментируйте и попытайтесь объяс- нить полученный результат. 14.4. РАБОТА № 3. Исследование индексных структур данных Цель работы: изучение индексных структур и приобретение навыков ис- пользования инструментальных средств управления индексами. Задачи: – освоить программные средства создания, модификации и анализа ин- дексных структур данных; 17 / 19 189 – изучить структуру объектов системного каталога, ответственных за хранение параметров индексов; – изучить формат индексных страниц для различных типов индексов. Методические указания Работа содержит четыре взаимосвязанных задания, каждое из которых направлено на изучение многоуровневых индексных структур данных для ин- дексов четырех различных типов: – некластеризованного индекса по неуникальным столбцам таблицы при условии отсутствия кластеризованного индекса; – кластеризованного уникального индекса; – некластеризованного индекса при условии наличия в таблице кластери- зованного индекса; – некластеризованного индекса с включенными столбцами. Каждое задание предполагает постановку несложного эксперимента с привлечением программных компонентов базы данных и последующее прове- дение анализа полученных результатов. В процессе выполнения заданий продолжится знакомство с компонента- ми системного каталога базы данных (таблица SysIndexes) и программными средствами, используемыми для управления индексами: – SQL-операторы Create/Alter/Drop Index; – команды Page и ShowContig системной утилиты DBCC; – системная хранимая процедура sp_spaceused; – TVF-функция sys.dm_db_index_physical_stats(). Задание 1. Анализ структуры индексных страниц для неуникального некластеризованного индекса 1.1. Используя SQL-команду Create Database, создайте пользовательскую базу данных (например, Index_Test_1) с простейшей файловой структурой: один файл данных в одной группе. 1.2. Используя SQL-команду Create Table, создайте в этой БД таблицу MyTable_4, схема которой включает три целочисленных столбца и четвертый столбец строкового типа. Use Index_Test_1 CREATE TABLE MyTable_4 (Key_0 INT NOT NULL, Key_1 INT NOT NULL, Key_2 INT NOT NULL, Data CHAR(61) NOT NULL) Листинг 4.25 Создание таблицы MyTable_4 Примечание. Длина поля Data (61 байт) выбрана для удобства просмот- ра файловых страниц командой DBCC PAGE: так как каждый слот страницы 18 / 19 190 содержит два служебных поля суммарной длиной в 7 байт, то общая длина каждого слота составит ровно 80 байт (7+3*4+61). 1.3. Определите идентификатор этой таблицы и убедитесь в том, что си- стемная таблица SysIndexes содержит ровно одну запись, соответствующую таблице MyTable_4, и при этом таблица MyTable_4 не является владельцем ни одной файловой страницы. Объясните этот факт. 1.4. Вставьте в таблицу MyTable_4 одну строку данных. USE Index_Test_1 DECLARE @key0 INT, @key1 INT, @key2 INT, @dat CHAR(30) SET @key0=1000*RAND(),@key1=1000*RAND(), @key2=1000*RAND() SET @dat=STR(@key0)+STR(@key1)+STR(@key2) INSERT into MyTable_4 values(@key0,@key1,@key2,@dat) Листинг 4.26 Вставка строки в таблицу MyTable_4 1.5. Используя хранимую процедуру sp_spaceused, определите количе- ство страниц, занятых данными этой таблицы и всеми ее индексами, включая IAM-страницу. 1.6. Повторным запросом к таблице SysIndexes определите: – количество записей в системной таблице SysIndexes, соответствующих таблице MyTable_4; – значения полей Root, First, FirstIAM и IndID для каждой из этих записей; – категории объектов (Heap, ClusteredIndex или NonClusteredIndex), соот- ветствующих таблице MyTable_4. 1.7. С помощью команды DBCC PAGE просмотрите содержимое всех стра- ниц, владельцем которых является таблица MyTable_4. 1.8. Создайте НЕкластеризованные НЕуникальные индексы по полям Key_0, Key_1 и Key_2 таблицы MyTable_4 (листинг 4.26), затем повторно вы- полните п. 1.5 и 1.6. Use Index_Test_1 Create NONCLUSTERED Index NonClInd_0 ON MyTable_4(Key_0) Листинг 4.27 Создание некластеризованного индекса NonClInd_0 по полю Key_0 1.9. Командой DBCC PAGE просмотрите заголовки и основное содержи- мое корневых и листовых страниц всех трех индексов (используйте значения 1, 2 и 3 для четвертого параметра этой команды). Определите: – глубину индексов; – номера страниц корневого и листового уровней; – формат ссылки с корневого уровня индекса на промежуточный (не ли- стовой) уровень; – формат ссылки с листовой страницы на страницу данных в куче (heap). 19 / 19 191 1.10. Вставьте в таблицу еще 1000 строк. 1.11. Проанализируйте полученный результат с помощью хранимой про- цедуры sp_spaceused, команды DBCC ShowContig и TVF-функции sys.dm_db_index_physical_stats(). 1.12. Вставьте в таблицу еще 100 000 строк (возможно, придется немного подождать) и выполните повторный анализ полученного результата. 1.13. Сформулируйте ответы на следующие вопросы. 1. Для чего и в каких случаях рекомендуется использовать индексы сле- дующих типов: «кластеризованный индекс», «некластеризованный индекс», «уникальный индекс», «индекс с включенными стобцами»? 2. На какие эксплуатационные показатели работы базы данных оказывает влияние индексирование данных в таблицах? 3. В каких ситуациях наличие индексированных столбцов таблиц может привести к снижению производительности работы базы данных? 4. Какие ограничения накладывает SQL-Server на использование индек- сов? 5. Каков формат ссылки с корневого уровня индекса на промежуточный (не листовой) уровень? 6. Каков формат ссылки с листовой страницы некластеризованного ин- декса при условии, что в таблице отсутствует кластерный индекс? 7. Как связаны между собой значения параметров «порядок индекса» и «глубина индекса» индексной структуры данных? 8. Какова глубина индексов, построенных при выполнении заданий 1.8, 1.10 и 1.12? Задание 2. Анализ структуры индексных страниц для кластеризованного индекса Для выполнения задания рекомендуется создать новую базу данных (например, Index_Test_2) с простейшей файловой структурой. 2.1. Создайте в БД Index_Test_2 таблицу MyTable_5 (листинг 4.27). use Index_Test_2 CREATE TABLE MyTable_5 (Key_0 IDENTITY CONSTRAINT Key0_PK PRIMARY KEY, Key_1 INT,Key_2 INT, Data CHAR(68)) Листинг 4.28 Пример создания таблицы с первичным ключом Примечание 1. Параметр IDENTITY, установленный для целочисленного поля Key_0, присваивает полю статус идентификатора, значения которого при вставке строк в таблицу сервер будет присваивать автоматически (по умолчанию автоинкрементно с шагом 1, начиная с 1). Примечание 2. Ограничение первичного ключа (PRIMARY KEY) для поля Key_0 гарантирует уникальность значений этого поля в таблице, даже при 1 / 24 192 отсутствии свойства IDENTITY. По умолчанию сервер автоматически создает кластеризованный индекс по первичному ключу. 2.2. Определите адреса файловых страниц Root, First и FirstIAM для кла- стеризованного индекса поля Key_0 таблицы MyTable_5. 2.3. Вставьте в таблицу 10 строк (листинг 4.28) и повторите п. 2.2. USE Index_Test_2 DECLARE @key0 INT,@key1 INT,@key2 INT,@dat CHAR(30) SET @key1=1000*RAND(),@key2=1000*RAND() SET @dat=STR(@key1)+STR(@key2) INSERT into MyTable_5 values(@key1,@key2,@dat) Go 10 Листинг 4.29 Вставка 10-ти строк в таблицу MyTable_5 2.4. Выполните задания, аналогичные п. 1.8–1.12, в контексте базы дан- ных Index_Test_2 применительно к таблице MyTable_5. Задание 3. Анализ структуры индексных страниц некластеризованного индекса при условии наличия кластеризованного индекса 3.1. Для выполнения задания будет использоваться ранее созданная база данных Index_Test_2 и уже существующая и заполненная таблица MyTable_5. 3.2. Создайте в таблице MyTable_5 некластеризованный индекс по полю Key_1. use Index_Test_2 Create NONCLUSTERED Index NonClInd_1 ON MyTable_5(Key_1) Листинг 4.30 Создание индекса в ранее заполненной таблице 3.3. Дождитесь завершения процесса построения индекса и затем повторно выполните задания 2.2–2.4. Задание 4. Анализ структуры индексных страниц некластеризованного индекса с включенным столбцом 4.1. Для выполнения задания будет использоваться ранее созданная база данных Index_Test_2 и заполненная таблица MyTable_5, в которой уже созданы индексы по двум полям: уникальный кластеризованный индекс по ключевому полю Key_0 и неуникальный некластеризованный индекс по полю Key_1. Остальные два поля таблицы неиндексированы. 4.2. Создайте в таблице MyTable_5 некластеризованный индекс по полю Key_2 с включенным полем Data. 2 / 24 193 use Index_Test_2 Create NONCLUSTERED Index incl_Ind_2 ON MyTable_5(Key_1) INCLUDE(Data) Листинг 4.31 Создание индекса c включенным столбцом 4.3. Повторно выполните задание 3.3. 14.5. РАБОТА № 4. Анализ процедурных планов SQL-запросов Эта работазавершает цикл из четырех работ, направленных на изучение физической модели данных, поддерживаемой MS SQL-Server. Цель работы: изучение стратегий построения процедурных планов ис- полнения SQL-запросов, реализуемых оптимизатором, и приобретение практи- ческих навыков анализа и управления производительностью. Задачи: – ознакомиться с основными низкоуровневыми операторами, используе- мыми для построения и описания процедурных планов; – освоить технику анализа процедурных планов соответствующими язы- ковыми средствами (SET SHOWPLAN, SET STATISTICS), а также средствами их графической визуализации; – провести экспериментальное исследование влияния индексирования таблиц БД на производительность выполнения типовых SQL-запросов; – по результатам проведенного анализа сделать выводы о стратегии рабо- ты генератора процедурных планов и эффективности применения различных индексных структур. Методические указания Работа содержит два задания, каждое из которых направлено на изучение стратегий построения процедурных планов исполнения следующих типовых SQL-запросов при использовании различных типов индексов по столбцам базо- вых таблиц: – процедурные планы реализации простейших однотабличных SQL- запросов вида Select … From |