StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Change index fillfactor

Change index fillfactor

ChanKK — Thu, 09 Sep 2010 12:01:25 GMT

Hi
I have PK defined to all tables via DDT and deployed to customer already. I found that I missed out index fillfactor. I tried to change the fillfactor and rebuild package to deploy to customer again. However, I hit error PK constaint must be drop first before able to change fillfactor.

What should I do for this? Should I remove the PK, and create it again via DDT as workaround?

Thank you

RE: Change index fillfactor

Ivan George Borges — Thu, 09 Sep 2010 12:01:25 GMT

Yeah, Jason... I was able to reproduce it over here. Will forward it to the guys. ;)

RE: Change index fillfactor

Jason Seidell — Thu, 09 Sep 2010 10:55:47 GMT

Sorry I haven't been following up, I am running SF 1.7.0.6.

Steps to reproduce (after you have opened DDT and selected a project):

Open Table -> Indexes -> Edit Index (for primary key) and set the fill factor to a specific value, in my case 95, save and close
Modify the table structure, change column name, data type, anything, save and close
Reopen Table -> Indexes -> Edit Index and fill factor is now set back to zero. Only affects the index for the primary key.

Regards,

Jason

RE: Change index fillfactor

Trent L. Taylor — Tue, 24 Aug 2010 16:08:45 GMT

Jason,

I am trying to reproduce this and can't. What are the steps to reproduce? What version of StrataFrame do you have installed?

RE: Change index fillfactor

Greg McGuffey — Tue, 24 Aug 2010 16:06:33 GMT

Jason, the developers are looking into this. I'll get back to you (or they will) once we've had a look at it.

RE: Change index fillfactor

Jason Seidell — Tue, 24 Aug 2010 10:47:21 GMT

Still having to deal with this.

I am frustrated because the DDT resets the index values back to zero everytime you modify the table structure. I don't know why and it is a real pain to either reset all the indexes back to whatever I want them to be after every time I open a table structure and then hit save. So usually I just ignore them and skip past the errors on the deployment log, which is also a pain since you can't copy and paste or otherwise save the DDT deployment log.

Requested solutions:

Do not modify the fillfactor percentage when doing table structure saves (In the DDT editor).
Set a default fillfactor index as a database property and have it automatically apply to all indexes (In the DDT editor).
Allow copy/paste of deployment log, or not make the form modal so I can view it and still make changes to the DDT, without having to find the text deployment log and then find where I was at.
Just not modify the fillfactor on deployment for exisiting indexes. I don't know if this is possible or if fillfactor is a required paramater, unless you are creating a new index just leave it alone for existing indexes.

RE: Change index fillfactor

Jason Seidell — Tue, 27 Jul 2010 14:45:24 GMT

I ran into this on some of our databases because the DDT was setup (by default) to set the FillFactor to 0, which tells MSSQL to use whatever the default fillfactor value is. The problem can occur when a DBA has a script that runs that drops and rebuilds or re-organizes indexes and sets a new fillfactor value different than the default. I had to do this on some of our larger indexes becuase the index files were getting extraneously large and filled with large amounts of blank space. Then the DDT wouldn't work until you changed the FillFactor in the DDT to the exact same percentage as the index already in the database. I was able to fix by just changing to make the numbers match, but if you aren't in control or even know what the end server is set to the above mentioned solution would probably work better. But you may bear in mind that another DBA maybe upset to have their indexes reset/rebuilt to a different value for optimization reasons.

Jason

RE: Change index fillfactor

Edhy Rijo — Wed, 21 Jul 2010 23:19:18 GMT

Hi ChanKK,
I believe you will need to create a Pre-Post Deploy Script to take care of that situation. Keep in mind that you can use the Priority field to control which script or functionality to run first. You can create the scripts to drop the PK constraint in SQL Server Management Studio (SMS) then copy them to the DDT Pre-Deploy script.

Here is a sample of what I have in one of my DDT Post-Deploy Script:
[codesnippet]
/****** Object: Index [DuplicateImportCheck_idx] Script Date: 04/07/2010 09:50:41 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TransactionItemsImport]') AND name = N'DuplicateImportCheck_idx')
DROP INDEX [DuplicateImportCheck_idx] ON [dbo].[TransactionItemsImport] WITH ( ONLINE = OFF )
[/codesnippet]

[quote]PS
When copying the script from the SMS do not use the command "GO" or "USE database" since it will give you an error in the DDT when trying to deploy your package.[/quote]