0

I changed the Fill Factor option from 1 to 75.

When I run a query that displays the fill Factor I am seeing 75

But I read online that I have to stop and start the sql Server services inorder for this change to be effective. Is that True?

http://sqlmag.com/database-administration/fill-factor-truth

You must stop and restart the SQL Server service for the change to take effect. The new fill factor will be in effect when you see it in the run-value column

  • Are you sure you queried the run value and not config value? What query did you use to check. – LowlyDBA Mar 27 '15 at 18:23
  • In addition to restarting, please also note that Fill Factor will not go back and change your old data to the new fill factor, so your indexes will remain how they are currently. Only new data coming in get's that fill factor. If you reindex or somewhere it fills up more/less it won't go back and change it back to that value. – Ali Razeghi Mar 27 '15 at 19:48
3

But I read online that I have to stop and start the sql Server services inorder for this change to be effective. Is that True?

Yes, you have to stop and restart sql server services.

fill factor (%)

This has is_dynamic = 0 in the master.sys.configurations. This means that when you run sp_configure 'fill factor (%)', 75, the VALUE will be set to 75, but it wont take affect until you restart sql server.

You should check value in use for master.sys.configurations or run_value of the sp_configure output.

enter image description here

3

Yes, it is clearly stated here which options require a restart: https://msdn.microsoft.com/en-us/library/ms189631.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.