Задание для самостоятельной работы 8.3. Оптимизация системы индексов

Задание:

· Используйте собранный вами протокол выполнения команд (файл C:\AdventureWorksTrace.trc) для анализа системы индексов средствами Database Tuning Advisor.

· Сохраните предлагаемые рекомендации в файле C:\IndexTuning.sql.

Решение:

1. Запустите Database Tuning Advisor (меню Пуск àПрограммы àMicrosoft SQL Server 2008 àPerformance Tools) и подключитесь к своему локальному серверу SQL Server 2008.

2. На вкладке General выберите созданный вами файл C:\AdventureWorksTrace.trc, а в списке баз данных — базу данных AdventureWorks.

3. На вкладке Tuning Options снимите флажок Limit Tuning Time и установите переключатели в положение Indexes and indexed views, Full partitioning, Do not keep any existing PDS. Нажмите на кнопку Start Analysis на панели инструментов.

4. Дождитесь окончания анализа, затем перейдите на вкладку Reports и просмотрите созданные отчеты.

5. В меню Actions выберите команду Save Recommendations и сохраните сгенерированные команды SQL по выполнению рекомендаций в файле C:\IndexTuning.sql.

Задание для самостоятельной работы 8.4 Дефрагментация таблиц и индексов

Задание:

1. Произведите анализ степени фрагментации индекса PK_Address_AddressID для таблицы Person.Address в базе данных AdventureWorks на вашем сервере SQL Server 2008.

2. Произведите перестроение всех индексов и обновление статистики в базе данных Foodmart средствами SQL-DMO. Убедитесь, что обновление статистики произведено.

Решение:

К пункту 1 — анализ степени фрагментации:

Соответствующая команда может выглядеть так:

USE AdventureWorks

GO

DBCC SHOWCONTIG ('Person.Address', 'PK_Address_AddressID')

К пункту 2 — перестроение индексов в базе данных:

Соответствующий код на языке VBScript может выглядеть так:

Dim oServer, oDB, oTable

Set oServer = CreateObject("SQLDmo.SqlServer2")

oServer.LoginSecure = True

OServer.Connect

Set oDB = oServer.Databases("AdventureWorks")

For Each oTable In oDB.Tables

If oTable.SystemObject = False Then

OTable.RebuildIndexes

OTable.UpdateStatistics

End if

Next

Для того, чтобы убедиться, что обновление статистики произошло, можно открыть свойства статистики (например, для индекса PK_Address_AddressID), нужно:

1. В SQL Server Management Studio открыть контейнер Statistics - имя_сервера àDatabasesàимя_базы данныхà Tables àимя_таблицыà Statistics,

2. Открыть свойства нужной статистики и на вкладке Generalипросмотреть информацию о дате создания статистики.

Задание для самостоятельной работы 8.5 Управление уровнем блокировок

Задание:

1. Откройте новое окно редактора скриптов и перейдите к базе данных AdventureWorks.

2. Выполните команды

BEGIN TRAN

UPDATE person.address SET City = 'Test'

3. Засеките время выполнения этой команды (показывается в нижнем правом углу SQL Server Management Studio).

4. Затем выполните команду

sp_lock @@SPID

и просмотрите блокировки, которые установлены вашим процессом. SQL Server должен использовать только блокировки уровня объекта (TAB).

5. Выполните команду

Rollback tran

6. Отключите эскалацию блокировок и еще раз выполните команду

BEGIN TRAN

UPDATE person.address SET City = 'Test'

7. Засеките время выполнения этой команды, а затем выполните команду

sp_lock @@SPID

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

8. Выполните команду

Rollback tran

9. Отключите возможность применения блокировок уровня страницы для таблицы Person.Address и еще раз выполните команды

BEGIN TRAN

UPDATE person.address SET City = 'Test'

10. Засеките время выполнения этой команды, а затем выполните команду

sp_lock @@SPID

Хранимая процедура sp_lock должна вернуть только информацию о блокировках уровня записи.

11. Выполните команду

Rollback tran

12. Отключите возможность применения блокировок уровня записи для таблицы Person.Address и еще раз выполните команды

BEGIN TRAN

UPDATE person.address SET City = 'Test'

13. Засеките время выполнения этой команды, а затем выполните команду

sp_lock @@SPID

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

14. Выполните команду

Rollback tran

Решение:

К пункту 6:

Для отключения эскалации блокировок можно выполнить команду