(Возврат на основную страницу)
Брэд М. МакГи (Brad M. McGehee)
В этом эксклюзивном интервью Фернандо Гуэреро (Fernando Guerrero), технологический директор и консультант по SQL Server ведущей британской компании в области ИТ-обучения QA, на основе собственного опыта и знаний рассказывает, как «выжать» максимум производительности из связки «ADO .NET+SQL Server».
Фернандо Гуэреро, обладающий званием SQL Server MVP (Most Valuable Professional, самый ценный профессионал), является автором книги «Microsoft SQL Server 2000 Programming», вышедшей в издательстве Que, и сейчас работает над книгой об ADO.NET и SQL Server для издательства Apress. Гуэреро также пишет статьи для журнала SQL Server Magazine и неодно-кратно выступал с докладами по SQL Server на конференциях SQL Pass, TechEd, VBUG и VBIT.
БМ: Разработчики приложений, использующих SQL Server в качестве серверной СУБД, всегда заинтересованы в максимальной производительности SQL Server. Что нового предлагает ADO.NET разработчикам для достижения такой производительности?
ФГ: Поставщик данных SQL Server .NET подключается непосредственно к SQL Server при помощи TDS-потока (Tabular Data Stream, поток табличных данных). Это «родной» формат пакетов, используемый SQL Server, и он не требует подключения через другие уровни, например ODBC или OLEDB. Программисты привыкли, что самый быстрый способ подключиться к SQL Server — «родное» обращение к ODBC из C++. Но теперь на сцене появился новый персонаж: поставщик данных SQL Server .NET.
Рекомендую вам выучить все, что можно, о классе SQL-Command и взаимодействии с хранимыми процедурами и определяемыми пользователем функциями при помощи объектов SQLCommand. Использование класса SQLDataReader и объектов SQLParameter будет способствовать успеху вашего приложения.
БМ: Что разработчикам придется «забыть» и «выучить» при переходе с ADO на ADO.NET?
ФГ: Полагаю, самая трудная для изучения концепция — разница между подсоединенным и отсоединенным уровнями. С точки зрения ADODB, объект Recordset когда-то был чем-то, подключенным к БД; объект же DataSet не знает, откуда поступают данные. Различия настолько сильны, что у объекта DataSet нет даже свойств и методов для подключения к БД. Разработчики, привыкшие к отсоединенной модели, смогут без проблем адаптироваться к новым правилам. Программистам, использующим серверные курсоры и пессимистичное наложение блокировок, придется труднее.
ADO.NET позволяет разделить две роли программи-ста. Клиентский уровень взаимодействует с данными, зная очень мало или не имея сведений о реализации серверного уровня. Программисты промежуточного уровня будут выполнять от имени клиентских приложений подсоединенные операции, например заполнение объекта DataSet или обновление серверной БД в соответствии с правилами для объекта DataSet.
Но придется забыть о концепции «просмотра» — в ADO.NET нет методов типа MoveNext, MovePrevious и др. Со времени dBase эти операции были одной из составляющих жизни программиста, однако теперь нам придется научиться взаимодействовать с «наборами» записей.
В ADO.NET реализована широкая поддержка XML, и к моменту выхода SQLXML 3.0 программистам следует получить максимально возможный объем знаний об XML. Чтобы стать хорошим разработчиком БД, необязательно быть экспертом по XML, однако это не только не помешает, но и даст вам значительные преимущества.
БМ: Что должен знать администратор БД (DBA) об ADO.NET, чтобы получить от использования этой технологии максимум производительности?
ФГ: Администраторам следует тесно пообщаться с программистами БД промежуточного уровня и выяснить, как последним требуется получать и обновлять данные SQL Server, чтобы можно было создавать для них наиболее эффективные стратегии индексирования и наложения блокировок, а также определяемые пользователем функции и хранимые процедуры. Утилита SQL Server Profiler — всегда хороший друг серьезного администратора, и в этой ситуации она поможет вам глубже понять принципы взаимодействия ADO.NET и SQL Server.
Иногда одним из интересных способов повышения производительности может стать создание индексированных представлений, и здесь вам поможет мастер Index Tuning Wizard.
Я действительно считаю, что любой администратор БД, желающий «выжить» на этом динамичном рынке, должен знать об ADO.NET как можно больше. И, учитывая возможности работы с этой технологией, которые будут реализованы в следующей версии SQL Server, наличие таких знаний становится все более важным.
БМ: Назовите самые распространенные ошибки разработчиков, отрицательно влияющие на производительность приложений на основе SQL Server? Как их избежать?
ФГ: Думаю, самая большая ошибка разработчика при взаимодействии с SQL Server — синдром «SELECT * FROM myTable». Если раздела WHERE нет, это «дурно пахнет». А «*» возвращает слишком много данных. Разработчик должен получать только те поля тех записей, которые необходимы ему в конкретный момент времени. Еще одна значительная проблема — непонимание стратегий JOIN. Меня всегда удивляло чрезмерное использование соединения LEFT OUTER JOIN. Когда я вижу его, я всегда спрашиваю разработчика: «Это точно то, что вы хотите получить?». А что говорить о неправильно построенных запросах, приводящих к неожиданным запросам с соединениями CROSS JOIN? Для начала таким специалистам следовало бы хорошо разобраться в языке SQL.
Еще одна распространенная проблема — использование дорогостоящих серверных курсоров, которое исключает эффективную работу с наборами; часто это обусловлено нехваткой знаний о принципах работы реляционных БД. В любой процедуре с курсором Transact-SQL я пытаюсь заменить его более эффективными и простыми операциями с наборами. За последние 9 лет работы с SQL Server мне встретилось очень немного ситуаций, в которых единственным решением проблемы было использование курсоров.
Проблемы параллелизма — еще один стандартный источник проблем «производительности». Вы бы удивились, узнав, сколько времени уходит у соединения только на ожидание снятия блокировки с занятых ресурсов. Пользователь считает такое ожидание проблемой производительности, хотя на самом деле процессор сервера практически бездействует и просто ожидает снятия блокировок. Ключ к решению данной проблемы — выбор правильного уровня изоляции.
Также разработчики часто уделяют недостаточно внимания проблемам безопасности. «Если это выполняется не под учетной записью sa, оно вообще не будет работать». Слышали когда-нибудь такое утверждение? Сколько приложений подключаются к БД SQL Server под учетной записью sa и без пароля… Хакеры прекрасно это знают, и весьма рады широкой распространенности подобной практики.
Однако самая большая общая ошибка — нехватка знаний о данных, их структуре и о том, как ваша конкретная СУБД управляет этими данными. Я уже говорил, что администратор должен знать принципы работы ADO.NET. Исходя из тех же соображений, программист клиентской части или промежуточного уровня должен знать как можно больше о базах данных.
БМ: Дайте несколько советов, как добиться максимальной производительности от приложения и SQL Server при использовании ADO.NET?
ФГ: Упомянуть здесь все решения будет сложно, и поэтому я расскажу лишь о нескольких.
Подключайтесь к SQL Server с использованием поставщика данных SQL Server .NET. Этот простой совет поможет вам значительно повысить производительность. Для подключения к другим источникам данных используйте более универсальный поставщик, например OLE DB .NET, но помните: что вы выиграете в гибкости, то потеряете в производительности.
При работе с объектами SQLCommand старайтесь как можно чаще использовать метод ExecuteNonQuery — это самый эффективный способ выполнять запросы из ADO.NET. Если вам требуется получить несколько значений или отдельную запись, используйте параметры вывода объектов SQLCommand, а не более дорогостоящие методы, такие как объекты SQLDataAdapter, SQLDataReader или объекты DataSet со строгим контролем типов.
Рассматривайте объект DataSet только как МИНИ-БД, определенную в виде НЕБОЛЬШОГО набора связанных данных, служащего определенной цели. Если в команде, заполняющей объект DataSet, нет раздела WHERE, дважды подумайте, стоит ли ее выполнять. Данные придется передавать с сервера по сети клиенту, а полоса пропускания всегда ограничена. Чем больше данных вы получаете, тем больше нагрузка на сервер БД и тем больше возникает проблем параллелизма.
«Получайте поздно, освобождайте рано» или «Подключайтесь как можно позднее, считывайте как можно скорее и как можно быстрее отсоединяйтесь».
БМ: Расскажите немного о том, как новый способ «родного» доступа к SQL Server посредством TDS-потоков повышает производительность. Почему этого способа не было в предыдущих версиях ADO?
ФГ: Прямой TDS-доступ реализован в предыдущих технологиях доступа к данным: как в ODBC, так и в OLE DB. Поставщик OLE DB Provider for SQL Server и ODBC-драйвер SQL Server — это библиотеки, предназначенные для естественного взаимодействия с SQL Server посредством TDS-потоков, способа, который SQL Server внутренне использует для взаимодействия с окружающим миром. Проблема заключалась в том, что приходилось программировать непосредственно для поставщика OLE DB Provider for SQL Server (SQLOLEDB) или ODBC-драйвера, которые создавались программистами на C++ для программистов на C++. Использовать RDO или ADO было проще, но тут возникал лишний уровень, создававший дополнительную нагрузку и снижавший производительность. Однако и сейчас многие программисты предпочитают более простой интерфейс (ADO) производительности (SQLOLEDB).
Кроме того, новый поставщик выполняется в управляемом коде, что расширяет возможности контроля кода, снижает утечки памяти и улучшает выявление ошибок.
Еще одно преимущество нового поставщика — то, что группа разработки методов доступа к данным была частью команды разработчиков SQL Server, т. е. обе команды смогли глубже понять, как оптимизировать доступ к SQL Server из клиентского приложения.
Если кому-то интересны отличия принципов работы нового поставщика, выполните с помощью утилиты Profiler трассировку поступающих на SQL Server данных и попытайтесь выполнить несколько операций, используя ADODB и поставщик SQL Server .NET. Вы увидите, что команды преобразуются в язык Transact-SQL более естественным образом, и для DBA не возникает изумляющих моментов.
БМ: Как осуществляется управление пулом соединений в ADO.NET? Каковы доводы «за» и «против» пула соединений?
ФГ: Управление пулом соединений осуществляется в ADO.NET практически так же, как в OLE DB. Использование пула заметно снижает нагрузку по созданию и уничтожению подключений в SQL Server. При условии, что контекст безопасности не меняется, каждая строка подключения создает новый пул соединений, и когда клиент завершает соединение, .NET- приложение сохраняет этот пул, т. е. данное конкретное соединение остается подключенным к SQL Server для дальнейшего использования.
Этот способ организации соединений не создает особых проблем, однако программистам следует быть очень осторожными с кодом завершения. Новому пользователю данного конкретного соединения будут доступны любой временный объект или незавершенная транзакция, что может привести к неожиданным результатам. Итак, перед отсоединением из ADO.NET не забудьте удалить все созданные временные объекты и должным образом завершить все транзакции, поскольку отсоединение необязательно означает, что SQL Server закроет подключение.
Использование пула соединений значительно повышает масштабируемость приложений, открывающих в разное время множество подключений к SQL Server. Типичным примером здесь будут Web-приложения. Тем не менее, чтобы задействовать в них пул соединений, потребуется решить некоторые вопросы безопасности. Если используются средства проверки подлинности SQL Server или Windows, для каждого сочетания «строка подключения + контекст безопасности» будет создаваться собственный пул соединений, т. е. использование пула соединений становится практически бессмысленным. Основа управления пулом соединений — высокая вероятность при открытии нового подключения получить соединение непо-средственно из пула, не предлагая SQL Server создать новое соединение.
Решением этой проблемы может стать реализация какой-нибудь нестандартной системы проверки подлинности, при которой доступ к SQL Server будет осуществляться через ограниченное число предопределенных ролей/учетных записей и которая позволит ограничить число пулов соединений и не повысить коэффициент повторного использования открытых соединений.
Помните также, что пул соединений несовместим с прикладными ролями SQL Server.
Если вы решили использовать пул соединений в компонентах промежуточного уровня, лучше будет воспользоваться возможностями пула объектов COM+, который, с точки зрения доступа к БД, предоставляет ту же функциональность, что и пул соединений.
И чтобы прояснить распространенное недопонимание, скажу: пул соединений не имеет ничего общего с SQL Server. Иначе говоря, SQL Server не подозревает о существовании пула соединений. SQL Server видит соединения, организованы они в пул или нет, и точка.
БМ: Как проще всего перейти на ADO.NET? Назовите полезные для изучения ADO.NET ресурсы.
ФГ: Документация ADO.NET Framework содержит исчерпывающую информацию для знакомства с имеющимися классами, методами и свойствами. Тем не менее примеры выполнения тех или иных действий в ней практически отсутствуют. Рекомендую вам изучить примеры, устанавливаемые вместе с .NET Frame-work в папку samples (расположение по умолчанию — C:\Program Files\Microsoft.NET\FrameworkSDK\Samp-les\QuickStart\howto\samples). Там имеется множество отличных примеров использования ADO.NET.
Еще один хороший ресурс — официальная группа новостей ADO.NET: News://msnews.microsoft.com/microsoft.public.dotnet. framework.adonet
Web-узел GotDotNet (http://www.gotdotnet.com) — неплохой ресурс по .NET в целом.
На Web-узле DevX по адресу http://www.devx.com/db-zone/defaultsql.asp можно найти статьи по ADO.NET.
Использованию и переходу на ADO.NET посвящено множество книг, и я уверен, что еще больше книг вый-дет в скором времени. Не буду упоминать здесь какое-то конкретное издание, но думаю, все согласятся, что Билл Вон (Bill Vaughn) — компетентный специалист в этой области.
Сертифицированные учебные центры Microsoft предлагают отличный курс по этой теме: MOC 2389: Prog-ramming with ADO.NET (http://www.microsoft.com/TRAINCERT/SYLLABI/2389BFINAL.ASP).
БМ: В дополнение ко всему вышесказанному, есть ли у вас какие-нибудь еще комментарии и предложения по достижению максимальной производительности в связке «ADO.NET + SQL Server»?
ФГ: Хочу еще раз повторить, что разработчики на ADO.NET должны знать как можно больше о SQL Server, а администраторы БД SQL Server — как можно больше об ADO.NET. Только взаимопонимание позволит нам создавать эффективные приложения для работы с БД, соответствующие потребностям наших пользователей. Итак, разработчики на .NET — начинайте мыслить категориями Transact-SQL, а DBA — уделите время знакомству с миром .NET.
Все о чем я говорил выше, относится к подсоединенному уровню, поскольку принципы работы объекта DataSet не зависят каким-либо образом от SQL Server. Разработчики пользовательского интерфейса могут сконцентрироваться на отсоединенном уровне и обеспечить пользователю удобство работы; знать что-либо о принципах работы SQL Server этим разработчикам не требуется. Тем не менее способ заполнения объекта DataSet данными и способ внесения изменений из объекта DataSet в БД SQL Server целиком связаны с подсоединенным уровнем, и именно к ним относятся все мои комментарии.
Эдвард Уолен (Edward Whalen), Performance Tuning Corporation
Часть самых распространенных проблем производительности SQL Server связана с подсистемой ввода — вывода.
Поскольку основная функция SQL Server — манипулирование данными, находящимися в памяти или подсистеме ввода — вывода, любые проблемы ввода — вывода равносильны проблемам производительности SQL Server. Структура СУБД SQL Server проектировалась для максимально эффективной работы с данной подсистемой.
В статье обсуждаются настройка и основополагающие концепции подсистем ввода — вывода. Разобравшись с их ограничениями, вы сможете проектировать и определять для них подходящий размер для достижения оптимальной производительности. Сначала мы рассмотрим ограничения и принципы работы жесткого диска. Далее я опишу подсистемы RAID и дам рекомендации по их оптимизации и настройке. Затем мы обсудим более совершенные подсистемы ввода — вывода, такие как сети SAN и NAS.
Понимание взаимодействия SQL Server и подсистемы ввода — вывода расширит ваши возможности в плане оптимизации этой подсистемы. Правильно настроенная подсистема ввода — вывода обеспечит оптимальную работу SQL Server. Некорректно настроенная подсистема легко может стать узким местом и сильно снизить производительность.
Почему так важна производительность ввода — вывода?
Производительность подсистемы ввода — вывода — ключ к производительности SQL Server. Более того, производительность чтения критична, а она играет не столь значительную роль в производительности этой СУРБД. Когда выполняется запрос, для получения от системы нужных данных пользователю приходится ожидать завершения операций чтения. При изменениях БД блок отложенной записи заносит новые данные в БД через некоторое время, и поэтому, хотя производительность записи и важна, пользователям никогда не приходится ожидать завершения операций записи (за исключением записи в журнал транзакций).
Используем преимущества SQLXML в приложениях на ASP.NET
Рэнди Холоуэй (Randy Holloway)
Web-службы на основе SQLXML позволяют напрямую обращаться к XML-данным из приложений на ASP.NET.
SQLXML — это пакет дополнительных утилит, расширяющий имеющуюся в SQL Server поддержку работы с XML-данными. С появлением SQLXML версии 3.0 стало возможно создавать Web-службы, вза- имодействующие с SQL Server. Web-службы на основе SQLXML позволяют выполнять хранимые процедуры и пользовательские функции, а также поддерживают шаблоны.
По сравнению со специальными Web-службами для доступа к данным, возможности Web-служб на основе SQLXML в плане гибкости и переносимости не так уж и велики, однако они идеальны для быстрой разработки приложений, поскольку их можно предоставлять без создания какого-либо кода! Кроме того, данная те-хнология хорошо подходит для публикации отчетов в Web. Код клиентского приложения, использующего Web-службы на основе SQLXML, может быть очень небольшим. Затем можно задать вид, отформатировать или проанализировать набор результатов.
Здесь рассказывается, как предоставить хранимую процедуру в виде Web-службы и создать простой ASP.NET-клиент на основе Web Form для доступа и проверки этой службы.
Вам потребуются навыки работы с SQL Server 2000 и IIS, включая конфигурирование виртуального каталога в IIS и назначение разрешений пользователям в SQL Server 2000. Подробнее об этом — в SQL Server Books Online и документации SQLXML 3.0. Для работы необходимы экземпляр SQL Server 2000, компьютер Windows 2000 Server с установленными IIS, анализатор MSXML 4.0 компании Microsoft, пакет SQLXML 3.0, а также среда разработки Visual Studio .NET (VS.NET).
Действительно ли нужна временная таблица
Рэнди Дайесс (Randy Dyess)
Есть много статей, советующих избегать использования временных таблиц для повышения производительности запросов. Я согласен, но хотел бы добавить, что иногда этого не избежать.
работая с очень большими БД SQL Server (объем самой большой из них превышает 2.2 терабайт), я обнаружил, что чаще всего могу избежать использования временных таблиц, но иногда они бывают очень удобными. В этой статье мы обсудим использование временных таблиц и его альтернативы с точки зрения быстродействия запросов и трудоемкости их сопровождения.
Большинство источников, отговаривающих от использования временных таблиц, совершенно верно утверждают, что это приводит к проблемам с производительностью. Проблемы возникают из-за блокировки БД tempdb во время создания временной таблицы и операций ввода — вывода при работе с ней. Кроме того, причиной снижения производительности может стать потенциально возможное блокирование tempdb, если для создания временной таблицы и последующих операций с ней используются транзакции, не говоря уже о многочисленных проблемах, возникающих у SQL Server при работе с временными таблицами. Не отрицая этого, я собираюсь рассказать о причинах, побуждающих к использованию временных таблиц. Признаюсь сразу, что не нашел ни одной причины для использования глобальной временной таблицы и сам ее не использовал, поэтому ее мы обсуждать не будем.
Рэнди Дайесс (Randy Dyess)
Перекомпиляция хранимых процедур — это один из моментов, о которых чаще всего забывают при оптимизации работы SQL Server и программ на Transact-SQL.
С недавних пор я стал уделять внимание
этому вопросу в компании, где работаю, а также при анализе хранимых процедур,
предоставленных другими клиентами. При этом я заметил, что у разработчиков про-
грамм на языке SQL возникают трудности, если они не учитывают частоту
перекомпиляции создаваемых ими программ. Во время перекомпиляции хранимая
процедура устанавливает на объектах, на которые она ссылается, блокировку
compile. Если происходит перекомпиляция достаточного числа хранимых процедур,
работа БД может быть заблокирована. Перекомпиляция хранимых процедур происходит
в любой БД; это обычное явление во время ее работы. Но если данная процедура
компилируется при каждом запуске, администратор БД или разработчик программ на
Transact-SQL должен найти решение, которое позволит избежать перекомпиляции.
База данных, в которой происходит перекомпиляция тысяч хранимых процедур,
постоянно страдает от потерь производительности и кратковременных блокировок,
что негативно отражается на ее пользователях. Поскольку у оптимизатора запросов
SQL Server 2000 на самом деле существенно повышена способность к повторному
использованию кэшированных планов исполнения, в SQL Server 2000 возникает лишь
малая часть подобных проблем.
Рэнди Дайесс (Randy Dyess)
Последние несколько лет я был странствующим администратором БД SQL Server и пытался наработать контакты в небольших и средних компаниях, что включало организацию, документирование и настройку/оптимизацию имеющихся там установок SQL Server. За это время мной был замечен один беспокоящий факт: никто ничего не документировал. Я рассыпался в благодарностях, если находил человека, знавшего пароли sa для всех установок SQL Server, не говоря уже о тех, кто знал текущие параметры настройки СУБД.
Меня часто спрашивали: как я могу прийти в компанию с 50 и более БД SQL Server, выполняющихся на сотне и более серверов, где нет никакой документации, нет администратора БД, работающего полный день, или где его заменяет разработчик или менеджер, и за очень короткое время привести все это в более или менее приемлемое состояние? Мой ответ — все дело в практике, и пособие по хранимым процедурам, написанное мной за несколько лет, позволяет мне быстро провести аудит установок и БД SQL Server и получить информацию для дальнейшей работы.
Эта статья открывает серию публикаций, посвященных вышеупомянутым хранимым процедурам и освоенным мной методам работы. При переходе на новое место или работе над новым проектом они помогут вам создать и изучить новую рабочую среду.
(Возврат на основную страницу)