Вопросы и ответы по SQL«Узкие места», связанные с процессором, восстановление и перемещение баз данных и прочее

Под редакцией Нэнси Мичелл (Nancy Michell)

Вопрос. Использование ресурсов процессора моим SQL Server™ начало неожиданно подскакивать за пределы обычного, хотя вроде бы ничего не произошло. Не добавлялись новые пользователи, не ломалось оборудование, не создавались новые таблицы. Так в чем причина?

Совет: доступ в ходе создания индексов

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

При каждом создании, удалении или переделке кластеризованного индекса SQL Server устанавливает блокировку изменения схем (SCH-M) на таблицу, исключая пользовательский доступ к данным в течение операции. Но это в случае создания кластеризованного индекса для таблицы. Для сравнения, при создании кластеризованного индекса для столбца SQL Server помещает совместную (S) блокировку на таблицу, что, хотя также исключает обновление данных в соответствующей таблице, по крайней мере позволяет выполнять команды SELECT, просматривая таким образом данные.

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

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

При проведении оперативных операций с индексом для таблицах SQL Server по-прежнему помещает блокировку SCH-M для кластеризованного индекса или совместную (S) блокировку для некластеризованного индекса на обрабатываемую таблицу, но лишь на очень короткие промежутки времени — в начальной и конечной фазах операции над индексом. Следовательно, этот вариант открывает больше возможностей для запросов и обновлений соответствующей таблицы в ходе процесса создания индекса. Отметьте, что оперативное создание индексов и работа над ними доступны лишь в SQL Server 2005 Enterprise Edition.

Ответ. «Узкие места», связанные с процессором и возникающие внезапно и неожиданно, без каких-либо изменений или дополнительной нагрузки на сервер, могут быть вызваны целым рядом причин, наиболее распространенные из которых включают неоптимальный план запросов, неудачную настройку сервера SQL, неверное проектное решение приложения или базы данных, и наконец, недостаточные ресурсы оборудования.

При возникновении подобной ситуации в первую очередь следует определить, ограничен ли сервер ресурсами процессора, и если это так, определить команды, являющиеся основными потребителями ресурсов процессора локальной системы SQL Server. Узнать, ограничен ли сервер ресурсами процессора, можно через системный монитор, посмотрев показания счетчика «Процессор: \% загруженности процессора». Если значение времени, используемого процессором, равно или выше 75 процентов, наличествует узкое место, связанное с процессором.

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

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

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

Для получения списка 50 наиболее требовательных к ресурсам процессора команд SQL используйте запрос на Рис. 1.

Figure 1 50 основных пожирателей ресурсов процессора

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

Вопрос. Мне пришлось восстановить базу данных SQL Server 2005 с ленты. После восстановления большинство разрешений пользователям на доступ к этой базе данных оказались утеряны. Что я сделал неверно при восстановлении? Данные оказались в порядке, но разрешения превратились в кашу.

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

Вопрос. Я разработал приложение, использующее хранимую процедуру для нарезки данных XML в реляционные таблицы при помощи OpenXML в SQL Server 2005 с пакетом обновления 1 (SP1). Документы XML не превышают 5 кБ (2,5 кБ в среднем). Хранимая процедура вызывается много раз параллельно (до 50 раз).

Конфликты при блокировках вызывают серьезные проблемы, и мне кажется, что причиной этого является OpenXML. Как вы считаете?

Ответ. Хотя OpenXML может работать быстрее при нарезке или преобразовании данных в одном потоке, чем узловой метод, последний лучше приспосабливается к увеличению числа потоков, особенно при параллельном использовании. А если уж используется OpenXML, то улучшить общую производительность помогут нижеследующие советы.

Вместо пятикратного вызова OpenXML с одинаковым шаблоном строки (о чем, можно заметить, говорилось в вопросе) следует извлечь все данные с одинаковым шаблоном строки во временную таблицу и затем выполнять выборку из нее. Попробуйте освободить память с помощью sp_xml_removedocument как можно раньше. Кроме того, неплохо бы избегать использования подстановочных знаков, таких как * и //, когда только возможно; предоставление явного пути улучшает работу запроса.

Вопрос. DBCC SHRINKFILE работает на моем сервере очень медленно. Поможет ли многопроцессорный компьютер улучшить производительность? Что можно сделать для улучшения ситуации?

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

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

Отметьте, что перемещение страниц для данных больших двоичных объектов (LOB) будет медленным, поскольку придется читать внутристрочные данные, чтобы найти корни данных LOB.

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

Дополнительные сведения по операциям сжатия можно найти в блогах blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspx и blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx.

Где получить справку при перемещении базы данных

• Как перемещать базы данных между компьютерами, использующими SQL Server.
• Как перемещать базы данных SQL Server на новые местоположения, используя функции отсоединения и присоединения в SQL Server.
• INF: Использование параметра WITH MOVE в команде RESTORE.
• Как справляться с проблемами разрешений при перемещении базы данных между серверами, использующими SQL Server.
• INF: Как передавать имена входа и пароли между экземплярами SQL Server.
• INF: Знакомство с мастером копирования баз данных SQL Server 2000 и устранение ошибок.
• Входные данные пользователей и разрешения на базе данных могут оказаться неверными после ее восстановления.
• Невозможно восстановить резервные копии системных баз данных на несовпадающую сборку SQL Server.
• Как извлечь определенную таблицу или строки из резервной копии базы данных или резервной копии журнала транзакций в SQL Server.

Совет: изменение портов

Каждый, кто знаком с SQL Server, знает, что по умолчанию SQL Server прослушивает порт 1433. Хотя именованные экземпляры SQL Server настроены на использование динамических портов (а значит, могут выбрать любой доступный порт при запуске SQL Server), экземпляр SQL Server по умолчанию всегда ведет прослушивание на порту 1433. Очевидно, что если сервер ведет прослушивание на порту по умолчанию, и егонастройка безопасности не максимально строга, он подвергается риску. Но изменение порта по умолчанию может сорвать атаки. Делается это следующим образом.

Откройте диспетчер настройки SQL Server и разверните конфигурацию сети SQL Server 2005, затем Протоколы. Далее дважды щелкните TCP/IP. Список свойств TCP/IP и их функций показан в диаграмме ниже, так что расставляйте в соответствии с ней.

Отметьте, что ядро СУБД SQL Server может прослушивать несколько портов на одном IP-адресе, так что перечислите порты, которые хотите использовать, разделяя их запятой, в формате: 1433,1500,1501. Если необходимо настроить один IP-адрес на прослушивание нескольких портов, необходимо также указанть значение «нет» для параметра «Слушать все» во вкладке «Протоколы» диалогового окна «Свойства TCP/IP».

Теперь щелкните каждый адрес правой кнопкой мыши и выберите «Свойства» для определения IP-адреса, который желаете настроить. Если диалоговое окно «Динамические порты TCP» содержит 0, указывая, что ядро СУБД ведет прослушивание на динамических портах, удалите 0. В поле «Свойства IP» диалогового окна порта TCP введите номер порта, прослушивать который следует данному IP-адресу, и нажмите кнопку OK. На панели консоли щелкните «Службы SQL Server 2005», а на панели свойств щелкните правой кнопкой мыши SQL Server (<имя экземпляра>) а затем выберите перезапуск для остановки и перезапуска SQL Server.

После настройки SQL Server на прослушивание определенного порта клиент может подключиться к порту одним из трех способов. Можно запустить службу обозревателя SQL Server для подключения к экземпляру механизма СУБД по имени; можно создать псевдоним на клиенте, указывающий номер порта; или можно запрограммировать клиента на подключение при помощи индивидуальной строки подключения.

Благодарим за ответы на вопросы этого месяца следующих специалистов по информационным технологиям корпорации Майкрософт: Чэда Бойда (Chad Boyd), Синди Гросс (Cindy Gross), Джона Хаддена (John Hadden), Салима Хакани (Saleem Hakani), Стивена Джианга (Stephen Jiang), Махеша Наяка (Mahesh Nayak), Пола Рэндэла (Paul Randal), и Уэйна Ю (Wayne Yu).

© 2008 Корпорация Майкрософт и компания CMP Media, LLC. Все права защищены; полное или частичное воспроизведение без разрешения запрещено.