Рассылка закрыта
При закрытии подписчики были переданы в рассылку "Вопросы и ответы по MS SQL Server" на которую и рекомендуем вам подписаться.
Вы можете найти рассылки сходной тематики в Каталоге рассылок.
MS SQL Server - дело тонкое...
Информационный Канал Subscribe.Ru |
#221<< #222 |
СОДЕРЖАНИЕ Блокировки SQL Server 7.0/2000 - теория и практика устранения проблем
По материалам статьи KB224453
Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
Данная статья является новой редакцией для SQL Server 7.0 и выше статьи 162361
INF: Understanding and Resolving SQL Server Blocking Problems базы знаний Майкрософт, которая была написана
для SQL Server 6.х Введение
Блокировки присутствуют в любой реляционной системе управления базами данных (СУБД), т.к. на блокировках основана
работа механизм обслуживания параллельных запросов. SQL Server порождает блокировку, когда один SPID блокирует
определенный ресурс, а второй SPID пытается применить несовместимый тип блокировки на том же самом ресурсе.
Как правило, интервал времени, на который первый SPID блокирует ресурс, является очень маленьким. Когда первый
SPID снимает блокировку и высвобождает ресурс, второе подключение накладывает свою блокировку на ресурс и
продолжает свою работу. Это - нормальная ситуация и такое может происходить по многу раз за день без заметного
влияния на производительности сервера.
Блокирование и увеличение количества блокировок до тех пор, когда это уже оказывает влияние на производительность, происходит обычно в одном из следующих случаев:
В первом случае, проблема решается через некоторое время, когда SPID снимет блокировку. Однако данную ситуацию трудно отследить с помощью SQL Server Enterprise Manager или используя запросы на T-SQL, в том случае, если различные SPID блокируют разные ресурсы длительное время, потому что картина постоянно меняется. Вторая ситуация приводит к неизменному состоянию, которое легче диагностировать. Сбор информации о блокировках Чтобы избежать трудностей поиска проблем блокирования, администратор базы данных может использовать SQL скрипты, которые постоянно контролируют состояние блокирования на SQL Server. Эти скрипты могут обеспечить информацию о блокировках через некоторые промежутки времени, помогая понять причины блокирования. Для описания того, как контролировать блокирование с использованием SQL скриптов, см. следующие статьи в базе знаний Microsoft:
Сценарии, приведенные в этих статьях, решают задачи, представленные ниже: 1. Нахождение самого первого SPID в цепочке блокирования.
b. Раскрыть контейнер Management; затем Current Activity. c. Выбрать Locks / Process ID. Наряду с информацией о блокировании, там имеется информация и о том какие SPID блокируют друг друга. 2. Определение запроса, который выполняется в контексте этого SPID:
Для этого можно воспользоваться командой DBCC INPUTBUFFER (<spid>)
b. Раскрыть контейнер Management; затем Current Activity c. Выбрать Process Info. В панели детализации появится список SPID. d. Дважды нажать на блокирующем SPID, чтобы увидеть какую последнюю команду на T- SQL он выполняет. 3. Определение типа блокировки, которую удерживает SPID:
Эту информацию можно получить с помощью хранимой процедуры sp_lock.
b. Раскрыть контейнер Management; затем Current Activity. c. Раскрыть Locks / Process ID. Здесь можно просмотреть подробную информацию о блокировках, установленных конкретным SPID. 4. Определение уровня вложенности транзакций и статуса выполнения блокирующего SPID. Уровень вложенности транзакций можно определить с помощью глобальной переменной @@TRANCOUNT. Также это можно определить, запросив выборку из таблицы sysprocesses: SELECT open_tran FROM SYSPROCESSES WHERE SPID=<номер блокирующего SPID> go
Значение, которое возвращает переменная @@TRANCOUNT - относится к SPID, и показывает уровень вложенности транзакций,
который в свою очередь объясняет причину удерживания блокировки. Например, если значение больше нуля, это означает,
что SPID выполняет транзакцию (в этом случае предполагается что блокировка сохраняется в зависимости от уровня
изоляции транзакций). Получение информации с помощью профайлера (SQL Server Profiler) Часто для того, чтобы полностью исследовать проблему блокировок, необходимо использовать профайлер. Если в контексте SPID выполняется состоящая из нескольких инструкций транзакция, с помощью DBCC INPUTBUFFER можно определить только последнюю инструкцию. Но может так случиться, что блокировку породит команда, которая в транзакции выполнялась раньше. Профайлер позволяет Вам отследить все команды, выполняющиеся внутри этой транзакции. Следующие шаги помогут Вам настроить трассу профайлера для сбора необходимой информации: 1. Запустите SQL Server Profiler. 2. В меню Tools выберите пункт Options. 3. Убедитесь в том, что выбраны опции All Event Classes и All Data Columns. 4. Нажмите OK. 5. В меню File выберите пункт New, затем Trace. 6. На вкладке General введите имя трассировки и файл, в котором будут сохранены результаты трассировки. 7. На вкладке Events добавьте следующие события в свою трассу:
Также для дальнейшего исследования проблемы, вы можете включить в трассу и представленные ниже события. Но нужно помнить, что уже перечисленных событий вполне достаточно, чтобы определить источник проблемы, а добавление новых событий увеличит нагрузку на сервер и добавит информации в трассе.
8. На закладке Data Columns убедитесь, что выбраны следующие колонки: Start Time, End Time, Connection ID, SPID, Event Class, Text, Integer Data, Binary Data, Application Name, NT User Name, и SQL User Name. Если Вы используете дополнительные события из второй таблицы, добавьте также: Duration, CPU, Reads, и Writes. 9. На вкладке Filters исключите внутренние ошибки SQL Server. Для этого в окне Trace Event Criteria выберите Severity и введите значение 24 в поле:
10. Нажмите OK.
Для получения более подробной информации о мониторинге ошибок, возвращенных клиенту от SQL Server, читайте статью
из базы знаний Microsoft 199037
INF: Trapping Error Messages Sent to Clients from a SQL Server. (Отслеживание сообщений об ошибках,
возвращенных клиентам от SQL Server) ПРОДОЛЖЕНИЕ СЛЕДУЕТ За и против использования команды SELECT, представлений и хранимых процедур в SQL Server
По материалам статьи G. Vijayakumar
Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server Когда я начинал изучать SQL Server, я вначале не мог понять разницы между командой SELECT, представлением и хранимой процедурой. Казалось, они все выполняют практически одинаковую задачу (получение данных) и мне хотелось узнать все за и против их использования. Почему SQL Server предлагает три разных варианта получения данных из базы данных? Как разработчик и администратор баз данных, я захотел узнать все что можно об этих вариантах, почему они могут быть полезными и когда их нужно использовать. Эта статья - результат моего опыта, она объясняет разницу между командой SELECT, представлением и хранимой процедурой для администратора баз данных или разработчика, которые только знакомятся с SQL Server. Я надеюсь, что эта статья будет вам полезна. По мере прочтения этой статьи вы сможете выполнять представленный в статье код в Query Analyzer для лучшего понимания различий между командой SELECT, представлением и хранимой процедурой. Я разделил эту статью на 3 части для лучшей подачи информации. Введение Чтобы начать рассмотрение различий между командой SELECT, представлением и хранимой процедурой, мне нужно напомнить о системной таблице syscacheobjects. Она используется для хранения информации о скомпилированных объектах и их планах выполнения. Причиной напоминания об этой таблице являются хранящиеся в ней скомпилированные команды SELECT, представления и хранимые процедуры, и я использовал эту таблицу, чтобы экспериментировать и узнать больше о том, как эти 3 разных объекта хранятся и используются в SQL Server. Если вы не знаете эту системную таблицу, то вам будет полезно взглянуть на нее. Она хранится в базе данных master и может быть просмотрена при помощи Enterprise Manager или Query Analyzer. Если вы будете выполнять примеры из этой статьи, то вам нужно будет выполнять команду DBCC FREEPROCCACHE перед каждым запуском. Эта команда удаляет в таблице syscacheobjects все кэшированные объекты и позволяет проводить более аккуратные тесты. Теперь давайте создадим таблицу и добавим в нее несколько строк перед рассмотрением различий между командой SELECT, представлением и хранимой процедурой. Создание тестовой таблицы Я предполагаю, что у вас есть база данных для создания таблицы. Если нет, то вам нужно создать базу данных. После этого нам нужно создать таблицу для экспериментирования. Create Table DummyTable1 ( EmpId Int, EmpName Varchar(8000) ) Теперь давайте добавим несколько записей в эту таблицу с помощью следующего скрипта: Insert Into DummyTable1 Values (1, Replicate ('a',20)) GO Insert Into DummyTable1 Values (2, Replicate ('b',20)) GO Insert Into DummyTable1 Values (3, Replicate ('c',20)) GO Insert Into DummyTable1 Values (4, Replicate ('d',20)) GO Insert Into DummyTable1 Values (5, Replicate ('e',20)) GO Insert Into DummyTable1 Values (6, Replicate ('f',20)) GO Insert Into DummyTable1 Values (7, Replicate ('g',20)) GO Insert Into DummyTable1 Values (8, Replicate ('h',20)) GO Insert Into DummyTable1 Values (9, Replicate ('i',20)) GO Insert Into DummyTable1 Values (10, Replicate ('j',20)) GO Таблица DummyTable1 теперь содержит достаточное количество строк для экспериментирования с различиями между командой SELECT, представлением и хранимой процедурой. Давайте начнем с команды SELECT и посмотрим, чем она отличается от представления или хранимой процедуры. Команда SELECT Теперь давайте взглянем на содержимое таблицы, выполнив следующую команду в Query Analyzer для новой таблицы. SELECT EmpId, EmpName FROM DummyTable1 GO
Как предолагается, данные были введены перед тем, как была выполнена эта команда. Теперь давайте запустим следующую команду для очистки кэша. DBCC FREEPROCCACHE GO Очистка процедурного кэша предотвращает повторное использование скомпилированной команды SQL. Это значит, что когда в следующий раз мы запустим ту же команду, она будет снова скомпилирована. Теперь давайте выполним следующие команды для вывода информации кэша о созданной таблице. Эта информация хранится в системной таблице SQL Server syscacheobjects. SELECT EmpId, EmpName FROM DummyTable1 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.syscacheobjects GO В результате мы получим много столбцов, но нас пока интересуют только 4 из них, которые показаны ниже.
Вот что означает эта информация: Cacheobjtype: тип хранимого в кэше объекта, который может включать:
* Скомпилированный план Мы в основном сконцентрируемся на скомпилированном и выполненном планах. Refcounts: количество других кэшированных объектов, ссылающихся на этот кэшированный объект. Количество, равное 1, относится к базе. Usecounts: количество раз, которое этот кэшированный объект использовался со времени его появления в кэше. Sql: текст команды. Теперь давайте выполним ту же команду SELECT: SELECT EmpId, EmpName FROM DummyTable1 GO
Значение Usecounts увеличилось. SQL Server использовал тот же скомпилированный план выполнения для команды SELECT и увеличил значение Usecounts выполненного плана. SQL Server будет использовать тот же скомпилированный план выполнения, когда мы выполняем ту же команду SELECT. Теперь давайте добавим выражение 'WHERE' в команду SELECT и посмотрим результат в таблице master.dbo.Syscacheobjects. SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 5 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GOSQL Server has not used the existing cache plan because of the change in the SELECT statement. SQL Server will generate a new cache plan for the SELECT statement along with old cache plan. SQL Server не использовал существующий кэшированный план из-за изменения команды SELECT. SQL Server сгенерирует новый план в кэше для команды SELECT, оставив старый план.
Давайте выполним ту же команду SELECT с разным empid и проверим результат. SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Хотя мы передали разные значения empid, SQL Server использовал тот же скомпилированный план и увеличил значение Usecounts выполненного плана. Даже если другой пользователь выполнит команду SELECT с другими значениями empid, то все равно при этом будет использован тот же скомпилированный план и увеличено значение Usecounts выполненного плана. Теперь давайте выполним ту же команду с именем пользователя в команде SELECT и проверим результаты. SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Теперь у нас в кэше есть еще 2 строки, потому что мы использовали разные имена пользователей в команде SELECT. SQL Server генерирует новые скомпилированный план и выполненный план для разных пользователей. Если же этот пользователь выполнит команду SELECT больше одного раза, то SQL Server использует тот же скомпилированный план и только увеличит значение Usecounts выполненного плана. Давайте выполним ту же команду SELECT с другим empid и проверим результаты. SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Теперь давайте выполним ту же команду с указанием базы данных и владельца в той же команде SELECT и посмотрим результаты. SELECT EmpId, EmpName FROM vijay.dbo.DummyTable1 WHERE EmpId = 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Как администраторы или разработчики баз данных, мы можем минимизировать количество созданных скомпилированных планов, если будем добавлять имена базы данных и владельца и выражение WHERE в команду SELECT. Не изменяйте состав команды SELECT, чтобы не создавать новый план выполнения, без особой необходимости. Если вы это сделаете, то SQL Server создаст новый план выполнения команды SELECT. Мы можем минимизировать количество планов выполнения, чтобы повысить производительность системы. Теперь давайте выполним следующую команду для очистки кэша перед экспериментированием с хранимыми процедурами. DBCC FREEPROCCACHE GO ОКОНЧАНИЕ СЛЕДУЕТ Статьи на русском языке
Что такое GUID
Identifying Blocking Locks Самые популярные темы недели
Репликация мочит PK на подписчике!!!?
Документация по MS SQL Server 2000? Профессиональное руководство по SQL Server: хранимые процедуры, XML, HTML (+CD)
Теория и практика построения баз данных 9-е изд.
|
#221<< #222 |
Вопросы, предложения, коментарии, замечания, критику и т.п. оставляйте Виталию Степаненко и Александру Гладченко в форуме: Обсуждение рассылки
|
http://subscribe.ru/
http://subscribe.ru/feedback/ |
Подписан адрес: Код этой рассылки: comp.soft.winsoft.sqlhelpyouself |
Отписаться |
В избранное | ||