Post reply

Summing Logical Reads Each Day for a Server

  • October 2, 2014 at 11:22 am

    #297060

    We would like to benchmark our logical reads daily to show our improvement as we tune the queries over time.

    I am using sys.dm_exec_query_stats summing the Physical and Logical Reads. Is this a viable option for gathering this metric? Is this a viable metric to gather?

    select sum(total_physical_reads) as TotalPhyReads, sum(total_logical_reads) as TotalLogReads from sys.dm_exec_query_stats;

    I would appreciate your advice and guidance on how best to provide performance based metrics.

    Thanks,

    John

  • October 3, 2014 at 12:24 am

    This was removed by the editor as SPAM

  • October 3, 2014 at 2:56 am

    #1749898

    john.petrey (10/2/2014)

    I am using sys.dm_exec_query_stats summing the Physical and Logical Reads. Is this a viable option for gathering this metric?

    No. That DMV shows the total logical reads for a query still in cache. It's not the data for today, right now, last week or anything so specific. Summing it shows the total reads done by all queries still in cache, whether they were cached 1 minute ago or 2 weeks ago.

    Is this a viable metric to gather?

    Not really. Without knowing what queries are running, how often they're running, a total's not much help. Say it dropped by 20% one day. Does that mean that the queries running were more optimal? That fewer queries were running? That the workload pattern was different? That the queries the users were running were returning on average less data than the previous day (one day they were looking at data for the year, the next day for the month), etc

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 3, 2014 at 9:08 am

    #1750004

    Agreed. I think sys.dm_db_index_operational_stats would be more useful to capture overall I/O, although it still doesn't link it to a specific query, unless you can do that yourself just based on the time frame to which it applies.

    That data is running totals. Capture at whatever time intervals you need, then take deltas of the values to get the activity for a specific period of time.

    If you want I/O as related to specific queries, you'll probably need to capture actual execution plans as well.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 3, 2014 at 9:26 am

    #1750017

    Thank you for all of the input!!

Viewing 5 posts - 1 through 4 (of 4 total)

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