Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Объясните про индексы временных таблиц  [new]
Дурачина
Guest
Объясните мне, старому дураку, как оно должно работать.
Насколько я понимаю план запроса создается до выполнения батча.
В батче создается временная таблица (а-ля create table #Table), заполняется данными, к ней создается индекс, и дальше идет выборка из нее. Как оптимизатор поймет, что для этой выборки нужен только что созданный индекс?
9 сен 13, 13:26    [14813622]     Ответить | Цитировать Сообщить модератору
 Re: Объясните про индексы временных таблиц  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Дурачина
Как оптимизатор поймет, что для этой выборки нужен только что созданный индекс?

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Microsoft
1.Whenever a schema change occurs for any of the objects referenced by a batch, the batch is recompiled. "Schema change" is defined by the following:
...
  • Adding an index to a table or an indexed view
  • 9 сен 13, 13:39    [14813764]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Дурачина
    Guest
    Гость333,
    Спасибо за информацию. А не подскажете ли еще рецепт, как до запуска батча подглядеть план?
    Дело в том, что пока временной таблицы нет, артизан и план показывать не хочет.
    9 сен 13, 14:05    [14813965]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Дурачина
    артизан и план показывать не хочет.

    А если воспользоваться родным SSMS?
    9 сен 13, 14:19    [14814065]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Дурачина
    Guest
    Гость333,
    Спасибо.
    Да, действительно в QA показывает, но только если перед этим выполнить создание всех временных таблиц и индексов в батче.
    9 сен 13, 14:42    [14814232]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Дурачина
    в QA показывает, но только если перед этим выполнить создание всех временных таблиц и индексов в батче.

    У вас MSSQL 2000?
    Сравнил сейчас поведение 2000 и 2005+. В 2000, действительно, при попытке просмотра плана выводится "Invalid object name #tmp". Начиная с 2005, компилятор поумнел и видит создание временных таблиц в батче.
    9 сен 13, 15:01    [14814370]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Дурачина
    Guest
    Гость333,

    Да, у меня 2000. А можно еще один вопрос?
    Вот часто наблюдал такую картину.
    Создаю временные таблицы, заполняю их данными, потом нужно сделать из них выборку.
    Смотрю план, оптимизатор пишет, что будет table scan по временным таблицам (это понятно - индексов нет), в колонке warnings пишет, что желательно бы статистику по связываемым полям.
    Создаю после вставки данных необходимый индекс, смотрю план и вижу, что оптимизатор в колонке warnings уже ничего не пишет (его все устраивает), вместо table scan уже используется index seek, но стоимость запроса при этом не уменьшилась, а наоборот выросла. Чем это можно объяснить?
    10 сен 13, 07:47    [14817516]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104751
    Дурачина
    но стоимость запроса при этом не уменьшилась, а наоборот выросла.

    Стоимость одиночного запроса всегда 100%
    Стоимость одного запроса в пакете зависит от всего пакета
    10 сен 13, 09:29    [14817840]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Дурачина
    Guest
    Glory
    Дурачина
    но стоимость запроса при этом не уменьшилась, а наоборот выросла.

    Стоимость одиночного запроса всегда 100%
    Стоимость одного запроса в пакете зависит от всего пакета

    В QA - да, она в процентах, а вот в артизане в каких-то попугаях (TotalSubtreeCost). И вроде, чем меньше это число на всем запросе, тем он быстрее выполняется. Но почему-то в вышеописанном мной случае при использовании индекса это число становится больше.
    10 сен 13, 12:30    [14819209]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104751
    Дурачина
    В QA - да, она в процентах, а вот в артизане в каких-то попугаях (TotalSubtreeCost).

    Subtree - это какая то часть запроса. Стоимость всего запроса - 1
    10 сен 13, 12:32    [14819222]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Дурачина
    Но почему-то в вышеописанном мной случае при использовании индекса это число становится больше.

    А репро-скрипты можете привести?
    10 сен 13, 12:35    [14819253]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Дурачина
    Guest
    Гость333,

    С репро будет сложнее - данные с боевой банковской базы и они очень объемные (в случае чего со мной не будут церемониться, как со Сноуденом).
    10 сен 13, 12:49    [14819383]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    baracs
    Member

    Откуда: Москва
    Сообщений: 7217
    Дурачина
    ... в каких-то попугаях (TotalSubtreeCost). И вроде, чем меньше это число на всем запросе, тем он быстрее выполняется.
    Четкой связи тут нет. Там же учитывается I/O Cost и CPU Cost.
    CPU Cost может быть очень высокой, а время выполнения запроса - низким.
    Дурачина
    Но почему-то в вышеописанном мной случае при использовании индекса это число становится больше.
    Так, запрос-то стал быстрее?
    10 сен 13, 13:51    [14819829]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    Дурачина
    С репро будет сложнее - данные с боевой банковской базы и они очень объемные (в случае чего со мной не будут церемониться, как со Сноуденом).

    А что вы там в банке тоже шпионите за населением? =) И все это складываете в сиквельные базы, ай-ай-ай =)
    Если серьезно, то прямой корреляции между TotalSubtreeCost и временем выполнения нет.
    TotalSubtreeCost показывает оценочную стоимость плана в неких условных единицах, по которым оптимизатор оценивает варианты между собой.
    Самая простая причина, по которой оценочная стоимость может не отражать реальное выполнение это то что она оценочная.
    Оценка происходит до выполнения, и может быть ошибочной (по многим причинам).

    Самый простой пример, допустим вы говорите у вас нет статистики (у вас кстати что, отключено автосоздание? т.е. пока индекс или руками не создашь - статистики не будет?) - оптимизатор делает догадку о предполагаемом числе строк, допустим думает что будет выбрано 30% от таблицы. В таблице 100 000, оптимизатор думает, что будет выбрано 30 000.
    Вы строите индекс, появляется статистика, оптимизатор на основе гистограммы вычисляет, что будет выбрано допустим 70 000 строк.

    На самом деле выбирается 90 000.
    Оба варианта выбирают одинаковое число строк в реальности. Но т.к. оценка в первом варианте ниже, то и план на ее основе дешевле. Во втором варианте - строк предполагается больше и естественно план дороже. И чаще лучше тот, который ближе к действительности (хотя бывают интересные случаи, когда из-за недооценки выбирается более удачный план и запрос в итоге работает быстрее).
    +
    -- WARNING!!! Отключается создание статистики на tempdb, только тестовый сервер
    use tempdb;
    alter database tempdb set auto_create_statistics off;
    go
    create table t1(a int primary key, b int);
    insert t1 select number, number from master..spt_values where type = 'p' and number between 1 and 100;
    create index ix_b on t1(b);
    create table #t(a int, b int);
    insert #t select number%10, number%10 from master..spt_values cross apply(select top(100) f=1 from master..spt_values) f
    where type = 'p' and number between 1 and 1000;
    go
    set statistics xml on
    select * from t1 join #t t2 on t1.b = t2.a where t2.b <= 10;
    set statistics xml off
    create clustered index ix_a on #t(a);
    create index ix_b on #t(b);
    set statistics xml on
    select * from t1 join #t t2 on t1.b = t2.a where t2.b <= 10;
    set statistics xml off
    go
    drop table #t, t1;
    alter database tempdb set auto_create_statistics on;
    

    Картинка с другого сайта.
    Это самая простая причина. Есть и другие.

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

    Кстати, насчет репро. Если репро привести затруднительно, то рассмотрите возможность приложить планы (actual) в виде файлов. Если нехотите, чтобы кто-то узнал вашу секретную схему данных - воспользуйтей утилитой SQL Sentry Plan Explorer. Там есть кнопочка Anonimize. Правда у вас 2000 сервер, это не айс...
    10 сен 13, 14:47    [14820355]     Ответить | Цитировать Сообщить модератору
     Re: Объясните про индексы временных таблиц  [new]
    Дурачина
    Guest
    Спасибо всем-всем за отклик и информацию. Но я не прощаюсь и еще вернусь сюда ума-разума набираться.
    10 сен 13, 15:31    [14820700]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить