Re: Massive amoutns of Reading


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns969C64F2AD678Yazorman@xxxxxxxxxxxx
> Greg D. Moore (Strider) (mooregr_deleteth1s@xxxxxxxxxxx) writes:
>
> Well, there is a standard recommendation to set "max degree of
parallelism"
> on an HT machine to at most the number of physical processors, thus for
> server A to 2.

Tried that, no joy.
>
> That alone may not give you a better query plan, but at least lower
> load the CPUs. But since it was read-ahead reads that are killing you,
> this would be a minor improvement.
>
> You need to analyse the query plans on A and B. If the plan on B does
> not have parallelism, but the plan A has, then try to add
> "OPTION (MAXDOP 1)" to the query.
>

Remind me of the syntax on this?

In any way, the temporary fix was fairly simple.

In the query itself or the join (which actually is better in our case)
giving a JOIN hint (doesn't really matter which one I use) immediately
improves the performance of the query.

So for now we've added the hint to the VIEW.

I'm going to plan out an upgrade to SP4 with the AWE hotfix in the next week
or so and see if that provides the "real" fix for this query.

Very annoying though.

Thanks for the suggestions though.


> --
> Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp


.

Relevant Pages

  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Its baaack
    ... every time the query is run. ... initial plan. ... There are times when the optimizer simply gives up ... > parallelism and other times the optimizer decides to take the long way ...
    (microsoft.public.sqlserver.programming)
  • Re: Reindex doesnt work
    ... The answer is that the whole affair with maintenance plan and reindexing ... SQL Server builds a query plan for the first time, ... passing a high-water mark as parameter. ...
    (microsoft.public.sqlserver.tools)
  • Re: Reindex doesnt work
    ... The answer is that the whole affair with maintenance plan and reindexing ... SQL Server builds a query plan for the first time, ... passing a high-water mark as parameter. ...
    (microsoft.public.sqlserver.tools)
  • Re: Planning multiple queries
    ... > so it seems to me a new plan should be prepared for the second query, ... If you submit them all in one batch, SQL Server generates a query plan ... If no statistics get updated as result of the query, ...
    (comp.databases.ms-sqlserver)