Re: What's the deal with PAGEIOLATCH_SH? - PAGEIOLATCHIssue20040426.zip (0/1)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 10:23:39 +1000

Hi James.

ok - the PK issue is likely at the heart of your performance problem. I
suggest that you materialise that table, then use the ITW to collect index
recommendations, test them & see what performance you can achieve. Without a
PK, internal iterative steps in the query plan probably require individual
scans rather than seeks which can be very expensive.

The fact that the table is "generated by joining other tables" is probably
contributing to the problem as well. What does this mean? Are you referring
to a view? If the joined tables are a view, you might consider even
materialising the view, which could help out as well.

It's vital that you understand the implications of NOT having a clustered
index on a table where other indexes (non-clustered) exist. It's not just
about having ranges - the physical implementation of NC indexes is actually
different where they exist on a table that does / doesn't have a C index.
This can have a big impact on performance alone, especially if the udpates
are causing page splits that cause physical NC leaf pointers to be updated
where there's no C index.. You should read up on that issue a bit..

Regards,
Greg Linwood
SQL Server MVP

"James Bradley" <jbradley@isa-og.com> wrote in message
news:fu8r80p436u13enj91lvgtehtsvs09m2fl@4ax.com...
> Hi Greg
>
> That table has no primary key and no clustered index. This table is
> currently being generated by joining other tables, but it will soon be
> detached. I do have one field that is a good candidate for a PK -
> shown as Idx2. I know it is a good practice to have a PK, but could
> that affect the performance?
>
> This table used to have very poorly chosen clustered index, which I
> have since removed. If I were to cluster it, it would probably be by
> Idx2 as well, but I don't see any real benefit for that. I don't have
> any ranges on that ID anyway.
>
> Thanks!
>
> Jami
>
> On Tue, 27 Apr 2004 08:54:15 +1000, "Greg Linwood"
> <g_linwoodQhotmail.com> wrote:
>
> >Hi James.
> >
> >I can't see any Primary Keys on those tables - do they not have PKs or
have
> >you just missed this in the script? This is very important. It's also
> >important to know if any PK / indexes are clustered indexes or
> >non-clustered.
> >
> >Your isolation of 500k rows and execution of the update taking 1 second
is
> >also important - it suggests to me that there's something seriously wrong
> >with the original query plan. SQL Server is certainly capable of handling
> >large updates like this, but I'd suggest something fundamental (such as
PK
> >being missing) is messing up the main query.
> >
> >Use of a clustered index might also help. If the table has a clustered
index
> >on the correct column, the query optimizer may be able to isolate the
1.5m
> >rows updated by this query using a range scan and treat them sort of as
if
> >they're in their own table. Getting the choice of the clustered PK can be
> >made manually or with the Index Tuning Wizard, which is a smart tool that
> >understands the SQL Query Optimizer's internal workings.
> >
> >To use the ITW, you put the update statement in the Query Analyser,
> >highlight it, then select the Query / Index Tuning Wizard and follow the
> >prompts. Try it with / without the "Keep all existing indexes" option
> >ticked. This tool is very powerful for making index suggestions, but also
be
> >aware that when you use it like this, it's only tuning for the individual
> >query you highlighted in QA & might make suggestions that are good for
that
> >query but not for the overall work done by the db. It can also be used
with
> >workloads captured by SQL Profiler for better overall index suggestions,
but
> >you'll probably get joy for now from just tuning for this query.
> >
> >There is a substantial difference between having a clustered index on a
> >table or not - it affects how ALL indexes on the table behave during
major
> >update statements like yours, so I suggest you spend some time reading up
on
> >clustered indexes & their relationship to non-clustered indexes as this
will
> >help you to understand what's going on under the hood. There's lots of
> >information in SQL Books Online & in SQL Server books on this important
> >topic.
> >
> >Not having Primary Key is such a major mistake that I'm assuming you've
just
> >missed this from your script - could you clarify that please?
> >
> >It would also be handy if you posted the actual query execution plan
rather
> >than the general statistics. Those stats aren't much use. The Query Plan
> >gives us absolute information about precisely HOW the query is physically
> >running & will likely lead to solving your problem. Do post this please -
> >either in graphical or text format won't matter.
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"James Bradley" <jbradley@isa-og.com> wrote in message
> >news:gkaq80lj2914utqu0s9ts7nf220jau4nvf@4ax.com...
> >> Hi Greg
> >>
> >> It took me a while, but I put together the create table/index calls
> >> for these tables. I had to rename the fields because of
> >> confidentiality issues.
> >>
> >> I've also attached the output from
> >> dbcc show_statistics for the relevant indices.
> >>
> >> I ran the full update statement, it took 2 hours, 24 minutes. I've
> >> attached the text output from the statistics for that run.
> >>
> >> I tried all 3 queries (the current one and the two you suggested
> >> below), and the query execution plans are identical!
> >>
> >> I don't need to do this atomically - I have exclusive use of the
> >> database when I need to run this. I could easily break it into
> >> several batches.
> >>
> >> One thing that is still bothering me -- let's say SQL is choosing a
> >> bad execution plan (hash join, etc.). Wouldn't I see the server busy
> >> doing this, even if it is inefficient? Right now, the server is not
> >> limited by either CPU or Disk I/O, and I would assume one of those
> >> must be limiting, unless locks/latches are blocking it.
> >>
> >> Thanks for all the help!
> >>
> >> On Mon, 26 Apr 2004 11:07:06 +1000, "Greg Linwood"
> >> <g_linwoodQhotmail.com> wrote:
> >>
> >> >Hi James
> >> >
> >> >Can you please send the full DDL for these two tables, including
create
> >> >table, indexes, keys, relations & DBCC SHOW_STATISTICS for these
tables /
> >> >indexes? I'm sceptible about it's choice of a hash join to perform
this
> >> >work.
> >> >
> >> >Another question for you - does this work need to be performed
atomically
> >as
> >> >a single unit of work or is it possible to break it into two or three
> >> >batches? This may increase performance tremendously.
> >> >
> >> >I'd suggest that the query written differently might yield a better
plan,
> >> >eg:
> >> >
> >> >UPDATE Table1
> >> >SET hasChildren=1
> >> >WHERE ID in (SELECT parentID FROM Table2)
> >> >
> >> >or
> >> >
> >> >UPDATE Table1
> >> >SET hasChildren=1
> >> >WHERE exists (SELECT * FROM Table2 where parentid = Table1.id)
> >> >
> >> >I'd suggest that batching updates might help even further if either of
> >those
> >> >re-written queries did yield better plans.
> >> >
> >> >Regards,
> >> >Greg Linwood
> >> >SQL Server MVP
> >>
> >
>

Relevant Pages

  • Re: SELECT Optimalization
    ... This query would always require an index scan (at least up to and ... clause is a good solution. ... and execution plan is as simple as posible (100% clustered index scan) ... Are you sure the bottleneck is in SQL Server and not on the client? ...
    (comp.databases.ms-sqlserver)
  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... Without having primary key in the table, will it slow down the query that I ... the clustered index on PACKET_TIME, and instead have a clustered index on ...
    (microsoft.public.sqlserver.programming)
  • Re: Extremely Slow Table
    ... In this case, the table is the road, and the query plan is the car. ... The optimizer makes it choice of plan from the statistics SQL Server ...
    (comp.databases.ms-sqlserver)
  • Re: IS NULL on field is not using index placed on that field
    ... > criteria in your WHERE clause the query engine needs to look through 2 ... > NULL" you get a nice quick plan using your nonclustered index too. ... > efficiently by just scanning the whole clustered index until it's got ... > even took out the null values and made the DeliverDate column NOT NULL ...
    (microsoft.public.sqlserver.programming)
  • Re: Whats the deal with PAGEIOLATCH_SH? - PAGEIOLATCHIssue20040426.zip (0/1)
    ... That table has no primary key and no clustered index. ... This table used to have very poorly chosen clustered index, ... SQL Server is certainly capable of handling ... >being missing) is messing up the main query. ...
    (microsoft.public.sqlserver.programming)