I'm new to SQL Server and I was wondering if anyone can help me with this question.
In the following code:
create table t(i int,j char(3000))
create table t1(i int,j char(3000))
create unique clustered index ixt on t(i) with (FILLFACTOR=20)
declare @n int = 0
while @n < 1000
insert into t values(@n*2,'a')
insert into t1 values(@n*2,'a')
set @n = @n+1
create unique clustered index ixt1 on t1(i) with (FILLFACTOR=20)
1: select * from t where i between 100 and 150 (returns 16 logical reads)
2: select * from t1 where i between 100 and 150 (returns 30 logical reads)
Can anyone please tell me why statement 2 returns more logical reads than statement 1 ?
Thank you very much!
It's pretty easy. You told SQL Server to only allow pages to be filled by 20% as a part of the index rebuild on the two indexes.
For the index on the "t" table...
It was specified before the table had any data in it so each page could contain two 3011 byte rows. Fill Factor has NO "maintenance affect". That is, SQL Server will build the table (clustered index) according to the Fill Factor at the time the index was built. That has nothing to do with how full the table can get. In this case, there are 500 pages at the leaf level and each row has two pages. And, there's only 1 root page that the select has to look through.
For the index on the "t1" table...
That index was built after the data was added. Since each row is 3011 bytes in length, each row occupies about 37% of a page, which is larger than the 20% Fill Factor. Each page must contain at least 1 row and cannot contain 2 or more in this case because each row is larger than the Fill factor. Instead of the table being spread across just 500 pages, it is now spread across 1000 pages... doubling the number of reads to read the same data.
Run this and see...
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('t') ,NULL,NULL,'Detailed');
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('t1'),NULL,NULL,'Detailed');
Remember... Fill Factor is only observed during the creation of the index or a REBUILD/REORGANIZE. It is not maintained during inserts, updates, or deletes.
From Books Online:
[font="Arial Black"]Important: [/font]
The fill-factor setting applies only when the index is created, or rebuilt. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)