Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

High read in profile

Posted on 2009-12-23
4
Medium Priority
?
508 Views
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?
0
Comment
Question by:jung1975
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 1000 total points
ID: 26116355
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 ....
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 26117474
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."

http://www.databasejournal.com/features/mssql/article.php/1473141/Monitoring-Performance-With-SQL-Server-Profiler.htm
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1000 total points
ID: 26117558
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
0
 
LVL 13

Assisted Solution

by:MikeWalsh
MikeWalsh earned 500 total points
ID: 26119434
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: http://scarydba.wordpress.com/tag/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: http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/

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

HTH!

Mike Walsh
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

580 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