Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4   вперед  Ctrl      все
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior,

И кстати, основная стоимость у вас составляет вовсе не скана, а спула (меньшую часть) и джойна по предикату (большую часть). Т.е. грубо говоря, ему похрену, что там сканить или искать - один фиг одна страница! Взгляните уже на статистику чтений:
+
(100 row(s) affected)
Table 'Dictionary'. Scan count 0, logical reads 6787200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTabe'. Scan count 1, logical reads 7158, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 3343 ms,  elapsed time = 3392 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Table 'Worktable'. Scan count 1, logical reads 6787399, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DictionaryH'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTabe'. Scan count 1, logical reads 7158, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 52407 ms,  elapsed time = 52446 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

6787200 vs 6787399 - Она практически одинакова!
А теперь посмотрите на процессорную составляющую:
CPU time = 3343 ms VS CPU time = 52407 ms. - Основная разница в процессорном времени, которая уходит на поиск нужной строки из 100, а не в I/O.
Mind говорил про чтение одной страницы, я говорил про чтение одной страницы. Я еще упомянул процессорную составляющую, которая пренебрежительно мала, если это один скан. Который и будет один в случае соединения маленькой таблицы с большой, т.к. сервер помещает меньший набор строк во внешний цикл. Вы же зажали оптимизатору яйца в тиски, заставив делать 3 (или первоначально 60) миллионов операций скана, после чего из этого месива выбирать нужные строки, после этого пальцы веером - нашли крутой пример из жизни...обалдеть...
И никакой скан не "неэффективный" на маленьких таблицах, зависит, что мы будем делать с результатами скана, и можем ли мы избавиться от лишней работы уже на этапе выборки...
15 июн 12, 16:06    [12721371]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow, прошу вас. Прочитайте всё до конца ничего не пропуская. Внимательно. Не по диагонали. Да, многое лишнее может быть, но лучше всё оговорить (CRC), чтоб небыло дальнейшего непонимания.
+ для SomewhereSomehow
SomewhereSomehow
Вы привели два конкретных примера с цифрами, сравнивать которые - некорректно.
Где Я написал что Я их сравниванивал, а не ВАМ так показалось. Вы с тех самых пор непрвильно поняли и вам придётся всё забыть и правильно перечитать тему, чтобы понять то что я имелл ввиду.

SomewhereSomehow
При этом не учли или проглядели ключевой момент различия в производительности.
Где???
Ау. Вы именно не поняли. Я понимаю на 120% что тут происходит, это вы лишь 20% понимаете все слова, ведение разговора, причины собеседников. Поэтому прошу вас ещё раз - Поставьте СЕБЯ в ситуацию что вы что-то профтыкали и перепрочитайте тему.
Пожайлуста. Потому что вы не выдержите моё досканального доказательства по полочкам всех промежуточных выкладок. И это выматывает нас всех.

SomewhereSomehow
Теперь вы пытаетесь выкручиваться придумывая какие-то сравнения "всех возможных вариантов" или "элементы плана на практике".
Выкручиваться от чего???
Ничего я не придумывал, это я лично вам пытаюсь объяснить направление мышления и кикие вопросы ставяться. Я (один) что виноват что вы не догадываетесь о всех промежуточных выкладках и вам надо всё разжёвывать, для чего я писал тото и то-то. Я же опускал 99% промежуточные выкладки надеясь что вы сами догадаетесь (иначе мы погрязнем в писанине, и я не терпеливый).
Не нужно вот этих приближённых слов "придумывая какие-то" и приближённого понимания, средней по больнице.
Конкретику пожайлуста. Вы что не умеете вести беседу нормально как специалист?
Я вас прекрасно понимаю и повторяю что вы непрвильно предположили там-то и там-то (см последние посты) и поэтому прошу вас переосмыслить.

Вы SomewhereSomehow продолжаете не реагировать на аргументы. Вы их игнорируете. А я настаиваю. Я их зря не привожу - для вас лично. Повторяю в N-й раз, один из них:
При сложных планах оптимизатор не может (тяжелее) поменять порядок соединения. Т.к. в запросе 20 объектов, к примеру, и он не может поменять их местами. И поэтому он тупит. Вы сами хорошо чувствете кагда оптимизатор тупит, у вас есть предположения из-за чего и т.п.? Если нет то я в шоке! Нам не о чем разговаривать.

Или вы ждёте что я выдам 100500 часов видео своей многлетней жизни программера, на всех 100500 запросах и на всех 100500100500 планах оптимизатора, буду тыкать носом в монитор и логоврить "смотри SomewhereSomehow, и тут оптимизатор ступил так-то, и тут ступил вот так-то, и тут и везде, и вот нигде он так не делал и вот- ак не делал" ?????

Соответственно я и употребляю "по моему опыту я делаю такое предположение с хорошей долей вероятности что" то-то и то-то.
А вы не говорите "а по моему так-то", вы тупо говорите "я не знаю - следовательно". Этот аргумент пролетатет.

Начните читать отсюда 12698558. И если у вас возникнут вопросы, задавайте, я обязательно отвечу.
Только не забывайте какие предположения вы делаете. Чтобы можно было увидеть где была ошибка:
1. Из нескольких предположений интепретации слов/предложений/высказываний SomewhereSomehow выбрал неправильеый (не заметил остальные)
2. Mnior выразился неточными синонимами
3. Mnior выразился неточными мыслями
4. Mnior сделал очепятки
5. Mnior неправильно понял SomewhereSomehow или остальных
6. Mnior на знает вот "этого"
7. Mnior ошибся в логике
8. SomewhereSomehow на знает вот "этого"
9. SomewhereSomehow ошибся в логике
Кое что преувеличено или крикливо. Но надеюсь что вам понимание важнее эмоций.
Никаких личностей, надеюсь на быстрое исправление недопонимания.
---------------------------------
+ Логика
SomewhereSomehow
Вот что я говорил, вообще-то
SomewhereSomehow
То что Mnior привел неудачный пример, вообще не исключает удачного примера
А я о том же - это ВАШЕ понимание.
Никаких примеров я не приводил. Это вы так его поняли. ВЫ.
Вы поставили мой пример к Вашему вопросу, и вы не догадываетесь к чему Я то приводил (не к тому что вы думаете).

Вы знаете что существует ЕДИНСТВЕННЫЙ вариант доказательста? Докавывается ТОЛЬКО от противного. Банальная логика и теория доказательств.

"Исключить" невозможно т.к. теория незамкнута. Но можно легко опровергнуть контр примером.
Который я и потребовал.

Если никто не сможет привести контр пример против "что кластера лучше кучи палюбэ" тогда хамба. Тогда надо придумывать доказательство что это так - что нереально сложно. Но от этого отношение и предположения никак не поменяются - я буду настаивать - "куча зло".
15 июн 12, 16:30    [12721601]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow
пальцы веером - нашли крутой пример из жизни...обалдеть...
FacePalm.JPG
И что вы там курите, что вам всякая фигня в голову лезет.

Вам же потом будет обидно что вы это наговорили, от того что вы всего лишь не правильно его (меня) поняли.
Жду когда вы перепочитаете и зададите нужные вопросы. (см пост выше)
15 июн 12, 18:03    [12722393]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior,

Я прекрасно понял каждый ваш пост.
Если вы продолжаете настаивать, что с самого первого 12706153 поста с примером, вы имели ввиду только демонстрацию того, как оптимизатор может провтыкать с кучей в гипотетической части, гипотетического сложного запроса (хотя, по сути, этот тест 12712884 был произведен только после того как я указал 12710456 на роковую ошибку в порядке соединения) - то давайте спишем на недопонимание друг друга. Как оно было на самом деле, пусть каждый решит сам для себя.
Однако, у меня к вам просьба, когда вы выкладываете два запроса, снабжаете их замерами времени, выносите результаты замеров в комментарии, а потом выносите в выводы таблицу результатов - это воспринимается людьми как сравнение! И не важно что вы не назвали прямо. "Если оно выглядит как утка, плавает как утка и крякает как утка, то это, вероятно, и есть утка"(с).
Будет репро с кучей, без отрезания яйц оптимизатору (я кстати говорил, и повторяю, что такой пример наверняка есть) - добро пожаловать. Это правда не будет однозначным аргументом против кучи, но будет предостережением. А заниматься словесной эквилибристикой дальше ("а я вот тут сказал А, но имел ввиду Б") нет желания.
15 июн 12, 19:05    [12722663]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
Mind
попробую найти пруф
Жду с нетерпением.
Ну вот хотя бы:
+ Таблицы и данные
CREATE TABLE dbo.Dictionary (
	 ID	int	IDENTITY
	 CONSTRAINT [PK_Dictionary] PRIMARY KEY
	,Data	Int	NOT NULL
)
CREATE TABLE dbo.BigTabe (
  ID INT IDENTITY(1,1)
	, Dictionary	int
	,[Date]		Date
	,Data	char(100)	NOT NULL
	,CONSTRAINT [PK_BigTabe] PRIMARY KEY (
		 ID
	)	
)

CREATE TABLE dbo.BigTabeH (
  ID INT IDENTITY(1,1)
	, Dictionary	int
	,[Date]		Date
	,Data	char(100)	NOT NULL
)

INSERT	dbo.Dictionary
SELECT	Top(1000) V.number
FROM	master.dbo.spt_values V
WHERE	V.[type] = 'P'

INSERT	dbo.BigTabe(Dictionary, Date,Data) WITH (TABLOCKX)
SELECT	 X.ID
	,DateAdd(Year	,Y.number
	,DateAdd(Month	,M.number
	,DateAdd(Day	,D.number,0)))
	,Y.number
	+M.number
	+D.number
FROM	     dbo.Dictionary		X
	JOIN master.dbo.spt_values	Y ON Y.[type] = 'P' AND Y.number between 1900 and 2000
	JOIN master.dbo.spt_values	M ON M.[type] = 'P' AND M.number < 12
	JOIN master.dbo.spt_values	D ON D.[type] = 'P' AND D.number < 28
	
DROP TABLE dbo.BigTabeH	
	
INSERT dbo.BigTabeH WITH (TABLOCKX)(Dictionary, Date,Data) 
SELECT	 X.ID
	,DateAdd(Year	,Y.number
	,DateAdd(Month	,M.number
	,DateAdd(Day	,D.number,0)))
	,Y.number
	+M.number
	+D.number
FROM	     dbo.Dictionary		X
	JOIN master.dbo.spt_values	Y ON Y.[type] = 'P' AND Y.number between 1900 and 2000
	JOIN master.dbo.spt_values	M ON M.[type] = 'P' AND M.number < 12
	JOIN master.dbo.spt_values	D ON D.[type] = 'P' AND D.number < 28
	
CREATE INDEX IDX_BigTabe ON dbo.BigTabe (Dictionary)
CREATE INDEX IDX_BigTabeH ON dbo.BigTabeH (Dictionary)

+ Тест:
set statistics io on
set statistics time on

CHECKPOINT
DBCC DROPCLEANBUFFERS

SELECT	 
  B.Dictionary, 
  Sum(B.Data + D.Data)
FROM dbo.Dictionary	D
	INNER LOOP JOIN dbo.BigTabeH	B ON D.ID = B.Dictionary
WHERE D.Data BETWEEN 260 AND 270
GROUP BY B.Dictionary

CHECKPOINT
DBCC DROPCLEANBUFFERS

SELECT	 
  B.Dictionary, 
  Sum(B.Data + D.Data)
FROM dbo.Dictionary	D
	INNER LOOP JOIN dbo.BigTabe	B ON D.ID = B.Dictionary
WHERE D.Data BETWEEN 260 AND 270
GROUP BY B.Dictionary


Результаты. 10 секунд в пользу кучи.

(11 row(s) affected)
Table 'Dictionary'. Scan count 3, logical reads 10, physical reads 3, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTabeH'. Scan count 11, logical reads 374173, physical reads 20407, read-ahead reads 193950, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2813 ms, elapsed time = 39847 ms.


(11 row(s) affected)
Table 'Dictionary'. Scan count 3, logical reads 10, physical reads 3, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTabe'. Scan count 11, logical reads 1945281, physical reads 17777, read-ahead reads 190072, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2172 ms, elapsed time = 49212 ms.
16 июн 12, 00:52    [12723801]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow
Если вы продолжаете настаивать, что с самого первого 12706153 поста с примером, вы имели ввиду только демонстрацию того, как оптимизатор может провтыкать с кучей в гипотетической части, гипотетического сложного запроса
Всего лишь немного уточню:
Хотел показать во сколько обойдётся тот или иной профтык, а не "как именно скуль тупит".
SomewhereSomehow
(хотя, по сути, этот тест 12712884 был произведен только после того как я указал 12710456 на роковую ошибку в порядке соединения)
Какая нафиг роковая ошибка - о чём вы?

Первый пост показывает стоимость отсутствия упорядоченности как таковой. Не оптимизации планов, не косяки, а математика/физика упорядоченности.
Как можно это показать, если в обоих планах она есть (в вашем последующем посте) (или явно индексом или при помощи дополнительного оператора Sort).

Какое нафиг "после"?
Или вы думаете что профтык до циклового скана невозможен в принципе?
Ошибку на табличных переменных вы и сами знаете. Но да, может ли куча на это влиять - не знаю.
Аргумент как раз такой - наличие дополнительного оператора Sort может не дать компилятору дойти до нужного плана. Лишнее действие лишний расчёт стоимости, а время ограничено. Понимаете? А ещё фиг поймёшь как лягут эвристики и какой будет порядок предложенных планов.
Так что лишний оператор тоже стоит чего-то. Вы можете замерять стоимости работы самого компилятора?

Но главное упоминание Карлсона немного показательно. Скажи кто твой друг ... :)
SomewhereSomehow
Однако, у меня к вам просьба, когда вы выкладываете два запроса, снабжаете их замерами времени, выносите результаты замеров в комментарии, а потом выносите в выводы таблицу результатов - это воспринимается людьми как сравнение!
Ну и требования. Я выдал скрипт чтоб не пришлось остальным его набирать руками. А далее пусть каждый сам поправляет эти два запроса всевозможными способами (тысячи их) и обоснованно соглашается или нет с утверждения собеседников или показывает интересные варианты.
Я делал пару десятков разных запросов (а не два), перед тем как выдать тот злосчастный пост, все их выкладывать бессмысленно, да и отличаются они парочкой слов (хинтов, порядком и т.п.).
Каждый запрос отвечает на разные вопросы и показывает разные тонкости понимания процессов, виденья всей картины.
SomewhereSomehow
"Если оно выглядит как утка, плавает как утка и крякает как утка, то это, вероятно, и есть утка"(с)
Утка лишь у вас в голове. Там крокодил.
SomewhereSomehow
А заниматься словесной эквилибристикой дальше ("а я вот тут сказал А, но имел ввиду Б") нет желания.
Я сказал Крокодил и вижу Крокодила, а вы утка-утка.
16 июн 12, 01:24    [12723860]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Ребят, мы скатываемся в холивар по поводу кучи и кластерного индекса. Условие было другое - куча не более 100 записей помещающихся на одной странице. Иначе можно привести массу примеров, еще более ужасных, и скатимся в холивар.

Mind,
Понимаете, Mnior, с самого начала играет нечестно. Мало того, что он сел в лужу с первым примером, но исправился во втором, но и привести тот самый "гипотетический сложный запрос", когда оптимизатор без всяких хинтов сделает глупость - он пока не смог. Зато софистика и казуистика на ура. Доказываем 2*2=5, прибегаем к приемам уничижения оппонента по матану, и аргументам "а я тут вот, а вы тут ах" =)

Хотя, кстати, изначально такого настроя не было, ведь в главном все соглашались, что нужно смотреть на практике, а так, смысла рассуждать нет, и скорее разницы тоже нет - но упертость появилась после того как ткнули носом!

А надо было всего лишь сказать:
- Ок, я облажался с этим примером, но давайте возьмем и там и там хинты, вы видите насколько куча проигрывает индексу за счет того, что индекс может осуществлять выбор сразу, а в случае кучи надо тратить процессор!
- Да, вижу, но блин, возможно ли это в реальных запросах, ведь в реальных случаях меньшая таблица будет внешней?
- Да, может и не возможно, но вы будете отрицать что такого никогда не будет?
- Нет, не буду, но с такими случаями не знаком.
- Ок, я тоже не знаком, т.к. не могу привести пример, но все-таки это возможно?
- Не стану отрицать.
- Ну и порешим на том, что возможно, но практических доказательств пока нет.
ВСЕ, ЛЯДЬ! Весь вопрос решен! Только нужно было сраз признать ошибку и вывести на правильный диалог, а не выкручиваться.

В итоге: НЕТ РАЗНИЦЫ по стоимости чтения (о чем мы все изначально говорили). Но может быть разница по процессору, при крайне-крайне неблагоприятных условиях, т.е. гипотетическом "сложном" запросе, который никто в глаза не видел, или возможно кривой статистике (тогда первопричина не в этом) или растущих из жопы руках разработчика - что продемонстрировал Mnior (без обид).
Вопрос, как и тема, в принципе закрыты.
Можно добавлять разные примеры где выигрывает то или другое, но в концепцию они внесут не больший вклад, чем рекомендация.
16 июн 12, 01:25    [12723861]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ну вы блин даёте. Пока я сочинял одно тут вы ещё подкатываете.

Mind, спасибо. Посмотрю в понедельник, на самом сервере.
16 июн 12, 01:29    [12723867]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Я чувствую подкатывать - бессмысленно, вы не внемлете, сочиняйте дальше =)

Mnior
Всего лишь немного уточню:
Хотел показать во сколько обойдётся тот или иной профтык, а не "как именно скуль тупит".

Так и показали бы сразу включив во второй пример хинт тоже!!!!!!!! Е-мае! И сказали бы сразу!!! Поставили бы сразу два запроса в одинаковое положение и сказали "смотрите, блин, если мы представим, что эти планы, части плана сложного запроса - то вот как повел бы себя кластерный индекс, а вот как куча!" - и вопросов бы к вам не было!!! Хотя был бы один - предоставить такой запрос где это правда. Но вы сообразили это ближе к концу! И вы всерно, скажи так, выглядели бы лучше. А что в итоге? Софистика, казуистика...
Если вы изначально имели ввиду именно это, то извиняйте - надо быть телепатом, что это понять, и иизвините - за столько постов можно было выразить эту мысль, что выразил в двух предложениях. Ну про а сравнения - "я привел просто так, а не для сравнения" - это вообще цирк. Допустим я признал, что все так, остальное - на вашей совести.
Все nuff said.
Трольте в этой теме без меня. Я вас потроллю в другой теме, если будет оказия =)
п.с.
Если хотите продолжить, почта у меня открыта, пишите туда. Я думаю, нет смысла утомлять форумчан чтением этой белиберды. Все мои конструктивные замечания с примерами (заметьте почти в каждом сообщение есть конкретика sql, а не "матан"), всерно разбиваются о скалу вашей риторики.
16 июн 12, 01:43    [12723881]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
+ Ответ на более ранний пост. Думаю что там ещё есть что-то полезное и важное.
SomewhereSomehow
Хотя, кстати, изначально такого настроя не было, ведь в главном все соглашались, что нужно смотреть на практике, а так, смысла рассуждать нет
Это ошибка. Я не мог ранее и не могу сейчас привести те примеры профтыков. Ибо на корпоративных запросах, и нереально сложно их воссоздать тестово. Да и в итоге сойдётся в холивар "вы не правильно написали запрос" - естественно, если бизнес логика не описана (хотя неважно, ведь главное что на каком-то запросе скуль то тупит).
Мне хватает того что другие гуру со мной соглашались.
Смысл рассуждать есть.

SomewhereSomehow
но упертость появилась после того как ткнули носом!
Меня? Где?
SomewhereSomehow, поймите с мой стороны неправы только вы и почти везде. Так что не надо ляля и не надо скатываться.
Вы SomewhereSomehow продолжаете играть в игру "жулики и детективы", ища интригу которой нет.
Нигде своих профтыков я пока не вижу, если вы предположили что где-то я "согласился" умолчав - то предположение было неверно.
Наоборот, я привёл несколько аргументов на которые вы никак не среагировали. (а надо было)

SomewhereSomehow
А надо было всего лишь сказать:
- Ок, я облажался с этим примером, но давайте возьмем и там и там хинты, вы видите насколько куча проигрывает индексу за счет того, что индекс может осуществлять выбор сразу, а в случае кучи надо тратить процессор!
- Да, вижу, но блин, возможно ли это в реальных запросах, ведь в реальных случаях меньшая таблица будет внешней?
- Да, может и не возможно, но вы будете отрицать что такого никогда не будет?
- Нет, не буду, но с такими случаями не знаком.
- Ок, я тоже не знаком, т.к. не могу привести пример, но все-таки это возможно?
- Не стану отрицать.
- Ну и порешим на том, что возможно, но практических доказательств пока нет.
ВСЕ, ЛЯДЬ! Весь вопрос решен! Только нужно было сраз признать ошибку и вывести на правильный диалог, а не выкручиваться.
Не облажался. На сложных запросах выражение "внешняя малая таблица" неверно и даже бессмысленно (десятки таблиц). Нет - я знаком с профтыками, да - их тяжело вынести в тест. Это первое.

Второе. Не надо считать себя пупом земли и тут все бегают и пытаются вам что-то разжевать. Тут форум, я пишу ещё и те аргументы которые могут быть для вас очевидны а другим нет, или противоречить вашему представлению. Но вы возможно посчитали что эти доводы были приведены на ваши "вопросы".

Четвёртое.
1. Отсутствие доказательства несуществования:
- не доказывает наличия
- не даёт повода "советовать кучу"
- не запрещает "отговаривать от неё" (по каким либо аргументам)
2. Доказать несуществование примером невозможно. Примером можно доказать существование. Несуществование можно доказать только логическими выкладками. (основы теории доказательств)

Пятое. Стратегия логики "Возможно всё" не сопоставима со стратегией "Углублённого поминания". Этот аргумент ("возможно все") превращается в пыль перед теми кто углублялся в проблематику, независимо от формализации или наличия очевидного доказательства. Аргументы рулят. И тут нужно только обменяться опытом.
(уже писал об этом 12719888)

SomewhereSomehow
В итоге: НЕТ РАЗНИЦЫ по стоимости чтения (о чем мы все изначально говорили)
Где я вообще о нём (чтении) говорил???
Это вашу глупость её упоминания я тупо проигнорировал.

SomewhereSomehow
т.е. гипотетическом "сложном" запросе, который никто в глаза не видел
Вот не надо ляля, то что вы видели тысячи запросов и десятки тысяч планов и не нарывались на всевозможные множественные профтыки, не даёт вам никакого права предполагать, что не мог я или кто-то другой видеть миллионы запросов и миллиарды планов и дальнейшего их анализа и построения модели оптимизатора, и видеть их систематику.
То что вы не участвовали в тех темах где это не однократно обсуждалось не даёт вам право утверждать что их нет.
Ок. Давайте вы не будете на это давить.

SomewhereSomehow
Вопрос, как и тема, в принципе закрыты.
Ещё один рыцарь "справедливости"? Вопросы ещё открыты.
SomewhereSomehow
Можно добавлять разные примеры где выигрывает то или другое, но в концепцию они внесут не больший вклад, чем рекомендация.
Я хочу это довести до однозначности, а не просто рекомендация.


+ Ответ на последний пост
SomewhereSomehow
Я чувствую подкатывать - бессмысленно, вы не внемлете, сочиняйте дальше =)
Есть смысл, если хотите увидеть что ошибки были с вашей стороны. Что играя изначально "в интернете кто-то неправ" вы не увидели нужную интерпретацию слов а какой-то "заговор". Я часто говорю, и подразумеваю что я могу выражаться не очевидно. И привычка "искать виновных" может сильно подкачать и поставить в неловкую ситуацию. Вы должны понимать, что проблема взаимо-понимания намного больше чем думает обыватель. И на самом деле "заговор" в головах - а на самом деле ошибки в первоначальных предположениях.
Не надо думать что я о вас плохо думаю или издеваюсь. Я и сам попадал на такое. Меня часто мучают вопросы, а не паранойя у меня.
Всё нормально.
SomewhereSomehow
Так и показали бы сразу включив во второй пример хинт тоже!
Какой хинт? Вы о чём?
SomewhereSomehow
И сказали бы сразу!
Вы думаете так легко сразу видеть где профтыкивает собеседник? Или где нужно более точно сформулировать? После драки кулаками не машут. Эффект знания задним числом?
Поищите во всём треде про порядок ("поряд"). Пост за постом.
SomewhereSomehow
"смотрите, блин, если мы представим, что эти планы, части плана сложного запроса ...
И вы не могли это предположить с самого начала и искали ошибку в моих рассуждениях? "Игры в которые играют люди. Люди которые играют в игры."
Вот возьмите прокрутите эту мыслю много раз в голове и увидите её очевидность и необходимость ... ещё до моего первого поста.
SomewhereSomehow
и вопросов бы к вам не было!
Так всё драматично?
SomewhereSomehow
Но вы сообразили это ближе к концу!
Вот нинада ляля. Это я лично для вас сформулировал словами - разжевал.
Не надо разводить детсад и играть "кто первый". Это не имеет никакого значения - у каждого же цель понять.
SomewhereSomehow
Софистика, казуистика...
Может это просто ваша неприязнь к рассуждениям и повышенную их детализированность?
SomewhereSomehow
Если вы изначально имели ввиду именно это, то извиняйте - надо быть телепатом, что это понять, и иизвините - за столько постов можно было выразить эту мысль, что выразил в двух предложениях.
Я часто наивно предполагаю что говорю с людьми умнее меня. Есть такой косяк (уже писал об этом). Ищё раз ссори с мой стороны.


SomewhereSomehow
Так и показали бы сразу включив во второй пример хинт тоже!
Какой хинт? Вы о чём?

SomewhereSomehow
Я вас потроллю в другой теме, если будет оказия =)
Ок. Договорились. "Игры в которые играют люди. Люди которые играют в игры."
Что не убивает - делает нас сильней.

PS: Писать в личку это не надо, на личности переходить бессмысленно. Подробности выкладок могут быть интересны всем.
Скорее перегибаю палку лишними разборками полётов.
16 июн 12, 04:18    [12723937]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mind, Да!!!

Но Есть вопрос:
C чем связано что RID Lookup быстрее Key Lookup? Или дело в другом (в неправильной организации внутренних процессов) ? Т.к. странно что при буферизации кластер чуть быстрее.

ЭкспериментЗапрослогических чтенийфизических чтенийупреждающих чтенийВремя ЦПзатраченное время
CleanBigTabeH374 173953242 9593 48110 957
CleanBigTabe2 515 409211255 3823 63617 459
BuffBigTabeH374 173002 229255
BuffBigTabe1 517 306002 321234
Это проваливание в логических чтениях не спроста.

Заметил ещё одну странную особенность:
Если убрать хинт INNER LOOP, то сначала кажется что всё выравнивается, но есть странный нюанс:
1. CleanBuff -> Query1 (Heap) [11 сек] -> Query1 (Heap) [ 0 сек] -> Query1 (Heap) [ 0 сек] -> ...
2. CleanBuff -> Query2 (Clust) [10 сек] -> Query2 (Clust) [ 0 сек] -> Query2 (Clust) [ 0 сек] -> ...
3. CleanBuff -> Query1 (Heap) [11 сек] -> Query2 (Clust) [10 сек] -> Query2 (Clust) [10 сек] -> ...
4. CleanBuff -> Query2 (Clust) [10 сек] -> Query1 (Heap) [10 сек] -> Query1 (Heap) [ 0 сек] -> ...

Притом видно что в третьем случае происходит физическое чтение. При этом баг растолько стабильнный что хоть 100 раз запусти, всё равно тормозит (планы не меняются). Что-то тут не то. И плохо это освещено.
18 июн 12, 13:57    [12731147]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Мда, не думал я, что мой маленький вопрос вызовет столь жаркую дискуссию. Однако приятно, что такие гуру ms sql пришли на помощь. Что касается сабжа, тот ответ я получил где-то две страницы назад. А за одно ответы еще на несколько вопросов.
Спасибо вам ребята!
18 июн 12, 14:24    [12731501]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
Mind, Да!!!

Но Есть вопрос:
C чем связано что RID Lookup быстрее Key Lookup? Или дело в другом (в неправильной организации внутренних процессов) ? Т.к. странно что при буферизации кластер чуть быстрее.
Ага, это весьма любопытно. Потому что в теории RID Lookup быстрее Key Lookup. Ибо RID - 1 логическое чтение, тогда как для Key Lookup, количество чтений = количеству уровней кластерного индекса. В моем варианте это = 4, вот собственно и разница в 4 раза. Но на практике, этот вариант получаетсе не медленнее, а даже быстрее. Почему так происходит я, если честно, сказать затрудняюсь. Но у меня есть предположение, что поиск по RID дороже чем по ключу, даже не смотря на меньшее количество логических чтений. 

Если посмотреть на планы запросов, то они идентичны, за исключением дополнительного Compute Scalar для кучи. В свойствах этого оператора можно найти примерно следующее: Scalar BmkToPage([Bmk1002]), что по сути некий внутренний аналог функции sys.fn_PhysLocFormatter, который преобразует RID в file/page/slot. Есть подозрение, что как раз таки вызов этой функции для каждой строки отъедает процессорное время. На моей слабенькой машине с 2-мя цпу это хорошо заметно. Время ЦП для миллиона строк: 4407 и 3141 мс, для кучи и кластера соответственно. Или в пересчете на строки, это потеря примерно одной миллисекунды на каждый RID lookup.
19 июн 12, 03:06    [12735552]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mind
Ибо RID - 1 логическое чтение, тогда как для Key Lookup, количество чтений = количеству уровней кластерного индекса. В моем варианте это = 4, вот собственно и разница в 4 раза.
Замечательно. Но почему оно в первый раз равно 2.5 лимона, а потом 1.5? Почему оно прыгает?
Mind
Но у меня есть предположение, что поиск по RID дороже чем по ключу, даже не смотря на меньшее количество логических чтений.
Scalar BmkToPage([Bmk1002]), некий внутренний аналог функции sys.fn_PhysLocFormatter, который преобразует RID в file/page/slot.
Есть подозрение, что как раз таки вызов этой функции для каждой строки отъедает процессорное время.
Что бы такое допустило M$ - слабо верится. Вон там параллельное Identity работает приемлемо, а тут банальное RID to FPS тормозит, там же обычный массив. Может ани специально? С другой стороны SEEK по кластерному должен стоить на-а-амного дороже, пока по этим страницам побегаешь да просканишь.

Не, ну не то что не верится: Если M$ поддерживает кучу в стиле "обратная совместимость", "стандарт требует" то нормально - багов больше там где меньше требований.


И как на счёт последнего бага? Единственное что мне приходит в голову, так это:
- упреждающее чтение вытесняет только что загруженные данные (хотя в обоих случаях оно есть)
- выкрученная (разделённая) архитектура приводит к множественному обращению (Почему логические чтения зависят он наличия данных в кэше?)

И кажется что и природа обоих случаев всё-таки одинаковая. А вот как это увидеть, на какие конктерно системыне функции смотреть чтоб весь процесс почувствовать?
19 июн 12, 13:57    [12738317]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
Mind
Ибо RID - 1 логическое чтение, тогда как для Key Lookup, количество чтений = количеству уровней кластерного индекса. В моем варианте это = 4, вот собственно и разница в 4 раза.
Замечательно. Но почему оно в первый раз равно 2.5 лимона, а потом 1.5? Почему оно прыгает?
Упреждающее чтение читает больше страниц данных чем нужно. В этом легко убедится выключив его трейс флагами:
DBCC TRACEON(652, -1)
DBCC TRACEON(8744, -1)

Mnior
И как на счёт последнего бага? Единственное что мне приходит в голову, так это:
- упреждающее чтение вытесняет только что загруженные данные (хотя в обоих случаях оно есть)
С чем то подобным я уже сталкивался, но были проблемы с воспроизведением сего бага. Больше похоже на то, что куча вытесняет кластер из буфера, а вот кластер не может выкинуть кучу. Ну и кстати, на этих тестовых данных у меня проблема не воспроизводится:
3. CleanBuff -> Query1 (Heap) [11 сек] -> Query2 (Clust) [10 сек] -> Query2 (Clust) [0 сек] -> ...
Mnior
- выкрученная (разделённая) архитектура приводит к множественному обращению (Почему логические чтения зависят он наличия данных в кэше?)
А что такое "выкрученная (разделённая) архитектура"?

Mnior
И кажется что и природа обоих случаев всё-таки одинаковая. А вот как это увидеть, на какие конктерно системыне функции смотреть чтоб весь процесс почувствовать?
Я тоже не нашел на что смотреть.
Пробовал смотреть ожидания для холодного кэша. Вот что получил:

wait_type sum_duration avg_duration Count
PAGEIOLATCH_SH 31558 6 4950 Heap
PAGEIOLATCH_SH 42059 7 5547 Clust

но почему же ожидания от дисков для кластера получаются больше чем для кучи все равно не понятно. Читается примерно такой же объем данных. Возможно есть какие то отличия в характере нагрузки, но вот как выяснить, в чем разница - не понятно.
20 июн 12, 03:12    [12742711]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ссори что не сразу. Чертовски занят. Да и добавить особо нечего. :(
Mind
Упреждающее чтение читает больше страниц данных чем нужно. В этом легко убедится выключив его трейс флагами
Спасибо!

Отставание (в процентах) стало не особо меньше.

Баг усилился и немного поменялся! Теперь:
1. CleanBuff -> Query1 (Heap ) [16 сек] -> Query1 (Heap ) [ 0 сек] -> Query1 (Heap ) [ 0 сек] -> ...
2. CleanBuff -> Query2 (Clust) [15 сек] -> Query2 (Clust) [ 0 сек] -> Query2 (Clust) [ 0 сек] -> ...
3. CleanBuff -> Query1 (Heap ) [16 сек] -> Query2 (Clust) [39 сек] -> Query2 (Clust) [39 сек] -> ...
4. CleanBuff -> Query2 (Clust) [15 сек] -> Query1 (Heap ) [41 сек] -> Query1 (Heap ) [ 0 сек] -> ...
Разница:
ТипЭкспериментЧисло просмотровлогических чтенийфизических чтенийВремя ЦП (мс)затраченное время (мс)
ClustЧистые буфера 17508 397 63 546 5 81815 610
ClustПовторный запуск 17508 397 0 5 119 334
ClustПосле Heap 17508 397494 33211 20242 600
Heap Чистые буфера 17506 508 63 454 5 81615 949
Heap Повторный запуск 17506 508 0 5 599 350
Heap После Clust 17506 508505 39111 44943 831
Блин, и как воспользоваться ситуацией?
Ктось может помочь в поиске причин ? Призываю всезнающих гуру. <танцует, бъёт в бубен, читает мантры ...>
Видно, что количество физических чтений при заполненом буфере приблизилось к логическим. Но с кучей совсем ничего непонятно. Почему на него тоже повлияло, но далее уже нет.
Mind
Больше похоже на то, что кластер не может выкинуть кучу.
Ранее думал, что у только что загруженных страниц кластера счетчик чтения таймы чтения не такие "необходимые" как у страниц кучи, прочтённых перед этим.
Mind
Ну и кстати, на этих тестовых данных у меня проблема не воспроизводится
Тут минимум влияет размер памяти. У меня видимо магическим образом совпали нужные показатели. Вам нужно размеры таблиц видимо поменять или доступность пямяти. У меня кажись всего 4 гига под этот инстанс выделено.
Mind
А что такое "выкрученная (разделённая) архитектура"?
Ну... не обращайте особого внимания, можно проинтерпретировать это так
Стратегия M$ реализованная в данной архитектуре SQL не столь идеальна, что приводит к неприятным побочным явлениям.
Возможно совершенно другая организация буферов ...
Просто когда (давно) я читал те заметки об архите скуля - мне пказалось не столь продуманно, на сколько это можно было сделать. И я это списывал, на то что все не на столько предсказуемо в запросах, на практеке, чем может казаться, да и продукт используют в большом диапазоне задач.
Mind
но почему же ожидания от дисков для кластера получаются больше чем для кучи все равно не понятно
Может потому что страницы индекса (кластера) "далеко" от страниц данных.
21 июн 12, 11:15    [12751243]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
А тайминги страниц хранятся вмете со страницей или в служебных "таблицах" ?
21 июн 12, 11:21    [12751279]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
А тайминги страниц хранятся вмете со страницей или в служебных "таблицах" ?
тайминги страниц?
22 июн 12, 00:24    [12756762]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mind
тайминги страниц?
Ну, если мне не изменяет память, с 2005го стратегия вытеснения страниц изменилась. Вместо счётчика обращений к странице хранятся два последних таймстэмпа обращения. Вытесняются кажись, с максимальной разницей.
22 июн 12, 02:21    [12757076]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Mnior
Mind
тайминги страниц?
Ну, если мне не изменяет память, с 2005го стратегия вытеснения страниц изменилась. Вместо счётчика обращений к странице хранятся два последних таймстэмпа обращения. Вытесняются кажись, с максимальной разницей.
Угу.

http://www.gotdotnet.ru/blogs/denish/1956/
22 июн 12, 02:30    [12757081]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Гавриленко Сергей Алексеевич, ну и какие у вас предположения, почему при кластерном так тупит?
25 июн 12, 10:51    [12768613]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Апну темку.
Для тех кому лень читать суть.
Все соглашаются, что кластерный индекс стоит создавать особо не заморачиваясь, т.к. разницы вроде особой нет (аргумент типа - какая разница если читать всерно одну страницу).
Mnior при помощи хинтов изобретает пример (сразу или нет, не важно), когда из-за неправильного порядка соединения (маленькая таблица внизу) возникает сильная нагрузка на процессор, во время операции джойна, т.к. серверу хоть и без разницы что читается всего одна страница, зато строку выбирать нужную сразу или каждый раз фильтровать из 100 - разница есть.
Ок.
Загвоздка в том, что в реальности если оптимизатору не зажимать кое-что в тиски хинтами, в 99% случаев, все будет работать нормально. Найти этот гипотетический 1% случаев, когда будет такая форма плана, но без хинтов, тогда не смогли, хотя и не отрицали, что он возможен.
И я-таки придумал, как заставить оптимизатор выбрать такой план без хинтов.
Правда, у меня получилась существенная разница, в два раза, только при последовательном плане.
В остальном, все равно примерно одинаково. И тем не менее, мало ли когда сервер сгенерит последовательный план, например вдруг скалярная функция где-то затесается.
создаем данные все тем же скриптом (около миллиона строк если что)
+
USE tempdb
GO
CREATE TABLE dbo.Dictionary (
	 ID	TinyInt	IDENTITY
	 CONSTRAINT [PK_Dictionary] PRIMARY KEY
	,Data	Int	NOT NULL
)
CREATE TABLE dbo.BigTabe (
	 Dictionary	TinyInt
	,[Date]		Date
	,CONSTRAINT [PK_BigTabe] PRIMARY KEY (
		 Dictionary
		,[Date]
	)
	,Data	Int	NOT NULL
)
CREATE TABLE dbo.DictionaryH (
	 ID	TinyInt	NOT NULL
	,Data	Int	NOT NULL
)
GO
INSERT	dbo.Dictionary
SELECT	Top(100) V.number
FROM	master.dbo.spt_values V
WHERE	V.[type] = 'P'

INSERT	dbo.BigTabe
SELECT	 X.ID
	,DateAdd(Year	,Y.number
	,DateAdd(Month	,M.number
	,DateAdd(Day	,D.number,0)))
	,Y.number
	+M.number
	+D.number
FROM	     dbo.Dictionary		X
	JOIN master.dbo.spt_values	Y ON Y.[type] = 'P' AND Y.number between 1960 and 2000
	JOIN master.dbo.spt_values	M ON M.[type] = 'P' AND M.number < 12
	JOIN master.dbo.spt_values	D ON D.[type] = 'P' AND D.number < 28

INSERT	dbo.DictionaryH
SELECT	*
FROM	dbo.Dictionary
GO
update statistics dbo.Dictionary with fullscan;
update statistics dbo.DictionaryH with fullscan;
update statistics dbo.BigTabe with fullscan;


а вот собственно запрос в котором получается такой план и результаты:
set statistics time on
go
---------------------------
--HEAP
declare @1 int, @2 date, @3 tinyint, @4 tinyint, @5 int;
select @1=b.Data,@2=b.Date,@3=b.Dictionary, @4 = d.ID, @5 = d.Data
from dbo.BigTabe b left join dbo.DictionaryH d on D.ID > b.Dictionary
option(recompile
,maxdop 1
--,maxdop 2
--,querytraceon 8649 --parallel plan flag
)
go
/*
MaxDop 1: CPU time = 38953 ms,  elapsed time = 38980 ms.
MaxDop 2: CPU time = 53938 ms,  elapsed time = 22283 ms.
No maxdop (4 cpu in my case): CPU time = 54782 ms,  elapsed time = 19930 ms. (parallel plan choosen)
Forced parallel: CPU time = 53390 ms,  elapsed time = 19100 ms. (parallel plan)
*/
go
---------------------------
--CLUSTERED
declare @1 int, @2 date, @3 tinyint, @4 tinyint, @5 int;
select @1=b.Data,@2=b.Date,@3=b.Dictionary, @4 = d.ID, @5 = d.Data
from dbo.BigTabe b left join dbo.Dictionary d on D.ID > b.Dictionary
option(recompile
,maxdop 1
--,maxdop 2
--,querytraceon 8649 --parallel plan flag
)
/*
MaxDop 1: CPU time = 19750 ms,  elapsed time = 19751 ms.
MaxDop 2: CPU time = 19969 ms,  elapsed time = 19978 ms.
No maxdop (4 cpu in my case): CPU time = 19906 ms,  elapsed time = 19922 ms. (still serial choosen)
Forced parallel: CPU time = 36171 ms,  elapsed time = 18382 ms. (parallel plan)
*/
go
set statistics time off
go
--drop table dbo.Dictionary, dbo.DictionaryH, BigTabe;

Форма плана обусловлена тем, что
1) к большой таблице присоединяется маленькая, которая является кучей;
2) левый внешний джойн не коммутативен, переставить таблицы оптимизатор не может;
3) предикаты с неравенством он традиционно (хотя и не всегда) решает через loop join;
4) план по каким-то причинам (допустим они есть) получается последовательным
вот и получается, что если выполняются все эти условия, то возможно вышеописанное поведение.
16 авг 12, 16:32    [13021760]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
На всякий случай, прилагаю картинки планов, кому лень запускать:
+
Картинка с другого сайта.
16 авг 12, 16:47    [13021899]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow
И я-таки придумал, как заставить оптимизатор выбрать такой план без хинтов.
...
Ай, маладца.

SomewhereSomehow
2) левый внешний джойн не коммутативен, переставить таблицы оптимизатор не может;
3) предикаты с неравенством он традиционно (хотя и не всегда) решает через loop join;
Имеется ввиду не коммутативен из-за неравенства?

Меня этот Table Spool сильно напрягает. Неравенство и всё - он в плане. Нафига он там?
Особенно если там табличка плёвая.
Может эвристики оптимизатора слишком заточены под стандартные запросы.

PS: Иногда и на простых запросах может протупить, когда Optimization Level = SIMPLE
16 авг 12, 19:47    [13022890]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior
Имеется ввиду не коммутативен из-за неравенства?

Нет, я имел ввиду вообще, что a left join b неравно b left join a. Т.е. просто применить правило JoinCommute, для перестановки что с чем соединяется, оптимизатор не может.
Mnior
Меня этот Table Spool сильно напрягает. Неравенство и всё - он в плане. Нафига он там?
Особенно если там табличка плёвая.
Может эвристики оптимизатора слишком заточены под стандартные запросы.

А вот фиг знает! Как я это понимаю,это используется для того, чтобы при сбросе внутреннего цикла на начало, не сканировать повторно все записи, но в данном случае, все равно сканируется все и все записи добавляются в спул. Так что вероятно вы правы.
Это частично подтверждает следующий тест, где правило для спула отключено.
+
set statistics io, time on
go
declare @1 int, @2 date, @3 tinyint, @4 tinyint, @5 int;
select @1=b.Data,@2=b.Date,@3=b.Dictionary, @4 = d.ID, @5 = d.Data
from dbo.BigTabe b left join dbo.DictionaryH d on D.ID > b.Dictionary
option(recompile
,maxdop 1
--,maxdop 2
--,querytraceon 8649 --parallel plan flag
,queryruleoff BuildSpool
)
go
declare @1 int, @2 date, @3 tinyint, @4 tinyint, @5 int;
select @1=b.Data,@2=b.Date,@3=b.Dictionary, @4 = d.ID, @5 = d.Data
from dbo.BigTabe b left join dbo.DictionaryH d on D.ID > b.Dictionary
option(recompile
,maxdop 1
--,maxdop 2
--,querytraceon 8649 --parallel plan flag
--,queryruleoff BuildSpool
)
set statistics io, time off
go

без спула
автор
Таблица "DictionaryH". Число просмотров 1, логических чтений 1377600, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "BigTabe". Число просмотров 1, логических чтений 2907, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Время ЦП = 46281 мс, затраченное время = 46329 мс.

со спулом
автор
Таблица "Worktable". Число просмотров 1, логических чтений 2755399, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "DictionaryH". Число просмотров 1, логических чтений 1, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "BigTabe". Число просмотров 1, логических чтений 2907, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Время ЦП = 54828 мс, затраченное время = 54830 мс.

Извиняюсь что на русском, т.к. тестирую из дома, а дома так =)
Т.е. видно, что логических чтений в случае спула больше ровно в два раза, как и время больше на 10 секунд. Чтобы объяснить логику оптимизатора, почему он делает спул в этом конкретном запросе, надо подумать. М.б. это связано как-то с отсутствием информации об отношении между колонками по которым идет соединение, типа FK, но могу ошибаться. Короче надо исследовать. Если кто-то знает, внесите ясность, зачем здесь спул.

Mnior
PS: Иногда и на простых запросах может протупить, когда Optimization Level = SIMPLE
Не слышал про SIMPLE, м.б. trivial? если имеется ввиду trivial - спорить не буду, хотя представить трудно, обычно trivial это когда по-мнению оптимизатора есть единственно возможный хороший план исполнения. Правда, когда я писал статьи про оптимизатор, я пытался добиться от команды сиквела признаков квалификации удовлетворения запроса тривиальному плану, но ничего не добился =)
Для себя я решил, что это не решение принимаемое из-за отсутствия альтернатив (т.к. это реально не так, проверяется экспериментом), а решение принимаемое на основании только метаданных (типа кол-во страниц, строк, наличия индексов и т.д.), без построения структуры memo для оценки по стоимости, которая действительно не строится. По теме, может или нет, сказать не могу, надо опять же думать.
16 авг 12, 20:50    [13023137]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить