Вопросы и ответы по SQLПоиск блокировок, большие запросы, статистика ввода-вывода и многое другое

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

Загрузить исходный код для этой статьи: SQLQandA2007_08.exe (151KB)

В: Мне необходимо просмотреть имена всех заблокированных объектов во всех базах данных. Как это сделать?

О: В SQL Server™ 2000 получить текущую информацию о блокировках можно, либо исполнив запрос к системной таблице syslocks в схеме Master, либо запустив хранимую процедуру sp_lock, например:

SELECT * FROM master..syslocks
EXEC sp_lock

Однако предположим, что вам нужно по идентификатору объекта, находящемуся в выводе хранимой процедуры sp_lock или в колонке id результата запроса, получить действительное имя объекта.

В SQL Server 2005 SP1 и в более ранних версиях в функцию OBJECT_NAME было передать только один параметр: object_id. Таким образом, для правильного получения имени объекта перед запуском OBJECT_NAME приходилось переключаться в текущую (CURRENT) базу данных. Это затрудняло наблюдение за блокировками, поскольку для получения корректного имени объекта приходилось писать код, обходящий в цикле все базы данных.

USE DBNAME  
SELECT OBJECT_NAME(object_id)

SQL Server 2005 SP2 позволяет передавать в функцию второй параметр – идентификатор базы данных database_id.

OBJECT_NAME ( object_id [, database_id ] )

Теперь можно получить имена всех заблокированных объектов во всех базах данных запросом к таблице sys.dm_tran_locks:

SELECT
DB_NAME(resource_database_id),
OBJECT_NAME(resource_associated_entity_id, resource_database_id)
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT'

Обратите внимание, что такая конструкция работает только для SQL Server 2005 SP2 и более поздних версий. В предыдущих версиях SQL Server 2005 создается ошибка:

Msg 174, Level 15, State 1, Line 1
The object_name function requires 1 argument(s).

На рис. 1 показан другой пример. Присоединив таблицу sys.dm_exec_sessions, можно получить информацию об идентификаторах серверных процессов (SPID), установивших блокировки. Для получения дополнительных сведений воспользуйтесь электронной документацией по SQL Server по адресу technet.microsoft.com/library/ms130214(sql.90).aspx.

Figure 1 Поиск идентификаторов серверных процессов

SELECT
DB_NAME(resource_database_id) as DBName,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS ObjectName,
request_mode,
request_type,
request_session_id,
es.host_name,
es.login_name,
es.login_time
FROM
sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions es
ON tl.request_session_id=es.session_id
WHERE resource_type='OBJECT'

В: У меня есть большой динамический запрос SQL, длина которого, возможно, превышает NVARCHAR(max). Есть ли какой-нибудь способ, который позволил бы исполнить запрос, состоящий из одной длинной строки?

О: Если вы превысили длину NVARCHAR(max), то ваш запрос занимает 2 гигабайта! Вам, возможно, придется преобразовать все строчки, которые вы соединили в NVARCHAR(max). Однако есть более удобный способ обхода, который к тому же эффективен и в предыдущих версиях SQL Server, — сцепить вместе несколько небольших строк. Приведем пример:

DECLARE @q1 NVARCHAR(4000), @q2 NVARCHAR(4000), @q3 NVARCHAR(4000)
SET @q1 = 'SELECT...'
SET @q2 = 'FROM...'
SET @q3 = 'WHERE...'
EXEC (@q1 + @q2 + @q3)

Совет: Использование предложения OUTPUT

Появилась возможность проверять изменения, производимые операторами языка ЯМД (язык манипулирования данными), без использования триггеров. В SQL Server 2005 введено предложение OUTPUT — составная часть операторов ЯМД, которая помогает отслеживать изменения, сделанные в ходе любой модификации данных. Предложение OUTPUT позволяет сохранить набор результатов в таблицу или в табличную переменную.

Его функции аналогичны тому, что выполняти триггеры при помощи таблиц INSERTED и DELETED, которые использовались для доступа к строкам, которые были изменены при работе ЯМД. Чтобы увидеть, как это работает, прочитаем задом наперед значение адреса в таблице адресов, как показано на следующем рисунке. Логику кода, приведенного в данном примере, можно использовать для отслеживания любых изменений в данных и сохранения сведений об этих изменениях в таблице.

Вывод запроса может выглядеть примерно так:

Original Value:'1234 One Microsoft Way, Redmond, Wa.' has been changed to: '.aW,dnomdeR 
,yaW tfosorciM enO 4321'

Меняем адрес

--Create the address table
Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))

--Insert data
Insert into Address Values (234,567,'1234 One Microsoft Way, Redmond, Wa.')


--Declare a table variable
Declare @Recordchanges table (change Varchar(255))

--Update the address
Update Supplier.Address
Set Address=reverse(address)

--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' 
into @RecordChanges

--Query the changes from table variable
Select * from @RecordChanges

В: У меня есть критически важная кластерная установка SQL Server 2005 на Windows Server® 2003. Координатор распределенных транзакций Microsoft® (MS DTC) «кластеризован» на той же группе с помощью кворума кластера (Cluster Quorum), но использует выделенный дисковый ресурс (MS DTC имеет то же сетевое имя и IP-адрес, что и группа кворума кластера ). Мне хотелось бы привести конфигурацию в соответствие с практическими рекомендациями Майкрософт. В связи с этим посоветуйте, как переместить MS DTC в выделенную кластерную группу. Достаточно ли удалить службы MS DTC с помощью средства администрирования кластера и создать их снова на выделенной группе?

О: Поскольку выделенный дисковый ресурс для MS DTC у вас уже есть, можно просто удалить его и создать в новой группе. Также для новой группы придётся создать новое сетевое имя и присвоить виртуальный IP-адрес.

Вместо этого можно создать в кластерной группе новое сетевое имя и IP-адрес, а потом поменять зависимости на новый ресурс. Затем можно перетащить MS DTC в новую группу, при этом выделенный диск и новые ресурсы переместятся в нее.

В: Мне нужно иметь возможность просматривать статистику ввода-вывода на физических файлах базы данных. Что мне использовать?

О: Как в SQL Server 2000, так и в SQL Server 2005 существует системная функция fn_virtualfilestats. Кроме того, в SQL Server 2005 можно использовать функцию sys.dm_io_virtual_file_stats. Обе функции позволяют получить именно те данные, которые вам нужны. Эти функции возвращают статистическую информацию, собранную с момента последнего запуска SQL Server. Пример показан на рис. 2.

Figure 2 Получение статистики ввода-вывода по базе данных.

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

Запрос, показывающий информацию о вводе-выводе для SQL Server 2000, может выглядеть так:

SELECT *
FROM ::fn_virtualfilestats(default,default)
GO

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

SELECT *
FROM ::fn_virtualfilestats(7,default)
GO

Этот код SQL Server 2005 показывает файловую статистику для всех баз данных на сервере:

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
GO

Этот запрос возвращает статистику только по текущей базе данных:

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
WHERE DBID=db_id()
GO

В SQL Server 2005 есть также новая системная функция sys.dm_io_virtual_file_stats, которая со временем должна заменить устаревшую функцию fn_virtualfilestats:

sys.dm_io_virtual_file_stats( 
{ database_id | NULL },
{ file_id | NULL }
)

Вот как она используется:

SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)

Если на основании вывода функций сбора статистики необходимо составить отчет, который показывал бы имена файлов и баз данных, используйте код, который можно загрузить с сайта журнала TechNet Magazine. На веб-узле есть примеры кода как для SQL Server 2000, так и для SQL Server 2005.

В: Как проще всего узнать, сработал ли триггер во время операции удаления данных? Есть идеи?

О: При работе с триггерами, обрабатывающими удаление, вставку или модификацию записей, чтобы узнать, сработал ли триггер во время операции удаления, используются различные приемы. Часто сравнивают значения счетчиков виртуальных таблиц удаленных и добавленных записей. Однако проще применить функцию Columns_Updated.

Если во время транзакции удаления срабатывает триггер, функция Columns_Updated всегда возвращает двоичное значение 0x. В следующем примере проверяется, сработал ли триггер на удаление:

IF Columns_Updated() = 0x

Совет: Инициализация файлов данных и журналов.

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

В SQL Server 2005 файлы можно инициализировать мгновенно, что позволяет сократить время перечисленных выше файловых операций. Мгновенная инициализация файлов восстанавливает дисковое пространство без заполнения его нулями. Вместо этого содержимое диска перезаписывается по мере поступления новых данных. Файлы журналов нельзя мгновенно инициализировать. Мгновенная инициализация файлов работает только в операционных системах Windows XP, Windows Server 2003 или более поздних версиях.

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

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

Thanks to the following Microsoft IT pros for their technical expertise: Christian Bolton, Dan Carollo, Robert Davis, Jorge Guzman, Saleem Hakani, Ward Pond, Kalyan Yella, and Paolo Zavatarelli.

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