none
SP Reads

    Question

  • Looking at a simple SP, profiler says 17k reads. I look at the execution plan, SQL Sentry plan explorer, of course. 

    Operation: clustered Index Seek.

    Estimated Rows:7

    Actual Rows: 1

    Estimated Row size: 25 B

    Actual Data size: 25 B

    Estimated Data size: 179 B

    Size of one row: 773 Bytes (from sp_help Table)

    From Trace, the SP does 17,268 reads. Lets round it to 17,000

    1 page = 8kb = 8192 bytes

    17k pages = 17,000*8192 =  139,264,000 bytes. = 139 MB

    Curious why does it take 17k reads for the SP to do clustered index seek, there is no key lookup etc, the columns are part of the clustered key. The table has about 3 million rows. Does the reads total up due to the number of B-tree lookups the index has to do before getting to the exact row needed ?

    why is there so much difference between actual data size and Reads ?

    What i am missing ?


    Ranga

    Monday, May 19, 2014 6:29 PM

Answers

  • thank you Dan. Maybe i should change my question to: "How is logical Reads related to Actual Data Size" in a operator. To your question, No, i don't see any implicit conversion in the plan, using sql sentry plan explorer.

    Ranga

    Hi Ranga,

    i don't really understand your question. The IO measure is independet from the "data size".

    The IO is measured in pages and if the clustered index will be used it has to touch every single "complete" record!

    Taking in consideration the example from Dan you will have two different data sizes with the following statements:

    SELECT * FROM dbo.demo_table
    WHERE c1 = 1;
    
    SELECT c1, c2 FROM dbo.demo_table
    WHERE	c1 = 1;

    Both examples will use an INDEX SEEK but will use the clustered index (which is the table!). If you check the row size you will have >8,000 bytes for each row while the second one will be a minimum of it because you only access c1 and c2. Both queries will use the same index!


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Wednesday, May 21, 2014 5:46 AM

All replies

  • The Reads are for the whole plan, not for a single operator.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, May 19, 2014 6:50 PM
  • To add on to Dave's response, are all of the leftmost keys of the clustered index specified in the seek predicate?  Are there any type conversions that might result in a non-sargable expression?

    Below is an example that shows the symptoms you describe due to implicit conversion:

    CREATE TABLE dbo.Table1(
    	 Col1 int NOT NULL
    	,Col2 varchar(10) NOT NULL
    	,Col3 char(8000)
    	,CONSTRAINT PK_Table1 PRIMARY KEY(Col1, Col2)
    	);
    GO
    --load 17K rows
    WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    	,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
    INSERT INTO dbo.Table1 WITH(TABLOCKX)
    SELECT 1, num, ''
    FROM t16M
    WHERE num <= 17000;
    GO
    
    --show 17K reads for a single row
    SET STATISTICS IO ON;
    GO
    SELECT Col1, Col2
    FROM dbo.Table1
    WHERE
    	    Col1 = 1
    	AND Col2 = 1;
    GO
    SET STATISTICS IO OFF;
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Proposed as answer by Uwe RickenMVP Tuesday, May 20, 2014 6:11 AM
    • Unproposed as answer by Ranga1 Tuesday, May 20, 2014 2:32 PM
    • Edited by Dan GuzmanMVP, Moderator Wednesday, May 21, 2014 3:20 AM removed extraneous CREATE INDEX
    Tuesday, May 20, 2014 12:46 AM
    Moderator
  • The clustered keys are part of the data rows, so the entire table must be read to get the clustered keys.

    There is a b-tree for the N-1th levels of the index, but you still have to read the entire mass of the table.

    What did your select look like?

    Josh

    Tuesday, May 20, 2014 4:14 AM
  • CREATE TABLE dbo.Table1( Col1 int NOT NULL ,Col2 varchar(10) NOT NULL ,Col3 char(8000) ,CONSTRAINT PK_Table1 PRIMARY KEY(Col1, Col2) );

    CREATE CLUSTERED INDEX cdx_Table1 ON dbo.Table1 (Col1, Col2); GO

    Hi Dan,

    there is a mistake in your script. You try to create a clustered index twice :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Tuesday, May 20, 2014 6:02 AM
  • Hi Ranga,

    in addition to the great hint from Dan just the "implicit conversion" on the left side (which is the attribute) will cause the described behaviour.

    The predicate for col1 will cause an implicit conversion because 1 will be handled as tinyint. This will run the query with the predicate:

    col1 = CONVERT_IMPLICIT(int, 1)

    This part of the query will not cause a "non sargable" query but the second one which has to convert explicit the col2 to a new data type

    CONVERT_IMPLICIT(int, col2) = CONVERT_IMPLICIT(int, 1)

    Now the whole table need to be scanned because EVERY value of col2 has to be converted to check the predicate.

    Thank you Dan to that great example!


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Tuesday, May 20, 2014 6:11 AM
  • Thanks to everyone who has replied, I have posted the same question at sqlperformance.com where you can see the actual plan. Statement1 is the most expensive one and shows it does 17k reads in profiler.

    https://answers.sqlperformance.com/questions/1896/plan-io-vs-reads-in-profiler.html


    Ranga

    Tuesday, May 20, 2014 2:18 PM
  • thank you Dan. Maybe i should change my question to: "How is logical Reads related to Actual Data Size" in a operator. To your question, No, i don't see any implicit conversion in the plan, using sql sentry plan explorer.

    Ranga

    Tuesday, May 20, 2014 2:29 PM
  • Hi Dan,

    there is a mistake in your script. You try to create a clustered index twice :)

    Thanks for pointing that out.  I forgot to remove the CREATE INDEX after adding the primary key.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, May 21, 2014 3:20 AM
    Moderator
  • thank you Dan. Maybe i should change my question to: "How is logical Reads related to Actual Data Size" in a operator. To your question, No, i don't see any implicit conversion in the plan, using sql sentry plan explorer.

    Ranga

    Hi Ranga,

    i don't really understand your question. The IO measure is independet from the "data size".

    The IO is measured in pages and if the clustered index will be used it has to touch every single "complete" record!

    Taking in consideration the example from Dan you will have two different data sizes with the following statements:

    SELECT * FROM dbo.demo_table
    WHERE c1 = 1;
    
    SELECT c1, c2 FROM dbo.demo_table
    WHERE	c1 = 1;

    Both examples will use an INDEX SEEK but will use the clustered index (which is the table!). If you check the row size you will have >8,000 bytes for each row while the second one will be a minimum of it because you only access c1 and c2. Both queries will use the same index!


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Wednesday, May 21, 2014 5:46 AM