• sqldummy79 (11/25/2012)


    I was wondering if there is a way to "trap" or track the amount of data (in bytes) that a select statement pulls when querying a table or tables. I'm trying to show some co-workers that using "select *" instead of "select <column_name>" is better. The only way I feel I can accomplish this is to show them the difference in the amount of data that they are pulling.

    Thanks in advance

    SET STATISTICS IO ON is likely what you're looking for.

    I believe that your logic is flawed though. Your statement is true only if the idexes on the table are not used even on a small table such as the following...

    USE AdventureWorks;

    SET STATISTICS IO ON

    --===== All columns from an indexed table (9 reads)

    SELECT * FROM HumanResources.Employee;

    --===== All columns from an identical but non-indexed table (8 reads)

    SELECT * FROM #NoIndexes;

    --===== 2 columns from an indexed table (5 reads)

    SELECT EmployeeID, LoginID

    FROM HumanResources.Employee;

    --===== 2 columns from an identical but non-indexed table (8 reads)

    SELECT EmployeeID, LoginID

    FROM #NoIndexes;

    ---------------------------------------------------------------------------

    --===== All columns from an indexed table (2 reads)

    SELECT * FROM HumanResources.Employee WHERE EmployeeID = 137;

    --===== All columns from an identical but non-indexed table (8 reads)

    SELECT * FROM #NoIndexes WHERE EmployeeID = 137;

    --===== 2 columns from an indexed table (2 reads)

    SELECT EmployeeID, LoginID

    FROM HumanResources.Employee

    WHERE EmployeeID = 137;

    --===== 2 columns from an identical but non-indexed table (8 reads)

    SELECT EmployeeID, LoginID

    FROM #NoIndexes

    WHERE EmployeeID = 137;

    If you compare what looks like a savings in the number of reads (for the single row criteria examples) with the last arrow to the left of the "Actual Execution Plan", as Gail suggested, you will see that even the single row criteria returned less (estimated, in this case) data and less data is better for the "Pipe".

    Unless you're thinking of something else, the code above (along with the "Actual Execution Plan") proves that including only the columns you need is better for the "Pipe" whether you have an index or not and whether you have criteria or not.

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)