locked
Puzzle with Statistics IO and Indexing RRS feed

  • Question

  • Good afternoon,

    I've recently found a puzzling situation when I come to my database.  I've found a query which is used heavily, so decided to analyse it in SQL Tuning Analyser, and it recommended 2 indexes for me.  Great, I thought I'd give them a go and see what happened.  So, I traced the query both before and after applying the indexes and am now stumped by the results.  Hopefully somebody can shed some light on this for me.

    Before the index

    Table 'Worktable'. Scan count 158, logical reads 1483, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'xxxxxxxxx'. Scan count 4, logical reads 1217, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


    After the index

    Table 'xxxxxxxx'. Scan count 11213, logical reads 26161, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    While profiler tells me that after the index, the query uses 33% less CPU and about 33% less time to perform, I'm not sure if the larger scan count and larger logical reads is a good or bad thing.  What is happening differently after the index?  Ideally we want to do as much as possible to reduce access to TempDB and reduce CPU usage as much as possible.  Would adding the indexes (based purely on this information) be of benefit?

    I'm hoping I've included sufficient information to be able to get an answer.  I've not included the query purely because it is a third party application and so we have no control over that part of the system, so indexing is the only thing we can really look in to.

    Regards

    Wednesday, May 16, 2012 12:16 PM

Answers

All replies

  • My understanding is that the scan count is the number of physical pages that needed to be accessed to execute your query. Simplistically, if there are no (or insufficient) indexes to help then SQL needs to read a lot more pages than it would otherwise.

    Larger scan count is not he good indication and same goes with the logical reads.

    Have you updated statistics of the index after you have done the index???


    • Edited by Vimarsh Wednesday, May 16, 2012 12:40 PM
    Wednesday, May 16, 2012 12:29 PM
  • http://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/14/scan-count-meaning-in-set-statistics-io-output.aspx

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

    Wednesday, May 16, 2012 12:34 PM
    Answerer
  • The bottom line is that the scan count can be very misleading.

    It can be a large number when an index is being used efficiently, or a small number when a million pages have to be read from disk.

    Fun for all.

    Josh

    Wednesday, May 16, 2012 3:15 PM
  • My understanding is that the scan count is the number of physical pages that needed to be accessed to execute your query. Simplistically, if there are no (or insufficient) indexes to help then SQL needs to read a lot more pages than it would otherwise.

    Larger scan count is not he good indication and same goes with the logical reads.

    Have you updated statistics of the index after you have done the index???


    Neither of these has anything to do with physical I/O against the data files.  Logical reads occur entirely in memory, physical reads and read ahead reads occur against the data files as physical I/O.  High scans and logical reads are not on their own a sign of any problem.  STATISTICS IO is useless without the ShowPlanXML/STATISTICS_XML for the statement being executed.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, May 16, 2012 3:44 PM