create index ix_test on ships(name) with statistics_only
go
set showplan_all on
go
set noexec on
go
select *
from ships with (index = ix_test)
go
set showplan_all off
go
set noexec off
go
В итоге — Index 'ix_test' on table 'ships' (specified in the FROM clause) does not exist.
SAN — это, конечно, не система хранения данных, а сеть хранения данных
Мне об этом писали в личку, но я не стал исправлять. В России такие вендоры как EMC, HP и NetApp на официальных ресурсах используют форму «системы хранения данных». Мне она в таком виде нравится больше.
Хотя вопрос не принципиальный, поэтому пусть будет «сеть/система».
Если честно, не понял вопрос, мало данных. Отвечаю насколько понял. Если у вас есть доступ к этой БД, нет возможности подключения к ней с помощью SQL Server Management Studio, чтобы сгенерировать скрипты мастером, но есть возможность обращаться к INFORMATION_SCHEMA, то вы, в принципе, и «вручную» можете себе все объекты заскриптовать. Если же доступа к INFORMATION_SCHEMA и системным представлениям типа sys.tables нет — то увы.
Вы под сбором статистики имеете в виду обновление статистики? Если нет, то ниже можете не читать :)
Обновление статистики после перестроения индексов имеет смысл, поскольку при этом обновится автоматически созданная статистика.
Статистика, вообще, нужна оптимизатору для построения плана выполнения запроса. При дефрагментации/перестроении она не играет роли. Выбор index scan/index seek в плане выполнения, если упрощённо, зависит от количества записей в таблице и от предполагаемого результата запроса. Т.е. если вы из таблицы, содержащей 15 миллионов строк хотите получить всего 1 строку — логично использовать index seek. Если же вам нужно будет получить 10 миллионов строк, будет оправдано использование index/table scan'a. Вот для того чтобы понять сколько строк вернётся запросом и используется статистика.
уменьшая фрагментацию, мы увеличиваем плотность данных на страницах
Из-за fill factor = 80 вы местами плотность данных не увеличиваете, а уменьшаете :). (это я занудствую, в общем-то, вы же писали зачем это делаете, но кто-то может удивиться, увидев, что после процедур, направленных на увеличение плотности данных, файл данных вырастает)
Про статистику я не забыл, msdn утверждает, что обновление статистики произойдет автоматически, если после rebuild мы явно не напишем STATISTICS_NORECOMPUTE = ON, я явно не писал
И это верно, но статистика обновится только по тем индексам, для которых был сделан REBUILD. Для индекса, «подвергшегося» дефрагментации (REORGANIZE), статистика не обновится. И также не обновится статистика, созданная SQL Server'ом автоматически.
page_count > 5 – я считаю, что перестраивать индексы с малым количеством страниц не имеет смысла
Перестраивать индексы с малым количеством страниц действительно не имеет смысла, только границу надо поднять как минимум до 8. До тех пор пока индекс содержит меньше 8 страниц, он размещается в смешанных экстентах и его дефрагментация/перестройка может не принести желаемого результата (msdn).
Я для себя выбрал границу в 128 страниц, т.е. только те, которые «весят» больше мегабайта. Сейчас вот правда задумался — не поднять ли планку ещё :).
DECLARE defragCur CURSOR FOR
SELECT
[object_id],
index_id,
table_name,
index_name,
avg_frag_percent_before,
fill_factor,
partition_num
FROM dba_tasks.dbo.index_defrag_statistic
ORDER BY [object_id], index_id DESC --Сначала не кластерные индексы
Вы не накладываете отбор по @currentProcID, соответственно, в результате выполнения запроса вы получите вообще все записи из index_defrag_statistics и, следовательно, будете дефрагментировать/перестраивать все индексы, когда либо записанные в неё. Причём вполне возможна ситуация когда один и тот же индекс сначала перестроится, потом дефрагментируется, потом снова перестроится и т.д.
Если вы перед запуском этой процедуры очищаете таблицу — то зачем вам @currentProcID?
Ну и вообще — вопрос с дефргаментацией/перестройкой индексов достаточно сложный. Не понятна «полезность» этой процедуры. Ну точнее полезность понятна — индексы становятся ровные, красивые и т.д. Но, я ни разу не видел СЕРЬЁЗНЫХ проблем с производительностью, вызванных фрагментированием индексов.
Степень фрагментации индекса никак не учитывается оптимизатором, соответственно и не влияет на план запроса. Фактически, если используется поиск по индексу (INDEX SEEK) — степень фрагментации никак не повлияет на выполнение запроса — головка и так будет носиться по диску, выцепляя отдельные страницы индекса. Если же при выполнении запроса используется INDEX SCAN на очень большой таблице — вот тут может быть проблема из-за того, что SQL Server не сможет читать данные последовательно большими кусками. Но такие запросы и так достаточно медлительны и фрагментированность индекса на производительность, в итоге, влияет не сильно.
Так же, бывает, что запрос «тормозит», админ выполняет перестройку (ALTER INDEX REBUILD) индекса и запрос начинает летать — тут дело не столько в самой перестройке индекса, сколько в попутном обновлении статистики, которое почему-то часто забывают — как и автор топика, не добавил её в скрипт обслуживания индексов, что, имхо, неправильно.
Что бы вы сделали на моем месте? Начали разбираться с 1С, или щелкнули кнопку отката?
Так вам 1С надо было обновлять или нет? Если нет — зачем начали? Если надо — так выбор очевиден, по моему.
Только в сути вопроса 1С не имеет отношения, я описываю ситуацию восстановления только касательно ОС, а там мало ли что именно у людей «гавкнется».
Да, вы правы, сорри, за возможно излишне резкие комментарии. Просто сама ситуация, когда из-за пустячной причины кто-то (даже не я) теряет 30 часов, героически сражаясь с последствиями… Ну как-то это неправильно всё.
Имхо, всего можно было бы избежать, если бы вы не пытались все делать сразу на «боевой» системе, а сначала «поигрались» бы на тестовом окружении.
Всё так и должно быть. 8.2 не заменяет предыдущую версию, а становится рядом.
Вы, конечно, извините, но 30 часов своего времени вы потратили исключительно из-за своего непрофессионализма. Из-за того, что из консоли «пропал» кластер восстанавливать винду? Are you fucking kidding me?
Позвонили бы лучше своему 1С-нику, чтобы пришел на следующий день с утра на пять минут раньше и заново прописал базы, раз у вас не было времени на изучение.
То что вьюхи полезны — я не спорю и рассматривать нужно каждый случай отдельно. Вполне вероятно, что автор самой статьи тоже не против такого подхода.
Возможно, это моя вина, как переводчика, что я не смог этого передать — читая саму статью, у меня не возникало мыслей, что от представлений вообще надо отказываться. Сорри.
Теперь к примеру.
Вот диаграмма с этими таблицами — FK есть.
Теперь запросы:
SELECT OrderDate
FROM
(
SELECT soh.SalesPersonID,
a.City,
soh.OrderDate,
soh.PurchaseOrderNumber,
soh.AccountNumber,
sd.OrderQty,
sd.UnitPrice
FROM Sales.SalesOrderHeader AS soh
JOIN Person.Address AS a
ON soh.ShipToAddressID = a.AddressID
JOIN Sales.SalesOrderDetail AS sd
ON soh.SalesOrderID = sd.SalesOrderID
)temp
WHERE SalesPersonID=277
Время выполнения 74 миллисекунды, 960 операций чтения.
SELECT soh.OrderDate
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesPersonID = 277 ;
Время выполнения 59 мс, 703 операции чтения
Вместо внутреннего, LEFT JOIN:
SELECT OrderDate
FROM
(
SELECT soh.SalesPersonID,
a.City,
soh.OrderDate,
soh.PurchaseOrderNumber,
soh.AccountNumber,
sd.OrderQty,
sd.UnitPrice
FROM Sales.SalesOrderHeader AS soh
LEFT JOIN Person.Address AS a
ON soh.ShipToAddressID = a.AddressID
LEFT JOIN Sales.SalesOrderDetail AS sd
ON soh.SalesOrderID = sd.SalesOrderID
)temp
WHERE SalesPersonID=277
Время выполнения 70 мс, 931 операция чтения.
Скриншот с актуальными планами выполнения (они идут в том же порядке, как у меня перечислены запросы):
Не смотря на то, что выигрыш не настолько велик, как в оригинале, но он все равно есть. Мне кажется, что Грант (автор), поторопился с примером. Поскольку мне пришлось удалить данные из таблицы SalesOrderDetail — так, чтобы одной записи из SalesOrderHeader соответствовала одна запись из SalesOrderDeatil, иначе результаты запросов были разными.
Дак, как бы, в том и смысл, что когда пишем запрос вручную — мы не втыкаем лишние соединения, а когда «относимся ко вьюхам как к таблицам» и выбираем данные из вьюх — от них никуда не денешься.
Если навтыкать соединений — план будет точно такой же, как у запроса из вьюх, поскольку они все равно «разворачиваются» до запросов.
Или я вас не правильно понял?
Ага, нашел. Все зависит от приоритетов типов данных. Типы данных с более низким приоритетом, приводятся к типам с более высоким. В статье тип столбца nvarchar (практически самый низкий приоритет), соответственно он приводится к более высокому int'у. В вашем примере все тоже самое.
А вот, кстати, да. Тут надо будет покопать. Если выполнять пример из статьи — все будет как описано — Index Scan с неверным типом и Index Seek с правильным.
Да понятно, что AlwaysOn — это в разы круче зеркалирования (и, увы, дороже), просто удивило его первоначальное сравнение с репликацией :).
В зеркале, кстати, в случае Enterprise Edition, можно с партнера делать Snapshot'ы и обращаться к ним на чтение, так что не совсем «мертвый груз» :)
1. Ага, но в статье этого нет.
2. Database Mirroring имел «специфические» требования к модели восстановления. Она должна быть полной (про bulk-logged не уверен, но вроде даже в ней нельзя было использовать). Про ваше ИМХО понятно, но советую посмотреть это.
3. «возможность обеспечить отказоустойчивость данных малой кровью» — она была и раньше. Начиная с 2005-го сервера database mirroring работал в Standard и Enterprise редакциях, а вот AlwaysOn — только в Enterprise, плюс подразумевается наличие кластера Windows — а это уже, все-таки, не совсем «малой кровью». Надеюсь, что когда SQL Server окончательно избавится от зеркалирования, в Standard появится урезанная версия ALwaysOn.
Я это все к тому, что последняя фраза очень странная — SQL Server и так был «продуктом Enterprise-уровня».
Функция AlwaysOn (доступная, к слову, только в Enterprise Edition) предназначена для обеспечения отказоустойчивости баз данных.
Мне кажется, стоит добавить, что оно может обеспечивать отказоустойчивость не одной, а нескольких БД. Т.е. в одну Availability Group может входить несколько БД и они будут вместе «переезжать» в случае файловера.
Работает эта технология, осуществляя репликацию базы данных между серверами, точнее — лога транзакций
Эта штука скорее усовершенствованный Database Mirroring, а не репликация и именно из-за нее Database Mirroring в SQL Server 2012 — это «deprecated feature».
Появился функционал, который позволяет не кривя душой называть MS SQL 2012 продуктом Enterprise-уровня, с чем я нас всех и поздравляю.
А без Columnstore индексов нельзя было назвать MS SQL Server продуктом Enterprise уровня? AlwaysOn не считаем, поскольку он является фактически эволюционировавшим зеркалированием, которое появилось еще в 2005-м SQL Server.
В итоге — Index 'ix_test' on table 'ships' (specified in the FROM clause) does not exist.
Мне об этом писали в личку, но я не стал исправлять. В России такие вендоры как EMC, HP и NetApp на официальных ресурсах используют форму «системы хранения данных». Мне она в таком виде нравится больше.
Хотя вопрос не принципиальный, поэтому пусть будет «сеть/система».
Обновление статистики после перестроения индексов имеет смысл, поскольку при этом обновится автоматически созданная статистика.
Статистика, вообще, нужна оптимизатору для построения плана выполнения запроса. При дефрагментации/перестроении она не играет роли. Выбор index scan/index seek в плане выполнения, если упрощённо, зависит от количества записей в таблице и от предполагаемого результата запроса. Т.е. если вы из таблицы, содержащей 15 миллионов строк хотите получить всего 1 строку — логично использовать index seek. Если же вам нужно будет получить 10 миллионов строк, будет оправдано использование index/table scan'a. Вот для того чтобы понять сколько строк вернётся запросом и используется статистика.
Из-за fill factor = 80 вы местами плотность данных не увеличиваете, а уменьшаете :). (это я занудствую, в общем-то, вы же писали зачем это делаете, но кто-то может удивиться, увидев, что после процедур, направленных на увеличение плотности данных, файл данных вырастает)
И это верно, но статистика обновится только по тем индексам, для которых был сделан REBUILD. Для индекса, «подвергшегося» дефрагментации (REORGANIZE), статистика не обновится. И также не обновится статистика, созданная SQL Server'ом автоматически.
Перестраивать индексы с малым количеством страниц действительно не имеет смысла, только границу надо поднять как минимум до 8. До тех пор пока индекс содержит меньше 8 страниц, он размещается в смешанных экстентах и его дефрагментация/перестройка может не принести желаемого результата (msdn).
Я для себя выбрал границу в 128 страниц, т.е. только те, которые «весят» больше мегабайта. Сейчас вот правда задумался — не поднять ли планку ещё :).
Вы не накладываете отбор по @currentProcID, соответственно, в результате выполнения запроса вы получите вообще все записи из index_defrag_statistics и, следовательно, будете дефрагментировать/перестраивать все индексы, когда либо записанные в неё. Причём вполне возможна ситуация когда один и тот же индекс сначала перестроится, потом дефрагментируется, потом снова перестроится и т.д.
Если вы перед запуском этой процедуры очищаете таблицу — то зачем вам @currentProcID?
Ну и вообще — вопрос с дефргаментацией/перестройкой индексов достаточно сложный. Не понятна «полезность» этой процедуры. Ну точнее полезность понятна — индексы становятся ровные, красивые и т.д. Но, я ни разу не видел СЕРЬЁЗНЫХ проблем с производительностью, вызванных фрагментированием индексов.
Степень фрагментации индекса никак не учитывается оптимизатором, соответственно и не влияет на план запроса. Фактически, если используется поиск по индексу (INDEX SEEK) — степень фрагментации никак не повлияет на выполнение запроса — головка и так будет носиться по диску, выцепляя отдельные страницы индекса. Если же при выполнении запроса используется INDEX SCAN на очень большой таблице — вот тут может быть проблема из-за того, что SQL Server не сможет читать данные последовательно большими кусками. Но такие запросы и так достаточно медлительны и фрагментированность индекса на производительность, в итоге, влияет не сильно.
Так же, бывает, что запрос «тормозит», админ выполняет перестройку (ALTER INDEX REBUILD) индекса и запрос начинает летать — тут дело не столько в самой перестройке индекса, сколько в попутном обновлении статистики, которое почему-то часто забывают — как и автор топика, не добавил её в скрипт обслуживания индексов, что, имхо, неправильно.
Не стоит искать злого умысла в том, что объяснимо простой человеческой глупостью. Какой-нибудь чиновник что-нибудь не так понял.
Так вам 1С надо было обновлять или нет? Если нет — зачем начали? Если надо — так выбор очевиден, по моему.
Да, вы правы, сорри, за возможно излишне резкие комментарии. Просто сама ситуация, когда из-за пустячной причины кто-то (даже не я) теряет 30 часов, героически сражаясь с последствиями… Ну как-то это неправильно всё.
Имхо, всего можно было бы избежать, если бы вы не пытались все делать сразу на «боевой» системе, а сначала «поигрались» бы на тестовом окружении.
Вы, конечно, извините, но 30 часов своего времени вы потратили исключительно из-за своего непрофессионализма. Из-за того, что из консоли «пропал» кластер восстанавливать винду? Are you fucking kidding me?
Позвонили бы лучше своему 1С-нику, чтобы пришел на следующий день с утра на пять минут раньше и заново прописал базы, раз у вас не было времени на изучение.
Возможно, это моя вина, как переводчика, что я не смог этого передать — читая саму статью, у меня не возникало мыслей, что от представлений вообще надо отказываться. Сорри.
Теперь к примеру.
Вот диаграмма с этими таблицами — FK есть.
Теперь запросы:
Время выполнения 74 миллисекунды, 960 операций чтения.
Время выполнения 59 мс, 703 операции чтения
Вместо внутреннего, LEFT JOIN:
Время выполнения 70 мс, 931 операция чтения.
Скриншот с актуальными планами выполнения (они идут в том же порядке, как у меня перечислены запросы):
Не смотря на то, что выигрыш не настолько велик, как в оригинале, но он все равно есть. Мне кажется, что Грант (автор), поторопился с примером. Поскольку мне пришлось удалить данные из таблицы SalesOrderDetail — так, чтобы одной записи из SalesOrderHeader соответствовала одна запись из SalesOrderDeatil, иначе результаты запросов были разными.
Если навтыкать соединений — план будет точно такой же, как у запроса из вьюх, поскольку они все равно «разворачиваются» до запросов.
Или я вас не правильно понял?
В зеркале, кстати, в случае Enterprise Edition, можно с партнера делать Snapshot'ы и обращаться к ним на чтение, так что не совсем «мертвый груз» :)
2. Database Mirroring имел «специфические» требования к модели восстановления. Она должна быть полной (про bulk-logged не уверен, но вроде даже в ней нельзя было использовать). Про ваше ИМХО понятно, но советую посмотреть это.
3. «возможность обеспечить отказоустойчивость данных малой кровью» — она была и раньше. Начиная с 2005-го сервера database mirroring работал в Standard и Enterprise редакциях, а вот AlwaysOn — только в Enterprise, плюс подразумевается наличие кластера Windows — а это уже, все-таки, не совсем «малой кровью». Надеюсь, что когда SQL Server окончательно избавится от зеркалирования, в Standard появится урезанная версия ALwaysOn.
Я это все к тому, что последняя фраза очень странная — SQL Server и так был «продуктом Enterprise-уровня».
Мне кажется, стоит добавить, что оно может обеспечивать отказоустойчивость не одной, а нескольких БД. Т.е. в одну Availability Group может входить несколько БД и они будут вместе «переезжать» в случае файловера.
Эта штука скорее усовершенствованный Database Mirroring, а не репликация и именно из-за нее Database Mirroring в SQL Server 2012 — это «deprecated feature».
А без Columnstore индексов нельзя было назвать MS SQL Server продуктом Enterprise уровня? AlwaysOn не считаем, поскольку он является фактически эволюционировавшим зеркалированием, которое появилось еще в 2005-м SQL Server.