Sql Server fill factor for all indices using tsql

Question

I need to put my DB in a bacpac file in order to import it into Azure. When I try to export, I get an error because any indexes have a value of fillFactor.

I found how to set the fillFactor value for all indexes, but I cannot specify 0, the value should be between 1 and 100. If I change the value in the control studio, I can set it to 0.

The problem is that I have many indexes to modify, and I would like to change the fillFactor value to all of them via tsql.

Any ideas ?.

Thanks.

Answer 1

SQL Azure does not seem to support FILLFACTOR :

"The Azure SQL Database does not support the FILLFACTOR statement on the CREATE INDEX statement. If we create indexes in the Azure SQL Database, we find that the index values ​​of the fill index are 0."

You need to remove all FILLFACTOR from the CREATE INDEX scripts. Similarly, SORT_IN_TEMPDB and DATA_COMPRESSION and several other options are also not supported.

A complete list of supported keywords in SQL Azure can be found here .

Update : SQL Azure V12 (introduced in 2015) supports FILLFACTOR . See here .

Answer 2

This is not a direct way to T-SQL. Although it creates a pure T-SQL solution that you can apply to your database.

Your results may vary depending on your database ... For example, low referential integrity can make this a bit more complicated.

In addition, this is due to a disclaimer for your own risk :-)

• Get the database that you want to transfer to the SSDT project

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

This is a good way to port any schema to Azure independently ... This is better than just creating a bacpac file .. fix ... export ... fix .. etc. Therefore, I would recommend doing this anytime you want to transfer your database to Azure

For FILLFACTOR fixes, I simply used find and replace to remove all FILLFACTORS from the generated schema files ... Fortunately, I used DB so that they were all set to 90, so it was pretty easy to find a solution and replace (CTRL-SHIFT-F ) ... If your values ​​vary, you can probably use the RegEx Visual Studio search functions to find all the placeholders and simply remove them from the indexes.

I'm not so good at RegEx, but I think it works

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

At this point, you will have to fix any additional exceptions related to Azure compliance. The links provided describe how to do this.

2. Now that you are at the point where you have an SSDT project that meets the requirements of AZURE SQL.

Here comes ACTION AT YOUR OWN RISK

I used these scripts to remove all FK, PK, and Unique Constraints from the database.

 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 

I do this because AFAIK the only way to completely remove the fill parameter is to reset and recreate the index. This is due to a cascading set of problems: - / PK with fill factors it is necessary that FK fall, etc. There is probably a smarter way to do this so that you do not remove ALL FK and PK and you look at the dependency trees ...

3. Now go back to the Azure-compatible SSDT project and make a SCHEME for COMPARISON of this project with your DB ... This will create a script that recreates all your FK, PK and unique restrictions (without Fill Factor) .... At this point, you can just click "Update", or you can click the button to the right of the update that will generate a script that you can use ... So now armed

   • script above to remove FK, Pks and Unique.
   • script created by SSDT
   • Simple testing and viewing of the specified scenarios to ensure that nothing was missed.

You should be able to upgrade your current database to Azure compatible SCHEMA

Additional thoughts:

In my case, fill factors in the production database did nothing useful. They were created by default only. In your case, filling factors can be important, so do not just delete them all in your box without azure production, not knowing the consequences.

There are additional things to consider when performing this task in a production system ... For example, this can lead to mirror delays, and this can cause your log files to grow in a way that you don't expect. Which is important only if you turn directly to production ...

It would be nice if they all worked with FILL FACTOR 100: - /

There are third-party tools there (as I heard) that you can use to upgrade to Azure ...

Another option is to use https://sqlazuremw.codeplex.com/

Use this to create an Azure compatible SCHEMA, and then it uses BCP to copy all the data.

BUT, if you want to make your current SCHEMA Azure compatible so that you can create a bacpac file for upload to Azure, this worked for me when I had to do this.

EDIT: Azure V12 supports fill ratios

Answer 3

I found here a very useful script that will perform the task of assigning a new value to all indices and rebuild them. If you are not afraid to use dynamic T-SQL, you may find it useful for your task and environment, just set the values ​​accordingly. (I did not find license information on the original page to copy the script here)

 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 

Answer 4

It seems you want to use the default server fill factor (0), which omits the FILLFACTOR from the creation scripts. You cannot do this by simply restoring the index, you must reset and recreate it (see here ). There seems to be no clean way to do this, although this is a moot point right now.

Answer 5

something simpler for all tables in one database:

  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 

generates statements that you can then copy and execute.

Answer 6

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

performs this work. 0 is 100 (see http://msdn.microsoft.com/en-us/library/ms177459.aspx ), which means there are no spaces in the index.

you need to run this for each index. rebuilding can take considerable time.