mistaken idea #25: Several mistaken ideas about filling factor
It's all wrong.
25a) filling factor is always present.
No, it's through. Books OnlineCan see (translator: I did not find this sentence in the new version of BOL):
Important:
The fill factor only takes effect when an index is created or rebuilt, and the SQL Server storage engine does not always ensure that the free values and fill factors in the page are consistent. The padding factor is meaningless if you want to keep the free values in the page and the fill factor that you specify. This is because the page needs to be paged even if it is dissatisfied.
fill factor 0 and 100 are different
Error, by Bol a sentence can be seen
Filling factors 0 and 100 are all in one sense.
c) Fill factor set to 0 will reserve space on non-leaf nodes
This is wrong, this point Bol did not say, I do not know where this misunderstanding came from, but this is absolutely wrong. You can verify this by using the following code:
Copy Code code as follows:
CREATE DATABASE foo;
Go
Use Foo;
Go
CREATE TABLE T1 (C1 INT IDENTITY, C2 CHAR (1000) DEFAULT ' a ');
CREATE CLUSTERED INDEX t1c1 on T1 (C1);
Go
SET NOCOUNT on;
Go
INSERT into T1 DEFAULT VALUES;
Go 10000
Next, set the fill factor to 0 and rebuild the index
Copy Code code as follows:
SELECT [Fill_factor] from sys.indexes
WHERE NAME = ' t1c1 ' and [object_id] = object_id (' t1 ');
Go
ALTER INDEX t1c1 on T1 REBUILD with (FILLFACTOR = 100);
Go
After the code above executes, view the value of the M_FREECNT column in the established page, which is the value of the free space in the page:
Copy Code code as follows:
EXEC sp_allocationmetadata ' T1 ';
Go
DBCC Traceon (3604);
DBCC PAGE (foo, 1, 164, 3); --The root page, from the SP output
Go
DBCC PAGE (foo, 1, 162, 1); --the page ID in the DBCC page output above
Go
With the above code you can see that the value is 10, which means that there is no space reserved in the industry. This is a misunderstanding, the implementation details of the above Sp_allocationmetadata please read this blog post: This blog post。
