Администрирование sql сервера

Администрирование Microsoft SQL Server

Правильное конфигурирование ваших серверов SQL и регулярное выполнение задач обслуживания ваших баз данных – это ключ к достижению высокой эффективности работы. В этой лекции вы узнаете о средствах динамического конфигурирования Microsoft SQL Server 2000, которые помогают упростить процесс конфигурирования системы баз данных. Вы также узнаете, как использовать мастер Database Maintenance Plan Wizard для создания автоматизированного плана обслуживания ваших баз данных. Использование планов обслуживания – это отличный способ поддерживать ваши базы данных в рабочем состоянии.

Средства автоматического конфигурирования SQL Server

SQL Server содержит множество автоматических средств, предназначенных для снижения расходов, которые обычно связаны с конфигурированием и настройкой системы управления реляционными базами данных ( RDBMS ). В этом разделе вы ознакомитесь с некоторыми из этих средств. (Те, кто работал с Microsoft SQL Server 7, уже знают об этих средствах, поскольку они были введены именно в SQL Server 7.) Вы узнаете, как они действуют, как их использовать для облегчения вашей работы администратора баз данных ( DBA ) и как переопределять при необходимости эти автоматические средства.

Динамическое управление памятью

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

Как действует динамическое управление памятью

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

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

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

Таким образом, выбор между динамическим и ручным управлением памятью определяется степенью изменчивости использования памяти в системе. Применяя мониторинг использования памяти системой SQL Server, вы можете определить, изменяется ли количество используемой памяти каким-либо регулярным образом или остается достаточно стабильным. Для мониторинга использования памяти вы можете использовать Microsoft Windows 2000 Performance Monitor . Счетчик Total Server Memory (KB) внутри объекта SQLServer: Memory Manager показывает количество памяти в килобайтах (Кб), которое использует в данный момент SQL Server. Чтобы определить, как изменяется использование памяти в течение времени, следите за этим счетчиком в окне диаграмм ( chart window).

Пул памяти

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

• Буферный кэш. Содержит страницы базы данных, считанные в память. Буферный кэш обычно забирает основную часть пула памяти.
• Память для соединений. Используется каждым соединением с SQL Server. Память для соединений содержит структуры данных, с помощью которых отслеживается контекст каждого пользователя; это информация о позиционировании курсора, значения параметров очереди и информация хранимых процедур.
• Структуры данных. Содержит глобальную информацию о блокировках и дескрипторах базы данных, включая информацию о владельцах блокировок, о типах захваченных блокировок, а также о различных файлах и группах файлов.
• Кэш журнала. Используется для информации журнала, которая будет записана в журнал транзакций. Он также используется, когда происходит чтение последней информации, записанной в этот кэш. Использование кэша журнала повышает производительность операций записи в журналы. Кэш журнала не следует путать с буферным кэшем.
• Кэш процедур. Используется для хранения планов исполнения операторов Transact-SQL (T-SQL) и хранимых процедур, когда происходит их выполнение.

Поскольку в случае использования динамического управления памятью распределение памяти динамически изменяется, пул памяти может все время увеличиваться или уменьшаться. Кроме того, указанные пять компонентов пула памяти тоже могут динамически изменять свои размеры. Это распределение недоступно для конфигурирования; управление осуществляет SQL Server. Например, когда требуется больше памяти, чтобы в кэше процедур можно было хранить больше операторов T-SQL, SQL Server может забирать часть памяти из буферного кэша и использовать ее для кэша процедур.

Использование дополнительной памяти

Количество памяти, доступной для SQL Server, зависит от используемой операционной системы Windows. Microsoft Windows NT Server 4 поддерживает 4 Гб памяти, 2 Гб из которых выделяется для пользовательских процессов и 2 Гб резервируется для использования системой. Это ограничение в 2 Гб представляет максимальное количество памяти, которое может быть выделено для SQL Server в NT 4.0. Но в системе Windows NT Server 4 Enterprise Edition количество виртуальной памяти, выделяемой для процесса, на 50 процентов больше – 3 Гб. Это увеличение стало возможным, так как память, выделенная для системы, была сокращена до 1 Гб. Такое увеличение виртуальной памяти, выделенной для процессов, позволяет вам увеличивать размер пула памяти до величины, близкой к 3 Гб. Чтобы активизировать эту поддержку в Windows NT 4 Enterprise Edition, вы должны добавить флаг /3GB к строке загрузки в файле Boot.ini, что можно сделать с помощью значка System (Система) в панели управления.

Имеется две версии операционной системы Windows 2000, в которых SQL Server 2000 Enterprise Edition может использовать интерфейс расширенной памяти Windows 2000 Address Windowing Extensions ( AWE ) API, поддерживающий адресные пространства большего размера. SQL Server поддерживает около 8 Гб в системе Windows 2000 Advanced Server и около 64 Гб в системе Windows 2000 Datacenter Server. AWE поддерживается только в этих двух операционных системах и не поддерживается в Windows 2000 Professional. (Для получения более подробной информации см. «Платформа Microsoft Windows 2000» этой книги и тему «Using AWE Memory on Windows 2000» [Использование AWE -памяти в Windows 2000] в Books Online.)

Администрирование серверов с помощью управления на основе политик Administer Servers by Using Policy-Based Management

Область применения: SQL Server База данных SQL Azure Azure Synapse Analytics (Хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Управление на основе политик — это система, основанная на политиках, для управления одним или несколькими экземплярами SQL Server SQL Server . Policy-Based Management is a policy based system for managing one or more instances of SQL Server SQL Server . Используйте его для создания условий, содержащих выражения условия. Use is to create conditions that contain condition expressions. Затем создайте политики, применяющие условия к целевым объектам базы данных. Then, create policies that apply the conditions to database target objects.

Например, вы как администратор базы данных должны убедиться, что компонент Database Mail отключен на определенных серверах, и поэтому создаете условие и политику, задающую этот параметр сервера. For example, as the database administrator, you may want to ensure that certain servers do not have Database Mail enabled, so you create a condition and a policy that sets that server option.

ВАЖНО! IMPORTANT!! Политики могут повлиять на работу некоторых функций. Policies can affect how some features work. Например, система отслеживания измененных данных и репликация транзакций используют таблицу systranschemas, не имеющую индекса. For example, change data capture and transactional replication both use the systranschemas table, which does not have an index. При включении политики, согласно которой все таблицы должны иметь индекс, принудительная реализация этого условия приведет к отказу этой функции. If you enable a policy that all tables must have an index, enforcing compliance of the policy will cause these features to fail.

Используйте SQL Server Management Studio для создания политик и управления ими, чтобы выполнять следующие задачи: Use SQL Server management Studio to create and manage policies, to:

Выбор аспекта управления на основе политик, содержащего настраиваемые свойства. Select a Policy-Based Management facet that contains the properties to be configured.

Указание условия, определяющего состояние аспекта управления. Define a condition that specifies the state of a management facet.

Указание политики, содержащей условие, дополнительные условия, фильтрующие наборы целей и режим оценки. Define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.

Проверка соответствия экземпляра SQL Server SQL Server политике. Check whether an instance of SQL Server SQL Server is in compliance with the policy.

Для политик, выполненных с ошибкой, обозреватель объектов формирует предупреждение о критическом состоянии в виде красного значка рядом с целью и узлами, расположенными выше в дереве обозревателя объектов. For failed policies, Object Explorer indicates a critical health warning as a red icon next to the target and the nodes that are higher in the Object Explorer tree.

ПРИМЕЧАНИЕ. Когда система вычисляет набор объектов для политики, системные объекты исключаются из него по умолчанию. NOTE: When the system computes the object set for a policy, by default the system objects are excluded. Например, если набор объектов для политики относится ко всем таблицам, политика будет неприменима к системным таблицам. For example, if the object set of the policy refers to all tables, the policy will not apply to system tables. Если пользователям требуется оценить политику, используя системные объекты, их можно явно добавить в набор объектов. If users want to evaluate a policy against system objects, they can explicitly add system objects to the object set. Однако несмотря на то, что все политики поддерживаются режимом оценки проверка по расписанию , в режиме проверка при внесении изменений по соображениям производительности поддерживаются не все политики с обязательными наборами объектов. However, though all policies are supported for check on schedule evaluation mode, for performance reason, not all policies with arbitrary object sets are supported for check on change evaluation mode. Дополнительные сведения см. в разделе https://blogs.msdn.com/b/sqlpbm/archive/2009/04/13/policy-evaluation-modes.aspx For more information, see https://blogs.msdn.com/b/sqlpbm/archive/2009/04/13/policy-evaluation-modes.aspx

Три компонента управления на основе политик Three Policy-Based Management components

Управление на основе политик включает три следующих компонента. Policy-Based Management has three components:

Управление политиками. Policy management. Администраторы политик создают политики. Policy administrators create policies.

Явное администрирование. Explicit administration. Администраторы выбирают одну или несколько управляемых целей и выполняют явную проверку соответствия целей определенной политике или выполняют явную настройку целей для соответствия политике. Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.

Режимы оценки. Evaluation modes. Существует четыре режима оценки, три из которых могут быть автоматизированы: There are four evaluation modes; three can be automated:

По запросу. On demand. В этом режиме политика непосредственно указывается пользователем. This mode evaluates the policy when directly specified by the user.

При изменении запретить. On change: prevent. В этом автоматизированном режиме для предотвращения нарушения политики используются триггеры DDL. This automated mode uses DDL triggers to prevent policy violations.

ВАЖНО! IMPORTANT! Если параметр конфигурации сервера nested triggers отключен, политика При изменении: запретить не будет правильно работать. If the nested triggers server configuration option is disabled, On change: prevent will not work correctly. Управление на основе политик с помощью триггеров DDL определяет и выполняет откат DDL-операций, не соответствующих политикам, использующим режим оценки. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Удаление триггеров DDL управления на основе политик или отключение вложенных триггеров приведет к отказу этого режима оценки или к непредвиденным результатам. Removing the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.

При изменении: только внесение в журнал. On change: log only. В этом автоматизированном режиме используется уведомление о событии для определения политики при возникновении соответствующего изменения. This automated mode uses event notification to evaluate a policy when a relevant change is made.

По расписанию. On schedule. В этом автоматизированном режиме для периодического определения политики используется задание агента SQL Server SQL Server . This automated mode uses a SQL Server SQL Server Agent job to periodically evaluate a policy.

Если автоматизированные политики не включены, то управление на основе политик не оказывает влияния на производительность системы. When automated policies are not enabled, Policy-Based Management will not affect system performance.

Термины Terms

Целевой объект, управляемый на основе политик Сущности, управляемые на основе политик, например экземпляр Компонент SQL Server Database Engine SQL Server Database Engine , база данных, таблица или индекс. Policy-Based Management managed target Entities that are managed by Policy-Based Management, such as an instance of the Компонент SQL Server Database Engine SQL Server Database Engine , a database, a table, or an index. Все цели на экземпляре сервера формируют иерархию целей. All targets in a server instance form a target hierarchy. Набор целей — это набор целей, являющихся результатом применения набора фильтров к иерархии целей, например: все таблицы базы данных, владельцем которой является схема HumanResources. A target set is the set of targets that results from applying a set of target filters to the target hierarchy, for example, all the tables in the database owned by the HumanResources schema.

Аспект управления на основе политик Набор логических свойств, моделирующих поведение или характеристики определенных типов управляемых целевых объектов. Policy-Based Management facet A set of logical properties that model the behavior or characteristics for certain types of managed targets. Количество и характеристики свойств встраиваются в аспект и могут быть добавлены или удалены только создателем аспекта. The number and characteristics of the properties are built into the facet and can be added or removed by only the maker of the facet. Тип цели может реализовать один или несколько аспектов управления, а аспект управления соответственно может быть реализован одним или несколькими типами цели. A target type can implement one or more management facets, and a management facet can be implemented by one or more target types. Некоторые свойства аспекта могут применяться только в определенной версии. Some properties of a facet can only apply to a specific version..

Условие управления на основе политик Policy-Based Management condition
Логическое выражение, определяющее набор разрешенных состояний целей управления на основе политик, с точки зрения аспекта управления. A Boolean expression that specifies a set of allowed states of a Policy-Based Management managed target with regard to a management facet. SQL Server SQL Server пытается просмотреть параметры сортировки при вычислении условия. tries to observe collations when evaluating a condition. Если параметры сортировки SQL Server SQL Server не совпадают в точности с параметрами сортировки Windows, проверяйте состояние, чтобы определить, каким образом алгоритм разрешает конфликты. When SQL Server SQL Server collations do not exactly match Windows collations, test your condition to determine how the algorithm resolves conflicts.

Политика управления на основе политик Policy-Based Management policy
Условие управления на основе политик и ожидаемое поведение, например режим оценки, целевые фильтры и расписание. A Policy-Based Management condition and the expected behavior, for example, evaluation mode, target filters, and schedule. Политика может содержать только одно условие. A policy can contain only one condition. Политики могут быть включены или отключены. Policies can be enabled or disabled. Политики хранятся в базе данных msdb. Policies are stored in the msdb database.

Категория политики управления на основе политик Policy-Based Management policy category
Определяемая пользователем категория для помощи в управлении политиками. A user-defined category to help manage policies. Пользователи могут классифицировать политики по различным категориям. Users can classify policies into different policy categories. Политика принадлежит только к одной, и только одной категории политики. A policy belongs to one and only one policy category. Категории политик применяются к базам данных и серверам. Policy categories apply to databases and servers. На уровне базы данных применяются следующие условия. At the database level, the following conditions apply:

Владельцы базы данных могут подписать базу данных набором категорий политики. Database owners can subscribe a database to a set of policy categories.

Только политики из подписанных категорий могут управлять базой данных. Only policies from its subscribed categories can govern a database.

Все базы данных неявно подписаны категорией политики по умолчанию. All databases implicitly subscribe to the default policy category.

На уровне сервера категории политик могут применяться ко всем базам данных. At the server level, policy categories can be applied to all databases.

Действующая политика Effective policy
Действующими политиками цели являются те политики, которые управляют этой целью. The effective policies of a target are those policies that govern this target. Политика действительна по отношению к цели только при выполнении следующих условий. A policy is effective with regard to a target only if all the following conditions are satisfied:

Политика включена. The policy is enabled.

Цель принадлежит к набору целей политики. The target belongs to the target set of the policy.

Цель или один из ее предков подписан к группе политики, содержащей эту политику. The target or one of the targets ancestors subscribes to the policy group that contains this policy.

Анализ работы MS SQL Server, для тех кто видит его впервые

Недавно столкнулся с проблемой — занедужил SVN на ubuntu server. Сам я программирую под windows и с linux “на Вы”… Погуглил по ошибке — безрезультатно. Ошибка оказалась самая типовая (сервер неожиданно закрыл соединение) и ни о чем конкретном не говорящая. Следовательно, надо погружаться глубже и анализировать логи/настройки/права/и т.п., а с этим, как раз, я “на Вы”.

В результате, конечно, разобрался и нашел всё что нужно, но время потрачено много. В очередной раз думая, как глобально (да-да, во всём мире или хотя бы на ⅙ части суши) уменьшить бесполезно потраченные часы — решил написать статью, которая поможет людям быстро сориентироваться в незнакомом программном обеспечении.

Писать я буду не про линукс — проблему хоть и решил, но профессионалом вряд ли стал. Напишу про более знакомый мне MS SQL. Благо, уже приходилось много раз отвечать на вопросы и список типовых уже готов.

Если вы админ в Сбере (или в Яндексе или ), вы можете сохранить статью в избранное. Да, пригодится! Когда к вам, в очередной раз, с одними и теми же вопросами придут новички — Вы дадите им ссылку на нее. Это сэкономит Ваше время.

Если без шуток, эта СУБД часто используется в небольших компаниях. Часто совместно с 1С либо другим ПО. Отдельного БД-админа таким компаниям держать затратно — надо будет выкручиваться обычному ИТ-шнику. Для таких и пишу.

Какие проблемы рассмотрим

Если сервер вам сообщает “закончилось место на диске Е” — глубокий анализ не нужен. Не будем рассматривать ошибки, решение которых очевидно из текста сообщения. Также не будем рассматривать ошибки по которым гугл сразу выдает ссылку на msdn с решением.
Рассмотрим проблемы по которым не очевидно что гуглить. Такие как, например, внезапное падение производительности или, например, отсутствие соединения. Рассмотрим основные инструменты для настройки. Рассмотрим средства анализа. Поищем где лежат логи и другая полезная информация. И в целом, попробую в одной статье собрать нужную информацию для быстрого старта.

Начнем с лидера списка частых вопросов, настолько он опережает всех, что рассмотрим его отдельно. Вдобавок, об этом пишут во всех статьях про работу MS SQL — и я не буду нарушать традицию.

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

В интернете полно рецептов как это делать, приводятся примеры скриптов. Предположу, что все те методы для “профи” и новичкам непонятны. Что ж, опишу способ наипростейший: для его внедрения вам потребуется только владение мышью.

• SSMS — приложение “Microsoft SQL Server Management Studio”, находится в “Пуске”. Устанавливается отдельной галочкой (Client management tools) с дистрибутива сервера. Начиная с 2016 версии, доступно бесплатно на сайте MS в виде отдельного приложения. Старшие версии студии нормально работают с младшими версиями сервера. Наоборот — тоже иногда работают (основные функции).
  docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms “SSMS is free! It does not require a license to install and use.”
• Profiler — приложение “SQL Server Profiler”, находится в “Пуске”, устанавливается вместе с SSMS.
• Performance Monitor (Системный монитор) — оснастка панели управления. Позволяет мониторить счетчики производительности, журналировать и просматривать историю замеров.

Обновление статистики с помощью “плана обслуживания”:

• запускаем SSMS;
• подключаемся к нужному серверу;
• разворачиваем в Object Inspector дерево: Management Maintenance Plans (Планы обслуживания)
• правой кнопкой на узле, выбираем “Maintenance Plan Wizard”
• в визарде мышкой отмечаем нужные нам задачи:
  • rebuild index (перестроить индекс)
  • update statistics (обновить статистику)
• отметить можно обе задачи сразу, либо сделать два плана обслуживания по одной задаче в каждом (смотрим “важные замечания” ниже);
• далее, отмечаем галочками нужную нам БД (или несколько). Делаем это для каждой задачи (если выбрали две задачи — будет два диалога с выбором БД).
• Next, Next, Finish

После этих действий у вас создастся (а не выполнится) “план обслуживания”. Запуск можно выполнить вручную — правой кнопкой на нем, выбрать “Execute”. Либо настроить запуск через “SQL Agent”.

• Обновление статистики — неблокирующая операция. Можно выполнять в рабочем режиме. Дополнительную нагрузку конечно создаст, но ведь у вас и так всё тормозит, будет чуть больше — незаметно.
• Перестроение индекса — блокирующая операция. Запускать только в нерабочее время. Есть исключение — Enterprise редакция сервера допускает выполнение “онлайнового ребилда”. Эта опция включается галочкой в настройках задачи. Обратите внимание, галочка есть во всех редакциях, но работает только в Enterprise.
• Конечно, эти задачи необходимо выполнять регулярно. Предлагаю простой способ определения, как часто это делать:
  • при первых проблемах выполняете план обслуживания;
  • если помогло — ждете пока не начнутся проблемы снова (как правило, до очередного закрытия месяца/расчета зп/ и т.п. массовых операций);
  • получившийся срок нормальной работы и будет вам ориентиром;
  • например, настройте выполнение плана обслуживания в два раза чаще.

Сервер работает медленно — что делать?

Используемые сервером ресурсы

Как и любой другой программе, серверу нужны: время процессора, данные на диске, объемы оперативной памяти и пропускная способность сети.

Оценить нехватку того либо иного ресурса в первом приближении можно с помощью Task Manager (Диспетчер задач), как бы по кэпски это не звучало.

Посмотреть загрузку в диспетчере сможет даже школьник. Здесь нам надо просто убедиться, что если процессор загружен, то именно процессом sqlserver.exe.

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

Многие смотрят только загрузку процессора, но не надо забывать что СУБД — это хранилище данных. Объемы данных растут, производительность процессоров растет, а скорость HDD практически не меняется. С SSD ситуация получше, но терабайты на них хранить затратно.

Получается так, что я чаще сталкиваюсь с ситуациями, когда узким местом становится именно дисковая система, а не ЦПУ.

Для дисков нам важны следующие показатели:

• средняя длина очереди (операций ввода-вывода ожидающих выполнения, штук);
• скорость чтения-записи (в Мб/с).

Серверная версия диспетчера задач, как правило (зависит от версии системы), показывает и то и другое. Если нет — запускаем оснастку панели управления “Performance Monitor” (Системный монитор). Нас интересуют счетчики:

• Физический (логический) диск / Среднее время чтения (записи)
• Физический (логический) диск / Средняя длина очереди диска
• Физический (логический) диск / Скорость обмена с диском

Развернуто — можно почитать мануалы производителя, например тут social.technet.microsoft.com/wiki/contents/articles/3214.monitoring-disk-usage.aspx. Вкратце:

• Очередь желательно чтобы не превышала 1. Допустимы кратковременные всплески, если они быстро спадают. Всплески могут быть разными в зависимости от вашей системы. Для простого рэйда-зеркала из двух HDD — очередь больше 10-20 проблема. Для крутой библиотеки с супер кешированием я видел всплески до 600-800 которые мгновенно рассасывались, не приводя к задержкам.
• Нормальная скорость обмена тоже зависит от типа дисковой системы. Обычный (настольный) HDD “качает” по 50-100 Мб/с. Хорошая дисковая библиотека по 500 Мб/с и более. Для мелких случайных операций скорость меньше. Примерно так и ориентируйтесь.
• Эти параметры надо смотреть в комплексе. Если ваша библиотека качает 50Мб/с и при этом выстраивается очередь в 50 операций — явно что-то не так с железом. Если очередь выстраивается при прокачке близкой к максимальной — то скорее всего диски не виноваты — они просто больше не могут — надо искать способ уменьшить нагрузку.
• Нагрузку надо смотреть раздельно по дискам (если их несколько) и сопоставлять с размещением файлов сервера. Диспетчер задач может показать наиболее активно используемые файлы. Это удобно использовать, чтобы убедиться, что нагрузка идет именно от СУБД.

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

• проблемы с железом
  • погорел кэш, резко упала производительность;
  • дисковая система используется чем-то еще;
• Недостаток оперативной памяти. Свопинг. Ухудшилось кэширование, производительность упала (смотрим раздел про ОП ниже).
• Увеличилась пользовательская нагрузка. Необходимо оценить работу пользователей (проблемный запрос / новый функционал / увеличение количества пользователей / увеличение объема данных / и т.п.).
• Фрагментация данных БД (смотрим ребилд индексов выше), фрагментация файлов системы.
• Дисковая система достигла своих максимальных возможностей.

Если у вас последний вариант — не спешите выкидывать оборудование. Иногда из системы можно выжать чуть больше если подойти к проблеме с умом. Проверьте размещение файлов системы на соответствие рекомендуемым требованиям:

• не смешивайте файлы ОС с файлами данных БД. Размещайте их на физически разных носителях чтобы система не конкурировала с СУБД за ввод-вывод.
• БД состоит из файлов двух видов: данные (*.mdf, *.ndf) и логи (*.ldf). Файлы данных, как правило, больше используются на чтение. Логи — больше на запись (причем запись — последовательная). Из понимания этого факта, следует рекомендация размещать логи и данные на физически разных носителях, чтобы запись в лог не прерывала чтение данных (как правило, операция записи имеет приоритет выше чем у чтения).
• MS SQL для обработки запросов может использовать “временные таблицы”. Они хранятся в системной базе tempdb. Если у вас высокая нагрузка на файлы этой БД — то можно попробовать вынести ее на физически отдельные носители.

Резюмируя по размещению файлов, используйте принцип “разделяй и властвуй”. Оцените к каким файлам идут обращения и попробуйте их распределить на разные носители. Также, используйте особенности RAID систем. Например, RAID-5 читает быстрее чем пишет — что хорошо подходит для файлов данных.

• анализируем использование ОП и сети.
• смотрим детально работу пользователей используя SSMS, profiler и прямые запросы к системным представлениям.
• план и статистика запросов (рассмотрим несколько способов получения). live query statistics.
• waits (ожидания). текущая информация и статистика.
• проблемы с подключением к серверу. процессы/порты/протоколы

Администрирование sql сервера

В этом разделе описываются некоторые возможности Database Engine, которые относятся к системному окружению. Во-первых, в главе дается подробное описание системных баз данных, которые инсталлируются при установке всей системы. Другой темой, связанной с системой, является хранение данных. В этом разделе рассматриваются некоторые типы дисковых страниц, также как и способы сохранения на диске различных типов данных. Под конец объясняется использование системных утилит bср и sqicmd, а также системной команды DBCC.

Управление экземплярами сервера и поддержка баз данных

В этом разделе описываются средства для управления экземплярами Database Engine (Declarative Management Framework) и мастер для создания планов поддержки базы данных (Maintenance Plan Wizard). Declarative Management Framework (DMF) является новым средством в SQL Server 2008, которое используется для управления всеми экземплярами его баз данных. Maintenance Plan Wizard обеспечивает некоторое количество задач, которые, помимо всего другого, проверяют, правильно ли выполняется ваша база данных и находятся ли ваши таблицы и индексы в согласованном состоянии.

Резервное копирование и восстановление БД

В этом разделе рассматриваются две из наиболее важных задач, связанные с системой администрирования: резервное копирование и восстановление. Резервное копирование (backup) означает процесс создания копии базы данных (баз данных) и/или протоколов транзакций для разделения устройств, которые в дальнейшем при необходимости могут быть использованы для восстановления данных. Восстановление (recovery) — это процесс использования устройства резервной копии для замены неподтвержденных, несогласованных или потерянных данных.

Система автоматизации задач администрирования

Одним из наиболее важных преимуществ Database Engine по сравнению с другими реляционными СУБД является его автоматическое администрирование задач, а это снижает затраты. Вы можете автоматизировать все эти задачи, так что они появятся в регулярном расписании. Например, вы можете установить, что задача резервного копирования базы данных появляется каждую пятницу в 20:00, а задача резервного копирования протокола транзакций — ежедневно в полночь.

Репликация данных

Кроме распределенных транзакций, средства репликации данных являются еще одним способом получения доступа к окружению распределенных данных. Общее обсуждение этих двух методов проводится в начале этой главы. После этого вводятся элементы репликации и описываются существующие типы репликации.

Оптимизатор запросов

Вопросом, который обычно возникает, когда Database Engine (или любая другая система реляционной базы данных) выполняет запрос, является вопрос, как доступ к необходимым данным и обработка этих данных в запросе может быть выполнена с максимальной эффективностью. Компонент системы базы данных, ответственный за такую деятельность, называется оптимизатором запросов. Задачей оптимизатора запросов (или просто оптимизатора) является рассмотрение множества возможных стратегий выполнения поиска требуемых в запросе данных и выбор наиболее эффективной стратегии. Выбранная стратегия называется планом выполнения запроса. Оптимизатор принимает свои решения с учетом таких факторов, как: насколько велики по размерам таблицы, вовлеченные в запрос, какие существуют индексы и какие логические операции (and, or, not) используются в предложении where. Обычно такие факторы называются статистическими данными.

Настройка производительности

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