Re: Why I/Oread is a low Num on the exec. Plan but very high on tr
From: Steve Kass (skass_at_drew.edu)
Date: 11/20/04
- Next message: AnthonyThomas: "Re: HELP!!How will u use SQL server for unstructured information analy"
- Previous message: Andrew J. Kelly: "Re: ADO.net or TSQL Transactions"
- In reply to: ktf: "Re: Why I/Oread is a low Num on the exec. Plan but very high on tr"
- Next in thread: ktf: "Re: Why I/Oread is a low Num on the exec. Plan but very high on tr"
- Reply: ktf: "Re: Why I/Oread is a low Num on the exec. Plan but very high on tr"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 20 Nov 2004 16:28:40 -0500
ktf,
Did you compare the estimated plan rowcount on the index scans with
the actual rowcounts on those scans during the actual (slow) query
execution? If by "trace of the query analyzer" you mean the *estimated*
execution plan, it sounds quite possible you are seeing what I
suggested. Rebuilding indexes will not fix the problem.
It's very difficult to help out when you haven't provided any details of
the query, any tables, or any execution plans. You don't need to show
any of the column names or table names, but if you want more than wild
guesses, you should really provide more than vague descriptions of the
problem you are having.
SK
ktf wrote:
>Steve,
>Thank you very much for your very helpful explanation. I really rebuild all
>indexes in the order which I received through the patch and that was the last
>time. For the first time after six month I finally got the approval to
>rebuild indexes. I ran a dynamic db reindex to minimize human errors and
>establish a proper procedure document for the future DBAs for this complex
>project. So after that I was facing this high I/O reads. After two weeks I
>finally got the approval to recreate all index through running hard coded
>script, which I extracted from the database then I worked over night to put
>them in order. Before I was using this method (hard coded) and had done that
>many times. I was actually using the migration time to recreate indexes. This
>is a 24/7 mission critical system.
>So I even try to recreate them by orders.
>
>Andrew,
>Thank you for your answer. Tonight I created additional indexes just to
>match the exact where clauses but it did not do it.
>
>The bottom line is I see index scans all over the places but not seeks. No
>the trace of the query analyzer does not show a high number but the trace I
>put out there shows three million vs under 100. The same trace was showing
>very low number before I do the db reindex. I do not know if this is a
>security issue or the standard method caused this problem.
>
>Is there a place or function to see the mode of indexes or something like
>disable or enable index seeks? If there is how do I find how or who made that
>change. How should turn that back? Have you experienced this situation? I am
>really running out of time.
>Thank you
>
>"Steve Kass" wrote:
>
>
>
>>ktf,
>>
>> You mention an index scan in the execution plan. Is it
>>possible that the order of that index is such that the rows
>>you need to find are not going to be found anywhere near
>>the beginning? (Equivalently, does that scan show a small
>>number for estimated rowcount, yet a huge actual
>>rowcount if you look at the actual plan?)
>>
>> When I've seen dramatic differences between the
>>estimated execution plan estimated I/O and the actual
>>I/O, it has usually been due to correlated data that
>>undermine the query optimizer's cost estimation. It's
>>particularly bad when the extra I/O involves bookmark
>>lookups. (The solution is usually to add an index hint, change
>>indexing to avoid needing bookmark operations, or rewrite
>>a search predicate so that it is not a SARG.)
>>
>> It would really help to see more detail here, but I'll give a
>>quick example of what could be going on.
>>
>>--Scenario
>> The query is something like "find the oldest unpaid invoice".
>> There is a nonclustered index on (invoiceDate).
>> The Invoices table has 10,000,000 rows, 300,000 of them "unpaid".
>> All the unpaid invoices are among the most recent 1,000,000 invoices.
>>
>>This is a disaster. The optimizer knows that 3%
>>of the table has the value "unpaid", and assumes that
>>any way it searches, it will only have to look through about
>>33 rows to find "unpaid". So why not search in invoiceDate
>>order, so the first "unpaid" row will be the one you want?
>>That's much better than finding all 300,000 "unpaid" invoices
>>(assuming that column is also indexed), and then choosing the
>>earliest of those.
>>
>>Even if the invoiceDate index doesn't contain the paid/unpaid
>>column, you can go grab that value from the clustered index
>>30 or 35 times without any trouble.
>>
>>Unfortunately, instead of finding "unpaid" within the first
>>33 or so rows inspected, "unpaid" is not found until
>>more than 9,000,000 rows are inspected, each having
>>required a bookmark lookup in the clustered index.
>>The optimizer's estimation was performed correctly,
>>but the correlated data meant the estimate was very
>>far off.
>>
>>Steve Kass
>>Drew University
>>
>>
>>ktf wrote:
>>
>>
>>
>>>Mr. Kelly,
>>>I knew the definition for the DDL but for security reason I cannot provide
>>>the real col names. I needed to get general Idea for the root of my problems.
>>>Today I recreated indexes for the use of that sp on a different device with
>>>different names followed by update statistics and sp_compile sp name. It did
>>>not help me. Is there any tools I can use somewhere between the execution
>>>plan and the trace that can help me where the problem is. I understood when I
>>>recreate indexes the data within is not changing. How do I pinpoint the
>>>problem within the index?
>>>v/r
>>>ktf
>>>
>>>
>>>"Andrew J. Kelly" wrote:
>>>
>>>
>>>
>>>
>>>
>>>>Without seeing the DDL I can't say. DDL stands for Data Definition
>>>>Language. When someone asks for the DDL they want to see how the table is
>>>>actually defined. You can script the table and indexes with Enterprise
>>>>Manager and post that here so we can see what we are up against.
>>>>
>>>>--
>>>>Andrew J. Kelly SQL MVP
>>>>
>>>>
>>>>"ktf" <ktf@discussions.microsoft.com> wrote in message
>>>>news:04E87FB9-4F77-4A10-874B-2DB7284EDC2B@microsoft.com...
>>>>
>>>>
>>>>
>>>>
>>>>>I did not get about: Can you post the DDL for the table and the indexes and
>>>>>the query that is causing all these reads?
>>>>>I see Index Scan on the execution plan for that case what I should do?
>>>>>Thank you,
>>>>>ktf
>>>>>
>>>>>"Andrew J. Kelly" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>First off there is no need to do an sp_recompile if you just updated the
>>>>>>stats. Anytime the stats get updated it will force any plan that uses
>>>>>>those
>>>>>>stats to be recompiled the next time they are run. If you used DBCC
>>>>>>DBREINDEX to reindex the tables then it also updates the stats and the
>>>>>>manual updating of them is redundant. If the number of reads went from
>>>>>>under
>>>>>>100 to over 3 million you are most likely doing a table or index scan.
>>>>>>Can
>>>>>>you post the DDL for the table and the indexes and the query that is
>>>>>>causing
>>>>>>all these reads? Also how many rows are there that match that value and
>>>>>>how
>>>>>>many total?
>>>>>>
>>>>>>--
>>>>>>Andrew J. Kelly SQL MVP
>>>>>>
>>>>>>
>>>>>>"ktf" <ktf@discussions.microsoft.com> wrote in message
>>>>>>news:F223DE8B-90DD-433F-8537-574FFA530290@microsoft.com...
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Mr. Kelly and Mr. Hodgson,
>>>>>>>I did bounce between my active passive nodes before I go live on the
>>>>>>>production. For a particular stored procedure I had very few number of
>>>>>>>reads
>>>>>>>but after reindexing/ update statistics/ shrinking by file the number
>>>>>>>of
>>>>>>>reads for the same thing went up very high.
>>>>>>>Today I recreated indexes for the use of that only stored procedure,
>>>>>>>ran
>>>>>>>update statistics on the those tables involved then did the
>>>>>>>sp_recomplile
>>>>>>>stored procedure name. I ran the trace but the number is still way up 3
>>>>>>>Million vs under 100.
>>>>>>>I even used the Index Tuning Wizard, did the update statistics the
>>>>>>>sp_recompile, and still did not help me.
>>>>>>>
>>>>>>>The question I have is if I add a new index file device and create
>>>>>>>those
>>>>>>>indexes with drop existing option then drop the previous (old) index
>>>>>>>file,
>>>>>>>will it help me.
>>>>>>>
>>>>>>>All I want to do is to force the sql server to use indexes. How do I do
>>>>>>>that
>>>>>>>other than what I had done. I need your help asap.
>>>>>>>Thank you,
>>>>>>>ktf
>>>>>>>
>>>>>>>"Andrew J. Kelly" wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>If you reindexed and then did a shrink you just destroyed all the good
>>>>>>>>work
>>>>>>>>you did in the reindex. A shrink operation will move pages from the
>>>>>>>>rear
>>>>>>>>of
>>>>>>>>the file to the front and will most definately fragment your tables
>>>>>>>>and
>>>>>>>>indexes all over again. A shrink is usually a bad idea. The db needs
>>>>>>>>a
>>>>>>>>lot
>>>>>>>>of free space to work properly especially when reindexing. You need
>>>>>>>>1.2
>>>>>>>>to
>>>>>>>>2.0 times the size of the table and indexes for reindexing and you get
>>>>>>>>best
>>>>>>>>results when you have LOT's of free space in the db at all times. The
>>>>>>>>number of reads sounds like a bad plan combined with fragmentation
>>>>>>>>caused
>>>>>>>>by
>>>>>>>>the shrink. Grow the db and reindex all the tables / indexes and see
>>>>>>>>if
>>>>>>>>that changes.
>>>>>>>>
>>>>>>>>--
>>>>>>>>Andrew J. Kelly SQL MVP
>>>>>>>>
>>>>>>>>
>>>>>>>>"ktf" <ktf@discussions.microsoft.com> wrote in message
>>>>>>>>news:E5CC7B6D-F050-4CE0-8F26-45BDBB666ED3@microsoft.com...
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>I recreated indexes and since then I have a very high number of I/O
>>>>>>>>>reads.
>>>>>>>>>It shows a high number on a trace but a very low number on the
>>>>>>>>>execution
>>>>>>>>>plan. I recreated indexes twice first dynamically then two weeks
>>>>>>>>>later
>>>>>>>>>through hard coded index script with drop existing option. Update
>>>>>>>>>statistics
>>>>>>>>>(dynamically) followed by shrinking by file was performed after.
>>>>>>>>>Because
>>>>>>>>>of
>>>>>>>>>high numbers of I/O reads my replication cannot catch up. The system
>>>>>>>>>is
>>>>>>>>>cluster SQL 2K on Win2K advanced server.
>>>>>>>>>I need to know why they a big difference and what could be the
>>>>>>>>>solution
>>>>>>>>>to
>>>>>>>>>have the performance back? Seams like the new indexes do not do
>>>>>>>>>their
>>>>>>>>>jobs.
>>>>>>>>>The I/O read was very low before these indexes recreations.
>>>>>>>>>Please help.
>>>>>>>>>Thank you.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>>>
>>>>
- Next message: AnthonyThomas: "Re: HELP!!How will u use SQL server for unstructured information analy"
- Previous message: Andrew J. Kelly: "Re: ADO.net or TSQL Transactions"
- In reply to: ktf: "Re: Why I/Oread is a low Num on the exec. Plan but very high on tr"
- Next in thread: ktf: "Re: Why I/Oread is a low Num on the exec. Plan but very high on tr"
- Reply: ktf: "Re: Why I/Oread is a low Num on the exec. Plan but very high on tr"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
