Использование утилиты SQL Profiler

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

Примечание. Перечень основных категорий событий и их основных свойств указаны в прилож.4.

Конфигурирование утилиты SQL Profiler для обнаружения попыток несанкционированного подсоединения к серверу

Запустите программу SQL Profiler (Пуск/Программы/Microsoft SQL Server / Profiler.

В окне утилиты щелкните по кнопке «New Trace» (Новая трассировка), после чего соединитесь с вашим сервером, используя аутентификацию Windows.

Установите параметры трассировки, используя данные табл.3.

Таблица 3

Параметры трассировки для проверки несанкционированных подключений

· Щелкните RUN, чтобы запустить трассировку.

· Запустите SQL Server Query Analyzer .

· Попытайтесь установить соединения с SQL Server с помощью SQL Server Authentication, используя учетную запись GLORIA без пароля.

· Сделайте еще одну попытку, задав другую учетную запись без пароля.

· Переключитесь в окно утилиты SQL Profiler. Опишите в отчете, какая информация появилась в окне трассировки?

· Переключитесь в SQL Server Query Analyzer и установите соединения, используя Windows Authentication.

· Переключитесь в окно утилиты SQL Profiler и исследуйте трассировочную информацию. Какая информация зарегистрирована?

· Остановите и закройте активную трассировку.

Конфигурирование трассировки для регистрации долго выполняющихся запросов

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

· В окне SQL Profiler щелкните по кнопке New Trace, после чего установите соединение с сервером, используя аутентификацию Windows.

· Настройте параметры трассировки в соответствии с табл.4.

Таблица 4

Параметры трассировки для регистрации времени выполнения запросов

· Щелкните RUN, чтобы начать трассировку.

· Переключитесь в SQL Server Query Analyzer.

· Откройте файл Labfiles\L08\OrderWorkload.sql, просмотрите его содержимое, а затем выполните. Этот файл моделирует активность SQL Server с помощью ряда запросов к базе данных Northwindcopy.

· После завершения запросов переключитесь в SQL Profiler и остановите трассировку.

Обратите внимание, что самая долго выполняемая команда SELECT имеет вид:

SELECT c.CompanyName, p.ProductName, SUM(d.Quantity * d.UnitPrice)

FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID

JOIN [Order Details] d ON o.OrderID = d.OrderID

JOIN Products p ON d.ProductID = p.ProductID

WHERE p.ProductID in (5, 11, 12)

GROUP BY c.CompanyName, p.ProductName

· Скопируйте команду запроса из нижней части окна трассировки (эта команда вам пригодится в следующем приложении).

· Закройте SQL Profiler.

3. Использование SQL Server Query Analyzer для анализа долго выполняющихся запросов

Формирование статистических характеристик производительности и графического плана выполнения запроса

· Переключитесь в SQL Server Query Analyzer.

· Откройте новое окно запроса и вставьте в него текст сохраненной в буфере команды долго выполняющегося запроса.

· В списке DB откройте базу данных Northwindcopy/

· В меню Query выберите пункт Current Connection Properties (свойства текущего соединения). В открывшемся окне установите флажки для настройки статистики Set statistics Time (статистика по времени) Set Statistics IO (статистика по количеству операций ввода-вывода).

· Выполните запрос.

· На панели результатов щелкните по вкладке Messages. Скопируйте результаты с нее в ваш отчет. Просмотрите их. Обратите внимание на использование времени процессора и счетчики операций ввода-вывода.

· В меню Query выберите пункт Display Estimated Execution Plan (показать план оценки выполнения запроса).

Сколько сканирований таблиц и индексов использовалось для обработки этого запроса? Что можно сделать для повышения производительности?

Формирование статистики производительности для запроса при наличии индексов

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

· Откройте новое окно запроса в Query Analyzer.

· Вставьте в него текст команды запроса и в списке баз данных установите базу данных Northwind.

· Выполните запрос.

· Просмотрите вкладку Message, Скопируйте текст сообщения в ваш отчет. Сравните его с предыдущим сообщением. Сделайте выводы.

· Просмотрите план выполнения запроса (Query|Display Estimated Execution Plan). Сколько сканирований таблиц и индексов использовались для обработки данного запроса. Что можно сделать для повышения производительности?

· Закройте все открытые окна.

4. Мониторинг сведений о блокировках

В этой процедуре вы будете выполнять несколько транзакций одновременно и отслеживать поведение блокировок SQL Server, используя инструкции языка SQL и окно Current Activity программы SQL Server Enterprise Manager. С помощью Query Analyzer вы будете имитировать активность нескольких клиентов, установив три соединения.

В двух соединениях Вам следует запустить программу, приведенную ниже. Программа выполняет редактирование одной из строк таблицы Employees базы данных Northwindcopy. Данная программа запускает транзакцию, которая умышленно не содержит команды для завершения или отката COMMIT TRAN или ROLLBACK TRAN, чтобы задержать транзакцию на сервере. Для показа идентификатора серверного процесса (spid) для соединения используется системная хранимая процедура sp_lock.

USE NorthwindCopy

BEGIN TRAN

UPDATE Employees

SET LastName = 'SMITH'

WHERE EmployeeID =1

Просмотр информации о блокировках

· Используя утилиту Query Analyzer, установите соединение с аутентификацией Windows (File|Connect…).

· В окне Query Analyzer запустите системную процедуру sp_lock, чтобы просмотреть, какую информацию о блокировках она предоставляет (см. рис. 3).

Рис.3. Информация о блокировках

· Просмотрите таблицу блокировок и зафиксируйте ее данные.

· Выполните системную хранимую процедуру с номерами SPID, которые вы получили на предыдущем шаге, например, так: EXEC sp_who 53

· Зафиксируйте имена пользователей, связанных с данными процессами.

· Запустите программу SQL Server Enterprise Manager, разверните свой сервер, разверните папку Management, в ней рубрику Current Activity, а в ней Locks/Process ID, после чего щелкните по номеру последнего SPID. В окне детализации посмотрите сведения о процессе и типе блокировки.

Удержание блокировки на сервере

· Переключитесь в Query Analyzer, откройте новое соединение (File|Connect…), используя аутентификацию Windows. Первое соединение не закрывайте!

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

Какие типы блокировок связаны с этим процессом? Каков режим блокировки каждого ресурса?

Переключитесь в SQL Server Enterprise Manager, щелкните правой кнопкой мыши по рубрике Current Activity, выполните команду «Обновить». Раскройте рубрику Locks/Process ID, чтобы просмотреть блокировки второго соединения.

Примечание. Описание основных типов блокировок и механизмов их действия находится в прилож.5.