Как не повредить базу данных и что делать, если при работе с MS SQL Server не хватает памяти

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

В этой статье я хочу поделиться некоторыми практическими советами по использованию инструментов для работы с MS SQL Server. Стандартный набор инструментов для MS SQL Server — это SQL Server Management Studio и SQL Server Profiler. Также расскажу об использовании интересной утилиты SQL Query Stress Tool.

Предупреждение: при работе с базами данных есть риск повредить данные, поэтому перед использованием указанных советов обязательно обратитесь за помощью к ментору или более опытным коллегам!

Что делать, если не хватает памяти?

Проблема: иногда из-за большого количества объектов, создаваемых скриптом, не хватает памяти, и SQL Server Management Studio падает. Также дополнительные операции по визуализации / созданию гридов с результатами — это трудоемкий и ресурсоемкий процесс. В целом это существенно замедляет выполнение скрипта.

Решение: переключить в SQL Server Management Studio вывод результатов выполнения запроса в текстовую консоль или в файл (самый быстрый вариант).

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

В режиме вывода с гридами скрипт за 35 минут создал 634 пользователя — и Management Studio закрэшилась.

После перезапуска Management Studio и переключения в режим вывода в файл за 4 минуты 40 секунд были созданы оставшиеся 1366 пользователей (без крэшей и ошибок).

Как не повредить базу данных?

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

Решение: Если нет уверенности, что скрипт, который модифицирует данные (INSERT, UPDATE, DELETE), выполнится успешно — можно для проверки обернуть скрипт в транзакцию с откатом (ROLLBACK). Затем после проверки / отладки запроса можно заменить ROLLBACK на COMMIT и выполнить транзакцию еще раз.

Также важно перед транзакцией указывать SET XACT_ABORT ON. Если не указать эту настройку, то может возникнуть ситуация, что при возникновении ошибки часть транзакции будет применена, а часть отменена:

-- To be sure that data will be rolled back in case of any errors
SET XACT_ABORT ON
-- New transaction
BEGIN TRAN
    -- Count of rows before manipulation
    SELECT COUNT(*) FROM [tTable]
    -- Dataset before manipulation
    SELECT * FROM [tTable]

    -- Some data manipulations
    -- ...

    -- Count of rows after manipulation
    SELECT COUNT(*) FROM [tTable]
    -- Dataset after manipulation
    SELECT * FROM [tTable]
ROLLBACK

Разбор реального кейса

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

При достаточно большом количестве данных запрос, который делал выборку статистики пользователей за 2 месяца, завершался по timeout. Необходимо было сгенерировать большое количество тестовых данных для проверки.

Примечание: скриншоты приведены для иллюстрации процесса, но в целях соблюдения NDA уменьшены, чтобы сделать их нечитабельными.

Шаг 1: определить, где хранятся данные для сбора статистики пользователей системы

Запускаем трассировку в SQL Server Profiler и смотрим запросы, которые выполняются при построении отчета по статистике пользователей системы.

Затем используя SQL Server Management Studio в коде хранимой процедуры находим таблицу, которая хранит данные по статистике.

Шаг 2: написать запрос для вставки одной строки, содержащей тестовые данные

Следующий запрос генерирует случайным образом тестовые данные для идентификатора пользователя в диапазоне 1000-1300 и даты в диапазоне 60 дней от текущей даты:

-- Generate random data for userPropID (1000-1300) and logDate from (Today-60 days) to Today
INSERT INTO [dbo].[tUsageLog]([userPropID], [logDate])
 VALUES (CAST(1000 + RAND() * 300 AS INT), CAST(SYSDATETIME() AS DATETIME) - RAND() * 60);

Шаг 3: сгенерировать массив тестовых данных

Для этих целей можно воспользоваться утилитой SQL Query Stress Tool. Подробный пример использования утилиты можно посмотреть по этой ссылке.

Запускаем SQL Query Stress Tool и подключаемся к базе данных.

Копируем запрос (в примере запрос на вставку повторяется 10 раз), заполняем количество итераций и количество потоков, нажимаем GO.

В результате выполнения генерируется большое количество тестовых данных. В приведенном ниже примере это 10 строк * 10 потоков * 1000 итераций = 100 000 строк тестовых данных.

Шаг 4: сделать тестовые данные более презентабельными

Чтобы сделать тестовые данные более реалистичными, можно удалить часть сгенерированных тестовых данных за выходные дни и нерабочие часы:

-- Remove holidays
DELETE FROM [dbo].[tUsageLog] WHERE [logID] IN
(SELECT TOP 95 PERCENT [logID] FROM [dbo].[tUsageLog]
 WHERE DATEPART(DW, [logDate]) IN (6,7));

-- Remove non-working hours
DELETE FROM [dbo].[tUsageLog] WHERE [logID] IN
(SELECT TOP 95 PERCENT [logID] FROM [dbo].[tUsageLog]
 WHERE DATEPART(HH, [logDate]) < 9 OR DATEPART(HH, [logDate]) > 18);

Результат: