12

I could not find a satisfactory answer. I fully understand that "Shrink" will cause index defragmentation, and that can worsen performance.

But, is not this option to reduce defragmentation?

So, to activate this option, is it better or worse? Please do not increase my confusion. I ask you to give me a clear and simple answer: it is better, or, it is worse.

Then if you want, give me your explanation that I will gladly read and appreciate.

enter image description here

Diego Soto
  • 223
  • 1
  • 2
  • 6

2 Answers2

11

That is the equivalent of shrinking. To prove it, check the box and click the Script button at the top to see the shrink commands that it produces.

Generally speaking, that’s a great learning tool too - whenever you want to learn what something in the UI does, try that script button.

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
9

Contrary to the existing answer, this does make a change to the SQL other than allowing you to specify a size as can be seen in the two generated scripts below (at least on my version of Enterprise Manager - SQL Server 2017).

-- Checkbox Unchecked
DBCC SHRINKFILE (N'My_DB' , 0, TRUNCATEONLY)

-- Reorganise Checkbox Checked
DBCC SHRINKFILE (N'My_DB' , 48491)

For the unchecked option, TRUNCATEONLY is applied which prevents the SQL server from performing any data reorganising. This is great if you want to release some space quickly without having an immediate performance impact, however it can effect ongoing performance as the database will become fragmented.

TRUNCATEONLY Releases all free space at the file's end to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_size is ignored if specified with TRUNCATEONLY.

Ref: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017

TLDR; (rewritten based upon comments):

  • Reorganising can allow for additional space to be released rather than just releasing the free space allocated at the end of the database (only under certain circumstances is this beneficial).
  • Reorganising can affect performance until the procedure is complete (as is warned when using the SSMS interface to do so)
  • Reorganising will fragment your indexes, so rebuild them afterwards
  • Reorganising can experience a short term performance impact while the database physically moves the data pages around.
Community
  • 1
Radderz
  • 191
  • 1
  • 4