Re: Why I/Oread is a low Num on the exec. Plan but very high on tr
From: ktf (ktf_at_discussions.microsoft.com)
Date: 11/20/04
- Next message: Adam David Moss: "Re: Microsoft SQL Server Login"
- Previous message: Alexis Robles: "Re: Microsoft SQL Server Login"
- In reply to: Andrew J. Kelly: "Re: Why I/Oread is a low Num on the exec. Plan but very high on tr"
- Next in thread: Steve Kass: "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 12:07:09 -0800
Well they both show 3 million at the trace and the same IOs.
Thank you
"Andrew J. Kelly" wrote:
> Running a query or sp in Query Analyzer is not always the same as running it
> from your application. Maybe this can shed some light on your issue:
>
>
> http://tinyurl.com/l59s Parameter sniffing
>
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "ktf" <ktf@discussions.microsoft.com> wrote in message
> news:AEB4BA06-A40A-418E-B889-181BB85319A0@microsoft.com...
> > 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: Adam David Moss: "Re: Microsoft SQL Server Login"
- Previous message: Alexis Robles: "Re: Microsoft SQL Server Login"
- In reply to: Andrew J. Kelly: "Re: Why I/Oread is a low Num on the exec. Plan but very high on tr"
- Next in thread: Steve Kass: "Re: Why I/Oread is a low Num on the exec. Plan but very high on tr"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
