Отправляет email-рассылки с помощью сервиса Sendsay
  Все выпуски  

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 170


Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 170 (29 декабря 2007 г.)

Новым посетителям сайта

SQL Exercises Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.

Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 57-ти задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений.

Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.

Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.


С наступающим 2008 годом, коллеги!

Пусть новый год принесет вам большие творческие успехи и благополучие!

Новости сайта

§ По предложению inkerman изменил подсчет рейтинга задач второго этапа, исключив из результата время автора задачи.

§ Уточнил формулировку задачи 45, чтобы в этой простой задаче для обучающего этапа не искали сложных вариантов. Спасибо remi_ за то, что в последний раз привлек мое внимание к этой задаче :-).

§ Подлатал задачу 86, добавив вариант проверочных данных от VetaleG.
Проверьте свои решения.

§ Новые лица в ТОР 100 и вернувшиеся туда:
Артём С. (задач 124, время 6.186)
zjor (124, 91.975)

§ Продвинулись в рейтинге:
man2 (136, 52.639)
AlexFJ (135, 130.849)
iglbeat (128, 18.694)
Tunin (127, 36.151)
Bulldozer (127, 204.928)
Чумазик (126, 58.663)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
Лори (120, 22.964)
CasperGhost (120, 59.073)
antihero (116, 12.666)
Sysa (112, 16.509)
Scorpion (111, 32.594)

§ На этой неделе сертифицированы:
man2 (B07006885) [AR] - г.Москва, Россия
Borik (A07023528) [BK] - г.Владикавказ, Россия
Bulldozer (B07015223) [AR] - г.Москва, Россия
Чумазик (B07020577) [AR] - г.Братск, Россия
remi_ (A07024261) [BK] - г.Санкт-Петербург, Россия

§ Число подписчиков - 3678

Число участников рейтинга - 13842

Число участников второго этапа - 1195

Сертифицировано на сайте - 220 - 221

Лучшие результаты (ТОР 20)

No Person Number of
Sel_ex
Last_Sel Number of
DML_ex
Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Белогурова К. (Katy_Ekb) 143 143 21 346 155 9.563 4.673 18 27 Oct 2007 11 Dec 2007
2 Креславский О.М. (Arcan) 143 143 21 346 269 17.514 7.720 18 26 Oct 2007 26 Dec 2007
3 Карасёва Н.В. (vlksm) 143 143 21 346 529 38.118 12.414 18 17 Oct 2007 28 Dec 2007
4 Голубин Р.С. (Roman S. Golubin) 143 143 21 346 795 69.101 43.885 18 22 Oct 2007 28 Dec 2007
5 Северюхин Ю.А. (Venser) 142 142 21 342 200 4.950 .655 14 19 Sep 2007 27 Nov 2007
6 Тарасов Д.Б. (Gavrila) 142 142 21 342 755 23.423 2.501 14 20 Sep 2007 28 Dec 2007
7 Солдатенков Ю.С. (SolYUtor) 142 142 21 342 490 17.853 2.695 14 20 Sep 2007 28 Dec 2007
8 Печатнов В.В. (pvv) 142 140 21 342 62 15.622 2.768 14 20 Dec 2007 28 Dec 2007
9 Шептунов П.П. (Dzen) 142 142 21 342 279 8.186 3.499 14 02 Oct 2007 15 Nov 2007
10 Мурашкин И.В. (lepton) 142 142 21 342 544 15.800 5.539 14 18 Sep 2007 27 Dec 2007
11 Мальцев А.В. (Палкин) 142 142 21 342 422 49.738 7.690 14 13 Oct 2007 20 Nov 2007
12 Васьков Е.В. (Johan) 142 141 21 342 493 14.350 12.767 14 24 Nov 2007 30 Nov 2007
13 Бураков С.Г. (burakov58) 142 142 21 342 974 51.702 19.814 14 30 Sep 2007 09 Nov 2007
14 Агапов В. (KERBEROS) 135 141 20 326 89 6.143 1.262 11 20 Nov 2006 27 Jul 2007
15 Кувалкин К.С. (Cyrilus) 139 141 20 334 1014 12.506 2.519 11 04 Sep 2007 28 Dec 2007
16 Зверев Д.Л. (dimzv) 138 68 21 334 1503 9.374 4.938 11 16 Dec 2007 16 Dec 2007
17 Войнов П.Е. (pаparome) 141 142 21 338 765 2.788 .049 10 28 Sep 2007 21 Dec 2007
18 Любченко В.А. (IAS56) 141 142 21 338 377 177.859 20.130 10 16 Sep 2007 28 Dec 2007
19 Валуев Д.И. (Fiolent) 141 139 21 338 1547 122.201 66.360 10 29 Nov 2007 28 Dec 2007
20 Юлдашев М.Р. (Snowbear) 140 142 21 334 874 4.350 .188 6 09 Dec 2007 28 Dec 2007

Лучшие результаты за неделю

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 Tarasov (aatarasoff) 57 57 109 34 143 690 27 Dec 2007
2 >Итальянцев А. (italian) 54 54 104 11 115 1149 28 Dec 2007
3 Mogilevceva E.L. (Luzerenok) 46 46 87 6 93 1721 28 Dec 2007
4 Бердутина О.А. (Angelll) 46 46 85 1 86 1915 28 Dec 2007
5 Жигмонт А.В. (Спец) 36 49 80 0 80 1663 28 Dec 2007
6 Krotov (dfjo) 40 40 76 0 76 2251 27 Dec 2007
7 Shigapov (dxist) 39 39 75 0 75 2286 27 Dec 2007
8 Shary A.A. (Aliaksei_Shary) 28 49 63 0 63 1695 28 Dec 2007
9 Шугаев В.А. (shuga) 32 32 60 3 63 2917 27 Dec 2007
10 >Yang L. (hydra) 21 43 46 15 61 1634 28 Dec 2007
11 Иванов Д.И. (demonjak) 33 33 61 0 61 3037 27 Dec 2007
12 Kuznetsov (Libra) 30 30 56 3 59 3181 27 Dec 2007
13 >Билык О.В. (bil) 22 30 41 16 57 2588 28 Dec 2007
14 Zhuk V. (higera) 29 39 57 0 57 2665 27 Dec 2007
15 Корниенко А.В. (Forza) 31 31 56 1 57 3314 27 Dec 2007
16 >Arcanoid G.A. (arcadius) 30 30 56 0 56 3404 28 Dec 2007
17 >Leduc B. (Bruno) 30 30 55 0 55 3488 28 Dec 2007
18 >Рожков (АлексейПР) 27 58 54 0 54 1230 28 Dec 2007
19 Козлов М.Ю. (baha) 23 86 53 0 53 438 27 Dec 2007
20 Гарбузов Д.С. (denis_g) 17 55 35 17 52 752 27 Dec 2007
21 Барков А. (Snull) 28 28 51 0 51 3829 24 Dec 2007
22 Короповский К.Е. (Shikaka) 28 28 50 1 51 3845 25 Dec 2007
23 >Klinov V. (KV7) 28 28 50 0 50 3966 28 Dec 2007

Изучаем SQL

Обнаружение фрагментации индексов в SQL Server 2005

Tim Chapman (оригинал: Detecting index fragmentation in SQL Server 2005 )
Перевод Моисеенко С.И.

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

Фрагментация в действии

Имеется два типа фрагментации: внешняя и внутренняя. Внешняя фрагментация имеет место, когда логический порядок индекса не соответствует его физическому порядку. Это заставляет SQL Server 2005 выполнять дополнительную работу, чтобы вернуть упорядоченные результаты. По большей части, внешняя фрагментация не является слишком большой проблемой для конкретного поиска, который возвращают очень небольшое число записей, или запросов, которые возвращают результирующие наборы, не требующие упорядочения.

Внутренняя фрагментация имеет место, когда есть слишком много свободного пространства на страницах индекса. Как правило, некоторое свободное пространство желательно, особенно когда индекс только что создан или перестроен. Вы можете задать коэффициент заполнения (Fill Factor) при создании или перестройке индекса, чтобы указать процент заполнения страниц индекса во время его создания. Если страницы индекса будут слишком сильно фрагментированы, это приведет к более длительному выполнению запросов (из-за дополнительных операций чтения, требуемых для нахождения данных), и заставит ваши индексы становиться большими, чем это необходимо. Если, напротив, свободного пространства нет на страницах данных индекса, то изменение данных (прежде всего в результате вставки) вызовет разделение страниц, выполнение чего также потребует дополнительных расходов ресурсов системы.

Обнаружение фрагментации

В версиях до SQL Server 2005, для определения фрагментации конкретной таблицы или индекса использовалась команда DBCC SHOWCONTIG. Этот инструмент хорошо выполняет работу по детализации фрагментации и дает возможность вернуть данные в виде dataset для дополнительного программирования.

SQL Server 2005 обеспечивает лучшие возможности по сравнению с DBCC SHOWCONTIG, предоставляя динамическую функцию управления для обнаружения фрагментации индекса. Функция sys.dm_db_index_physical_stats принимает в качестве параметров базу данных, таблицу базы данных и индекс, для которого Вы хотите определить фрагментацию. Есть несколько опций, которые позволяют Вам указать уровень детализации при рассмотрении фрагментации индексов. Функция sys.dm_db_index_physical_stats возвращает табличные данные, относящиеся к одной конкретной таблице или индексу.

Пример фрагментации в действии

Так как sys.dm_db_index_physical_stats является функцией, я мог бы использовать новый оператор CROSS APPLY , чтобы соединить c моим системным представлением sys.tables для возвращения фрагментации для всех таблиц в базе данных; но эта функция не позволяет мне сделать так. Я думаю, что это происходит оттого, что функция была написана до того, как появилась функциональная возможность CROSS APPLY. В результате придется написать функцию обертки вокруг функции sys.dm_db_index_physical_stats, чтобы иметь возможность выполнить соединение с представлением sys.tables. Вот скрипт создания этой функции-обертки:

 

CREATE FUNCTION sys_PhysicalIndexStatistics_Wrapper
 (
         @DatabaseID INT,
         @ObjectID INT,
         @IndexID INT,
         @PartitionNumber INT,
         @Mode INT
 )
 RETURNS @IndexStats TABLE
 (
         DatabaseID SMALLINT,
         ObjectID INT,
         IndexID INT,
         PartitionNumber INT,
         IndexDescription VARCHAR(100),
         AllocationTypeDescription VARCHAR(100),
         IndexDepth TINYINT,
         IndexLevel TINYINT,
         AverageFragmentation FLOAT,
         FragmentCount BIGINT,
         AverageFragmentSize FLOAT,
         TablePageCount BIGINT,
         AveragePageSpaceUsed FLOAT,
         RecordCount BIGINT,
         GhostRecordCount BIGINT,
         VersionGhostRecordCount BIGINT,
         MinimumRecordSize INT,
         MaxRecordSize INT,
         AverageRecordSize FLOAT,
         ForwardedRecordCount BIGINT
 )
 BEGIN     INSERT INTO @IndexStats
         (
             DatabaseID, ObjectID, IndexID, PartitionNumber, IndexDescription, AllocationTypeDescription, IndexDepth,
             IndexLevel, AverageFragmentation, FragmentCount, AverageFragmentSize, TablePageCount,
             AveragePageSpaceUsed, RecordCount, GhostRecordCount, VersionGhostRecordCount, MinimumRecordSize,
             MaxRecordSize, AverageRecordSize, ForwardedRecordCount
         )
     SELECT
             database_id, object_id, index_id, partition_number, index_type_desc,
             alloc_unit_type_desc, index_depth,
             index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count,
             avg_page_space_used_in_percent, record_count, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes,
             max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
        FROM
                 sys.dm_db_index_physical_stats
              (
                        @DatabaseID,
                        @ObjectID,
                   @IndexID,
                   @PartitionNumber,
                   @Mode
              )
     RETURN
END

 

Чтобы вызвать эту функцию, я просто использую оператор CROSS APPLY как соединение представления sys.tables с функцией обертки. Ниже приведен типичный запрос, который возвращает все таблицы, у которых средняя фрагментация (AverageFragmentation) больше ноля:

 

SELECT t.name,s.*
 FROM sys.tables t
 CROSS APPLY sys_PhysicalIndexStatistics_Wrapper(DB_ID(), object_id, NULL, NULL, NULL) s
 WHERE AverageFragmentation > 0

 

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

Теперь мне нужно понять, что говорят результаты. Меня больше всего интересует столбец AverageFragmentation, который говорит о логической фрагментации индекса или кучи. Если показатель превышает 30 %, я серьезно рассмотрю вопрос о переиндексацию таблицы. Я могу смириться с показателем менее чем 30 %, но можно бы выполнить и реорганизацию индекса.

Почитайте статью в BOL о sys.dm_db_index_physical_stats. В ней подробно описаны остальные столбцы, которые возвращает эта функция.

Процедура перестройки

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

Чем больше Вы будете работать с функцией sys.dm_db_index_physical_stats, тем больше Вы будете понимать, когда ваши таблицы требуют переиндексации. Я надеюсь, что использование функции sys.dm_db_index_physical_stats поможет Вам гарантировать максимально эффективное использование ваших таблиц и индексов.

17-12-2007

Полезная информация

§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.

§ Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях:
del.icio.us
dzone.com
Digg.com
Reddit.com
stumbleupon.com

Контакты

По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.

Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"

В избранное