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

Dmitry67 · Post by **Dmitry67** » 12 Feb 2004 12:20

типа sysprocesses...
Вопросов собственно два, они связанные

1. Я стал обращать внимание на то что ингода бывают записи
select * from sysprocesses where spid=blocked
То есть процесс блокировал самого себя
Что это ?

2. Какая вообще consistency у этих таблиц ?
Как я понимаю на самом деле это список в памяти который все время меняется
Когда делается select то... делается копия списка или "по живому" ?
Можно тогда объяснить страныне значения изменениями на ходу ?

chepackav · Post by **chepackav** » 12 Feb 2004 18:51

Обратите внимание, что кроме spid в sysprocesses есть еще колонка kpid - т.е. один процесс SQL Server'а может быть расщеплен на несколько threads операционной системы (обычно это многопроцессорных серверах происходит) и при неправильной оптимизации эти threads могут друг-друга блокировать. Я с этим сталкивался года полтора назад, но это был откровенный глюк в SQL Server, который был позже исправлен.

tengiz · Post by **tengiz** » 12 Feb 2004 19:42

1. Как уже написал chepackav, при выполнении параллельного плана такие вещи могут наблюдаться. Но только это не глюк, а вполне ожидаемое явление. При параллельном выполнении запроса один поток является диспетчером и ему нужно ждать пока другие выполнят свою работу, отсюда и blocked==spid для потока-диспетчера.

2. псевдотаблицы типа sysprocesses читают внутренние стуктуры данных "по живому", поэтому для минимального ущерба дла производительности, синхронизация при доступе к таким структурам во время сканирования с целью диагостики сведена к абсолютному минимуму. Поэтому никакой row-to-row consistency не гарантируется.

chepackav · Post by **chepackav** » 12 Feb 2004 19:59

tengiz wrote:1. Как уже написал chepackav, при выполнении параллельного плана такие вещи могут наблюдаться. Но только это не глюк, а вполне ожидаемое явление. При параллельном выполнении запроса один поток является диспетчером и ему нужно ждать пока другие выполнят свою работу, отсюда и blocked==spid для потока-диспетчера.

2. псевдотаблицы типа sysprocesses читают внутренние стуктуры данных "по живому", поэтому для минимального ущерба дла производительности, синхронизация при доступе к таким структурам во время сканирования с целью диагостики сведена к абсолютному минимуму. Поэтому никакой row-to-row consistency не гарантируется.

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

tengiz · Post by **tengiz** » 12 Feb 2004 20:53

chepackav wrote:В моем случае это был глюк, т.к. процесс приходилось убивать - возникал deadlock, который самим SQL Server'ом не обнаруживался - проблему исправили в каком-то сервис-паке.

А Вы не помните номер билда? Такой баг действительно был, но не в релизе, а в одной из бет и был исправлен до выхода продукта. Я что-то не помню, чтобы после релиза нечто подобное ещё раз фиксалось. Хотя могу и ошибаться - прошло уже почти четыре года. Если, конечно, речь идёт о SQL 2000.

Dmitry67 · Post by **Dmitry67** » 13 Feb 2004 00:20

thanks et merci
tengiz
Вас видно редко но еще спасибо что отвечате
и друим тоже спасибо

tengiz · Post by **tengiz** » 13 Feb 2004 01:44

Dmitry67 wrote:Вас видно редко но еще спасибо что отвечате

Будут хорошие вопросы, на которые сложно найти ответы в документации - как тот, что начал эту тему - с удовольствием буду отвечать чаще

.

YellowMan · Post by **YellowMan** » 13 Feb 2004 12:03

А вот такой вопрос - как сервер работает с временными таблицами ?
На sql.ru нашли интересную особенность - если в одной сессии создать временную таблицу, а потом в другой сессии в sysobjects поменять ее имя на какое-то другое, таблица становится видна в обоих сессиях. Что происходит в этом случае ?

Dmitry67 · Post by **Dmitry67** » 13 Feb 2004 12:19

В смысле, update tempdb..sysobjects set name=... ?

chepackav · Post by **chepackav** » 13 Feb 2004 15:29

YellowMan wrote:А вот такой вопрос - как сервер работает с временными таблицами ?
На sql.ru нашли интересную особенность - если в одной сессии создать временную таблицу, а потом в другой сессии в sysobjects поменять ее имя на какое-то другое, таблица становится видна в обоих сессиях. Что происходит в этом случае ?

Я вот с гораздо более неприятной проблемой, связанной с временными таблицами, воюю - у меня периодически пользователи делают достаточно большие выборки во временные таблицы (10 - 100 000 записей), потом с использованием этих данных выполняют запросы к другим таблицам - для ускорения запросов по временным таблицам строятся индексы - вот тут-то и появляется узкое место - у каждого пользователя своя временная таблица, но вот таблица sysindexes в tempdb - одна на всех и при создании индексов пользователи отчаянно блокируют эту таблицу. Как с этим бороться - непонятно.

Dmitry67 · Post by **Dmitry67** » 13 Feb 2004 15:48

Сделать постоянную таблицу с колонкой u=@@spid
Эта таблица заменит все временные

Merle · Post by **Merle** » 13 Feb 2004 19:22

Dmitry67 wrote:Сделать постоянную таблицу с колонкой u=@@spid

И сделать эту колонку первой в кластерном индексе.. :wink:

YellowMan · Post by **YellowMan** » 16 Feb 2004 13:06

Dmitry67 wrote:В смысле, update tempdb..sysobjects set name=... ?

Именно так - попробуйте

Только не = а like. Понятно что в реальном проекте такое не попользуешь - слишком рискованно. Просто такой вот занимательный факт.

Насчет sysindexes в tempdb - никак не поборешься

Вариант с постоянной таблицей и SPID тоже не всегда проходит - в основном что ты не можешь управлять раздачей SPID и не можешь всегда отследить когда коннект отвалился. Плюс накладные расходы на то чтобы проверить на и удалить старые записи - что само по себе тоже не быстро.

tengiz · Post by **tengiz** » 16 Feb 2004 21:48

YellowMan wrote:Вариант с постоянной таблицей и SPID тоже не всегда проходит - в основном что ты не можешь управлять раздачей SPID и не можешь всегда отследить когда коннект отвалился. Плюс накладные расходы на то чтобы проверить на и удалить старые записи - что само по себе тоже не быстро.

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

Dmitry67 · Post by **Dmitry67** » 16 Feb 2004 21:53

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

Это какой то очень неприятный момент
Спасибо

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()

Привет

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

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

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

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

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

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