В конференции по MS SQL был вопрос, как же все таки отключить transaction log. Правда, вопрошающего сразу запинали, сказали, что он в корне не прав, и лог - это правильно. Вопрощающий сказал - Ладно, пусть не всегда, а иногда отключать, я ДБА, я лучше знаю, нужен он или нет. Ему сказали - гнать таких ДБА, лог - это свято, без него нельзя.
Наверное, меня тоже гнать надо, но мне лог мешает.
вот к примеру...
У нас есть база, достаточно большая. По ней, естественно, надо строить отчеты. Большие и сложные. За один проход не считаются, а требуют создания большого количества промежуточных результатов.
Обычно в таких случаях создают временную табличку, промежуточные результаты складывать. И всем по большому счету плевать, если при работе с этой временной табличкой произойдет ошибка (не вставилось все или не все удалилось) - просто ловят ошибку и выходят из процедуры. Она ведь временная, и так дропнется по выходу из области видимости - кому она нужна?!
А у нас в качестве временных табличек используются постоянные (удобнее нам так). И мне все равно, если при удалении из этой таблички произойдет ошибка и удалятся не все данные - я ловлю ошибку и просто выхожу из процедуры. Тоже самое при вставке. Ну не волнует меня непротиворечивость данных в этих табличках - они по смыслу временные. Но! Я вынужден тратить дисковое пространство под лог(а места на диске не так уж и много), я вынужден терпеть накладные расходы на обслуживание лога (а сервер - не бесконечной мощности), я вынужден терпеть взаимоблакировки пользователей (а вдруг кто-то поменяет данные! ой!) и т.д.
Поэтому так и тянуться руки отключить лог - а НЕКАК!!!! А хочется....
И вообще, что это за механизм транзакций такой?
Почему переход на следующий уровень вложенности вверх или вниз так различается? Почему я должен об этом помнить? "так, здесь - begin tran, тут - save tran..."?
Почему нельзя открыть две паралельные транзакции в одном коннекте? В одной (маленькой и быстрой) получать номер документа, во второй (большой и медленной) создавать спецификацию документа? Почему кто-то должен ждать момента, пока другой не отпустит давно ненужный ему ресурс?

2ASCRUS
По поводу Sybase и временных таблиц, создающихся при коннекте - не знал,
это второй этап моих мечтаний и претензий к MS SQL.
По поводу целостности данных, которые важнее скорости - не согласен.
Это - отчет, и это - ВРЕМЕННЫЕ данные, они умрут после отчета. Но если я
бы смог заставить отчет считаться не 40 минут, а 20... это был бы праздник
Recovery Model - Simple, но когда я вставляю (или удаляю) 2*10e6 строк, не сильно
и помогает :-)
А глобальные временные таблицы MS SQL - это да... ужжжас....

2Sergey Ch
На клиента... 26 Gb... не реально :-( Да и к тому же, при изменении алгоритма
расчета отчета менять не процедуру, а код клиента...

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

2Scott Tiger
Механизм транзакций применяется как к постоянным, так и ко временным таблицам.
И наш подход к решению задач - не думаю, что это ошибка. Как из процедуры формирования
начальных остатков передать данные в процедуру расчета движения и формирования
конечных остатков, кроме как через таблицу?

Informix... IBM Informix Extended Parallel Server, V8.3, цитата:
Несмотря на то, что EPS не поддерживает нелогируемые базы, он поддержиавет нелогируемые
постоянные и временные таблицы.

2All
Все эти постоянно-временные таблицы у нас хранятся в Tempdb. Дело в том, что у TempDb
есть особенность - данные в ней временные, база пересоздается при рестарте сервера,
поэтому в отличие от других баз изменения в ней пишуться не СРАЗУ, а потом, по мере
необходимости (могут и вообще не писаться). Проверить достаточно просто - посмотреть
профайлер, чтения/запись.
Но этого все равно мало, крайне мало...

2ASCRUS
Действительно, данные по сессиям у нас разделяется по @@SPID.
Для удобства, ходим не напрямую к таблицам, а к View с условием.
По поводу табличных переменных - они могут быть ооочень большими,
т.к. все равно транслируются во временные таблицы (см. sysobjects
в tempdb во время выполнения запроса, имена похожи на @___table___)

Где взять базу?
Нечто вроде биллинга, ~500000 абонентов, 3.6 года, ~310 записей на одного абонента+Справочники+остатки+так, по мелочи.
Генерится за 6-10 часов.

По поводу временных локальных и глобальных таблиц
Локальные таблицы видны и существуют в пределах сессии. Но есть определенный гемморой с их использованием (ее надо создать, если ее нет, и опять-таки очистить, если она есть)
Глобальные временные таблицы видны всем сессиям и существуют до тех пор,
пока хоть одна сессия их используетя. Гемморой тот же, что и при использовании локальных временных таблиц, плюс самому разделять данные между сессиями.
Теперь еще такая особенность.... если в пакете (процедуре, запросе)
вперемешку идут операторы DML и DDL, то это приводит к постоянным рекомпиляциям процедур. При сложных процедурах и при большой нагрузке на
сервер это очень печально....
К операторам DDL относятся такие операторы как create table....
Т.е. любые действия с временныеми таблицами практически неизбежно приводят к рекомпиляциям.

2Alexeyvg
Если я правильно помню, то в MS SQL лог - это журнал изменения данных,
а не место для хранения самих данных.
я прекрасно понимаю, что для хранения 1 гига информации этот самый гиг надо выделить - я не против этого.
Я против того, что когда мне надо удалить 1000 записей из временной таблицы в случае, если получилось удалить только 900 записей, MS SQL не откатывал транзакцию назад и не восстанавливал все 1000 записей, а оставлял эти 100 неудаленных.
Аналогично при обновлении. Не удалось обновить все записи - черт с ним, отрапортуй ошибку и забудь. Мне НЕ НУЖНО восстанавливать базу в состояние до обновления.
По поводу моего "К тому же в памяти временные таблицы хранятся до тех пор, пока есть память". Временные таблички отличаются от постоянных крайне мало (только тем, что автоматом дропаются, когда уже не нужны). Они все равно хранятся в базе TempDB, база хранится на диске. Просто по возможности TempDB не сбрасывает свои страницы на диск. Грубо говоря, если у обычной базы включено только кэширование чтения и отключено кэширование записи, то для TempDB включено и кэширование чтения и кэширование записи.

2Glory
Я не ставлю в вину логу то, что процедуры работают медленно - это было бы просто глупо. Я недоволен тем, что в моем случае обеспечивается абсолютно ненужная мне функциональность - обеспечение журналирования моих действий. Просто в процессе оптимизации оказалось, что для увеличения скорости необходимо или - ну, почти как минимум чудо или гений, или, хорошо бы, отключить все ненужные операции.
Мне не нужно производить откаты. Меня вполне бы устроили... как бы так сказать... мини-транзакции на уровне страницы.
Предположим, в табличке 10 страниц, я начал удаление. Удалили 1-2-3 записи (1 страницу), записал это в лог, затем 2-я страница, 3-я.. если на 4-й произошла ошибка, будут откачены изменения и 4-й, и 1-2-3. Меня устроило бы, чтобы откатывалось изменение только 4-й страницы - исключительно ради физической целостности.
В принципе, так и приходится... вместо того чтобы сразу удалить данные из таблицы, удалять их порциями по 15000-20000 штук- становится легче, но не до конца.
2SergSuper
Таблицы-переменные - они переменные, и видны исключительно внутри процедуры. К тому же, если я не ошибаюсь, они не участвуют в явных транзакциях, но при обновлении такой таблицы она опять-таки, или вся обновится или не вся - атомарно, так сказать.
2Ggg
select ... into #temptable...
насколько я помню, на время выполнения такой операции накладывается SchemaStabilityLock на TempDB, что есть не очень, даже совсем нехорошо.
Кроме того, это только вставка... А прочие операции? Это даже если забыть про перекомпиляции и прочие прелести....

В общем, грустно как-то, господа....

2Glory
ну... 1000000, это я конечно слегка приврал, наверное. На самом деле около 490000.
По поводу оптимизации на аппаратном уровне... Знаю я прекрасно, что 10 RAID на 4х дисках на 40-50% быстрее 5-го на 3-х при том же объеме доступного пространства. Знаю, что и стоят диски не слишком то и дорого.
Но мне нужно работать на том, что есть. В смысле покупки железа я заказчикам не слишком и указ.
По поводу такого количества строк во временных данных... ну, к примеру, расчет сальдо всеж-таки проще было бы делать за один проход по всему диапазону счетов, а не разбивая счета на поддиапазоны.
Если ошибки нет, то лог и не мешает? Мешает. При изменении 8000-й странички сервер помнит, что он поменял еще 7999 страничек? Помнит. И под это дело выделил место в логе, правильно? Затем. Это ведь транзакция? Транзакция. Значит, должны быть блокировки. В определенный момент сервер решает, что дешевле блокировать не построчно, и даже не экстентами, а наложить лок на таблицу. В результате все ждут. Понятное дело, все это благополучно разрешается разведением по разным табличкам.
2Cat2
Откаты транзакций в базе достаточно редки. Специально статистику не вел, если честно, но предполагаю что порядка 10-15 раз в день, причем откат на уровне максимум 60 строк.
При расчете отчетов откатов нет вообще. Там просто негде возникнуть ошибкам (ну правда, негде!). Но возможность для отката, причем принудительно, мне предоставляют.
Вообще говоря, все описанные проблемы я благополучно решаю, все работает в пределах допустимого времени ожидания и все такое. Просто немного неприятно, что мне приходится иногда сильно изворачиваться, чтобы приемлимо решить ту или иную задачу.
А еще, блин, достало то, что даже после 3-го сервиспака, QA в плане запросе какую-то ересь пишет.

2Denis A
К сожалению, в некоторых случаях я думаю лучше, чем сервер. Ну, по крайней мере, при некоторых ключевых выборках те планы запросов, которые я ему навязываю, оказываются лучше, чем те, которые он выбирает сам.
А Truncate Log on Checkpoint - это все ж таки не то (см. BOL).

2All
Кстати, может быть я зря напрягаюсь. Просто не с чем сравнить производительность (сколько искал - ни один из производителей подобных систем не приводит скоростных характеристик), так что может быть все и так хорошо.
Устал в последнее время немного, вот и озлобился :-)
Хотя, у MS SQL действительно есть некорые не совсем объяснимые с моей точки зрения моменты.
Попробуйте это скрипт
У меня при первой вставке ошибка 8624, Internal SQL Server error.
А при 2-й все нормально

create table temp(SPID smallint default @@SPID,Summ int)
go

create view TEmp_v as select SUmm from temp where SPID = @@SPID
go

insert into TEmp_v(Summ)
select Sum(uid) from sysobjects
go

insert into TEmp_v(Summ)
select Summ from (select Summ=Sum(uid) from sysobjects)S
go

2Glory
>и после этого продолжаете утверждать, что все ваши проблемы только в том,
>что SQL сервер ведет transaction log ???

Я этого как-раз не утверждал, да и не буду. Просто если бы можно было бы отключить лог, мне в 6 из 80 отчетов было бы полегче.
По поводу железа...
Заказчику было предложено 2 конфигурации железа: рекомендуемая и минимально необходимая. Угадайте с 3-х раз, что он выбрал.

Вообще-то у меня вопрос был чисто риторический - я прекрасно знаю, что нельзя отключить лог в MS SQL, и, вообще говоря, для сервера БД это неправильно. Знаю, что существуют возможности порешать все проблемы. К примеру переписать отчеты по другому. Именно это и приходится делать. Но при написании отчета таким образом, чтобы обойти некторые проблемы, можно потерять прозрачное понимание методики расчета отчета. Да и в случае изменения алгоритма расчета есть большая вероятность, что его нужно будет переоптимизировать.
P.S.
И опять таки повторюсь: если есть какая-то функциональность - спасибо тебе, разработчик, подумал ты обо мне. Но дай мне возможность ее включать и выключать.
Тот же Informix, например, прекрасно понимает, зачем нужны нелоггируемые таблицы, а MS - нет. Обидно.

2IBMer
По поводу хранилища данных - хорошо бы, но...
есть всего несколько отчетов из многих, которым бы это пригодилось, и ради них требовать еще один сервер и т.д. - и мороки много, и все равно не дадут.

2Garya
Таблица "Псевдо-временная", spid-разделенная. В принципе, я уже это описывал. как описывал и то, почему у меня возникли определенные проблемы, и то, как я их решаю, причем более менеее успешно.
по поводу, почему бы не формировать таблицу триггерами и т.д....
Исходная таблица ~24 Gb, 98*10^6 строк, еженедельный(5.5 дня) прирост около 6*10^6 строк... мелкие выборки (вроде карточки абонента, долгов по ограниченному набору лицевых счетов и т.д.) считаются вполне приемлимое время (0.6 - 2.0 сек). а вот отчеты, требующие, к примеру, показа структуры задолженности по диапазонам долга в разбиении по районам, считаются достаточно долго.
Таблица оплат относительно небольшая, 12*10^6 строк. Плюс остатки на начало месяца (5*10^5 строк каждый месяц). Одним select'ом получить сальдо на указанную дату получается еще накладнее, чем посчитать отдельно сальдо входящее, начисления, оплату и затем высчитать сальдо исходящее.
вот и приходится прогонять через псевдо-временную таблицу все лицевые и считать для них сальдо и движение. Одновременно в таблице не бывает более 5-7 тысяч записей (все считается группами по 5 тысяч ЛС, число подобрано экспериментально). Просто логгирование транзитных и по смыслу временных данных мне представляется по крайней мере неразумным.

P.S.
Для тех отчетов, для которых это возможно, раз в сутки ночью считается суммарная информация. А для нескольких объем суммарной информации сопоставим с объемом исходной информации. Вот с этими то отчетами я и мучаюсь.
P.P.S.
В виду того, что пока вроде бы товарищ Б.Гейтс не собирается реализовывать то, что мне хотелось бы, а так же в виду полной риторичности моего вопроса, предлагаю тему закрыть.