Sql Server change fill factor value for all indexes by tsql

Question

I have to expoet my DB into a bacpac file to import it into Azure. When I try to export I get an error because any indexes have a fillFactor value.

I've found how to set a fillFactor value for all indexes but I can't specify 0, the value have to be between 1 an 100. If I change the value in the management studio I can set it to 0.

The problem is that I have got lots of indexes to change and I would like to change the fillFactor value to all of them trough tsql.

Any ideas?.

Thanks.

Answer 1

something simpler for all tables in a single 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 you can then copy & execute.

Answer 2

This isn't a straight T-SQL way of doing it. Though it does generate a pure T-SQL solution that you can apply to your DB.

Your results may vary depending on your DB... For example poor referential integrity might make this a bit trickier..

Also this comes with a DO AT YOUR OWN RISK disclaimer :-)

1. Get the DB you want to migrate into an 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 nice way to migrate any schema to Azure regardless... It's way better then just creating a bacpac file.. fixing... exporting...fixing.. etc... So I would recommend doing this anytime you want to migrate a DB to Azure

For the FILLFACTOR fixes I just used a find and replace to remove all the FILLFACTORS from the generated schema files... Luckily the DB I was using had them all set to 90 so it was fairly easy to do a solution wide find and replace (CTRL-SHIFT-F)... If yours vary then you can probably use the RegEx find features of Visual Studio to find all the fillfactors and just remove them from the indexes.

I'm not that great at RegEx but I think this works

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

At this point you'll have to fix any additional exceptions around Azure compliance.. The links provided describe how to go about doing this

2. Now that you're at the point where you have an SSDT project that's AZURE SQL compliant.

Here comes the DO AT YOUR OWN RISK PART

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

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 factor option is to drop and re-create the index. This comes with a cascading set of issues :-/ PK's with fill factors need the FK's dropped etc.... There's probably a smarter way to do this so you don't remove ALL FK's and PK's and you look at the dependency trees...

3. Now go back to your Azure Compliant SSDT project and do a SCHEMA COMPARISON of that project against your DB... This will create a script that recreates all your FK's, PK's, and Unique Constraints (without the Fill Factor).... At this point you can just click "update" or you can click the button just to the right of update which will generate the script you can use... So now armed with

   • the script above to remove FKs, Pks, and Unique.
   • The script created by SSDT
   • Ample testing and review of said scripts to ensure nothing was missed

You should be able to update your current DB to an Azure compliant SCHEMA

Additional Thoughts:

In my case the fill factors on the Production DB weren't really doing anything useful. They were just created as a default thing to do. In your case the fill factors might be important so don't just remove them all on your non Azure Production box without knowing the consequences.

There's additional things to consider when doing this to a production system... For example this might cause some mirroring delays and it might cause your log files to grow in a way you aren't anticipating. Which both only really matter if you're applying directly to production...

It'd be nice if setting them all to FILL FACTOR 100 worked :-/

There's 3rd party tools out there (so I've heard) that you can use to migrate to Azure...

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

Use that to create a SCHEMA that's Azure compliant and then it uses BCP to copy all the data.

BUT if you want to make your current SCHEMA Azure compliant so you can create a bacpac file to upload into Azure this worked for me the one time I've had to do it.

EDIT: Azure V12 supports fill factors