Logical Reads

  • sudhakara

    SSCrazy Eights

    Points: 9425

    when i execute my main sp , inner sp showing read count as 6000 ,but when execute that perticular sp its not showing that much reads .

    Explain why its showing like .......

  • GSquared

    SSC Guru

    Points: 260824

    Often, SQL Server will reduce the amount of work an inner query does if the outer query can limit the rows for the inner query. I'd have to at least see the query (and probably the table structure) to say that's what's going on for sure, but that is a very common thing.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • marques.miguel

    SSC Enthusiast

    Points: 186

    Hi everyone,

    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

    begin

    insert into t values(@n*2,'a')

    insert into t1 values(@n*2,'a')

    set @n = @n+1

    end

    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!

  • Jeff Moden

    SSC Guru

    Points: 993661

    marques.miguel (7/16/2016)


    Hi everyone,

    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

    begin

    insert into t values(@n*2,'a')

    insert into t1 values(@n*2,'a')

    set @n = @n+1

    end

    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.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    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. 😉

    Helpful Links:
    How to post code problems

  • marques.miguel

    SSC Enthusiast

    Points: 186

    Hello Jeff Moden,

    Thanks a lot for your reply and help !!!

  • Jeff Moden

    SSC Guru

    Points: 993661

    marques.miguel (7/17/2016)


    Hello Jeff Moden,

    Thanks a lot for your reply and help !!!

    No problem and thank you for the well laid out question with test code. It makes things easy.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    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. 😉

    Helpful Links:
    How to post code problems

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply