August 2006

You are currently browsing the monthly archive for August 2006.

Заметил одну неприятную странность в работе BCP. Связана она с выгрузкой в файл данных BLOB полей, в моём случае, это были поля с типом text. Как я догадываюсь, из-за особенностей хранения этих типов, выгрузка данных выполняется сервером существенно медленнее, чем данных не BLOB типов, даже если объёмы данных (в исчислении получаемого на диске текстового файла) будут равны. Причём, разница во времени выгрузки одинакового объёма данных, в моих реальных случаях, была очень большая, приблизительно в 10 раз BLOB поля выгружались медленнее! Например, на одном сервере, выгрузка BLOB полей занимала 16 часов (размер текстового файла получился 60Гб), а обычных данных – 20 минут (в тексте 8Гб). У таблицы предварительно были удалены все индексы, включая кластеризованный, а также были удалены все ограничения и объекты, которые ссылались на эту таблицу.

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

 

EXEC master..xp_cmdshell ‘bcp “SELECT Article_ID, ArticleBody, Annotation, Remarks, mainXML FROM DBNAME.dbo.Articles” queryout “F:\Articles_blobs.txt” -C866 -b 50000 -T -S SERVERANDINSTANCENAME -f”F:\Articles_blobs.fmt”‘
GO

В SQL Server 2005 появилась новая фича, которая позволяет немножко уменьшить “головную боль” от того, что оптимизатор запросов автоматически использует значения параметра, переданного в хранимую процедуру для определения плана исполнения запроса. Детали проблемы хорошо изложены тут: Методика, которая гарантирует в SQL Server 2000 выбор определенного плана исполнения запроса.
Только что наткнулся в BOL на новый и достаточно интересных хинт запроса, далее просто цитирую BOL (не взирая на все неточности и “ляпы” перевода)…


OPTIMIZE FOR – Подсказывает оптимизатору запросов, что возможно использовать частичное значение для локальных переменных, если запрос скомпилирован и оптимизирован. Значение используется только в процессе оптимизации запроса, но не в процессе выполнения. Подсказка OPTIMIZE FOR может быть использована для отмены определений параметров оптимизатором или при создании руководства плана. Дополнительные сведения см. в разделах Перекомпиляция хранимых процедур и Оптимизация запросов в используемых приложениях с помощью руководств планов.


Использование подсказки OPTIMIZE FOR


В следующем пример оптимизатору запросов дается распоряжение использовать значение ‘Seattle’ для локальной переменной @city_name при оптимизации запроса.


DECLARE @city_name nvarchar(30) SET @city_name = ‘Ascheim’ SELECT * FROM Person.Address WHERE City = @city_name OPTION ( OPTIMIZE FOR (@city_name = ‘Seattle’) )


В статье BOL “Статистика индексов″ есть такой абзац:

“Если параметр базы данных AUTO_UPDATE_STATISTICS установлен в значение ON (значение по умолчанию), то оптимизатор запросов автоматически обновляет статистические данные периодически, по мере изменения данных в таблице. Обновление статистики инициализируется всякий раз, когда статистика, используемая в плане выполнения запроса, не проходит проверку. Выборка случайна по страницам данных, и берется из таблицы или из наименьшего некластеризованного индекса по столбцам, нужным для статистики. Когда страница данных считана с диска, все строки на странице данных используются для обновления статистических данных. Почти всегда статистические данные обновляются при изменении примерно 20 процентов строк данных. Однако оптимизатор запросов всегда удостоверяется в том, что минимальное число строк выбрано. Таблицы, занимающие менее 8 мегабайт (МБ), всегда полностью просматриваются для сбора статистики”.

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

SELECT CAST ('['+ OBJECT_NAME(id) + '].[' + name + ']' AS nvarchar(261)) AS [Индекс] ,CONVERT (char(11), STATS_DATE(id, indid),13) AS [Статиcтика от:] ,CASE WHEN indid > 1 THEN CAST ((8 * CAST (used AS decimal(9,0)))/1000 AS decimal(9,2)) WHEN indid = 1 AND OBJECTPROPERTY(id, 'IsView') = 1 THEN CAST ((8 * CAST (used AS decimal(9,0)))/1000 AS decimal(9,2)) ELSE NULL END AS [Вес (МБ)] FROM sysindexes WHERE OBJECTPROPERTY(id, 'IsSystemTable' ) = 0 AND INDEXPROPERTY (id, name, 'IsAutoStatistics') = 0 AND INDEXPROPERTY (id, name, 'IsHypothetical' ) = 0 AND INDEXPROPERTY (id, name, 'IsStatistics' ) = 0 AND INDEXPROPERTY (id, name, 'IsFulltextKey' ) = 0 AND (indid between 2 and 250 OR (indid = 1 AND OBJECTPROPERTY(id, 'IsView') = 1)) AND (STATS_DATE(id, indid) IS NULL OR STATS_DATE(id, indid) < DATEADD(m, -1, GETDATE())) ORDER BY CONVERT (char(6), STATS_DATE(id, indid),112), [Вес (МБ)]



Иногда, жизнь нам преподносит сюрпризы… Кто бы мог подумать, что в оптимизаторе заложена продемонстрированная ниже бомба :( Одно радует, что разработчики обещали в будущем это пофиксить… Если Вас заинтересовало, о чём тут идёт речь, обратите внимание на разницу во времени исполнения разных вариантов применения инструкции TOP и ROWCOUNT




-- SQL Server 2005 USE AdventureWorks GO PRINT '---------------------------- проба TOP 100 --------------------------------'; SET STATISTICS TIME ON; SELECT TOP 100 * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber; SET STATISTICS TIME OFF; GO PRINT '---------------------------- ROWCOUNT 100 ---------------------------'; SET ROWCOUNT 100; SET STATISTICS TIME ON; SELECT * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber SET STATISTICS TIME OFF; GO SET ROWCOUNT 0; PRINT '---------------------------- TOP 101 --------------------------------'; SET STATISTICS TIME ON; SELECT TOP 101 * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber; SET STATISTICS TIME OFF; GO PRINT '---------------------------- TOP 101 WITH TIES ----------------------'; SET STATISTICS TIME ON; SELECT TOP 101 WITH TIES * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber; SET STATISTICS TIME OFF; GO PRINT '---------------------------- TOP 101 witl list all filds ------------'; SET STATISTICS TIME ON; SELECT TOP 101 [SalesOrderID] ,[SalesOrderDetailID] ,[CarrierTrackingNumber] ,[OrderQty] ,[ProductID] ,[SpecialOfferID] ,[UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks].[Sales].[SalesOrderDetail] ORDER BY CarrierTrackingNumber; SET STATISTICS TIME OFF; GO PRINT '----------------------------- TOP (@top=101) -----------------------'; DECLARE @top int; SET @top = 101; SET STATISTICS TIME ON; SELECT TOP (@top) * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber; SET STATISTICS TIME OFF; GO PRINT '----------------------------- TOP (@top=101) WITH TIES -------------'; DECLARE @top int; SET @top = 101; SET STATISTICS TIME ON; SELECT TOP (@top) WITH TIES * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber; SET STATISTICS TIME OFF; GO PRINT '----------------------------- ROWCOUNT 101 -------------------------'; SET ROWCOUNT 101 SET STATISTICS TIME ON; SELECT * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber; SET STATISTICS TIME OFF; GO -- На SQL Server 2000 этот порог равен TOP 1023


Получается, что если в условии исполнения запроса подразумевается сортировка по символьному, не индексированному полю, то для ограничения выборки превышающим 100 числом записей намного эффективнее использовать ROWCOUNT */