Манические таблицы MS SQL

tengiz · Post by **tengiz** » 16 Feb 2004 23:03

Dmitry67 wrote:Если SQL упрямо выбирает full table scan по соображениям эффективности, орять таки япрямо игнорируя индекс (и часто оказывается прав) то не приводит ли это к засаде с блокировками ?

Если выбор table scan был оправдан, то и засада не должна получиться смертельной, а скорее даже наоборот. При условии, что я правильно понимаю Ваш вопрос.

Dmitry67 · Post by **Dmitry67** » 16 Feb 2004 23:18

более точно сформулирую вопрос
пусть есть таблица BIG с озанчениями n от 1 до 1000000
По n есть индекс
Я ищу where n=123 все ok
Если я ищу where n<200000
Индекс перестает использоваться

Пусть в одной транзакции я удалил запись с n=800000
А в другой читаю count(*) where n<200000
Это при декларируемой row level locking и вероятности пересечения транзакий 20%

В более сложном случае where n in (select ... where str like '%sometxt%'
когда количество записей по like определяется эвристиками (я поводил эксперименты

) то эта проблема становтся более вероятной

То есть оптимизируя по скорости SQL ухудшает concurrency
Мне не качестся что forced index является правильным решеним. Скорее более тонкая проверка заблокированности

tengiz · Post by **tengiz** » 16 Feb 2004 23:42

Dmitry67 wrote:То есть оптимизируя по скорости SQL ухудшает concurrency. Мне не качестся что forced index является правильным решеним. Скорее более тонкая проверка заблокированности

Оптимизация по стоимости всё равно в среднем лучше, так как "оптимизация по concurrency" это то палка о двух концах: как бы лучшая concurrency, когда блокируется меньшее подмножество строк, может на самом деле оказаться заметно худшей - блокировка держится значительно дольше. Что касается более тонкой проверки заблокированности - а что Вы имеете в виду? Можно, конечно, пользоваться READ_PAST и NOLOCK, но это же только "костыли" - если приложение критично к таким хинтам, то в нём что-то сильно не так.

Dmitry67 · Post by **Dmitry67** » 17 Feb 2004 00:28

Увы tengiz. Есть еще такая дуракая вещь как WEB приложения. Где если тразакция не отработала в течение 30sec, то возникает таймаут. И сервер мало загружен, и индесы все есть, а проклятые table scans превращают систму в однозадачную

Неужели кругом вставлять hints...

А ко мне каждый таймаут приходит в виде бага

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

Ну это конечно я на наивном уровне неная внутренней структуры рассуждаю
Да и в yukon легче станет

tengiz · Post by **tengiz** » 17 Feb 2004 01:48

Dmitry67 wrote:Если scan идет по условию которое в пинципе может быь проидекировано и натыкается на блокировку то он не ждет а проверяет может ли быть эта блокировка исключена с помощью range lock. То есть физически для скорости использовать scan, а для логически для блокровк индекс

Эта идея в принципе рабочая, то только для её реализации требуется как минимум два индекса, так как иначе - т.е. если бы было достаточно одного, что означает, что это был бы covering index и не было бы index lookups - оптимизатору никогда не нужно было бы делать табличный скан.

<added>
К тому же на уровнях изоляции выше read committed это работает только в одну сторону и может помочь только скану. Updater-ы будут всё равно натыкатся на S блокировки, которые останутся после скана.
</added>

Dmitry67 · Post by **Dmitry67** » 18 Feb 2004 09:57

Было бы еще интересно получить 'карту кеша'
Вы понимаете про что я ?

tengiz · Post by **tengiz** » 18 Feb 2004 17:48

Dmitry67 wrote:Вы понимаете про что я ?

Честно говоря нет. Я и так и сяк пытался вывернуть эти слова, что-то ни во что осмысленное сложить не сумел.

<added>

OK, если карта кеша - это информация о памяти, в частности статистика по кешам, то в уже первеой бете очень много новостей по этому поводу. Посмотрите виртуальные табличные функции из каталога master.sys.

</added>

Dmitry67 · Post by **Dmitry67** » 18 Feb 2004 23:18

Нет
Было бы классно видеть hot spots, сколько % кешировано у каждой таблицы
У Btrees идут ли запоы в какое то одно место или вразброд
Короче предствьте себе таблицу
Пусть у нее есть pk который имеет некий монотонных характер
ТОгда данные можно изобразить синим да диске и красным в кеше
Будут полоски примерно как выглядит сильно фрагментированный диск в дефрагментаторе
Практиче толку мало набыо бы классно

tengiz · Post by **tengiz** » 19 Feb 2004 08:50

Dmitry67 wrote:Было бы классно видеть hot spots, сколько % кешировано у каждой таблицы У Btrees идут ли запоы в какое то одно место или вразброд
Короче предствьте себе таблицу...Практиче толку мало набыо бы классно

Вы будете смеяться, но такая информация возможно будет доступна. Не в виде цветных графиков, разумеется, а как системная таблица.

Merle · Post by **Merle** » 19 Feb 2004 21:17

Раз опять поднялась тема про Юкон..

Tengiz, если Вас не затруднит, не могли бы Вы ответить, на вот такой, совершенно неожиданно возникший у меня вопрос по индексам, а точнее по index include...

Если я правильно понял, то в случае index include включаемый столбец
присутствует только в листьевых узлах индекса (leaf node), и в ключе
не учавствует. В итоге, используя эту функциональность там, где
раньше применялся составной индекс для избегания ненужных bookmark
lookups, в силу меньшего размера ключа будет меньше количество
используемых страниц, меньше высота дерева и, как следствие, более
эффективный поиск, из-за меньшего количества дисковых операций.
Однако проведя вот такой несложный эксперимент:

Code: Select all

create table million(x int, y varchar(50))

  declare @i int
  set @I=0
  while @I<1000000 begin
    insert into million (x, y) values(@I, NewID())
    set @I = @I+1
    end

CREATE INDEX IX_Composite ON million(x,y)
CREATE INDEX IX_Include ON million(x) INCLUDE(y)

я выяснил, что статистика по составному и включаемому индексам практически полностью совпадает...

Code: Select all

SELECT *
FROM sys.fn_indexinfo ('million', '*' , DEFAULT, 'DETAILED')

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

Где я в своих рассуждениях ошибся?

P.S. И второй вопрос в догонку.. В Юконе все системные таблички
заменены на view для обратной совместимости (причем довольно
серьезно обрезаны, то есть через новые представление доступно больше
информации), однако при просмотре таблички sysindexes, я обнаружил
гораздо больше информации чем в sys.indexes. Более того, это вообще
единственное место, где я нашел разницу между составным и включаемым
индексами. А именно столбец used (я так понимаю это используемые страницы?) показывает, что index include использует их чуть меньше, но совсем не на много. А столбец dpages совпадает со столбцом pages в fn_indexinfo()

tengiz · Post by **tengiz** » 20 Feb 2004 04:50

Merle wrote:Где я в своих рассуждениях ошибся?

Коротко - нигде. Дело в том, что sys.fn_indexinfo возвращает количество leaf pages. Так что это баг в этой функции. Она должна либо возвращать полное количество страниц (тогда бы Вы и увидели разницу), либо колонка должна называться leaf_pages, а не просто pages. Кстати, спасибо за вопрос - Вы первый, кто это заметил. Во всяком случае я не нашёл, чтобы этот баг уже был зафайлен. Так что завтра "обрадую" теперешнего владельца этого кода (3 года назад это был частично мой код - но это точно не мой баг

).

А вот sys.sysindexes действительно показывает больше информации. dpages - это leaf pages, а used = dpages + internal pages. Откуда internal pages = used - dpages. Разница небольшая потому, что, во-первых у Вас varchar (50), который на самом деле занимает ровно столько, сколько символов в NewID(). Замените его на fixed length char (800) и сразу увидите другие результаты. Во-вторых, fanout внутренней страницы индекса не бывает меньше 8-9, то есть в самом худшем случае, overhead на внутренние страницы не может быть больше, чем примерно 12-13% от общего объёма данных. Т.е. не стоит расчитыват на то, что выйгрыш от include будет больше этого значения.

Но для очень больших объёмов данных, эти десять процентов могут быть критичны.

Dmitry67 · Post by **Dmitry67** » 20 Feb 2004 10:36

А вот еще бага
При попытке изменить пароль пользователя (не под sa) если активирована approle, выдается ошибка (у меня по фр) что "не могу использовать USE если активирована approle". Пароль при этом меняется.

Вскрытие показало что ошибка возникает в месте помеченном ***, то есть ПОСЛЕ смены пароля
Насколько эту ошибку можно игнорировать ?

Code: Select all

    -- CHANGE THE PASSWORD --
    update master.dbo.sysxlogins
   set password = convert(varbinary(256), pwdencrypt(@new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
   where name = @loginame and srvid IS NULL

   -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
   exec('use master grant all to null') -- ***********************************

tengiz · Post by **tengiz** » 20 Feb 2004 23:52

Dmitry67 wrote:А вот еще бага...

Спасибо, я перешлю и это тоже.

tengiz · Post by **tengiz** » 21 Feb 2004 00:43

tengiz wrote:Дело в том, что sys.fn_indexinfo возвращает количество leaf pages. Так что это баг в этой функции. Она должна либо возвращать полное количество страниц (тогда бы Вы и увидели разницу), либо колонка должна называться leaf_pages, а не просто pages.

Update: согласно владельцу, в документации к sys.fn_indexinfo однозначно сказано, что речь идёт о data pages. Поэтому формально всё в порядке. Но они рассмотрят вариант добавления колонки с полным количеством страниц.

Merle · Post by **Merle** » 21 Feb 2004 11:37

Спасибо Tengiz, а то я уже начал сомневаться...

Понятно, что боее-менее заметный эффект от использования index include будет только на больших объемах, но не обнаружив вообще никаких отличий, на вообщем-то не маленькой табличке, я уже не знал что и думать..
К тому же сбило с толку, что старая табличка sysindexes что-то все же показывает, а новая - sys.indexes - нет...