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.

Etesian

Answer 1

something simpler for all tables in a single database:

   select 'ALTER INDEX ALL ON '
   + quotename(s.name) + '.' + quotename(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.

Scrotum

Answer 2

SQL Azure apparently does not support FILLFACTOR:

"SQL Azure Database does not support specifying FILLFACTOR with the CREATE INDEX statement. If we create indexes in a SQL Azure database, we will find that the index fillfactor values are all 0."

You would have to remove all FILLFACTOR statements from the CREATE INDEX scripts. Likewise, SORT_IN_TEMPDB and DATA_COMPRESSION and several other options are also not supported.

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

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

Larghetto

Answer 3

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

Basalt

Answer 4

I found a very useful script here that would do the job of assigning a new value to all indexes and rebuilding them. As long as you are not afraid if using dynamic T-SQL you might find it useful for your task and environment, just set the values appropriately. (I didn't find the license information on the original page so I 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 

Dermal

Thanks, this is the same tsql that I'm using but the problema is that 0 is not valid in "set @fillfactor=0". – Etesian

0 is the default value meaning the indexes are filled to the full capacity, that equals 100 (Fillfactor units are percents). Did you try to set it to 100 and then export to .bacpac? – Dermal

I think you just leave FillFactor out of the statement (don't set it) and it will be created with default value of 0 – Felipafelipe

I still can't find a way to do this and I still receive the error regarding fill factor when deploying to Azure despite many claims the V12 is supposed to support fill factor.?? – Thaumaturgy

Answer 5

It seems you want to use the server default fill factor (0) which omits the FILLFACTOR statement from the creation scripts. There is no way to do this by just rebuilding the index, you must drop and re-create it (see here). There doesn't seem to be a clean way of doing this, though its kind of a moot point now.

Vitrine

Answer 6

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

does the job. 0 is equal to 100 (see http://msdn.microsoft.com/en-us/library/ms177459.aspx), meaning no gaps are left in the index.

you have to run this for every index. the rebuilding can take considerable time, though.

Danit

If I try with fillfactor=0 I get this message: Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100. I nedd to set it to 0 to be able to export my DB into a bacpac. – Etesian

Setting it to 0 does not work (any number between 1 and 100 does) – Correspond

just to understand your requirement: why do you want to set it to 0? – Danit

To migrate to SQL Azure (you can't have a Fill Factor value) – Correspond

why don't you use 100 then? It results in the same as 0, meaning no gaps in the index. – Danit

Because that still doesn't satisfy "exporting a data-tier application" for Azure. – Hickok