Professional Web Applications Themes

Explain query estimation plan - Microsoft SQL / MS SQL Server

When you say "larger query", do you mean that the number of rows in the tables differ? You can get a different query plan if you have two tables with vastly different row counts. If the row counts are similar, try updating the statistics or rebuilding indexes. -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql "John Robinson" <demon.co.uk> wrote in message news:VU7y4QGn97J$demon.co.uk... I have the following query select lodnum, sum(aqty) from job with (nolock) group by lodnum I have 2 databases with identical schemas. When I run ...

  1. #1

    Default Re: Explain query estimation plan

    When you say "larger query", do you mean that the number of rows in the tables differ? You can get a different query plan if you have two tables with vastly different row counts.

    If the row counts are similar, try updating the statistics or rebuilding indexes.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "John Robinson" <demon.co.uk> wrote in message news:VU7y4QGn97J$demon.co.uk...
    I have the following query

    select lodnum, sum(aqty) from job with (nolock) group by lodnum

    I have 2 databases with identical schemas.

    When I run the estimated execution plan on database 1 I get the
    following:

    a table scan on job
    a hash/match aggregate
    a select

    When I run the estimated execution plan on database 2 I get the
    following:

    a table scan on job
    a sort doing 'order by lodnum'
    a stream aggregate/aggregate
    a select

    Can anybody explain why they are different?

    The problem is that I have a much larger query that runs within a couple
    of
    seconds on database 1 but takes many minutes on database 2. Whilst
    trying to diagnose this problem I have encountered the above which I
    think is at the heart of my problem.

    Any help is greatly appreciated.
    --
    John

    Tom Guest

  2. #2

    Default Re: Explain query estimation plan

    What I mean by "larger query" is a larger SQL query. It is a select from
    a table joined to a select of another table and then joined to another
    select and then grouped, etc, etc.
    I do not mean number of rows in the table.

    Funny you should mention statistics - the database I am having trouble
    with does not have any statistics whereas the other database does.
    Could this be source of my problem?
    Both databases have the auto create and auto update statistics set to ON
    so why don't statistics get created?

    I have done some experimenting with the first database that does have
    statistics. I deleted the statistics and ran the query again and the
    statistics were recreated. Why is this not happening on my second
    database?

    Do you have any more thoughts?

    John

    In article <phx.gbl>, Tom Moreau
    <spam.me.cips.ca> writes 

    --
    John
    John Guest

  3. #3

    Default Re: Explain query estimation plan

    It's strange that there would be no statistics if auto-update was on. Just curious, if you try something like:

    update MyTable
    set
    MyCol = MyCol

    Does it update the statistics on it? If you still cant get it to update/create them, jus run UPDATE STATISTICS WITH FULLSCAN on the table and see if that gives it a kick.

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    ..
    "John Robinson" <demon.co.uk> wrote in message news:OjFEV7IcM$J$demon.co.uk...
    What I mean by "larger query" is a larger SQL query. It is a select from
    a table joined to a select of another table and then joined to another
    select and then grouped, etc, etc.
    I do not mean number of rows in the table.

    Funny you should mention statistics - the database I am having trouble
    with does not have any statistics whereas the other database does.
    Could this be source of my problem?
    Both databases have the auto create and auto update statistics set to ON
    so why don't statistics get created?

    I have done some experimenting with the first database that does have
    statistics. I deleted the statistics and ran the query again and the
    statistics were recreated. Why is this not happening on my second
    database?

    Do you have any more thoughts?

    John

    In article <phx.gbl>, Tom Moreau
    <spam.me.cips.ca> writes 

    --
    John
    Tom Guest

Similar Threads

  1. explain plan from pl/sql ?
    By Ibrahim in forum Oracle Server
    Replies: 10
    Last Post: November 7th, 11:00 PM
  2. Explain Plan for queries
    By ovkrishna in forum Informix
    Replies: 8
    Last Post: October 27th, 04:31 PM
  3. query's explain plan different in 8i and 9i?
    By G in forum Oracle Server
    Replies: 5
    Last Post: October 22nd, 02:38 PM
  4. Marty,I Have Another Explain Plan Question
    By Richard Winston in forum IBM DB2
    Replies: 4
    Last Post: September 15th, 08:05 PM
  5. Replies: 4
    Last Post: September 15th, 06:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139