Рассылка закрыта
При закрытии подписчики были переданы в рассылку "Вопросы и ответы по MS SQL Server" на которую и рекомендуем вам подписаться.
Вы можете найти рассылки сходной тематики в Каталоге рассылок.
MS SQL Server - дело тонкое...
| Информационный Канал Subscribe.Ru |
| #127<< #128 |
|
СОДЕРЖАНИЕ БЕЗОПАСНОСТЬ Ламмеры от SQL Server получили второе предупреждение... По материалам Panda Software и форума SQL.RU.
1. Вирус Helkern/SQLSlammer резидентный, т.е. перезагрузка спасает, но если в сети есть другие инфицированные компьютеры, то ненадолго.
Международная служба технической поддержки компании Panda Software получает огромное количество запросов из различных стран мира,
связанных с SQLSlammer. Поскольку этот червь не имеет каких-либо видимых признаков (файлов, электронных сообщений и
т.д.), его присутствие в системе обнаружить довольно трудно. Среди основных приложений, использующих Microsoft SQL Server, можно выделить следующие:
- Compaq Insight Manager; Следующие продукты включают MSDE, но не ставят его по умолчанию:
- Access 2002; Следующие продукты включают MSDE и ставят его по умолчанию:
- Application Center 2000; Для проверки, установлен ли у вас Microsoft SQL Server, выполните следующие шаги: Откройте окно команд на сервере. Введите следующую команду: "netstat -p udp –a" Посмотрите, присутствует ли нижеприведенная запись среди информации, выведенной на экран: UDP :ms-sql-m Данная строка означает, что сервер поддерживает Microsoft SQL Server и, следовательно, необходимо получить соответствующие модули обновления. Сделать это можно на вебсайте компании Microsoft по адресу: http://www.microsoft.com/technet/security/bulletin/MS02-039.asp Основной целью SQLSlammer является рассылка команд отказа в обслуживании запросов (Denial of Service) на серверы корпоративных сетей. Червь делает это, отправляя многочисленные пакеты данных, содержащих код вируса, на порт UDP 1434. Действия и характеристики этой вредоносной программы во многом схожи с червем Code Red, который, используя слабое место в IIS, распространяется, не оставляя никаких следов на традиционных накопителях информации. По данным Computer Economics, ущерб, нанесенный Code Red в 2001 году, составил 2,970 миллионов евро. Для получения более подробной информации о SQLSlammer, а также других вредоносных программах посетите сайт Вирусной Энциклопедии Panda Software по адресу: http://www.pandasoftware.com/virus_info/
SQL Server 2000 SP 3: What's New in Security
Unauthenticated Remote Compromise in MS SQL Server 2000 Индексы. Теоретические основы. (ПРОДОЛЖЕНИЕ)
Вольный перевод главы книги Design SQL Server 2000 Exam 70-229 © Sybex 2001
До этого момента мы обсуждали индексы, созданные на базе одного поля таблицы. Но индекс может быть создан на основании нескольких полей. В этом случае существует только одно ограничение – длина ключа индекса не должна превышать 900 байтов. Если индекс построен по полям с фиксированным размером, сумма длин этих полей должна не превышать эти 900 байт, если индекс построен по полям с переменной длинной, сумма максимальных размеров полей может превышать 900 байт, но само значение сумм по каждой записи не может быть больше 900 байт. Например в таблице есть два поля переменной длины по 500 байт. SQL Server позволяет создать составной (композитный) ключ на базе этих двух полей, если нет записей, сумма длин по обеим полям превышает 900 байт. Стоит обратить внимание на тот момент, что составной индекс для (Column1, Column2) является совершенно отличным от (Column2, Column1), а так же от индексов созданных по двум этим полям в отдельности. Как будет показано позже, Query Optimizer может использовать все эти индексы в зависимости от структуры запроса. Уникальный индекс существует для реализации целостности данных в БД. Как рассказывалось ранее целостность данных может быть гарантирована или Unique constrain или primary key. В основе обоих лежит уникальный индекс. Уникальный индекс гарантирует что каждое значение является уникальным в индексируемом поле или в случае с составным индексом – любая группа значений по полям составного индекса является уникальной. После создания уникального индекса, при попытке вставить повторяющееся значение, вы получите сообщение об ошибке 2601: Cannot insert duplicate key row in object tablename with index indexname. Теперь вы знакомы с терминологией и типами индексов используемых в SQL Server и мы можем приступить к рассмотрению как SQL Server производит работу с данными при наличии индексов и при их отсутствии. Доступ к записям при наличии или отсутствии индексов В зависимости от наличия/отсутствия индексов и их типов, SQL Server может выбрать различные способы доступа к данным таблицы:
- Сканирование таблицы. Когда индексы не используются и таблица не имеет кластерного индекса. Таблица храниться как
куча (heap) Когда запрос выполняется к таблице не имеющей кластерного индекса и без использования не кластерных индексов, выполняется простое сканирование таблицы. Для того что бы найти данные в куче (heap), SQL Server использует Index Allocation Map (IAM). IAM представляет страницу которая содержит карту всех экстентов, которые содержат данные указанной таблицы. SQL Server использует IAM что бы найти все страницы с данными. Рис.7 иллюстрирует методологию используемую SQL Server. 1 означает что экстент используется объектом, а 0 – что не используется. Любой запрос, который не может использовать индексы, работает по следующей методологии:
1. SQL Server делает запрос к системной таблице Sysindexes для нахождения FirstIAM страницы. Как вы видите, последовательность записей, получаемых конечным пользователем зависит от того в каком порядке данные хранятся в IAM а не от порядка вставки записей в таблицу. Это объясняет что нет никакой логики в порядке полученных данных, поскольку порядок их получения зависит от их порядка в IAM. Выполним следующий запрос: SELECT id, indid, fistIAM FROM sysindexes WHERE id=OBJECT_ID(‘CustomerCustomerDemo’) Получим результат:
Рис.7 Доступ к таблице без индекса Заметьте что indid имеет значение 0, что означает что данная таблица хранится как heap. В случае когда производится поиск по таблице без индексов, производится простое сканирование всех записей. Эта операция показывается в query execution plan следующей иконкой:  Доступ к данным с использованием кластерного индекса Как только в таблице имеется кластерный индекс, IAM более не используется для доступа к данным. IAM не исчезает вообще, но используется только для сопровождения таблицы как объекта в базе данных. Страницы данных взаимосвязаны и данные в них находятся в соответствии с кластерным индексом. Если выполнить запрос SELECT * FROM Customers без каких-либо условий WHERE, то система выполнить сканирование таблицы с использованием кластерного индекса. Эта операция очень похожа на простое сканирование таблицы. Главное различие между сканированием по кластерному индексу то, что результат сканирования вернется в порядке сортировки по кластерному индексу. Эта операция показывается в query execution plan следующей иконкой:  Теперь если выполнить поиск по кластерному индексу, SQL Server будет выполнять индексный поиск. Например: SELECT * FROM Customers WHERE customerid = ‘ALFKI’ Эта операция показывается в query execution plan следующей иконкой:  Заметьте что стрелочка изогнута что индицирует что SQL Server использует индекс для нахождения соответствующего значения. При выполнении этой операции SQL Server находит root page из таблицы Sysindexes и ищет совпадение значений по индексу. Рис.8 Показывает как выполняется указанный поиск. 
Рис. 8 Сначала SQL Server находит root page используя запрос SELECT id, indid, root FROM sysindexes WHERE id = OBJECT_ID(‘Customers’) Рассмотрим результат:
Indid равно 1, что указывает на то что это кластерный индекс. В случае heap это значение будет равно 0. Значение root - входная дверь для индекса. Как только отправная точка будет найдена, SQL Server найдет все значения удовлетворяющей условию WHERE. Это проиллюстрировано на Рис.8 Мы ищем записи, значение которых равно ‘London’. С root страницы мы находим что запись ‘London’ находится между ‘Johannesburg’ и ‘London 2’. Таким образом записи, удовлетворяющие условию начинаются на странице 11, где находится ‘Johannesburg’. Мы помним что записи отсортированы по этому полю. Следовательно SQL Server будет отбирать записи до конца 11й страницы и перейдет на следующую страницу пока будет находить записи ‘London’ (помните что страницы прилинкованы) В этом конкретном примере, SQL Server сканирует три физические страницы вместо пяти при полном сканировании таблицы. Доступ к данным с использованием не кластерного индекса При доступе к данным при помощи не кластерного индекса, следует обратить внимание что способ доступа будет зависеть от наличия кластерного индекса в таблице. Вспомним, что существует только два пути доступа к записям таблицы: через row ID и через кластерный индекс. Если удалить все индексы таблицы Customers и создать некластерный индекс по полю City, план исполнения запроса будет выглядеть следующим образом:  SQL Server использует индекс для нахождения записей, совпадающих с условиями запроса. На leaf level индекса, будут взяты row locator (row ID или cluster key). Выполним запрос SELECT id, indid, root FROM sysindexes WHERE id = OBJECT_ID(‘Customers’) Мы получим результат похожий на следующий:
Заметим что значение Indid имеет значение 2, индицирующее что запись ссылается на некластерный индекс. Значение между 2 и 250 зарезервированы для не кластерных индексов. Рис.9 Иллюстрирует процесс поиска когда таблица не имеет кластерного индекса. Как только SQL Server найдет root страницу, он сканирует root страницу и находит страницу(ы), которые соответствуют искомому результату. В нашем примере сервер читает 4 страницы, но выполняет доступ к 5 страницам. Поскольку искомое значение появляется дважды на одной физической странице, то будет произведено два логических чтения этой страницы, даже если было произведено только одно физическое чтение этой страницы. Если сравнить поиск с кластерным индексам, заметим что некластерный индекс имеет дополнительный leaf level, роль которого в кластерном выполняют сами данные. Рис.10 показывает поиск по не кластерному индексу при наличии кластерного. Разница с поиском без кластерного в том что SQL Server находит кластерные ключи на последнем уровне не кластерного индекса. А используя кластерный ключ, находит данные в таблице. Заметим что план выполнения запроса, увы, не показывает разницы. 
Рис. 9 Теперь вы имеете полное представление как SQL Server использует индексы при выборке данных. Теперь остается один большой вопрос – как SQL Server решает какие индексы использовать для конкретного запроса. 
Рис. 10 Посмотрим на приме с книгой: иногда бывает два и более индекса по книге. Например по теме и по ключевым словам. Читатель выбирает индекс, соответствующий его требованиям для конкретного случая. SQL Server поступает тем же самым образом. Но остается вопрос – если таблица имеет один кластерный индекс и четыре не кластерных, как SQL Server узнает какой из индексов использовать. Распределение статистики дает query optimiser возможность выбрать определенный индекс. Индексы не выбираются на основании анализа распределенной статистики. Немного истории – в SQL Server 6.5 статистика хранилась на странице в 2 Кб, независимо от размера индекса. Таким образом при увеличении размера индекса, уменьшалась точность описания распределения данных по индексу. В SQL Server 2000 статистика хранится в поле данных для хранения имиджей. То есть – растет пропорционально размеру индекса, это позволяет не терять точность при увеличении размера индексов. Что бы понять что из себя представляет статистика, рассмотрим следующий пример. Возьмем таблицу Orders из базы данных Northwind. Если мы выполним следующий запрос: SELECT TOP 24 OrderID, convert(char(11), OrderDate) FROM Orders ORDER BY OrderDate Вы получите следующий результат: OrderID OrderDate 10248 Jul 4 1996 10249 Jul 5 1996 10250 Jul 8 1996 10251 Jul 8 1996 10252 Jul 9 1996 10253 Jul 10 1996 10254 Jul 11 1996 10255 Jul 12 1996 10256 Jul 15 1996 10257 Jul 16 1996 10258 Jul 17 1996 10259 Jul 18 1996 10260 Jul 19 1996 10261 Jul 19 1996 10262 Jul 22 1996 10263 Jul 23 1996 10264 Jul 24 1996 10265 Jul 25 1996 10266 Jul 26 1996 10267 Jul 29 1996 10268 Jul 30 1996 10269 Jul 31 1996 10270 Aug 1 1996 10271 Aug 1 1996 В этом примере выбраны записи с 4 июля 1996 по 1 августа 1996. Теперь посчитаем сколько раз встречаются те или иные значения: SELECT convert(char(11), A.orderdate) as OrderDate , count(*) as '# of OrderDate' FROM (SELECT TOP 24 OrderID, OrderDate FROM Orders ORDER BY OrderDate) as A GROUP BY A.orderdate OrderDate # of OrderDate' Jul 4 1996 1 Jul 5 1996 1 Jul 8 1996 2 Jul 9 1996 1 Jul 10 1996 1 Jul 11 1996 1 Jul 12 1996 1 Jul 15 1996 1 Jul 16 1996 1 Jul 17 1996 1 Jul 18 1996 1 Jul 19 1996 2 Jul 22 1996 1 Jul 23 1996 1 Jul 24 1996 1 Jul 25 1996 1 Jul 26 1996 1 Jul 29 1996 1 Jul 30 1996 1 Jul 31 1996 1 Aug 1 1996 2 Когда SQL Server считает или сортирует данные, он заранее знает как много тех или иных значений он найдет в указанном запросе. Например, выполним следующий запрос: SELECT * FROM Orders WHERE OrderDate BETWEEN ‘1996-07-15’ AND ‘1996-07-20’ SQL Server знает что запрос вернет только 6 записей еще до того как будет произведен доступ к таблице. Назначение статистики базируется на простом алгоритме: для выбора стратегии доступа к данным, SQL Server должен знать как много записей вернет запрос. В SQL Server статистика не просто учитывает количество записей для того или иного значения. Первое, статистика более точно описывает распределение данных по значениям. Второе, зона описания статистики может не содержать всех значений индексируемого поля. Например, в таблице Orders 830 записей, но статистика хранится только для 186 значений. Как и почему именно 187 – этот алгоритм не рассматривается в данном издании. Выполним команду dbcc show_statistics (Orders,OrderDate) Statistics for INDEX 'OrderDate'. Updated Rows Rows Sampled Steps Density Average key length ----------------------------------------------------------------------------- Dec 23 2002 9:30AM 830 830 187 1.6842016E-3 12.0 Этот результат показывает следующее
- статистика была просчитана в последний раз 23 декабря 2002 Пожалуй самым интересным здесь будет значение плотности распределения. Если каждое значение в таблице уникальное, то плотность будет 1/830, то есть 0.12%. Но в нашем примере мы имеем 0.17% показывает что некоторые значения встречаются 2 и более раз. Например мы видим что для 8 июля 1996 встречается дважды. Теория говорит, что чем меньше плотность, тем лучше – это увеличивает избирательность, а следовательно и ценность построенного индекса. Например если колонка содержит только 3 значения, плотность распределения будет равна 33.3%, что показывает бесполезность построения индекса по данному полю. Индексы занимают место на диске и в оперативной памяти и отнимает быстродействие. В идеале самый лучший индекс имеет плотность распределения равную единице, деленной на количество записей в таблице - все записи уникальны. При построении индексов, обращайте внимание на плотность распределения – если она превышает 10%, то индекс можно считать бесполезным. Сканирование по таблице в таком случае будет более эффективным. Второй результат, возвращаемый командой dbcc show_statistics All density Average Length Columns ---------------------------------------------------------------- 2.0833334E-3 8.0 OrderDate 1.2048193E-3 12.0 OrderDate, OrderID Это дает очень интересный результат потому что плотность для одного поля OrderDate 0.2%, а для пары OrderDate, OrderID уже 0.12. Поскольку OrderID является primary key для таблице, то понижение плотности вполне очевидно. Последний результат может быть наиболее интересен для полного понимания важности статистики. RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS 1996-07-04 0.0 1.0 0 1996-07-15 7.0 1.0 6 1996-07-19 3.0 2.0 3 1996-07-25 3.0 1.0 3 1996-08-01 4.0 2.0 4 Во-первых, заметим что только пять значений в зоне распределения вместо 24 в самой таблице. Тем не менее учитывая значения по всем колонками, система знает сколько записей будет в выборке. Колонка RANGE_HI_KEY дает высшее значение для значения, сохраненного в статистике. Мы знаем что 1996-07-04 является первым значением и следующим за ним идет 1996-07-15. Между этими двумя значениями находится 7 записей. Колонка RANGE_ROWS дает нам эту информацию. Только три значения есть между 1996-07-15 и 1996-07-19, и так далее. Колонка DISTINCT_RANGE_ROWS содержит информацию о том сколько определенных (неповторяющихся) значений в интервале. Например в интервале с 1996-07-04 по 1996-07-15 есть 6 определенных значений из 7 записей в интервале. Это говорит что одно значение в указанном интервале повторяется дважды. Мы не указали еще одну колонку в результате команды dbcc show_statistics - AVG_RANGE_ROWS, которое является результатом простой арифметической операции RANGE_ROWS /DISTINCT_RANGE_ROWS. Анализируя эту информацию, можно определить как распределены данные в индексе, а так же как много записей вернет запрос. Как мы видели в предыдущих примерах, SQL Server достаточно точно знает как данные распределены по таблице при наличии индекса. Каждый раз при выполнении запроса, SQL Server первым делом оценивает статистику. Давайте рассмотрим несколько примеров для более полного понимания данного процесса. SELECT * FROM Orders WHERE OrderDate BETWEEN ‘1996-07-19’ AND ‘1996-07-25’ SQL Server сначала проверяет существования индекса по полю OrderDate или составного индекса, начинающегося с поля OrderDate. В таком случае SQL Server знает как много записей вернется в результате запроса. Query Analyser дает следующую картинку: Для того что бы посмотреть детали каждого этапа выполнения запроса, просто выберете нужный этап и кликните правой клавишей мыши. Мы видим что оценочное количество записей – 6, что и реально соответствует действительности. Теперь выполним этот запрос: SELECT * FROM Orders WHERE OrderDate BETWEEN ‘1996-07-19’ AND ‘1996-07-25’ AND CustomerID = ‘FOLKO’ SQL Server найдет один индекс OrderDate и один CustomerID. Он будет использовать их обоих и результатом будет пересечение по этим двум условиям. Оценка дает 6 значений по OrderDate и только одно для CustomerID. В этом конкретном случае SQL Server вернет только одно значение. Если заменить условие операции AND на OR, система может сделать объединение индексов или может предпочесть полное сканирование таблицы, если сочтет что такой путь будет более быстрым и имеет меньшую стоимость чем работа с индексами. В нашем конкретном случае в результате запроса: SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-19' AND '1996-07-25' OR CustomerID = 'FOLKO' Было произведено полное сканирование таблицы так как стоимость операции OR оказалась выше. Это объясняется тем что размеры таблицы сравнительно малы. Что же произойдет, если для таблицы нет индексов, следовательно нет статистики? Ответ прост: SQL Server не может работать без статистики! И он создаст статистику без каких либо индексов. Если выполнить запрос: SELECT * FROM Orders WHERE ShipCity=’Grass’ SQL Server автоматически создаст статистику для этого поля, поскольку нет индекса по нему. Для того что бы просмотреть все индексы, созданные для определенной таблицы, выполним запрос: SELECT name, first, root FROM sysindexes WHERE id=OBJECT_ID(‘Orders’) Мы получили результат: name first root PK_Orders 0xCD0000000100 0xCB0000000100 CustomerID 0x690100000100 0x6C0100000100 CustomersOrders 0x6F0100000100 0x720100000100 EmployeeID 0xDA0000000100 0xDA0000000100 EmployeesOrders 0xDC0000000100 0xDC0000000100 OrderDate 0xDE0000000100 0xDE0000000100 ShippedDate 0xE00000000100 0xE00000000100 ShippersOrders 0xE20000000100 0xE20000000100 ShipPostalCode 0x740100000100 0x770100000100 _WA_Sys_ShipCity_014935CB 0x000000000000 0x000000000000 Заметьте имя “индекса” _WA_Sys_ShipCity_014935CB с root адресам 0х0 – потому что это не индекс. Это статистика для поля ShipCity. Вам не стоит беспокоится о засорении вашей базы данных ненужной информацией – автоматически созданная статистика будет уничтожена так же автоматически когда SQL Server посчитает что она более не нужна. Просто доверьтесь SQL Server. (Хорош оборот, неправда ли!?) Вы можете так же изучить наличие статистики при помощи команды: sp_helpstats ‘Orders’ statistics_name statistics_keys _WA_Sys_ShipCity_014935CB ShipCity Эта статистика была создана автоматически потому что параметр базы данных AUTO_CREATE_STATISTICS установлен в “On”. Автоматическое обновление статистики является огромной помощью для DBA. Порой крайне сложно определить какая же статистика должна быть создана и эту функцию на себя взял SQL Server. Существует только одна проблема - поддерживать корректную на каждый момент времени статистику. Что произойдет если статистика устареет и не будет отражать реальную картину с данными в таблице. Ответ очень прост – выбор индекса может быть неверен. Представим что статистика была собрана когда в таблице было только 1 000 записей, а теперь ее размер 100 000. Что бы быть реально полезной, статистика должна содержать текущие реальные данные. SQL Server позволяет обновлять статистику автоматически без привлечения дополнительных усилий со стороны DBA. Проверить, установлен ли флаг автоматического обновления статистики, можно командой: SELECT DATABASEPROPERTYEX(‘dbname’,’IsAutoUpdateStatistics’) Если результат равен 1, это значит что опция автоматического обновления статистики включена. Установить опцию в этот режим можно командой ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS ON а выключить ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF Так же эту операцию можно установить используя хранимую процедуру sp_dboption, но в SQL Server 2000 она оставлена только для обратной совместимости и может исчезнуть в будущих версиях. Рекомендуется оставлять эту функцию включенной. В этом случает SQL Server будет сам обновлять статистику когда посчитает ее устаревшей. Алгоритм обновления полностью определяется SQL Server и зависит от количества обновлений, удалений и добавлений записей в таблицу. Если таблица имеет один миллион записей и только 100 из них были изменены (0.01%), вряд ли имеет смысл обновлять статистику поскольку такие изменения в таблице вряд ли драматически поменяли общую картину данных. Кроме того если размер таблицы более 8МБ (1 000 страниц), SQL Server не будет использовать все данные для вычисления статистики. Все эти ограничения разработаны для того что бы работа со обновлением статистики наносила как можно меньший удар на быстродействие сервера. Так же управлять автоматическим обновлением статистики можно при помощи хранимых процедур типа sp_autostats: Команда sp_autostats ‘Orders’ показывает включена ли опция автоматического обновления статистики для конкретных индексов и когда обновление было сделано в последний раз. Так же эта команда позволяет включить или отключить опцию обновление для всех индексов таблицы или для какого-то конкретного индекса. Итого что бы отключить автоматическое обновление статистики у вас есть несколько вариантов:
- ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS Как только автоматическое обновление будет отключено, вы будете вынуждены обновлять статистику вручную. Эта операция может быть выполнена при сопровождении индексов или операцией UPDATE STATISTICS. Полное описание UPDATE STATISTICS стоит изучить по BOL. Создание индексов и статистики. Создание индексов и статистики достаточно простой процесс и выполняется командами CREATE INDEX и CREATE STATISTICS. Давайте посмотрим как же индексы создаются. Как мы изучили ранее, существует два вида индексов: кластерные и не кластерные. В дополнение к этим типам, еще существуют дополнительные опции как: уникальный индекс, сложный индекс и т.д. Все это многообразие выбора необходимо для работы с индексами наиболее подходящими к конкретной ситуации. Кластерные и не кластерные индексы По умолчанию, индекс всегда создается не кластерным, если не указано иное. Таблица может иметь только один кластерный индекс. Процедура sp_helpindex возвращает полный список всех индексов для таблицы. Например sp_helpindex Orders index_name index_description index_keys CustomerID nonclustered located on PRIMARY CustomerID CustomersOrders nonclustered located on PRIMARY CustomerID EmployeeID nonclustered located on PRIMARY EmployeeID EmployeesOrders nonclustered located on PRIMARY EmployeeID OrderDate nonclustered located on PRIMARY OrderDate PK_Orders clustered, unique, primary key located on PRIMARY OrderID ShippedDate nonclustered located on PRIMARY ShippedDate ShippersOrders nonclustered located on PRIMARY ShipVia ShipPostalCode nonclustered located on PRIMARY ShipPostalCode Вы так же можете получить более удобный результат при выборе команды Tools a Manage indexes в Query Analyser. Заметим что все индексы находятся в PRIMARY file group. В случае необходимости повышения быстродействия, можно поместить индексы в другую file group, отличную от местонахождения таблицы, для баланса I/O операций между разными физическими дисками. Команда CREATE INDEX имеет ряд параметров. Давайте рассмотрим некоторые из них. 1) ASC|DESC ASC значит что индекс будет построен по возрастанию ключей. DESC соответственно – по убыванию. Эта опция не дает никакой разницы на поиск данных, но оказывает существенное влияние на скорость выполнения ORDER BY опции в запросах. 2) SORT_IN_TEMPDB Полезна при создании индексов. Эта опция может увеличить производительность системы во время создания индексов. Индексы создаются в два этапа. На первом создается временный набор данных с отсортированными ключами для не кластерного индекса. На втором – окончательный результат переносится на место его хранения в базе данных. Без указания данной опции временный результат создается в той же file group, где и будет создан индекс. При указанной опции, временный результат будет находиться в базе данных Tempdb. Преимущества очевидны, особенно если поместить базу данных Tempdb на другой физический диск. Выигрыш будет получен на обеих стадиях так как чтение и запись данных будут вестись параллельно на двух разных дисках. Вторая положительная сторона использования указанного параметра – созданный индекс будет менее фрагментирован. Отрицательный момент – в общем требуется больше памяти на дисках и надо уделять дополнительное внимание базе данных Tempdb. 3) IGNORE_DUP_KEY Очень хитрая опция. Она рассматривает поведение в случае вставки данных в уникальный столбец (или группу столбцов). Без этой опции если идет попытка вставки дублирующего значения, то вся вставка будет откачена (rolled back). Server: Msg 2601, Level 14, State 3, Line 1 Cannot insert duplicate key row in object 'TestTable' with unique index 'id1'. The statement has been terminated. С этой опцией вставка записей будет продолжена, а дублирующие записи отброшены с сообщением warning: Server: Msg 3604, Level 16, State 1, Line 2 Duplicate key was ignored. Пример для проверки: create table TestTemp (c1 int, c2 varchar(10)) create table TestTable (c1 int, c2 varchar(10)) create table TestTable_IGNORE_DUP_KEY (c1 int, c2 varchar(10)) create unique index id1 on TestTable(c1) create unique index id2 on TestTable_IGNORE_DUP_KEY(c1) with IGNORE_DUP_KEY insert TestTemp values(1,'test') insert TestTemp values(1,'test') insert TestTable select * from TestTemp insert TestTable_IGNORE_DUP_KEY select * from TestTemp По умолчанию индексы позволяют иметь дублирующие значения. Если необходимо что бы значения были уникальны, можно включить опцию UNIQUE в CREATE INDEX. Если при создании индекса будут найдены дублирующие значения, создание индекса будет остановлено с сообщением об ошибке: Server: Msg 1505, Level 16, State 1, Line 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '1'. The statement has been terminated. Кластерный индекс строится с опцией CLUSTERED: create CLUSTERED index id3 on TestTemp(c1) При попытке создания кластерного индекса на таблице, где он уже существует вы получите ошибку: Server: Msg 1902, Level 16, State 3, Line 1 Cannot create more than one clustered index on table 'TestTemp'. Drop the existing clustered index 'id3' before creating another. В создании композитного (сложного) индекса участвуют несколько полей таблицы. При создании индекса следует обращать внимание на порядок следования полей в индексе. Например, если создается индекс по полям Field1, Field2, то он может быть применен только в запросе где в критериях используются оба этих поля. Так же этот индекс будет полезен для условий, построенных для одного Field1. Для одного Field2 этот индекс не может быть применен. Если в дополнении к индексу по полям Field1, Field2 добавить индекс по полям Field2, Field1, то SQL Server при построении плана запроса будет анализировать какой из них более селективен в применении к ограничениям на условия запроса. Последний момент в построении композитного индекса по полям Field1, Field2 – он не равен сумме индексов по указанным полям. В случае когда в запросе могут быть использованы оба поля как критерий поиска при раздельных индексах по полям будет построено пересечение по индексам, что медленнее чем выборка из компаундного индекса. SQL Server 7 представил новую возможность – вычисляемые поля в таблице. SQL Server 2000 добавили возможность построения индекса по этому полю. Достоинство нововведения почти очевидна – при отсутствии индекса, значение вычисляется на лету при выполнении запроса. При наличии индекса, результат хранится на leaf level индекса и вычисляется во время добавления/изменения записи. Однако это выполняется только в том случае если этот индекс задействован при выполнении запроса. Индекс по вычисляемому полю может построен при выполнении ряда ограничений: - вычисляемое значение должно быть определенным и прецизионным. Определить выполнение этого правила можно выполнив пару проверок:
select columnproperty(object_id('tablename'),'columnname','IsDeterministic')
select columnproperty(object_id('tablename'),'columnname',' IsPrecise')
оба запроса должны дать результат 1 - опция ANSI_NULLS должна была быть установлена в ON при создании таблицы. Проверяется запросом:
select OBJECTPROPERTY(object_id('tablename'),'IsAnsiNullsOn')
- вычисляемое поле не должно иметь тип text, ntext или image Далее, шесть опций должны быть установлены в ON во время создания индекса:
- ANSI_NULLS И одна в OFF: - NUMERIC_ROUNDABOUT Все эти опции кроме ARITHABORT установлены корректно при использовании OLE-DB или ODBC. Это можно поправить следующим скриптом: declare @val int select @val=(value|64) from master..spt_values join master..sysconfigures on number=config where name = 'user options' exec sp_configure 'user options', @val RECONFIGURE Если семь указанных опций не уставновлены в нужное состояние, оптимизатор не будет рассматривать индекс для вычисляемого поля при анализе запроса. Fillfactor очень важный параметр для уменьшения фрагментирования. Он указывает на процент заполнения страницы на leaf level. Например если fillfactor=100, это значит при создании индекса на leaf level страницы будут заполнены на 100%. Это обеспечивает наименьшее фрагментирование данных на диске, но при вставке или изменении записи возможно расщепление страницы, что вызывает дополнительные I/O операции. При выставлении fillfactor меньше 100, на каждой вновь создаваемой странице индекса на leaf level будет оставляться свободное место и при вставке данных расщепление производиться не будет. То есть, значение 100 имеет смысл когда идет работа со статическими данными или для таблиц, где производится только добавление данных в конец таблицы(например индекс по identity полю). Опыт показывает что часто обновляемых таблиц оптимальным следует считать значение 80. Стоит так же помнить что SQL Server не придерживается строго указанных цифр – если, например, fillfactor выставлен в 50, а на странице может содержаться информация о 13 записях, то реально SQL Server будет хранить 7 записей, что дает реальный fillfactor 53.8 Помимо параметра fillfactor, регламентирующего заполнение на leaf level, существует параметр PAD_INDEX, который определяет заполнение на более высоких уровнях. Мы видели что система может сама создавать статистику для полей таблицы когда индекс не существует. Это работает, но отнимает ресурсы при выполнении запроса. Более того – автоматически создаваемая статистика создается только для одного поля, а для оптимизатора может быть необходима статистика по нескольким полям сразу. Для изучения синтаксиса CREATE STATISTICS обратитесь к BOL. Помните что статистика оказывает воздействие на решения принимаемые оптимизатором при построении плана запроса, но не оказывает воздействие на скорость выполнения запроса. Эта функция лежит на индексах. Фрагментация и сопровождение индексов Не смотря на то что большое число операций в SQL Server 2000 автоматизированы, сопровождение индексов может быть значительной частью работы администратора БД. Если понизилось быстродействие – первое на что следует обратить внимание, фрагментация БД. Фрагментация БД возникает когда данные в БД меняются. Когда запись удаляется, в файле БД высвобождается место. Когда вставляется новая запись, это может привести к расщеплению страниц, что приводит к появлению пустого пространства на страницах данных. Когда данный обновляются, это может привести к изменению размера записи и к возникновению двух ранее упоминавшихся случаев. Все это приводит к фрагментации. В SQL Server рассматриваются два типа фрагментации: внутренняя и внешняя. Внутренняя подразумевает пустоты внутри страницы. Внешняя – непоследовательность связей страниц. Если страницы не полностью заполнены данными, это приводит к дополнительным операциям I/O и переиспользованью оперативной памяти. Помните что страницы в оперативной памяти есть зеркальное отражение страниц на диске. В идеале страницы должны быть подлинкованы слева направо в порядке хранения данных. Вследствие расщепления страниц этот порядок может быть нарушен. Это приводит как к неполному заполнению страниц, так и к увеличению операций I/O вследствие непоследовательного положения цепочек страниц на диске – это вызывает дополнительные перемещения головок с цилиндра на цилиндр диска. А это одна из наиболее медленных дисковых операций. Команда DBCC SHOWCONTIG помогает определить как внутреннюю так и внешнюю фрагментацию. Выполним команду
DBCC SHOWCONTIG('Orders')
Изучим результат: DBCC SHOWCONTIG scanning 'Orders' table... Table: 'Orders' (21575115); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 20 - Extents Scanned..............................: 5 - Extent Switches..............................: 4 - Avg. Pages per Extent........................: 4.0 - Scan Density [Best Count:Actual Count].......: 60.00% [3:5] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 40.00% - Avg. Bytes Free per Page.....................: 146.5 - Avg. Page Density (full).....................: 98.19%Команда DBCC SHOWCONTIG работает на leaf level поэтому дает ответ только о положении страниц. Расшифруем результат:
-- Pages Scanned указывает количество страниц в таблице. В нашем примере их 20. В нашем примере мы имеем Avg. Page Density 98.19%, что означает что нет внутренней фрагментации (длина записей не всегда совпадает с размером страницы). С другой стороны Scan Density 60% и Extent Scan Fragmentation 40% говорит о внешней фрагментации. Если мы дефрагментируем таблицу а выполним эту команду еще раз, мы получим следующий результат: DBCC SHOWCONTIG scanning 'Orders' table... Table: 'Orders' (21575115); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 20 - Extents Scanned..............................: 3 - Extent Switches..............................: 2 - Avg. Pages per Extent........................: 6.7 - Scan Density [Best Count:Actual Count].......: 100.00% [3:5] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 146.5 - Avg. Page Density (full).....................: 98.19% Insert Вставка может приводить к фрагментации на leaf level. Кластерные индексы особенно чувствительны к вставке данных. Проблема в том что при расщеплении страниц, новая страница будет расположена в первом попавшемся свободном месте базы данных. При многочисленных вставках база данных рискует стать очень фрагментируемой. Updates При обновлении SQL Server всегда старается оставить запись на старом месте и избежать ее переноса на новое место. Но это невозможно при увеличении длины записи. В случае переноса записи SQL Server использует указатели на новое место. Это позволяет не перестраивать индексы. Стремление не обновлять индексы наносит негативный удар по производительности поскольку приводит к фрагментированию базы данных. Данный алгоритм вполне оправдан для OLTP БД. Deletes Как мы видели вставки приводят к внутренней и внешней фрагментации, а обновления к внешней. Удаления записи приводит к внутренней фрагментации – делает “дырки” на страницах. При удалении записи, SQL Server не удаляет физически эти записи, а помечает их как удаленные. При вставке или обновлении записей, свободное место от удаленных записей может быть переиспользовано. В противном случае каждые пол часа запускается процесс, который утилизирует удаленные записи. Удаление записей приводит в внутренней фрагментации и переиспользование места на диске приводит к дополнительным операциям I/O. И не забывайте что страницы на диске копируются в память – нерациональное использование дисковой памяти приводит к переиспользованью оперативной памяти.Поэтому следует внимательно следить за фрагментацией таблиц для достижения максимального быстродействия системы. Теперь вы понимаете как работает вставка, обновление и удаление данных и как это приводит к фрагментации. Для дефрагментации в SQL Server существует три пути:
- DBCC INDEXDEFRAG DBCC INDEXDEFRAG производит дефрагментирование leaf level для всех типов индексов и исправляет как внутреннюю так и внешнюю дефрагментацию. Однако эта команда не создает новые страницы, а лишь перетряхивает информацию в уже имеющихся. Таким образом сильно фрагментированные индексы не получат реальной помощи от этой команды. Эта команда имеет одно из главных достоинств: она накладывает блокировку на очень короткий период в отличии от иных команд.DBCC DBREINDEX может использована для перестройки индексов и возможно для изменения fillfactor. Если вы хотите перестроить все индексы для таблицы, необходимо выдать команду c пустым вторым параметром: Опция WITH DROP_EXISTING для команды CREATE INDEX позволяет удалить индексы и перестроить их при помощи одно команды. Эта команда особенно полезна для кластерного индекса поскольку некластерный индекс может быть не перестроен. Посмотрим на пример: вы выполняете команду CREATE INDEX WITH DROP_EXISTING без перестройки ключей. Поскольку кластерный индекс выполняет функцию row locator, то он остается не перестроенным. Следовательно и все остальные индексы останутся нетронутыми. Если будет удален кластерный индекс, это потребует перестройки всех не кластерных индексов (сменился row locator). Таким образом для перестройки всех индексов таблицы достаточно выдать команду для перестройки одного кластерного индекса. Вы можете модифицировать структуру индексов во время использования опции WITH DROP_EXISTING но только с одним ограничением – невозможно преобразование кластерного индекса в некластерный. Вот пример хранимой процедуры, выполняющей перестройку индексов для всех таблиц базы данных:
CREATE PROCEDURE sp_reindex_all_tables
AS
DECLARE reindex_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN reindex_cursor
DECLARE @tablename sysname
FETCH NEXT FROM reindex_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXECUTE ('DBCC DBREINDEX ("" + @tablename + "",""")')
FETCH NEXT FROM reindex_cursor INTO @tablename
END
CLOSE reindex_cursor
DEALLOCATE reindex_cursor
Отечественные статьи
Написание простого
грида для WinForms на основе ListView (vrtual mode)
Новые технические статьи Microsoft
Worst Practices - Making Changes to Live Objects "On The Fly"
Самые популярные темы недели
Междумордие
Тип
text, а также XML запросы
|
| #127<< #128 |
| http://subscribe.ru/
E-mail: ask@subscribe.ru |
Отписаться
Убрать рекламу |
| В избранное | ||
