Администрирование
28
Сен
11

Трактат о производительности MS CRM. Книга II: настройки базы данных

Раздельная установка

SQL Server

Наиболее оптимальным является раздельная установка: SQL на одной машине, CRM-сервер на другой. Это не только тупо увеличивает производственные мощности, но также помогает разграничить возможные проблемы и, как следствие, сузить места их поиска.

По поводу Reporting Services решайте сами. Лично я склоняюсь за установку RS либо на отдельной машине, что влечет за собой те же преимущества, что и отдельная установка SQL сервера, либо на той же машине, что и CRM-сервер, т.к. в этом случаи вам не потребуется развертывать IIS на SQL сервере и устанавливать коннектор к отчетам (пы.сы. данное утверждение не касается Reporting Services 2008 – ему для работы вообще не нужен IIS). С другой стороны установка Reporting Services по умолчанию вместе с SQL сервером в большинстве случаев происходит всегда гладко, и избавляет Вас от ручных действий, которые необходимо выполнять при раздельной установке и которые могут быть не всегда корректными.

Системные данные, файлы БД и логи транзакци

Время от времени, при наличии большого объема данных, предназначенных для добавления, изменения или удаления из приложения (MS CRM), в файл бызы данных (<ORG>_MSCRM) и в файл журнала транзакций может происходить интенсивная запись. Для оптимальной производительности, а именно, чтобы распараллелить нагрузку на физические диски, рекомендуется расположить на отдельных физических носителях (диски или наборы физических дисков объединенные в массивы):

  • Системные и серверные каталоги. Можно оставить их по умолчанию вместе с операционно системой на диске C (в предлагаемых программой установке каталогах по умолчанию – C:\Program Files\Microsoft SQL Server), т.к. при наличии достаточного количества оперативной памяти они не вызвают большой нагрузки;
  • Файлы баз данных MS CRM (<ORG>_MSCRM и MSCRM_CONFIG) и файлов темповой БД (TempDB);
  • Файлы журналов транзакций.

Например, для трёх дисков возможна такая конфигурация:

  • C:\Program Files\Microsoft SQL Server\ – файлы сервера баз данных, системные базы данных, журналы планов обслуживания и т.п.
  • D:\MSSQL\DATA\ – Файлы данных пользовательских баз данных и базы данных TempDB (*.mdf).
  • E:\MSSQL\LOG\ – Файл журнала регистрации транзакций пользовательской базы данных и базы данных TempDB(*.ldf).

Задать расположение файлов по умолчанию можно:

  • Либо во время установки SQL Server’а


    Заметьте, что при установке 2008 скуля у Вас гораздо больше возможностей для начальной настройки. Соответственно для 2005 Вам что-то предется настраивать после установки.

  • Либо поменяв дефолтные пути в настройках SQL сервера:
    • В SQL Server Management Studio


    • SQL запросом
      USE [master]
      GO
      EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\MSSQL\Data'
      GO
      EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'E:\MSSQL\LOG'
      GO
      


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

  • С использованием мастера копирования баз данных (точнее мы будем их переносить)





    Учтите, только одну вещь: т.к. БД мы пытаемся перенести в тот же сервер, то во время копирования необходимо указать отличное от исходного имя БД. А после переноса изменит его на прежнее!

  • С помощью SQL-запроса:
    • перенос файлов
      Сначала узнаем какие файлы есть у базы данных:

      USE master;
      SELECT name, physical_name
      FROM sys.master_files
      WHERE database_id = DB_ID('tempdb');
      

      затем правим параметры базы данных:

      ALTER DATABASE tempdb
      MODIFY FILE (NAME=tempdev, FILENAME='E:\mssql\log\tempdev.mdf')
      GO
      
      ALTER DATABASE tempdb
      MODIFY FILE (NAME=templog, FILENAME='D:\mssql\data\templog.ldf')
      GO
      


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

      USE superfirma_MSCRM;
      ALTER DATABASE superfirms_MSCRM
      SET offline WITH ROLLBACK IMMEDIATE;
      GO
      

      переносим ручками файлы в новое расположение, меняете путь к этим файлам как описано выше, а после возвращаете ее в онлайновый режим

      USE superfirma_MSCRM;
      ALTER DATABASE superfirms_MSCRM
      SET online;
      GO
      
    • отсоединение и присоединение БД
      Для отсоединения базы данных требуется монопольный доступ к ней и по этому преводим базу в однопользовательский режим

      USE superfirma_MSCRM;
      ALTER DATABASE superfirma_MSCRM
      SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      GO
      

      отсоединяем базу

      USE master;
      EXEC sp_detach_db 'superfirma_MSCRM', 'true';
      GO
      

      ручками перенесите файлы базы данных и логи. Затем создаем новую БД и присоединяем к ней перенесенные файлы:

      CREATE DATABASE superfirma_MSCRM ON
      ( FILENAME = N'D:\MSSQL\DATA\superfirma_MSCRM.mdf' ),
      ( FILENAME = N'E:\MSSQL\LOG\superfirma_MSCRM_log.ldf' )
      FOR ATTACH
      GO
      


Файловые группы БД

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

Чтобы создать вторичные файлы данных и журналов, выполните следуюшие:

  • Откройте свойства БД;
  • Перейдите на станицу Files;
  • Щелкните Add, чтобы создать втоичный файл. Задайте их имена, тип (лог или файл данных), размер и местоположение.


Настройик Базы данных

Прирост БД

Под файлы базы данных SQL Server резервирует определенное место на жестком диске и когда оно заканчивается скуль автоматичски наращивает его (это по умолчанию — Вы также можете вручную наращиваnm объем файлов). Автоматическое приращение файлов является ресурсоёмкой операцией, к тому же на время приращения SQL Server блокирует базу данных, поэтому, чем реже будет прирост, тем лучше.

  • Откройте свойства БД;
  • Перейдите на станицу Files;
  • Щелкните на многоточии в столбце Autogrowth и задайте новый объем приращения файла БД.


Асинхронное обновление статистики

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

ALTER DATABASE superfirma_MSCRM SET
AUTO_UPDATE_STATISTICS_ASYNC ON
GO

В этом случае статистика будет обновляться в асинхронном режиме, параллельно с выполнением запроса.


AWE/PAE/3Gb

Все 32-разрядные приложения имеют адресное пространство процесса размером 4 ГБ (то есть для 32-разрядного приложения может быть выделено не более 4 ГБ памяти). Но более того, операционная система Microsoft Windows предоставляет приложениям адресное пространство размером 2 ГБ (т.е., учитывая всякие системные погрешности, 1.7 Гб это максимум, который SQL Server может отъесть на 32битах), а оставшиеся 2 ГБ пространства резервируются операционной системой. Чтобы преодолеть эти ограничения используется несколько технологий:

  • /3GB
    Windows 2000 Server и более поздние версии операционных систем, включая Windows Server 2003, содержат параметр boot.ini, позволяющий приложениям получать доступ к 3 ГБ адресного пространства и ограничивающий адресное пространство ядра размером в 1 ГБ. Указывая такую опцию ОС делит память не 50/50 между ядром и прикладными программами а 25/75 (т.е. максимум 1 ГБ на ядро и 3 ГБ на программы).
    Редактирование boot.ini, файла с конфигурацией загрузки Windows, осуществляется так:

    • Включите отображение скрытых файлов и папок.
    • Найдите boot.ini в корневом каталоге системного диска (обычно C:\) и снимите с него атрибут «только чтение».
    • Открыв файл, Вы увидидите примерно следующее (в зависимости от ОС):
      [boot loader]
      ;timeout=3
      default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
      [operating systems]
      multi(0)disk(0)rdisk(0)partition(1)\WINDOWS=&quot;Microsoft Windows XP Professional RU&quot; /noexecute=optin /fastdetect
      
    • 4) Изменения вносятся в пункт, касающийся необходимой операционки (в данном случае она одна). Если необходимо внести параметр /3GB, то результат должен выглядеть так:
      multi(0)disk(0)rdisk(0)partition(1)\WINDOWS=&quot;Microsoft Windows XP Professional RU&quot; /noexecute=optin /fastdetect /3GB
    • 5) Сохраните файл и выставите параметр «только чтение» обратно.


  • /PAE
    Physical Address Extension — расширение адресуемого пространства за пределы ограничения 32 битной архитектуры (т.е. 4 Гб). Это происходит с помощью использования 36 бит для адресации и как следствие – позволяет использовать больше 4 ГБ памяти (максимум 64 ГБ). Если использовать 3GB и PAE одновременно, то нельзя использовать больше 16 Гб. Включается также как и 3GB. Включается так же как и /3GB.
  • AWE – применяя /PAE, приложениям памяти больше не достаётся. Для того, чтобы сами приложения ощутили этот объем им нужна эта опция (Address Windows Extension). Проще говоря, если PAE – это поддержка большого объема памяти со стороны железа и операционки, то AWE – это ее поддержка со стороны софта. Поэтому софт оперирующий огромными объемами данных (как Microsoft SQL Server) получит очень даже ощутимую пользу!
    Чтобы ее включить для AWE скуля (доступна в выпусках SQL Server Enterprise, Standard и Developer) необходимо запустить следующий скрипт (он устанавливает использование 6 Гб оперативки):

    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'awe enabled', 1
    RECONFIGURE
    GO
    sp_configure 'min server memory', 1024
    RECONFIGURE
    GO
    sp_configure 'max server memory', 6144
    RECONFIGURE
    GO
    


Применение этих опций зависит от объема оперативной памяти:

  • =4Гб /3GB
  • >4 Гб и <=16 Гб /3GB /PAE AWE
  • >16 Гб /PAE AWE (чтобы расширения AWE использовали диапазон памяти свыше 16 ГБ, параметр /3gb необходимо удалить из файла boot.ini, иначе система сократит количество доступной памяти до 16 гигов)

Перезапустить сервак не забудьте…

П.С. Параметры PAE (и AWE) нужны только для 32-х битных ОС, соответственно, в 64-х битных ОС подобные настройки не актуальны.

Уровень изоляции

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

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

Для улучшения производительности в MS CRM рекомендуется изменить уровень изоляции READ COMMITTED в READ_COMMITTED_SNAPSHOT и использовать SNAPSHOT изоляцию.

Суть этого в том, что по умолчанию в настройках SQL Server’а установлено так, что пока не закончено изменение данных текущей транзакцией, то эти данные не могут быть прочитаны. Если же разрешить READ_COMMITTED_SNAPSHOT и изоляцию SNAPSHOT, то если операции чтения будут запрашивать строки таблицы на момент их изменения, то вместо чтения самой строки им будет выдаваться снапшот этой строки сделанный на начало операции изменения. Таким образом время операции чтения уменьшится за счет того, что они не будут ждать окончания операции изменения.

Включается все это дело следующими командами:

ALTER DATABSE ORG_MSCRM
SET READ_COMMITTTED_SNAPSHOT ON;
ALTER DATABSE ORG_MSCRM
SET ALLOW_SNAPSHOT_ISOLATION ON;


Примечание

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

USE superfirma_MSCRM; 
ALTER DATABASE superfirma_MSCRM 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
GO

а затем вернуть обратно в многопользовательский:

USE superfirma_MSCRM; 
ALTER DATABASE superfirma_MSCRM 
SET MULTI_USER; 
GO

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

Отключение параллельных запросов

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

Помимо наличия более одного процессора, требуется также написание sql запросов опеределенным образом. К сожалению, MS CRM не может должным образом использвовать данный функционал. т.е. в данном приложении параллелизм не только полезен но и вреден, т.к. параллельные планы требует дополнительных ресурсов (в том числе времени) чтобы определить нужно ли распараллеливать запрос. Также это может вызвать блокировки и другие деградации производительности. Поэтому для SQL сервера, который используется для хранения БД CRM, этот параметр лучше отключить.

В заданной по умолчанию конфигурации SQL сервера значение максимальной степени параллелизма установлено в 0 (ноль), что определяет использовать все доступные процессоры. Установка этого значения в 1 (единицу) отключает параллельные запросы. Также учтите, что максимальная степень параллелизма является дополнительным параметром и настроить ее Вы cможете только включив отображение дополнительных параметров.

Отключаем параелельные запросы:

  • Либо через процедуру
    exec sp_configure 'show adv', 1;
    RECONFIGURE WITH OVERRIDE;
    exec sp_configure 'max degree', 1
    RECONFIGURE WITH OVERRIDE;
    


  • Либо через визуальный интерфейс — щелкните правой кнокой по имени сервере, выберите свойства, перейдите к расширенным настройкам и задйте параметер Max degree of parallelism в 1:


Конфигурация дисковой подсистемы для оптимальной производительности

Вначале любого диска есть раздел, который зарезервирован для мастер-блока начальной загрузки (MBR) и который занимает 63 сектора. Это означает, что если наш пользовательский раздел начинается с 64-го сектора (Microsoft Windows всегда создает раздел, начинающийся в 64-м секторе), он может пострадать от нарушения границ раздела.

При правильном задании начального смещения ввод-вывод SQL Server будет выровнен по границам дорожек хранилища и производительность диска возрастет. Операции ввода-вывода SQL Server записывают данные блоками, кратными 8 КБ, а операции ввода-вывода в базу данных работают с данными размером от 8 КБ до 1 МБ. Если мы имеем дело с подобным диском, то каждое энное (обычно 8-ое) чтение или запись будет выходить за границу, и диск должен будет выполнить две физические операции. Поэтому убедитесь в том, что начальное смещение кратно 8 КБ. При несоблюдении этого требования одна операция ввода-вывода может охватывать две дорожки, что приведет к снижению производительности.

Чтобы убедиться в правильности выравнивания диска, воспользуйтесь программой Diskpart.exe — средством создания разделов диска. Diskpart.exe — это средство Microsoft, которое позволяет явным образом задать начальное смещение основной загрузочной записи (MBR).

Выравнивание ввода-вывода SQL Server по границам дорожек хранилища с помощью программы Diskpart.exe:

  1. Если выравниваемый диск уже пуст (без данных), перейдите к пункту 3. Если диск содержит данные, создайте резервную копию диска, прежде чем выполнять последующие операции;
  2. Удалите все разделы на диске;
  3. Откройте окно командной строки и запустите программу Diskpart.exe;
  4. В командной строке средства Diskpart введите List Disk и нажмите клавишу ВВОД. Если диск, который требуется выровнять, отсутствует в списке, убедитесь в том, что он существует и доступен, с помощью оснастки «Управление дисками»;
  5. В командной строке средства Diskpart введите Select Disk X, где X — номер диска, отображаемый в выходных данных команды List Disk. Средство Diskpart должно вернуть сообщение, которое показывает, что «Disk X» — это выбранный диск;
  6. В командной строке средства Diskpart введите Create Partitio Primary Align=X, где X — значение, рекомендуемое поставщиком устройства хранения. Если поставщик устройства хранения не предоставляет конкретных инструкций, рекомендуется использовать значение 64;
  7. В командной строке средства Diskpart введите Assign Letter=<буква_диска>;
  8. После назначения буквы диска введите exit, чтобы завершить работу средства Diskpart;
  9. Используйте оснастку «Управление дисками» или команду Format, чтобы отформатировать раздел как NTFS-раздел.

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

Индексы

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

Соответственно, поддержание индексов в «тонусе» является ключевым моментов в минимизации ввода-вывода с дисков для всех запросов к базе данных. Команда ALTER INDEX может помочь в дефрагментировании индексов и в их перестроении для определенной таблицы.

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

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

Степерь фрагметации индексов

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

USE superfirma_MSCRM
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'superfirma_MSCRM'), NULL,NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent>30
GO


Выбор действия зависит от степени дефрагментации

  • > 30 % — перестроение индекса;
  • > 5 % и <= 30 % — реорганизация индекса;
  • < 5 % — при таком низком уровне фрагментации перестроение или реорганизация не нужны.

Команда реорганизации индекса

USE superfirma_MSCRM;
GO
ALTER INDEX PK_SdkMessageRequestBaseIds ON dbo.SdkMessageRequestBaseIds
REORGANIZE;
GO


Команда перестроения индекса

USE superfirma_MSCRM;
GO
ALTER INDEX PK_SdkMessageRequestBaseIds ON dbo.SdkMessageRequestBaseIds
REBUILD;
GO


Автоматическая оптимизация существующих индексов и создание новых индексов

Для оптимизацией индексов необходимо собрать информацию о наиболее важных и частых операциях, которые выполняют пользователи, посмотреть планы выполнения запросов и определить, какие индексы не нужны, а какие могут потребоваться дополнительно. Для такого анализа предназначена программа Database Tuning Advisor, которая входит в состав SQL Server. Это средство использует собранную информацию о выполненных запросах для того, чтобы определить наилучший набор индексов для базы данных.

Но перед применением Database Tuning Advisor — собрать информацию о запросах пользователей. А для этого как раз предназначена другая программ, входящая в пакет SQL Server — SQL Server Profiler. Выполните:

  • Start — Program — Microsoft SQL Server 2005 (или 2008) — Performance Tools — SQL Server Profiler.
  • Далле нужно начать сбор данных: File — New Trace — и подключиться к серверу SQL Server 2005, работу которого вы будете отслеживать (т.к. мы тут работаем с CRM, то нас интересует скуль на котором крутится БД CRM).
  • Появится окно Trace Properties, в котором нужно настроить параметры сеанса трассировки:
    • Задайте произвольное имя трпссировки;
    • На вкладке General в списке Use the template небходимо выбрать наиболее подходящий шаблон (сохраненные в специальном файле с расширением tdf настройки сеанса трассировки) для сбора информации об оптимизации и передачи Database Tuning Advisor — Tuning. В принципе, можно и не обращать внимание на настройки шаблона, а вручную определить параметры сбора информации (при помощи соседней вкладки Events Selection). Однако указание правильного шаблона поможет сэкономить время и избежать ошибок.
    • Информация трассировки может быть запротоколирована в файл или таблицу в SQL Server’е. Лучше использовать файл, т.к. это расходует меньше системных ресурсов и легче переносить (на анализ его можно запускать на любом SQl сервере, а не толь на том, где производилась трассировка). Этот файл можно использовать в разных ситуациях. Измените заданное ограничение размера файла в 5 Мбайт, т.к. на боевом сервере оно заполнится за минуты — задайте не меньше 1 Гбайта.
    • Параметр Server processes trace data можно использовать для увеличения надежности записи информации трассировки. По умолчанию обработкой данных трассировки занимается SQL Server Profiler, и происходит это на том компьютере, на котором он запущен (не обязательно на сервере). Если установить этот флажок, то обработкой информации трассировки будет заниматься сервер. Это гарантирует, что вся информация трассировки будет собрана (при снятом флажке в моменты пиковой нагрузки сервера часть информации может быть пропущена), но увеличит нагрузку на сервер.
  • После того как все нужные параметры трассировки будут настроены, можно нажать на кнопку Run и приступить к трассировке. Правильнее будет собирать информацию о работе пользователей в течение продолжительного времени, например, рабочего дняс (или еще дольше).
  • Когда соберете достаточно информации нажмите Stop (красный квадратик на панели инструментов).



После того как исходная информация о командах, выполняемых пользователями, готова, можно запускать Database Tuning Advisor

  • Start — Programs — Microsoft SQL Server 2005 (или 2008) — Performance Tools — Database Engine Tuning Advisor
  • Первое, что вам потребуется сделать, — подключиться к серверу, для которого будет производиться оптимизация индексов. Database Tuning Advisor будет автоматически запрашивать с этого сервера необходимую служебную информацию. Кроме того, этот сервер будет использоваться для хранения временной информации Database Tuning Advisor, поэтому выполнение анализа лучше планировать на нерабочее время.
  • В основном окне Вы увидите две вкладки: General и Tuning Options. Первое, что необходимо сделать — выбрать протокол рабочей нагрузки (workload). Им может быть таблица (в SQL Server) или файл трассировки. В данном примере мы создали файл — его и выбираем.
  • В параметре Database for workload analysis необходимо указать базу данных, в которой будут создаваться временные объекты Database Tuning Advisor. Конечно, такие временные объекты не должны создаваться ни в базе данных master (что предлагается по умолчанию), ни в рабочей базе данных. Лучше всего использовать для временных объектов тюнинга новую базу данных, специально созданную для этой цели.
  • В нижней части вкладки General выберите базы данных (а можете детализировать и до конкретных таблиц), для которых будет производиться анализ системы индексов — нас прежде всего интересуют базы данных имеющие отношение к MS CRM.
  • Вкладка Tuning Options предназначена для настройки параметров тюнинга. В большинстве случаев оптимальными параметрами будут следующие:
    • Не ограничивать время анализа: снимите флажок Limit tuning time. Как уже говорилось, анализ лучше планировать на нерабочее время. Кроме снятия нагрузки с рабочей базы данных такое решение одновременно позволит отвести на анализ столько времени, сколько потребуется Database Tuning Advisor. Конечно же, снятие ограничения на время обеспечит максимальное качество анализа;
    • Анализировать возможность применения индексов и индексированных представлений: установите переключатель Physical Design Structures to use in database в положение Indexes and indexed views. Это обеспечит максимальную гибкость при проведении анализа. Все остальные положения переключателя ограничивают варианты, которые будут рассматриваться при анализе;
    • Bспользовать полные возможности секционирования: установить переключатель Partitioning strategy to employ в положение Full partitioning;
    • Не сохранять какие-либо существующие физические структуры хранения: установите переключатель Physical Design Structures to keep in database в положение Do not keep any existing PDS. При этом будут генерироваться предложения не только по созданию новых индексов, но и по удалению существующих, если они практически не используются. Информация о том, какие индексы не используются и только замедляют операции по изменению данных, может оказаться очень полезной, а немедленно удалять индексы никто вас не заставляет.
  • После того как все настройки на этой вкладке произведены, можно нажать кнопку Start Analysis на панели инструментов. Если протокол рабочей нагрузки большой, а время для проведения анализа не ограничено, то для завершения этой операции может потребоваться несколько часов. Наблюдать за ходом выполнения вы можете на вкладке Progress (Прогресс).
  • После того как анализ завершен, откроется вкладка Recommendations. На ней будет представлен список объектов (индексов, индексированных представлений, разделов), которые нужно создать или удалить. При этом названия объектов, которые нужно удалить, будут зачеркнуты. Однако не стоит спешить внедрять эти рекомендации сразу же. Намного правильнее будет вначале ознакомиться с результатами анализа на вкладке Reports. Кроме общей статистики анализа, на этой вкладке представлены отчеты, при помощи которых можно получить информацию:
    • О том, какие запросы выполнялись чаще всего (отчет Event frequency report);
    • Насколько выиграет каждый запрос в производительности и какие индексы он использует или будет использовать в текущей и предлагаемой конфигурации индексов (отчеты Statement-index relations report (current) и Statement-index relations report (recommended));
    • О системе индексов в текущей и рекомендованной конфигурации (отчеты Index detail report (current) и Index detail report (recommended));
    • Какие индексы используются активно, а какие совсем не используются (отчеты Index usage report).
      При помощи других отчетов можно получить множество другой полезной информации, например, к каким представлениям, таблицам, столбцам в таблицах чаще всего выполнялись обращения, насколько улучшится производительность каждого запроса и т. п.
  • Если после изучения результатов анализа вы придете к мнению, что предложенные рекомендации имеет смысл воплотить в жизнь, то в вашем распоряжении — меню Actions (Действия) Database Tuning Advisor. Отметив галкой нужные рекомендации Вы можете использовать три пункта этого меню:
    • Apply recommendations (Применить рекомендации) — просто выполнить команды на создание или удаление объектов, немедленно или по расписанию, в зависимости от вашего выбора;
    • Save recommendations (Сохранить рекомендации) — рекомендации будут сохранены в файле скрипта. При этом вы сможете выбрать, использовать ли команды на создание и удаление объектов в оперативном режиме (с параметром ONLINE) или в автономном режиме (OFFLINE);
    • Evaluate recommendations (Оценить рекомендации) — это значит создать новый сеанс анализа, в котором предлагаемая конфигурация индексов, индексированных представлений и разделов будет считаться действующей, и провести анализ, приняв ее за исходную. Фактически в этом режиме вы проводите анализ типа «а что, если …».

Отметьте, что слепо полагаться на Database Tuning Advisor не стоит — ситуации, когда предлагаемые им рекомендации не оптимальны, иногда встречаются. Обязательно изучите сначала данные отчетов.




SQL Server 2005 Performance Dashboard

Также для рекомендации по созданию индексов можно получить от Performance Dashboard.

Важно

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

Дефрагментация

Базы данных SQL Server представляют собой большие файлы базы данных и журналов, которые со временем подвергаются фрагментации. Это вызвано стандартным поведением дисковой/файловой — когда на диске отсутствует достаточный по размеру отрезок для записи цельного куска данных, то данные физически будут расположены в разных частях диска (там где система ссможет найти для них место, т.е. данные будут записаны «фрагментами»). А фрагментация может вызвать большое количество ненужных операций ввода/вывода, которые в свою очередь приводят к снижению производительности SQL Server.

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

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

RAID-массивы

Парочка рекомндаций по дисковому хранилищу:

  • Для размещения файлов журналов транзакций необходимо использовать защищённые от отказа диска массивы? например RAID 10 или RAID 1. Это позволит обеспечить повышенную защиту от сбоев оборудования и достичь большей производительности записи.
    Примечание: дисковый массив RAID 10 обычно обеспечивает более высокую пропускную способность для приложений, интенсивно нагружающих подсистему записи. Выигрыш в производительности может быть разным в зависимости от реализации RAID на конкретном оборудовании. В общем случаем массив RAID 10 обладает самой высокой производительностью среди массивов RAID, предусматривающих защиту данных.
  • Не используйте для размещения файлов баз данных и особенно журналов транзакций массивы RAID5x или RAID6x, в силу крайне низкой скорости записи.

П.С. по мере появления новых предложений по оптимизации БД я также буду добавлять их сюда. Если у Вас есть какие-либо предложения не стесняйтесь их высказывать 🙂

Комментарии (11)
  • Костя 28.09.2009

    Привет.
    Какой выигрыш производительности получается? Делал тесты до и после?

  • slivka_83 28.09.2009

    Привет 🙂
    Нет, не делал… пока… точнее не попадалось пока развертывание которому небходима бала бы оптимизация 🙂

  • Костя 28.09.2009

    Думаю оптимизация не помешает никому.
    Прочту внимательнее все и попробую пару советов, спасибо

  • Александр 28.09.2009

    А CRM 2011 тоже не умеет использовать распаралеливание процессов ?

    В опциях этот функционал включен.

  • slivka_83 28.09.2009

    Всмысле распаралеливание? Распралеливание чего?

  • Дмитрий 28.09.2009

    Добрый день.
    А быстродействие отчетов каким образом можно увеличить?
    Очень долго формируются, некоторые по таймауту отваливаются…

  • slivka_83 28.09.2009

    Добрый день.
    НУ если не рассматривать ситуацию в лоб и не наращивать мощности сервера отчетов, то можно либо смотреть/оптимизировать SQL-запросы, либо упрощать (делить на части) отчет.

  • Дмитрий 28.09.2009

    А если посмотреть в лоб )
    Какие мощности нужны серверу отчетов? процессор? память? или что то ещё?

  • slivka_83 28.09.2009

    Да то же что и всем 🙂 И процессор и память.

  • Виталий 28.09.2009

    Нужен специалист по MS CRM 2011. Медленно работает, медленно открывает сущности. Работа на возмездной основе, Москва. почта kluchnick@bk.ru

  • slivka_83 28.09.2009

    Попробуйте также написать сюда http://axforum.info/forums/forumdisplay.php?f=71

*

code