SQL Server Profiler Library

Неделю назад я рассказывал как можно без побочных эффектов использовать SQL-сервер в true unit-tests. Теперь я расскажу где использовал предыдущий пост. Сегодня предлагаю интегрировать в свои продукты средства трассировки и профилирования встроенные в SQL-сервер. И в MSDE 2000, и в SQL Express 2005/2005 в том числе.

Когда речь идет о профилировании часто самое сложное - найти бутылочное горлышко. Хуже всего то, что как правило в среде разработки и тестирования система ведет себя совершенно не так в production environment. Мне в случае проблем с производительностью у какого нибудь заказчика приходится "отмазываться" простым вопросом, на который закзачик ответить толком не может. Я прошу описать в цифрах "профиль загрузки системы". Заказчик на какое-то время впадает в ступор, тем не менее проблема остается - а это не очень приятно.

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

Раньше я рассказывал как бороться с трафиком в на платформе WCF. Сегодня мой подопытный - IOPS. IOPS всегда были бутылочным горлышком в большинстве случаев. Понятно что диск намного медленнее работает чем память и CPU. Хуже другое:

• Масштабирование диска существенно дороже масштабирования RAM и CPU. Для клиент-серверных системы CPU и память наращиваются совсем просто – добавлением в кластер сервера приложений еще одного узла.
• Индустрия и технология производства дисков уверенно хранит показатель IOPS примерно на уровне 10-ти летней давности. Да, емкость и скорость последовательного чтения выросли на два порядка. А сколько вырос IOPS? В три раза? Всего-то :( Конечно последнее справедливо если закрыть глаза на новые SSD накопители с многоканальным контроллером памяти, например Intel X25 G2.

КПРР и структура журнала трассировки

• Текст sql-команды и со значениями всех параметров. Если бы я сейчас рассказывал презентацию, а не щелкал по клавишам, сейчас бы сказал “WOW! Ух-ты!”. Значения параметров сюда попадают ав-то-ма-ти-чес-ки.
• Идентификаторы подключения к SQL-серверу
• Количественные показатели использованных ресурсов: CPU, Duration, Reads, Writes. Вокруг них проходят любые занятия профилированием.

Любые поля журнала можно отключать за ненадобностью, но в библиотеке не предусмотрено отключения “Количественных показателей использованных ресурсов”. Здесь и далее выражение в кавычках - КПРР. Любые поля журнала трассировки можно и нужно отключать в зависимости от сценария использования за ненадобностью.

Идентификация целевой области наблюдения

Сессия трассировки без фильтрации включает все команды всех приложений, исполненных sql сервером за время сеанса трассировки. Так устроен MS SQL Server. Это плохо, хотя иногда и хорошо. Но филтьтр по контексту нагрузки на SQL Server все же есть. И это безулословно хорошая фича. И "на стороне" SQL сервера и на клиенте есть хорошая возможность фильтровать команды на “свои” и “чужие”. Вот параметры фильтра:

• Application Name (определяется строкой подключения к серверу)
• Database (определяется по строке подключения к серверу, фактическая БД с которой работает запрос, к этому полю отношения имеет мало)
• Client Host (обычно на клиенте эта строка равна Environment.MachineName)
• Client Process (на клиенте доступно с помощью Process.GetCurrentProcess().Id)
• Login (определяется строкой подключения к серверу)
• Server Process (на клиенте доступно в виде функции @@spid)

Сценарии использования

• Получить суммы КПРР, сгруппированные по типам удаленных вызовов? Одна сессия трассировки на всю систему. В ApplicationName включить имя action wcf-метода (или Request.Uri web-приложения).
• Получить полную подробную трассировку на каждый экземпляр удаленного вызова? На каждый unit of work отдельная сессия трассировки.
• Получить распределение загрузки ресурсов SQL сервера между своим продуктом и сторонними приложениями? Пожалуйста - единственная сессия трассирови на всю систему. В ApplicationName уникальное и хорошо известный идентификатор своего продукта|системы.
• Получить трассировку на каждую SQL команду? Одна сессия трассировки на каждую команду.

Последний сценарий – самый простой. Последний сценарий дает самый подробный отчет. Почему же нужны еще другие сценарии? Ответ – производительность.

Отчет по журналу трассировки

Раз я заговорил об трасисровке на продакшене, то и в библиотеке реализовал три способа вычитывания отчета трассировки:

• Детальный, как в SQL Server Profiler. Самый полный отчет - весь журнал вычитывается строчка за строчкой.
• Суммы по группам. "КПРР" суммируются по интересным группам.
• Тотальная сумма "КПРР" - количество запросов и четыре суммы использованных ресурсов

Производительность

Я взял в качестве подопытного кода самый легкий запрос: SELECT Null

• Первая колонка – кол-во конкурентных потоков.
• Commands – кол0во выполненных команд
• Duration – время затраченное на выполнение. Оно включает старт и стоп сессий трассировки и не включает время на создание и уничтожение конкурентных потоков.
• CPU – процессорное время использованное на “клиенте”.

Потребление ресурсов самого SQL-сервера здесь не интересно. Интересно другое - я накткулся на форуме на интересный термин профилирования: Оказывается можно различать серверную трассировку, она же real server side trace. И клиентскую трассировку. Под клиентской чувак Tibor (MVP) понимает сеанс трассировки выполняемый под SQL Server Profiler с записью журнала в файл или БД. Вот эта библиотека, о которой я сейчас рассказываю - это real server side tracing.

Два важных вывода:

Сама по себе трассировка  на производительность влияет незначительно. А вот сессия трассировки неприлично тормозит на два порядка в сценарии одна сессия трассировки на один запрос. Но это всего лишь пол беды. Хуже то что пуск и остановка сессии трассировки не масштабируется по ядрам процессора.

Использование RAM-диска драматически улучшает производительность инстанцирования сессии трассировки. Хотя масштабирование по ядрам процессора по прежнему отсутствует.

Дисклаймер юнит-тестов

Тесты проверяют только библиотеку, но не SQL сервер и не трассировку. Поэтому в тестах всего два ценных сценария:

• Ловится вызов хранимки. в unit-тесте проверяется что захвачена именно sp_executesql с нашим кодом. и код И вызов хранимой процедуры и любая команда с параметрами попадают в журнал трассировки.
• Ловится batch-код.

The true about a true unit-testing of Data Access.

Что здесь подразумеваю под true unit testing? То же что и всегда:

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

Любой [Test], не попадающий в это хорошо известное определение, true unit тестом не является. Я с этом полностью согласен. Какие следствия напрашиваются применительно к тестированию доступа к данным и бизнес-логики с зависимостью от MS SQL Сервера:

• Каждый экземпляр теста исполняется в своей песочнице. В нашем случае у каждого экземпляра теста своя уникальная БД.
• Вне зависимости от успешности прохождения тестов ресурсы должны быть освобождены, а изменения должны быть откатаны обратно. В нашем случае каждый экземпляр теста по окончании работы уничтожает свою рабочую БД.

В итоге имеет две проблемы:

1. Выбрать подходящий экземпляр SQL Server. И у разработчиков, и на билдовом сервере имеет место быть зоопарк версий и имен экземпляров SQL серверов. Теоретически можно принудить каждого внутри команды «поднять» экземпляр SQL-сервера с одинаковым именем для работы unit-тестов. Но за пределами команды, в тех же открытых проектах, принуждение маловероятное.
2. Уничтожить рабочую БД. Drop database было бы элементарным решением проблемы освобождения ресурсов, если бы не дефолтовый пулинг в SQL-сервере. Пулинг оставляет открытые соединения с БД – из-за этого Drop database без дополнительных приседаний падает.

Другие проблемы, мешающие воплотить в unit-тестах слоя доступа к данным true-принципы, мне неизвестны.

Voilà

Хочу поделится своей несложным окружением для unit-тестирования доступа к данным и любого кода работы с БД SQL сервер:

• AnySqlServer – подходящий экземпляр SQL Server
• DB – уникальное имя БД-песочницы
• MasterConnectionString – строка подключения к экземпляру SQL-сервера
• DbConnectionString – строка подключения к БД-песочнице
• SetUp() – создать БД-песочницу
• TearDown() – учичтожить БД-песочницу, если остаются незакрытые соедния к БД-песочнице, например соединения в пуле, то подключения обрываются.

  1: static class TestEnvironment
  2: {
  3:     static TestEnvironment()
  4:     {
  5:         var servers = SqlServiceInfo.Get(TimeSpan.FromSeconds(10));
  6:         foreach (var sqlInstance in servers.Instances)
  7:             if (sqlInstance.Status == ServiceControllerStatus.Running)
  8:                 if (sqlInstance.Description != null)
  9:                     if (!sqlInstance.IsExpress)
 10:                         if (SqlServerUtils.IsAdmin(sqlInstance.FullLocalName))
 11:                             AnySqlServer = sqlInstance.FullLocalName;
 12:     }
 13: 
 14:     public static readonly string AnySqlServer;
 15: 
 16:     public static readonly string DB = 
 17:         "UNITEST_" + Guid.NewGuid().ToString("N");
 18: 
 19:     public static string TracePath = 
 20:         Environment.SystemDirectory.Substring(0,2) 
 21:         + @"\\temp\\traces";
 22: 
 23:     public static readonly string WorkingAppicationName = 
 24:         "SqlTrace unit-test";
 25: 
 26:     public static string MasterConnectionString
 27:     {
 28:         get
 29:         {
 30:             return 
 31:                 "Application Name=SQL Unit Testing framework;" 
 32:                 + "Integrated Security=SSPI;" 
 33:                 + "Data Source=" + AnySqlServer + ";" 
 34:                 + "Pooling=false;";
 35:         }
 36:     }
 37: 
 38:     public static string DbConnectionString
 39:     {
 40:         get
 41:         {
 42:             return 
 43:                 "Application Name=" + WorkingAppicationName + ";" + 
 44:                 "Integrated Security=SSPI;" 
 45:                 + "Data Source=" + AnySqlServer + ";" 
 46:                 + "Pooling=true;"
 47:                 + "Initial Catalog=" + DB + ";"
 48:                 + "Max Pool Size=300";
 49:         }
 50:     }
 51: 
 52:     public static void SetUp()
 53:     {
 54:         Trace.WriteLine(
 55:             "Working SQL Server instance is " + AnySqlServer);
 56: 
 57:         using (var con = new SqlConnection(MasterConnectionString))
 58:         {
 59:             con.Open();
 60: 
 61:             var sql = "Create Database [" + DB + "]";
 62:             using (var cmd = new SqlCommand(sql, con))
 63:             {
 64:                 cmd.ExecuteNonQuery();
 65:             }
 66:         }
 67:     }
 68: 
 69:     public static void TearDown()
 70:     {
 71:         try
 72:         {
 73:             List<SqlServerUtils.ConnectionInfo> connections;
 74:             using (var con = new SqlConnection(MasterConnectionString))
 75:             {
 76:                 connections =
 77:                     SqlServerUtils.GetConnections(con)
 78:                         .FindAll(info => info.Database == DB && info.Spid > 50);
 79:             }
 80: 
 81:             SqlServerUtils.KillConnections(MasterConnectionString, connections);
 82: 
 83:             using (var con = new SqlConnection(MasterConnectionString))
 84:             using (var cmd = new SqlCommand("Drop Database " + DB, con))
 85:             {
 86:                 con.Open();
 87:                 cmd.ExecuteNonQuery();
 88:             }
 89:         }
 90:         catch(Exception ex)
 91:         {
 92:             Trace.WriteLine(
 93:                 "Failed to teardown unit test" + Environment.NewLine + ex);
 94:         }
 95:     }
 96: }

Подброр подходящего сервиса

Хочу отдельно, построчно, остановится на отборе подходящего экземпляра SQL-сервера:

• строка 7: Экземпляр windows-сервиса сервера должен быть в состоянии запущен :)
• строка 8: windows-аккаунт, под которым исполняется код (тест-кейс), должен иметь минимальные права на подключение к sql-серверу. Здесь Description - это значение функции @@version
• строка 9: MSDE 2000, SQL Express 2005/2008 не подходят - такой вот тест
• строка 10: windows-аккаунт, под которым исполняется код (тест-кейс), должен входить в группу sysadmin сервера

В вледующей серии - профилирование т.н. слоя доступа к данным в хранилище MS SQL Server.

Как работает SqlServiceInfo.Get(…)

1. В реестре, в строгом соответствии с официальной документацией Books Online выискиваются все описания сервисов всех экземпляров SQL Server: File Locations for Default and Named Instances of SQL Server
2. Далее тестируется каждый кандидат в своем рабочем потоке средствами пула потоков. Каждый кандидат проверяется на то что это зарегистрированный Win32-сервис Database Engine
3. У каждого экземпляра Database Engine определяется версия исполнительного файла и статус win32-сервиса
4. Каждый экземпляр тестируется на доступ с минимальными правами при подключении с помощью ADO.NET.