Sql Server изменить значение коэффициента заполнения для всех индексов по tsql

Question

Sql Server изменить значение коэффициента заполнения для всех индексов по tsql

Я должен выставить свою БД в BACPAC-файл, чтобы импортировать его в Azure. Когда я пытаюсь экспортировать, я получаю сообщение об ошибке, потому что любые индексы имеют значение fillFactor.

Я нашел, как установить значение fillFactor для всех индексов, но я не могу указать 0, значение должно быть между 1 и 100. Если я изменяю значение в Management Studio, я могу установить его на 0.

Проблема в том, что мне нужно изменить множество индексов, и я хотел бы изменить значение fillFactor на все из них через tsql.

Есть идеи?.

Благодарю.

7

sql azure t-sql bacpac fillfactor

Источник

user2381496 14 май '13 в 11:39

6 ответов

Другие вопросы по тегам sql azure t-sql bacpac fillfactor

user40067 27 апр '17 в 22:57 2017-04-27 22:57 · Answer 1 · 2017-04-27 22:57

Что -то более простое для всех таблиц в одной базе данных:

   select 'ALTER INDEX ALL ON ['
   + s.name+ '].['+ o.name+'] REBUILD WITH (FILLFACTOR = 99)' 
   from sys.objects o
   inner join sys.schemas s on o.schema_id = s.schema_id
   where type='u' and is_ms_shipped=0

генерирует заявления, которые вы можете затем скопировать и выполнить.

user953734 28 дек '14 в 08:02 2014-12-28 08:02 · Answer 2 · 2014-12-28 08:02

Это не прямой способ T-SQL сделать это. Хотя он генерирует чистое решение T-SQL, которое вы можете применить к своей БД.

Ваши результаты могут отличаться в зависимости от вашей БД... Например, плохая ссылочная целостность может сделать это немного сложнее...

Также это сопровождается заявлением об отказе от ответственности за свой риск:-)

Получите БД, которую хотите перенести в проект SSDT

http://msdn.microsoft.com/en-us/library/azure/jj156163.aspx http://blogs.msdn.com/b/ssdt/archive/2012/04/19/migrating-a-database-to-sql-azure-using-ssdt.aspx

Это хороший способ перенести любую схему в Azure, независимо от... Это намного лучше, чем просто создать файл bacpac... исправить... экспортировать... исправить... и т. Д.... Поэтому я бы рекомендовал делать это в любое время перенести БД в Azure

Для исправлений FILLFACTOR я просто использовал поиск и замену, чтобы удалить все FILLFACTORS из сгенерированных файлов схемы... К счастью, в используемой мной БД все они были установлены на 90, поэтому было довольно легко найти и заменить решение в широком диапазоне (CTRL-SHIFT-F)... Если ваши значения различаются, вы, вероятно, можете использовать функции поиска RegEx в Visual Studio, чтобы найти все факторы заполнения и просто удалить их из индексов.

Я не так хорош в RegEx, но я думаю, что это работает

WITH \((.)*FILLFACTOR(.)*\)

На этом этапе вам придется исправить любые дополнительные исключения, связанные с соблюдением Azure. Приведенные ссылки описывают, как это сделать.

Теперь, когда у вас есть проект SSDT, совместимый с AZURE SQL.

А вот и сделай на свой страх и риск

Я использовал эти сценарии для удаления всех FK, PK и уникальных ограничений из БД.

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE IN  ('FOREIGN KEY', 'PRIMARY KEY', 'UNIQUE')))
begin
    declare @sql nvarchar(2000)
    SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
    + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM information_schema.table_constraints
    WHERE CONSTRAINT_TYPE IN  ('FOREIGN KEY', 'PRIMARY KEY', 'UNIQUE')
    exec (@sql)
end


declare @qry nvarchar(max);
select @qry = 
(SELECT  'DROP INDEX [' + ix.name + '] ON [' + OBJECT_NAME(ID) + ']; '
FROM  sysindexes ix
WHERE   ix.Name IS NOT null and ix.OrigFillFactor <> 0
for xml path(''));
exec sp_executesql @qry

Я делаю это, потому что AFAIK единственный способ полностью удалить параметр коэффициента заполнения - удалить и заново создать индекс. Это связано с каскадным набором проблем:-/ PK с коэффициентами заполнения нужно отбросить FK и т. Д. Вероятно, есть более разумный способ сделать это, чтобы вы не удаляли ВСЕ FK и PK и смотрели на деревья зависимостей...

Теперь вернитесь к проекту SSDT, совместимому с Azure, и выполните СРАВНЕНИЕ СХЕМЫ этого проекта с вашей БД... Это создаст сценарий, воссоздающий все ваши FK, PK и уникальные ограничения (без коэффициента заполнения).... В в этот момент вы можете просто нажать "обновить" или вы можете нажать кнопку справа от обновления, которая сгенерирует сценарий, который вы можете использовать... Так что теперь вооружен
- скрипт выше для удаления FKs, Pks и Unique.
- Скрипт, созданный SSDT
- Достаточное тестирование и обзор указанных скриптов, чтобы убедиться, что ничего не пропущено

Вы должны иметь возможность обновить текущую базу данных до SCHEMA, совместимой с Azure.

Дополнительные мысли:

В моем случае коэффициенты заполнения на производственной базе данных на самом деле не делали ничего полезного. Они были просто созданы по умолчанию. В вашем случае факторы заполнения могут быть важны, поэтому не просто удаляйте их все из окна, не относящегося к Azure Production, не зная последствий.

Есть дополнительные вещи, которые следует учитывать при работе с производственной системой... Например, это может привести к некоторым задержкам зеркального отображения и может привести к росту ваших файлов журнала, что вы не ожидаете. И то, и другое действительно имеет значение, если вы подаете заявление непосредственно на производство...

Было бы хорошо, если бы установка их на FILL FACTOR 100 работала: - /

Есть сторонние инструменты (как я слышал), которые вы можете использовать для перехода на Azure...

Другой вариант - использовать https://sqlazuremw.codeplex.com/

Используйте это, чтобы создать SCHEMA, совместимую с Azure, а затем использовать BCP для копирования всех данных.

НО, если вы хотите сделать свой текущий SCHEMA Azure совместимым, чтобы вы могли создать BACPAC-файл для загрузки в Azure, это сработало для меня один раз, когда мне пришлось это сделать.

РЕДАКТИРОВАТЬ: Azure V12 поддерживает коэффициенты заполнения

user569711 10 май '14 в 14:41 2014-05-10 14:41 · Answer 3 · 2014-05-10 14:41

SQL Azure, видимо, не поддерживает FILLFACTOR:

"База данных SQL Azure не поддерживает указание FILLFACTOR с помощью оператора CREATE INDEX. Если мы создадим индексы в базе данных SQL Azure, мы обнаружим, что все значения коэффициента заполнения индекса равны 0."

Вы должны были бы удалить все FILLFACTOR заявления от CREATE INDEX скрипты. Точно так же, SORT_IN_TEMPDB а также DATA_COMPRESSION и некоторые другие опции также не поддерживаются.

Полный список поддерживаемых ключевых слов в SQL Azure можно найти здесь.

Обновление: SQL Azure V12 (представлен в 2015 году) поддерживает FILLFACTOR, Смотрите здесь.

user383761 01 сен '16 в 14:25 2016-09-01 14:25 · Answer 4 · 2016-09-01 14:25

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

user376692 14 май '13 в 12:04 2013-05-14 12:04 · Answer 5 · 2013-05-14 12:04

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

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
       ELSE
       BEGIN
          -- SQL 2000 command
          DBCC DBREINDEX(@Table,' ',@fillfactor)  
       END

       FETCH NEXT FROM TableCursor INTO @Table  
   END  

   CLOSE TableCursor  
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

user2331665 14 май '13 в 11:58 2013-05-14 11:58 · Answer 6 · 2013-05-14 11:58

ALTER INDEX yourindex ON table.column
REBUILD WITH (FILLFACTOR = 0);

делает работу 0 равно 100 (см. http://msdn.microsoft.com/en-us/library/ms177459.aspx), что означает, что в индексе не осталось пробелов.

Вы должны запустить это для каждого индекса. восстановление может все же занять значительное время.

-1

Источник

user2331665 14 май '13 в 11:58