locked
query optimizer works weirdly RRS feed

  • Question

  • I have a typical fact - dimensional data warehouse, and when I am querying data from it I see strange thing happens...

    sample query(query 1):

    Select count(1) from fct_device  t
    Where t.monitored_Id=2 and
          t.data_time_id in (
            select data_time_id from dim_date t
            where  data_date >= 28 and
                   not exists(select 1 from dim_date
                              where data_date > t.data_date and 
                              data_year = t.data_year and
                              data_month = t.data_month )
            )

    Tables:

        fct_device is partitioned by data_time id (int) and contains roughly 20,000,000 records.

        dim_date is a relatively small dimension table, containing ~30 records.

        If I only execute the sub-query(marked in bold) it returns 7 records(the sub-query is supposed to return all the end-of-month date)

    Query 1 takes more than 1 min to complete. The execution plan for query 1:

     |--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(int,[Expr1013],0)))
           |--Stream Aggregate(DEFINE:([Expr1013]=Count(*)))
                |--Nested Loops(Left Anti Semi Join, WHERE:(dim_date.[DATA_DATE]>fct_device.[DATA_DATE] AND ....)
                     |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[DATA_TIME_ID]))
                     |    |--Clustered Index Scan(OBJECT:(dim_date. where data_date>=28)
                     |    |--Table Scan(OBJECT:(fct_device AS [t])....)
                     |--Clustered Index Scan(dim_date)

    The dim_date(predicate data_date>=28) first does Nested Loops join with the fact table, and then join with dim_date again(to do the Not Exists process). And seems it doesn't use the partition at all(no parallelism)... 

    I initially believed query 1 will be optimized to execute the sub-query first and then do the other steps, and it will be equivalent to the query below:

    (query 2)

    Select count(1) from fct_device  t
    Where t.monitored_Id=2 and
          t.data_time_id in (
           20111031,20111130,20111231,20120131,20120229,20120331,20120430

           )

     Plan for query 2:

      |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[globalagg1006],0)))
           |--Stream Aggregate(DEFINE:([globalagg1006]=SUM([partialagg1005])))
                |--Parallelism(Gather Streams)
                     |--Stream Aggregate(DEFINE:([partialagg1005]=Count(*)))
                          |--Table Scan(...)

    Query 2 takes 15 sec to complete.   You can see it takes the advantage of the partition table by running in parallelism.


    After that I did another modification and it's the most weird part...

    query 3:

    Select count(1) from fct_device  t
    Where t.monitored_Id=2 and
          t.data_time_id in (
            select Cast(data_time_id as char) from dim_date t
            where  data_date >= 28 and
                   not exists(select 1 from dim_date
                              where data_date > t.data_date and 
                              data_year = t.data_year and
                              data_month = t.data_month )
            )

    The only change I made(marked in bold) is to cast the data_time_id to char in the sub-query first. I thought it would be a performance penalty because the data_time_id in both fact table(fct_device) and dimension table(dim_date) is INT type but I was wrong..

    Query 3 takes less than 1 min(still more than what query 2 takes of cause but better than query 1)

    Plan for query 3:

    |--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[globalagg1014],0)))
           |--Stream Aggregate(DEFINE:([globalagg1014]=SUM([partialagg1013])))
                |--Parallelism(Gather Streams)
                     |--Stream Aggregate(DEFINE:([partialagg1013]=Count(*)))
                          |--Hash Match(Right Semi Join, HASH:([Expr1012])=([t].[Data_time_Id]), RESIDUAL:(fct_device.[Data_time_Id]=[Expr1012]))
                               |--Bitmap(HASH:([Expr1012]), DEFINE:([Bitmap1015]))
                               |    |--Parallelism(Distribute Streams, Broadcast Partitioning)
                               |         |--Nested Loops(Left Anti Semi Join, WHERE:(dim_date.[DATA_DATE]>fct_device.[DATA_DATE]...)
                               |              |--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(int,CONVERT(char(30)...)
                               |              |    |--Clustered Index Scan(OBJECT:(dim_date where data_date>=(28))
                               |              |--Clustered Index Scan(OBJECT:(dim_date)
                               |--Table Scan(OBJECT:(fct_device where monitor_id =2 and PROBE([Bitmap1015])

    Seems the CONVERT makes the optimizer decide to execute the sub-query first, and apparently it made a very well decision..

    I think the key is to take advantage of the partition table but I am not an expert.. So can someone please help me understand what's going on here it would be great!

    thanks

    p.s Where can I  find some really good articles which can help me understand what execution plan shows(e.g. what is RESIDUAL in query plan)? many thanks

    p.p.s

    ----update-----

    The estimated execution gives the estimated cost:

    Query 1  25% (actual: more than 1min)

    Query 2  12% (actual: 15 sec)

    Query 3   63%(actual:  ~50 sec)

    And I already updated the statistics for both tables before querying.

    • Edited by iammac Monday, May 28, 2012 4:55 AM
    Monday, May 28, 2012 4:44 AM

Answers

  • The first thing I'd point out is that parallelism has absolutely nothing to do with partitioning, it is entirely cost based and you can have parallelism against non-partitioned tables.  Without the Actual Showplan XML for each of the statements it is going to be really difficult to explain to you the specifics about why you are getting what you have.  If you can post the DDL for both tables including the indexes and the partition scheme and function, it would be easier to give you better answers.  The optimizer rarely "works weirdly" and there is generally a very good reason that you get one behavior over another in SQL Server.  The answers to what that reason is lies in the Actual ShowplanXML for the statements and the DDL mentioned above.

    To understand what all the operators in SQL Server showplans mean, a good start would be Grant Fritchey's book, http://www.sqlservercentral.com/articles/books/65831/.  Beyond this, look at Paul White's blog, http://sqlblog.com/blogs/paul_white/, which gives more information about how the query optimizer works than any other location online.  You'll find really quickly if you go through Paul's blog posts and articles online that the optimizer isn't working weirdly.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by amber zhang Tuesday, May 29, 2012 1:44 AM
    • Marked as answer by amber zhang Monday, June 4, 2012 7:41 AM
    Monday, May 28, 2012 9:34 PM
  • The optimizer chooses between alternative plans (and whether or not to use parallelism) based on estimated costs derived primarily from cardinality estimates (not shown in your output, but also present in the information Jonathan refers to).  The cause of the poor plan selection here is likely due to a known bug (now fixed).  The bug causes an estimate of one row to be produced, and the rest of the plan suffers from this.  The details are described in the links below (note that trace flag 4199 needs to be enabled, and you need to be running the appropriate build and version).  A workaround is to store the rows from the dim_date subquery in a temporary table (not a table variable).  Replace the subquery with the temporary table, and you should find a good plan is produced.

    http://blogs.msdn.com/b/psssql/archive/2010/09/01/slow-query-performance-because-inaccurate-cardinality-estimate-when-using-anti-semi-join-following-upgrade-from-sql-server-2000.aspx

    http://support.microsoft.com/kb/2222998


    Tuesday, May 29, 2012 12:18 AM

All replies

  • Well here's the thing: Yes, the optimizer does "work weirdly", that is, it works things at another level than that at which we write SQL, and it's often a battle to understand what it thinks it's doing.  And this is true of most compilers for most languages, if you try to watch them working.  And that can be the case even when you WROTE the compiler yourself!  In fact, that's WHY we write compilers, to apply rules which otherwise require a lot of work on our part.

    And of course, that's when they get everything right, which they don't always do.

    As to your query, maybe you should also look at the logical reads generated by each query - and use the profiler for that, not "set statistics IO" which can sometimes be confused.

    Are you hoping that the compiler will limit the scan to just the proper partition for the data fetched in the sub-query?  I don't think it can be that smart, not even sure how to tell except for detailed read counts.  Or are you just hoping that the partitions encourage parallelism, even if 90% of it is going to be pointless?

    Finally, what kind of counts *do* these generate?

    Josh

    Monday, May 28, 2012 5:44 AM
  • The first thing I'd point out is that parallelism has absolutely nothing to do with partitioning, it is entirely cost based and you can have parallelism against non-partitioned tables.  Without the Actual Showplan XML for each of the statements it is going to be really difficult to explain to you the specifics about why you are getting what you have.  If you can post the DDL for both tables including the indexes and the partition scheme and function, it would be easier to give you better answers.  The optimizer rarely "works weirdly" and there is generally a very good reason that you get one behavior over another in SQL Server.  The answers to what that reason is lies in the Actual ShowplanXML for the statements and the DDL mentioned above.

    To understand what all the operators in SQL Server showplans mean, a good start would be Grant Fritchey's book, http://www.sqlservercentral.com/articles/books/65831/.  Beyond this, look at Paul White's blog, http://sqlblog.com/blogs/paul_white/, which gives more information about how the query optimizer works than any other location online.  You'll find really quickly if you go through Paul's blog posts and articles online that the optimizer isn't working weirdly.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by amber zhang Tuesday, May 29, 2012 1:44 AM
    • Marked as answer by amber zhang Monday, June 4, 2012 7:41 AM
    Monday, May 28, 2012 9:34 PM
  • The optimizer chooses between alternative plans (and whether or not to use parallelism) based on estimated costs derived primarily from cardinality estimates (not shown in your output, but also present in the information Jonathan refers to).  The cause of the poor plan selection here is likely due to a known bug (now fixed).  The bug causes an estimate of one row to be produced, and the rest of the plan suffers from this.  The details are described in the links below (note that trace flag 4199 needs to be enabled, and you need to be running the appropriate build and version).  A workaround is to store the rows from the dim_date subquery in a temporary table (not a table variable).  Replace the subquery with the temporary table, and you should find a good plan is produced.

    http://blogs.msdn.com/b/psssql/archive/2010/09/01/slow-query-performance-because-inaccurate-cardinality-estimate-when-using-anti-semi-join-following-upgrade-from-sql-server-2000.aspx

    http://support.microsoft.com/kb/2222998


    Tuesday, May 29, 2012 12:18 AM