locked
Logical read calculator RRS feed

  • Question

  • Hello,

    I am looking for logical read calculator.

    Everytime when i have to calculate Logical reads of the query and its quite a pain.

    Does anyone have a tool who can calculate the total logical reads of the query?

    Any help would be greatly appreciated.

    Thursday, November 28, 2013 6:00 AM

Answers

  • This is to inform you that i have found one link which is very helpful to count total logical reads.

    For the sake of everyone i am adding a link here. Thanks everyone for help.

    http://statisticsioparser.com/statisticsioparser/index.html

    • Marked as answer by Chickoo79 Thursday, November 28, 2013 9:05 AM
    Thursday, November 28, 2013 9:05 AM
  • It looks like you ran SET STATISTICS IO  command, so you cannot... Create a server trace profiler and capture logicalreads column into a table so later on you can aggregate SUM() on that column...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 28, 2013 9:05 AM
    Answerer
  • I don't know why you want to calculate the total amount of logical read for your query.

    I hope you already know that: logical read <> logical read

    What I mean with that, is that the cost associated with a logical read of a table or index scan is typically much higher than the cost of a logical read of an index seek.

    Because of that, 400 reads for a table scan could be much more expensive than 400 index seeks.

    If you need some kind of metric for performance, then you could consider the subtree cost of the root node of the actual query plan.


    Gert-Jan

    Thursday, November 28, 2013 11:09 PM

All replies

  • Use STATISTICS IO ON and look at the message pane in your results. It will show you the logical read of the query individual at table level. 

    SET STATISTICS IO ON
    Select * From tablename 
    SET STATISTICS IO OFF
    Thursday, November 28, 2013 6:35 AM
  • Using SQL Server Profiler  (server side trace) you can calculate it....

    SELECT TOP 120 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
            ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(qt.text)
             ELSE qs.statement_end_offset
             END - qs.statement_start_offset)/2)+1), 
    qs.execution_count, 
    qs.total_logical_reads, qs.last_logical_reads,
    qs.min_logical_reads, qs.max_logical_reads,
    qs.total_elapsed_time, qs.last_elapsed_time,
    qs.min_elapsed_time, qs.max_elapsed_time,
    qs.last_execution_time,
    qp.query_plan,
    qs.plan_handle
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qt.encrypted=0 
    ORDER BY qs.total_logical_reads DESC


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, November 28, 2013 7:24 AM
    Answerer
  • Thanks Uri. That will definitely help a lot but how can i calculate total logical reads of particular query that i have.

    Let's say

    I would like to calculate the total logical reads of following query execution plan, how can i calculate that?

    Table 'Con'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FType'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Fdg'. Scan count 282, logical reads 925, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Itn'. Scan count 1, logical reads 2770, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Offer'. Scan count 2, logical reads 1237, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Capital'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Mill'. Scan count 1, logical reads 122, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Millproject'. Scan count 1, logical reads 329, physical reads 0, read-ahead reads 54, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Thursday, November 28, 2013 8:19 AM
  • This is to inform you that i have found one link which is very helpful to count total logical reads.

    For the sake of everyone i am adding a link here. Thanks everyone for help.

    http://statisticsioparser.com/statisticsioparser/index.html

    • Marked as answer by Chickoo79 Thursday, November 28, 2013 9:05 AM
    Thursday, November 28, 2013 9:05 AM
  • It looks like you ran SET STATISTICS IO  command, so you cannot... Create a server trace profiler and capture logicalreads column into a table so later on you can aggregate SUM() on that column...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 28, 2013 9:05 AM
    Answerer
  • I don't know why you want to calculate the total amount of logical read for your query.

    I hope you already know that: logical read <> logical read

    What I mean with that, is that the cost associated with a logical read of a table or index scan is typically much higher than the cost of a logical read of an index seek.

    Because of that, 400 reads for a table scan could be much more expensive than 400 index seeks.

    If you need some kind of metric for performance, then you could consider the subtree cost of the root node of the actual query plan.


    Gert-Jan

    Thursday, November 28, 2013 11:09 PM