Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
Здравствуйте, прошу сильно не бить, а если и бить то не по почкам.
С sql я знаком дня этак 3, нужен скрипт перестроения индекса в T-SQL, кто может подсказать как сформировать правильный запрос?
Нужно что-то универсальное, т.к. серверов несколько, везде свой лес (свои настройки и структура бд), времени на изучение вопроса катастрофически нет. Ну и если не сильно универсальное то хотя бы в какую сторону копать, синтаксис команды ALTER INDEX (Transact-SQL) в силу отсутсвия знаний не дает ровным счетом ничего, просто из-за того, что нужно понимать всю суть всего что касается индексов, на что сейчас просто нет времени :(
Делаю обслуживание БД через sqlcmd с логированием результата и подтягиванием аларма в систему мониторинга, что бы быть в курсе где что не так с БД.

Так же хотел бы уточнить, верно ли я понимаю суть обслуживания БД SQL
1) проверка целостности БД
2) перестроение индекса
3) обновление статистики
4) очистка процедурного кэша
5) собственно сам бэкапинг базы
6) очистка журнала

p.s. ну и предвидя возможные вопросы, почему не делать все это через планы обслуживания - используем sql express :) ну и лично для меня в систему мониторинга так проще запросы подтягивать наверное
25 май 17, 13:33    [20511230]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
londinium
Member

Откуда: Киев
Сообщений: 1138
IvanIvan48,
тут на форуме скрипты ребилда индексов регулярно бегают. Например
25 май 17, 13:53    [20511362]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
londinium
Member

Откуда: Киев
Сообщений: 1138
https://ola.hallengren.com/downloads.html
25 май 17, 13:54    [20511363]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
Так же хотел бы уточнить, верно ли я понимаю суть обслуживания БД SQL
1) проверка целостности БД
2) перестроение индекса
3) обновление статистики
4) очистка процедурного кэша
5) собственно сам бэкапинг базы
6) очистка журнала
Все верно.
Еще возможен перенос на другой сервер, перенос некот. файлов БД на другой диск.
25 май 17, 14:01    [20511403]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
IvanIvan48,

а кэш вам чем мешает?
25 май 17, 14:03    [20511412]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
TaPaK
IvanIvan48,

а кэш вам чем мешает?


У какого-то индуса прочитал.
25 май 17, 14:05    [20511421]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
Владислав Колосов
TaPaK
IvanIvan48,

а кэш вам чем мешает?


У какого-то индуса прочитал.

именно так) но вроде как аргументирванно)

[youtube=
Ссылка на позицию в клипе: http://youtu.be/V3DeKk01hyo?t=29m17s
]
25 май 17, 14:38    [20511567]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
Короче вот тут он про очистку процедурного кэша, в предыдущем сообщении что-то ролик не заработал как должен был)


Ссылка на позицию в клипе: http://youtu.be/V3DeKk01hyo?t=29m17s
25 май 17, 14:39    [20511575]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
в ролике на 29ой минуте 17 секнде
25 май 17, 14:40    [20511580]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
есть ALTER INDEX (Transact-SQL)
можно ли сделать универсальный запрос по перестройке индексов или тут нужно прям дла каждой отдельно взятой базы все подстраивать до мелочей? сори если задаю глупые вопросы :)
25 май 17, 14:46    [20511602]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
IvanIvan48,

божественно... сразу после статистики... он мой кумир
25 май 17, 14:47    [20511606]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
автор
https://ola.hallengren.com/downloads.html

ну я так понял это что-то типа процедуры, по проще варианта нет, чем портянка на 5 страница 10 шрифтом?!)
25 май 17, 14:48    [20511610]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
TaPaK,

автор
Короче вот тут он про очистку процедурного кэша, в предыдущем сообщении что-то ролик не заработал как должен был)

раз бложике написал, не вырубишь топором из пытливых юных умов
25 май 17, 14:53    [20511624]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
человек_ниоткуда
Guest
1) проверка целостности БД
Не нужна если БД на СХД или RAID с проверкой целостности.
Не поможет если после проверки булет выполнено резервное копирование БД.
Не очень нужна для GPT-партиций (вопрос спорный).
В БД нужно включить запись checksum.

Суть в том, что если будет ошибка, то база об этом узнает в момент когда будет выполнен доступ к повреждённым данным и запретит доступ к БД ВСЕМ. После обнаружения ошибки, с большой вероятностью придётся БД из бекапа восстанавливать. Это реальный опыт обслуживания 200 машин со скулями на торговых точках.

2) перестроение индекса
Какое перестроение?

rebuild + reorganize по стандартному алгоритму microsoft нужен если БД (filegroup) расположена на одном шпиндельном диске.

rebuild не нужен если БД (filegroup) расположена на СХД.
rebuild не нужен если БД (filegroup) расположена на SSD диске.

Если если БД (filegroup) расположена на SSD диске или на СХД:
reorganize не нужен если sys.dm_db_index_physical_stats page_count меньше 1000
reorganize не нужен если sys.dm_db_index_physical_stats avg_page_space_used_in_percent меньше 75%, для таблиц c page_count от 1000 страниц и до 1310720 страниц. Для больших таблиц нужно смотреть индивидуально.

3) обновление статистики
Лучше сделать, чем не сделать.
Не нужно если делался rebuild индекса.
Нужно если статистики в таблице устаревают раньше чем SQL их автоматически обновляет.
Нужно если в таблице были есть операции вставки/удаления/обновления.
Нужно с полным сканированием, если простое обновление не помогает.

На простейшем уровне опеки БД, достаточно смотреть на изменения в sys.dm_db_index_usage_stats, и если есть изменения в цифрах user_updates+system_updates, то обновлять.

4) очистка процедурного кэша
Не нужно. Я б даже сказал вредно.

5) собственно сам бэкапинг базы
Нужно, причём такое чтоб базу потом восстановить можно было за удоволетворительный срок. Нужно об этом договориться с бизнесом.

6) очистка журнала
shrink? -- SHRINK нужен до того размера при котором не произойдёт вырастание журнала в рабочее время.
25 май 17, 15:29    [20511763]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
o-o
Guest
человек_ниоткуда
1) проверка целостности БД
Не нужна если БД на СХД или RAID с проверкой целостности.

RAID что, в курсе, какой порядок страниц индекса?
или знает, что каждой строке индекса должна соответствовать строка таблицы?
25 май 17, 16:00    [20511935]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
o-o
человек_ниоткуда
1) проверка целостности БД
Не нужна если БД на СХД или RAID с проверкой целостности.

RAID что, в курсе, какой порядок страниц индекса?
или знает, что каждой строке индекса должна соответствовать строка таблицы?

главное что бы файлик был целый
25 май 17, 16:05    [20511963]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36694
человек_ниоткуда
rebuild не нужен если БД (filegroup) расположена на СХД.
rebuild не нужен если БД (filegroup) расположена на SSD диске.
Ой, какая милота. Ребилд индексов, расположенных на схд, нужен из соображений перфоманса для любых индексов с случайной вставкой ключей. Ребилд индексов на SSD нужен для экономии места на SSD (и оперативной памяти вместе с этим).
25 май 17, 16:52    [20512160]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
1) проверка целостности БД
Не нужна если БД на СХД или RAID с проверкой целостности.
Спорно. Есть возможность поломать БД не касаясь работы оборудования.
Помницца, как-то пришлось прервать работу шринка большой базы. Закончилось suspect-ом. :)
А регулярная проверка действительно не нужна. Только при подозрениях.

Обновление статистики крупных нагруженных таблиц тоже время от времени нужно делать. В небизнес-время.
У нас вдруг почти колом стала большая база. В теч. неск. дней скорость упала до неприемлимой. Еле ворочалась. Оказалось в самую важную таблицу добавили поле и статистики грохнулись... После обновления ст-ки всего одной таблицы база снова ожила.
25 май 17, 17:05    [20512212]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
uaggster
Member

Откуда:
Сообщений: 767
человек_ниоткуда
1) проверка целостности БД
Не нужна если БД на СХД или RAID с проверкой целостности.

Гм... Это как? Что означает "с проверкой целостности"?
RAID5/1/10 и т.д.?
Так они могут писать "мимо" - только в путь! Какая-нибудь бяка в драйвере контроллера, и всё, понеслось!
(Дада. Я люблю сервера DEPO, куда деваться то).
человек_ниоткуда
Не поможет если после проверки булет выполнено резервное копирование БД.

Ну, первым шагом джоба DBCC CHECKDB, вторым (если не обломилось на первом шаге) - полный бэкап (ну, например).
Если база, скажем так, сотня-другая гигабайт - то всё за вполне себе небольшое время завершается.
Почему нет то?
человек_ниоткуда
Не очень нужна для GPT-партиций (вопрос спорный).

Почему? Объясните, правда не понимаю!
человек_ниоткуда
В БД нужно включить запись checksum.

Ну, это понятно...
25 май 17, 17:11    [20512240]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
uaggster
человек_ниоткуда
1) проверка целостности БД
Не нужна если БД на СХД или RAID с проверкой целостности.

...
RAID5/1/10 и т.д.?
Какая-нибудь бяка в драйвере контроллера, и всё, понеслось!
...

у нас такой случай был, бэкапилось все на тот же диск, думали что raid 10 это что-то вечное, из гранита, в итоге умерло все) после этого рейдам я доверяю еще меньше чем раньше.
25 май 17, 18:13    [20512540]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
Вы мне глупому нубу скажите, копать или не копать?)
Я так понял переиндексацию делать не стоит, базы максимум по 30-40 гиг и не сильно нагружены, т.е. изменения не прямо вот быстрые какие-то, просто пополняют потиху)
Что выполнять, что не выполнять? А то тут холивар разгорается :D
25 май 17, 18:16    [20512547]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
o-o
Guest
Бэкап всех баз раз в сутки или за сколько времени не жалко данные потерять
И dbcc checkdb всем базам раз в неделю.
25 май 17, 18:34    [20512585]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
1) проверяю целостность БД
2) обновляю статистику
3) делаю шринк
4) бэкаплю
так?

еще по t-sql
тут все верно по скриптам или как-то не так? а то sql сказал что он мол не отвечает за последствия предоставленного им кода)))

1) проверяю целостность БД
/chesk.sql
USE [mytest001]
GO
DBCC CHECKDB(N'mytest001') WITH NO_INFOMSGS

2) обновляю статистику
/stat.sql
use [mybase123]
GO
UPDATE STATISTICS [dbo].[Table_1]
WITH FULLSCAN

3)
/shrink.sql
USE [mytest001]
GO
DBCC SHRINKDATABASE (mytest001, TRUNCATEONLY)

4)
/backup.sql
BACKUP DATABASE [mybase123] TO DISK = N'C:\script\BASE\mybase' WITH RETAINDAYS = 3, NOFORMAT, INIT, NAME = N'mybase123_backup_2017_05_24_234900_4553030', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'mybase123' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'mybase123' )
if @backupSetId is null begin raiserror(N'Ошибка верификации. Сведения о резервном копировании для базы данных "mybase123" не найдены.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\script\BASE\mybase' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

так?
25 май 17, 18:54    [20512656]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
IvanIvan48,

индусы занятные люди, если их методику программирования спроецировать на повседневность, то они штаны, например, снимали бы, разрезая их по боковому шву, а при одевании - сшивали. Выглядит правильно, в общем-то, и свежо.
25 май 17, 19:10    [20512690]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индексов в T-SQL  [new]
IvanIvan48
Member

Откуда:
Сообщений: 13
автор
sp_updatestats выполняет инструкцию UPDATE STATISTICS, указывая ключевое слово ALL, на всех пользовательских и внутренних таблиц в базе данных. sp_updatestats выводит сообщения о ходе своего выполнения. По завершении обновления выдается отчет о том, что обновление статистики произведено для всех таблиц.

Процедура sp_updatestats обновляет статистику по отключенным некластеризованным индексам и не обновляет статистику по отключенным кластеризованным индексам.

Для дисковых таблиц sp_updatestats обновляет только статистику, требующую обновления, основываясь на modification_counter сведения в sys.dm_db_stats_properties представления каталога, таким образом предотвращаются ненужные обновления статистики по неизменным строкам. Статистика в таблицах, оптимизированных для памяти всегда обновляется при выполнении sp_updatestats. Поэтому не следует вызывать sp_updatestats чаще, чем необходимо.

sp_updatestats можно запустить повторную компиляцию хранимых процедур или других скомпилированного кода. Тем не менее sp_updatestats может не вызвать повторную компиляцию, если только один план запроса для целевых таблиц и индексов на них. Повторная компиляция в этих случаях будет не нужна даже при обновлении статистики.

Для баз данных с уровнем совместимости ниже 90 при выполнении процедуры sp_updatestats не сохраняет последнее значение параметра NORECOMPUTE для заданной статистики. Для баз данных с уровнем совместимости 90 или выше sp_updatestats сохраняет последнее значение параметра NORECOMPUTE для заданной статистики. Дополнительные сведения об отключении и повторном включении обновления статистики см. в разделе статистики.


я так понял, что просто по дефолту UPDATE STATISTICS делается только для строго указанных таблиц и прочего, когда их много, то видимо замудохаешься их переписывать, для удоства сделали процедуру sp_updatestats, которая автоматом все подтягивает, лучше использовать ее или все таки руками все шкрябать в скрипт?

рабочий вариант скрипта:
автор
USE [mytest001]
GO
EXEC sp_updatestats


лог после выполнения:
автор
Контекст базы данных изменен на "mytest001".
Обновление [sys].[sqlagent_jobs]
[sqlagent_jobs_clust], обновление не обязательно...
[sqlagent_jobs_nc1_name], обновление не обязательно...
Статистика по индексам 0 обновлена, 2 не потребовало обновления.
Обновление [sys].[sqlagent_jobsteps]
[sqlagent_jobsteps_clust], обновление не обязательно...
[sqlagent_jobsteps_nc1], обновление не обязательно...
[sqlagent_jobsteps_nc2], обновление не обязательно...
Статистика по индексам 0 обновлена, 3 не потребовало обновления.
Обновление [sys].[sqlagent_job_history]
[sqlagent_job_history_clust], обновление не обязательно...
[sqlagent_job_history_nc1], обновление не обязательно...
Статистика по индексам 0 обновлена, 2 не потребовало обновления.
Обновление [sys].[sqlagent_jobsteps_logs]
[sqlagent_jobsteps_logs_nc1], обновление не обязательно...
Статистика по индексам 0 обновлена, 1 не потребовало обновления.
Обновление [dbo].[Table_1]
[PK_Table_1], обновление не обязательно...
Статистика по индексам 0 обновлена, 1 не потребовало обновления.
Обновление [dbo].[sysdiagrams]
[PK__sysdiagr__C2B05B61256235CA], обновление не обязательно...
[UK_principal_name], обновление не обязательно...
Статистика по индексам 0 обновлена, 2 не потребовало обновления.
Обновление [sys].[queue_messages_1977058079]
[queue_clustered_index], обновление не обязательно...
[queue_secondary_index], обновление не обязательно...
Статистика по индексам 0 обновлена, 2 не потребовало обновления.
Обновление [sys].[queue_messages_2009058193]
[queue_clustered_index], обновление не обязательно...
[queue_secondary_index], обновление не обязательно...
Статистика по индексам 0 обновлена, 2 не потребовало обновления.
Обновление [sys].[queue_messages_2041058307]
[queue_clustered_index], обновление не обязательно...
[queue_secondary_index], обновление не обязательно...
Статистика по индексам 0 обновлена, 2 не потребовало обновления.
Обновление [sys].[filestream_tombstone_2073058421]
[FSTSClusIdx], обновление не обязательно...
[FSTSNCIdx], обновление не обязательно...
Статистика по индексам 0 обновлена, 2 не потребовало обновления.
Обновление [sys].[syscommittab]
[ci_commit_ts], обновление не обязательно...
[si_xdes_id], обновление не обязательно...
Статистика по индексам 0 обновлена, 2 не потребовало обновления.
Обновление [sys].[filetable_updates_2105058535]
[FFtUpdateIdx], обновление не обязательно...
Статистика по индексам 0 обновлена, 1 не потребовало обновления.
Статистика по всем таблицам обновлена.
25 май 17, 21:34    [20512952]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить