SQL Февраль 2010

Перевод приложений в среду SQL Server 2008, инструменты и подходы. Часть 2

Оптимизатор запросов: оптимизация корреляции дат

Майкл Оти (Michael Otey)

Несмотря на то что SQL Server 2008 R2 является промежуточным выпуском, Microsoft внесла немало значительных изменений в версию R2. Хотя большинство изменений, подобных PowerPivot, относятся к сфере бизнес-аналитики, R2 получила и ряд существенных изменений из других областей, в частности, новые редакции SQL Server 2008 R2, посредством которых SQL Server начинает лучше соответствовать редакциям операционной системы Windows Server.

Что важно отметить, в отличие от других версий R2, выпущенных в последнее время, SQL Server 2008 R2 попрежнему поддерживает 32разрядную x86 платформу. Давайте посмотрим, какие семь редакций предлагают для SQL Server 2008 R2. (Стоимость лицензий указана для американского рынка. Стоимость лицензий на территории России может отличаться. — Прим. ред.)

Одно из наиболее существенных нововведений — редакция, предназначенная для работы в массивно параллельной среде (Parallel Computing edition). До выхода в свет проект назывался Madison. Эта редакция может считаться эквивалентной Windows Server High Performance Computing (HPC) редакции. Редакция SQL Server использует массивно параллельное оборудование (Massively Parallel Processing (MPP)) с горизонтально масштабируемой архитектурой и поддерживает хранилища данных размером от 10 терабайт до 1 петабайта. Эта редакция SQL Server стоит $57489 для процессорной лицензии. (Число процессоров определяется по числу физических гнезд socket для установки чипа. Число ядер на чипе не играет роли. — Прим. ред.)

Другое несомненно важное изменение — введение новой редакции SQL Server 2008 R2 Datacenter. Эта редакция идет параллельно с Windows Server Datacenter Edition, и способна поддерживать до 256 логических процессоров. Она предназначена для решения наиболее крупных задач. Редакция Datacenter не имеет ограничений по используемой памяти (ограничения налагаются только возможностями операционной системы, которая в 64разрядном варианте способна использовать до 2 терабайт оперативной памяти http://www.microsoft.com/windowsserver2008/en/us/r2comparespecs.aspx. — Прим. ред.) и поддерживает до 25 экземпляров на одном сервере. Стоимость этой редакции также составляет $57489 для процессорной лицензии.

Наиболее распространенной и наиболее востребованной является Enterprise редакция. Она поддерживает до 64 логических процессоров 25 экземпляров, что не сильно отличается от текущих возможностей. Стоимость процессорной лицензии для этой редакции составляет $28749. Редакция Developer обеспечивает поддержку всей функциональности редакции Enterprise и стоит $50 на разработчика.

Стандартная редакция ориентирована на средние и малые организации, поддерживает до четырех процессоров и до 2 Tб памяти, не имеет ограничений на размер базы данных. Редакция поддерживает все BI подсистемы, включая Analysis Services, Integration Services и Reporting Services, но не всю функциональность Enterprise редакции. (В версии R2 в стандартную редакцию включена поддержка сжатия резервных копий. — Прим. ред.) Процессорная лицензия стоит $7499.

Эта редакция предназначена для использования вебпровайдерами. Редакция поддерживает до четырех процессоров, до 2 Tб памяти и не имеет ограничений на размер базы данных. Помимо реляционного движка редакция поддерживает Reporting Services. Лицензирование стоит $15 на процессор в месяц.

Эта редакция ориентирована на работу в рамках отдела или отделения организации. Поддерживает до двух процессоров, до 4 Гб памяти и не имеет ограничений на размеры базы данных. Из BI функциональности поддерживаются только Reporting Services. Стоимость $3899 на процессор.

Эта редакция, предназначенная для разработчиков и компаний, встраивающих реляционный движок в свои приложения, имеет три подверсии. Так, SQL Server 2008 R2 Express предоставляет только сам процессор данных; подверсия SQL Server 2008 R2 Express with Tools добавляет к нему SQL Server Management Studio; а SQL Server 2008 R2 Express with Advanced Services имеет еще и локальную версию Reporting Services. Все варианты SQL Server 2008 R2 Express редакции попрежнему остаются в свободном доступе.

Г. Виджьякумар (G. Vijayakumar)

Когда я только начал работать с SQL Server, то на первых порах не пользовался утилитой «Show Execution Plan» (отображение планов выполнения), чтобы должным образом проанализировать запрос. Всегда был уверен: если я составил запрос, то сделал это настолько хорошо, что лучше уже быть не может. Я даже никогда не задумывался о производительности запроса и не придавал особого значения знакомству с содержимым «плана выполнения» запроса. В начале своей карьеры я был вполне доволен уже тем, что мог извлечь данные, без какого-либо представления о том, каким образом мне их вернули и какого сорта «план выполнения» был использован для запроса. Считал, что с производительностью запроса должно было разбираться программное обеспечение SQL Server. Полагаю, для специалистов в области программного обеспечения, в начале их профессиональной деятельности, или когда они только приступают к освоению новой технологии, естественно не тратить время на изучение всего того, что им действительно необходимо знать прежде, чем браться за написание программного кода. Возможно, это объясняется тем, что ИТ — еще не вполне сформировавшаяся сфера деятельности с высокой конкуренцией.

Шло время, и данных в файле БД становилось все больше и больше. В один прекрасный день мой заказчик, работая с приложением, оказался неудовлетворен производительностью запроса. С недовольной миной он пришел ко мне и заявил, что вынужден был задержаться в офисе после окончания рабочего дня, чтобы завершить выполнение своих должностных обязанностей, поскольку запрос выполнялся слишком медленно. Поначалу я посоветовал ему нарастить системные ресурсы, например, как временное решение, увеличить объем жесткого диска в компьютере. Хотя стоимость жестких дисков не велика, однако это — сиюминутное решение проблемы снижения производительности при выполнении запроса. Заказчик наполовину согласился с тем, что со своей стороны сделает все, что требуется, но попросил меня заново проанализировать и настроить запросы, дабы решить проблему раз и навсегда, вместо того, чтобы предлагать постоянное наращивание системных ресурсов. Я обязан считаться с личным мнением заказчика, поскольку в такой индустрии, как ИТ, удовлетворенность заказчика является важным фактором. Обещал ему еще раз проанализировать и тщательно отрегулировать запросы.

Перевод приложений в среду SQL Server 2008, инструменты и подходы. Часть 2*

Дмитрий Артемов

*См. Дмитрий Артемов. Перевод приложений в среду SQL Server 2008, инструменты и подходы. Часть 1 // SQL Server для профессионалов. 2010. № 1.

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

В первую очередь стоит сказать, что я не рекомендую даже рассматривать серверы на базе x86 процессоров. Даже если у вас сейчас стоит 32 разрядная версия SQL Server 2000/2005, и вы собираетесь обновить железо, то оно должно быть только 64 битное (x64). Хотя бы потому, что в режиме Windows on Windows (WOW) 32 разрядное приложение получает полноценные 4 Гб памяти по сравнению с 1,7 Гб в 32 разрядной среде (если вы не используете переключатель 3 Гб).

Дисковая подсистема — это существенная часть вашей производительности. Даже если вся БД умещается в памяти, SQL Server обязан писать журнал транзакций, когда скорость записи недостаточно велика, то сервер начинает приостанавливать основную работу для обеспечения гарантированной записи изменений в журнал транзакций. Поэтому стойка должна отвечать самым высоким требованиям. Рекомендуемое время отклика для дисковой группы, на которой расположены файлы баз данных, составляет 10 мсек и менее в идеале и 20 мсек считается плохим, но терпимым результатом. Для журнала транзакций требования еще более жесткие и составляют 1–5 мсек. Рекомендуется форматировать стойку с размером блока 64 К, и перед форматированием обязательно выполните выравнивание секторов средствами DISKPART.EXE. Если вы уже перешли на работу в среде Windows Server 2008, то ручное выравнивание более не требуется, операционная система делает это автоматически. (См. http://technet.microsoft.com/ruru/library/bb738145.aspx. Там речь идет про Exchange, но это не важно.) Кроме того, имеет смысл проанализировать схему разбиения стойки с точки зрения выделения физических дисков серверам и взаимосвязь между набором дисковых шпинделей логическим дискам.

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

Так, конфигурация на рис. 1 может оказаться менее оптимальной, так как мы искусственно ограничиваем LUN 1–3 работой на шести шпинделях.

Тогда как конфигурация на рис. 2 использует все физические диски и лучше балансирует нагрузку. Чего не стоит делать, это разбивать диски таким образом, что кусок одного физического диска оказывается отдан различным задачам. Если вам нужна производительность, то физические диски должны быть выделены только под работу SQL Server.

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

Теперь давайте посчитаем. У нас есть как минимум рабочая БД и Tempdb. Системные базы данных можно в расчет не принимать. Т. е. у нас есть два журнала транзакций и как минимум два файла данных. Соответственно, следует формировать три файловые группы. Одну под данные Tempdb и рабочей БД, две другие под журналы транзакций. Почему так? Упомянутая выше рекомендация совершенно правильно строится на том факте, что доступ к базе данных обычно выполняется случайным образом, поэтому мы можем положить оба MDF файла на один набор шпинделей.

А вот журнал транзакций всегда пишется последовательно. Поэтому каждому журналу нужно отвести собственный набор физических дисков. Иначе, как только вы поместите оба журнала на один набор дисков о последовательном доступе можно забыть. Если упомянутые три набора складываются из достаточного числа дисков, чтобы обеспечить необходимое количество операций ввода\вывода, применяйте рекомендацию. Если нет, сделайте один большой массив и положите все туда. Да, никогда не используйте каталоги, предлагаемые программой установки по умолчанию. В противном случае, вы получите Tempdb, лежащую на системном диске, который, скорее всего, использует зеркало, в самом сервере и никакой производительности обеспечить не может.

Она не очень много может нам дать, так как SQL OS — собственная «операционная система» внутри SQL Server почти все берет на себя вплоть до диспетчирования процессов, управления памятью, процессором… Но коекакие настройки могут помочь:

File/Print Sharing: maximize data throughput for network applications. Настраивается через свойства My Network Places > Properties. Для основного сетевого соединения, используемого SQL server –> Properties. File and Print Sharing for Microsoft Networks — Properties. Должен быть выделен переключатель «optimize data throughput for network applications».

Свойства объекта My Computer –> Properties –> Advanced –> Performance –> Settings –> Advanced — должен быть выделен переключатель «Processor Scheduling: adjust for best performance of Background Services».

На той же вкладке диалогового окна должен быть выделен переключатель «Memory Usage: Adjust for best performance of Programs».

Еще одна засада ожидает нас после установки Windows Server 2003 Sp2. В системе автоматически активируется Scalable Networking Pack: SNP (TCP Chimney, RSS and NetDMA). Это функционал операционной системы, позволяющий передать нагрузку по обработке пакетов TCP/IP от процессора на сетевую карту. (Полное описание этого функционала можно найти в статье http://support.microsoft.com/kb/912222.)

При использовании этого функционала на серверах, с установленным SQL Server могли возникать проблемы, описанные в статьях: http://support.microsoft.com/kb/942861, http://support.microsoft.com/kb/918483.

Кроме того, были обнаружены случаи, когда активное SNP приводит к задержкам в исполнении пакетов команд, описанных в статье «Windows Scalable Networking Pack — Possible Performance and Concurrency Impacts to SQL Server Workloads» (http://www.liveinternet.ru/users/rss_sql_server_escalation_team/post86296048).

Если вы сталкиваетесь с описанными выше проблемами, а они довольно отчетливо видны на трассировках, которые мы снимали выше при подготовке и проведении тестирования, то придется поправить реестр (либо поставить фикс, который указан в статье рекомендуется установка исправления, описанного в статье http://support.microsoft.com/default.aspx?scid=kb;ENUS;948496).

1. Найти в реестре ключ: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters.

3. В диалоговом окне Edit DWORD Value ввести 0 в поле Value data и щелкнуть OK.

5. В диалоговом окне Edit DWORD Value ввести 0 в поле Value data и щелкнуть OK.

7. В диалоговом окне Edit DWORD Value ввести 0 в поле Value data и щелкнуть OK.

Настройки самого SQL Server, устанавливаются либо через графический интерфейс, либо средствами процедуры sp_configure.

Наиболее отчетливо на производительность влияют выделенная серверу память “max server memory” / “min server memory”. Памяти серверу нужно, чем больше, тем лучше, но не следует забывать о том, что он работает в среде Windows, у которой может быть также немало потребностей. Рекомендуется на каждые 16 ГБ физической памяти оставлять операционной систем не менее 1 ГБ. То есть если на сервере стоит 32 ГБ, отдайте SQL Server не более 30 ГБ. Еще одна настройка, вокруг которой только что религиозные войны не ведутся — это установка, определяющая уровень параллелизма при обработке запросов. Лично я считаю, что ее лучше всего ставить равной 1 или 2, а потом смотреть на индивидуальные запросы для определения полезности увеличения. Чего совершенно точно не стоит делать — это оставлять ее в значении умолчания — 0.

При написании запросов следует принимать во внимание, что интенсивное использование табличных функций (тех, которые называются Multistatement) может существенно понизить производительность и если есть возможность использовать inline (встроенные в текст запроса) функции, это стоит сделать. Не всегда стоит идти по пути создания универсальных функций типа таких, что делают преобразование даты или мелких строковых операций.

С точки зрения инкапсуляции функциональности это выглядит красиво, но с точки зрения производительности не всегда оправданно. Хороший пример «красивого» против быстрого кода, упомянутое выше вычисление максимума с OR критерием в запросе.

Не нужно слишком усложнять SELECT. Написать запрос, занимающий три экрана (я сам видел такие) конечно наполняет душу программиста гордостью, но вызывает судороги у оптимизатора. Иногда разбить запрос на части с промежуточными временными таблицами оказывается гораздо более производительным вариантом. Не стоит использовать табличные переменные для больших объемов данных. У них нет статистики, а SQL 2008 умеет перекомпилировать индивидуальные запросы, так что неоптимальный план хуже, чем пара рекомпиляций изза временных таблиц.

Ну, вот и все. Удачи вам в миграции. Будут вопросы — пишите dartemov@hotmail.com.

Что осталось за кадром? Много, мы вообще не говорили об анализе производительности промышленной системы, средствам работы с кешем процедур, анализе счетчиков perfmon, использовании расширенных событий для вылова проблемных запросов, но это мы оставим для следующих публикаций.

Оптимизатор запросов: оптимизация корреляции дат

Фабиано Аморим (Fabiano Amorim)

В версии SQL Server 2005 появилась новая возможность, которая осталась, скорее всего, незамеченной, но могла бы принести пользу всем тем, кто выполняет запросы с данными даты и времени. Каждому, кто имеет дело с хранилищами данных, занимается составлением расписаний или зависящим от времени ценообразованием, эта новинка могла бы помочь значительно ускорить выполнение запросов. Кому же и представлять
такую возможность, как не эксперту по оптимизатору запросов Фабиано Амориму (Fabiano Amorim)?

Начиная с версии SQL Server 2005, у базы данных есть параметр, который можно задать с помощью инструкции SET на уровне базы данных. Этот параметр называется DATE_CORRELATION_OPTIMIZATION и по умолчанию имеет значение OFF. Мне не приходилось видеть, чтобы ктонибудь использовал параметр DATE_CORRELATION_OPTIMIZATION, но полагаю, это объясняется только тем, что крайне скупое описание затрудняет получение справочных сведений и объяснений, необходимых для его применения.

Очень важно, чтобы разработчик или администратор БД понимали, какого типа приложения и какие запросы к базе данных могут выиграть от применения параметра DATE_CORRELATION_OPTIMIZATION, поскольку это может обеспечить потрясающий рост производительности. Однако сейчас, вам, вероятно, не терпится узнать, а что именно делает эта функциональная возможность?

Database Logging for Stored Procedures and Triggers in SQL Server 2005 - 2008. Seamless integration into existing Log4Net environments. Ability to set separate, independent log-levels on a per Procedure/Trigger basis. Inspired by LOG4PL/SQL.

Log4TSQL creates a set of Stored Procedures for database logging in MS SQL Server 2005 - 2008. You can perform logging from everywhere within your Stored Procedures and Triggers. This logging-framework can either be integrated into your existing Log4Net environment or it can be used solely for independent database logging.

Please refer to the Log4TSQL Developer's Manual for initial setup and refer to the included example-file prc_Log4TSQL_Example.sql to examine how to use Raycoon Log4TSQL in your own Stored Procedures and Triggers.

PS: Measured real-life-performance on my laptop: Up to 30 log-entries per millisecond.

This release contains important improvements regarding performance. Robustness and fault tolerance is already tested in production environment. Still there might be problems due to my recent adaptions, therefore it has beta status. The Documentation is not yet complete and will be updated ASAP.

SyncFx SP Generator is a simple program to generate the required stored procedures and objects for each change tracking enabled SQL Server 2008 table in order to develop applications using Microsoft Sync Framework 2.0.

This release is dedicated to the sample databases that ship for Microsoft SQL Server 2008R2 November CTP. See Database Prerequisites for SQL Server 2008R2 for feature configurations required for installing the sample databases. See Installing SQL Server 2008R2 Databases for step by step installation instructions.

All of the AdventureWorks sample databases for SQL Server 2008R2 are included in the AdventureWorks2008R2_NovemberCTP.exe self extracting zip file. When this zip file is run, all content is unzipped to a temporary directory and an installer application is automatically started. This application copies the database scripts and data files to the directory specified, and optionally installs sample databases. If some databases cannot be installed, a brief explanation is displayed and a link is provided for more information about how to resolve the installation issues.

The analysis services databases must be deployed manually using SQL Server Business Intelligence Development Studio after installation is complete. Please see Installing Analysis Services Database for more details.

The latest installer which is now available has better support for instances with different version of SQL Server, and also includes both the AdventureWorks 2008R2 schemas and data files and the original AdventureWorks schemas and data files which shipped with SQL Server 2005. Installing both AdventureWorks families will ensure that all samples will work correctly and that you'll be able to explore the new features in SQL Server 2008.

MS SQL Code Factory is a premier MS SQL Server GUI tool aimed at the SQL queries and scripts development. Key features include:

The application also provides you with a powerful set of tools to edit and execute SQL scripts, build visual diagrams for numeric data, customize user interface according to your needs and much more.

With all these features our software will be an everyday assistant in your work with MS SQL Server database server.

Содержание номера за Февраль 2010 год

SQL Server

Февраль 2010
№ 2 (110)

Содержание номера за Февраль 2010 год

SQL Server

Февраль 2010 № 2 (110)

Февраль 2010
№ 2 (110)