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

Как настроить и запустить Microsoft SQL Server

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

MS SQL Server

Данный программный продукт представляет собой систему управления базами данных ( СУБД ) реляционного типа, разработанную корпорацией Microsoft . Для манипуляции данными используется специально разработанный язык Transact-SQL . Команды языка для выборки и модификации базы данных построены на основе структурированных запросов:

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

Обзор возможностей MS SQL Server

СУБД является частью длинной цепочки специализированного программного обеспечения, которое корпорация Microsoft создала для разработчиков. А это значит, что все звенья этой цепи ( приложения ) глубоко интегрированы между собой.

То есть их инструментарий легко взаимодействует между собой, что во многом упрощает процесс разработки и написания программного кода. Примером такой взаимосвязи является среда программирования MS Visual Studio . В ее инсталляционный пакет уже входит SQL Server Express Edition .

Конечно, это не единственная популярная СУБД на мировом рынке. Но именно она является более приемлемой для компьютеров, работающих под управлением Windows, за счет своей направленности именно на эту операционную систему. И не только из-за этого.

Преимущества MS SQL Server :

• Обладает высокой степенью производительности и отказоустойчивости;
• Является многопользовательской СУБД и работает по принципу « клиент-сервер »;

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

Эволюция SQL Server

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

• Microsoft SQL Server 1.0 – вышел еще в 1990 году. Уже тогда эксперты отмечали высокую скорость обработки данных, демонстрируемую даже при максимальной нагрузке в многопользовательском режиме работы;
• SQL Server 6.0 – вышел в 1995 году. В этой версии впервые в мире была реализована поддержка курсоров и репликации данных;
• SQL Server 2000 – в этой версии сервер получил полностью новый движок. Большая часть изменений коснулась лишь пользовательской стороны приложения;
• SQL Server 2005 – увеличилась масштабируемость СУБД , во многом упростился процесс управления и администрирования. Был внедрен новый API для поддержки программной платформы .NET ;
• Последующие выпуски – были направлены на развитие взаимодействия СУБД на уровне облачных технологий и средств бизнес-аналитики.

В базовый комплект системы входит несколько утилит для настройки SQL Server . К ним относятся:

• SQL Server Configuration Manager :

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

• SQL Server Error and Usage Reporting :

Утилита служит для настройки отправки отчетов об ошибках в службу поддержки Microsoft .

• SQL Server Surface Area Configuration

Используется для оптимизации работы сервера базы данных. То есть вы можете настроить функционирование SQL Server под свои нужды, включив или отключив определенные возможности и компоненты СУБД .

Набор утилит, входящих в Microsoft SQL Server , может отличаться в зависимости от версии и редакции программного пакета. Например, в версии 2008 года вы не найдете SQL Server Surface Area Configuration .

Запуск Microsoft SQL Server

Для примера будет использована версия сервера баз данных выпуска 2005 года. Запуск сервера можно произвести несколькими способами:

• Через утилиту SQL Server Configuration Manager . В окне приложения слева выбираем « SQL Server 2005 Services », а справа — нужный нам экземпляр сервера БД . Отмечаем его и в подменю правой кнопки мыши выбираем « Start ».

• С помощью среды SQL Server Management Studio Express . Она не входит в инсталляционный пакет редакции Express . Поэтому ее нужно скачивать отдельно с официального сайта Microsoft .

Для запуска сервера баз данных запускаем приложение. В диалоговом окне « Соединение с сервером » в поле « Имя сервера » выбираем нужный нам экземпляр. В поле « Проверка подлинности » оставляем значение « Проверка подлинности Windows ». И нажимаем на кнопку « Соединить »:

Основы администрирования SQL Server

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

• SQL Server Surface Area Configuration – сюда следует обращаться, если нужно включить или отключить какую-либо возможность сервера баз данных. Внизу окна находятся два пункта: первый отвечает за сетевые параметры, а во втором можно активировать выключенную по умолчанию службу или функцию. Например, включить интеграцию с платформой .NET через запросы T-SQL :

• SQL Server Management Studio – является основным средством администрирования. В этой среде реализована возможность настройки сервера и баз данных, как через интерфейс приложения, так и с помощью запросов на языке T-SQL .

Основные настройки можно осуществить через « Обозреватель объектов », отображающий слева в окне приложения все основные элементы сервера в виде древовидного списка. Самой важной является вкладка « Безопасность ». Через нее можно настроить права и роли пользователей и администраторов для основного сервера, или отдельно для каждой базы данных:

Основная часть настроек сервера баз данных доступна в окне « Свойства сервера »:

Как видите, Microsoft SQL Server является настолько мощным средством для структуризации, хранения и модификации данных, что на его изучение потребуется много времени. А в статье мы лишь слегка углубились в основы сервера SQL .

Администрирование серверов с помощью управления на основе политик 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.

Администрирование 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.)

Анализ работы 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 (ожидания). текущая информация и статистика.
• проблемы с подключением к серверу. процессы/порты/протоколы