High read in profile

Posted on 2009-12-23
Last Modified: 2012-05-08
I found records(query and  SP)  with very high READ in Profiler.
What is HIGH READ mean in database? And how can I tune it in SQL 2005?
Question by:jung1975
    LVL 25

    Accepted Solution

    Read represents the pages read to get the result of the query. The lower the better.

    But when you need aal recordsof a table, all pages must be read.... (unless they are in cache). When not all records must be read there is the 'tuning' part.  See that the query does not do a full tables scan if an (new) index can prevent this, that the query is properly build to take advantage of the existing indexes, that as soon as possible only the realy needed records are kept ....
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    Kindly tune your queries with appropriate indexes and keep your Indexes up to date by doing periodical UPDATE STATISTICS or DB REINDEX tasks..

    "High READS might indicate table scans or not optimal indexes chosen by query optimizer. Usually, you want to keep READS parameter as low as possible because operations related to accessing hard drives (reading and writing) are the slowest ones in the system."
    LVL 25

    Assisted Solution

    Link above is an example on how things can go wrong
    - create a table (to store resulst of a profiler trace)
    - large numbers of records are inserted there
    - query afterwards will need to do a full table scan to return any result
    and is fixed
    - create index on entries you will be using
    - 'where reads >10000'  will pas by the index and so only need to get the pages where actualy are records that meet that filter

    But painfull example reason  1:
    But be aware, the article will bring only the reads down off the query ...... that you will use on your newly created table to find actions ..... that have high reads.  And that is a painfull mixup that probably many people not  can separate.

    But painfull example reason 2
    Additional comment on that index. Creating and maintaining indexes also takes time and place in the database
    ... so it only will fasten the index is many times used (this table looks to me
    ... so it will slow down the next profiler traces when you will reuse that trace-table later because maintaining index will take time
    LVL 13

    Assisted Solution

    Just to add a few comments to the above. The reads column in Profiler doesn't differentiate between logical (a read from memory) and physical (a read that had to go to the disks). That being said, high reads is a good thing to look for when tuning a query. I find that reads has a high correlation to duration and if I tune to reads, I am normally tuning the queries.

    So there are a few things to do. You can take all of the output from profiler as one person above suggests and find the queries but I don't know if you need to do that. There are some great Dynamic Management Views in 2005 for you to look into querying that show you info about your queries. Look in books online about SYS.DM_EXEC_QUERY_STATS and SYS.DM_EXEC_QUERY_PLAN from those articles you'll start to understand the DMVs and learn how to query them to find some of the "top offending" queries.

    Back to your query, though. Yes indexes, statistics, etc. that have all be said are a great thing to look at but for some guidance on where to look run your query in SQL Server Management studio with the query plan shown.

    Include the actual execution plan when you run your query. Also run this before you execute your query SET STATISTICS IO ON. This will output to the messages tab in your results pane the number of reads per table. Which table is the highest? That is where most of your reads are happening.

    Now you ran it by including the actual execution plan. Go ahead and click into the plan and look for the operators. It will potentially look confusing at first. A couple good blog posts to help out with this are below but a few things to keep in mind when reading may help you:

    1.) The cost percentages are a good guide. Not always accurate and the highest cost is not always your problem area but it tends to work out more often that way.

    2.) Read your query plan from right to left. The left most operator is your select or update and it passes instructions to the right. The data moves from right to left. At the right and top most area you'll see the first piece of data access (likely an index seek or scan). It passes data to the next operators.

    3.) Look for thicker arrows between operators. This means more data passed through. Look at a the actual counts and the estimated counts. Are they the same? Close to the same? If not then your statistics may be out of date. Look up statistics in books online to understand what they are and how to resolve.

    4.) If you click on the left most operator (the select or update/etc) with the properties window open you'll see what the optimizer believes are missing indexes. Study that. Don't just implement it or implement it as it is with all of the includes it may suggest. But study it and look at the plan and understand why the index is being suggested.

    5.) Look at the table scans on your larger tables, or the table with the larger reads. Table scans are not always bad but they can definitely be a cause of high reads and can bring a query down. So look at the missing indexes, think about the indexes that should be used and the distribution of data. Also remember that not all queries can use an index. Think of searching a phonebook or an index in the back of a book when looking at your query and ask "If I were given this question, could I seek right to the necessary page in the book?" Last_Name = 'Walsh'.. Yeah an index can help that. Last_Name like '%als%' not really. If you think of the back of the book or phonebook analogy that should make sense. In order for you to give me all the last names that contain "als" you'd have to look at every name and interrogate it. Same goes for more complicated examples such as a function against your column, etc.

    Some links to help get you started.. Look at these in addition to books online, it really is a great resource and it helps explain the how and why behind something:

    Grant Fritchey just wrote a great book on execution plans. Here are all of his blog postings he tagged with Execution Plans:

    Gail Shaw's entire blog is an excellent reads when it comes to query plans and situations like this. This post talks about estimated/actual counts:

    Browse around their blogs and those posts and spend some time in the execution plan area of books online.


    Mike Walsh

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now