Execution plan not correct for query


  • I have a stored procedure that is used by our data warehousing application to fill a certain table. The job that executes the SP runs very slow since a certain date. From 1 minute 30 secs to 2,5 hours from one day on the other. I have isolated the problem to the select query inside this SP. If I look at the query plan, they are both different for our development and our test environent, although the data warehouse model is exactly the same. Only the data differs.

    At a certain point in the execution plan SQL uses a nested loop join in the test environment, but a merge join in the development environment. I have looked up the differences and (summarized) it seems that for large data sets the merge join is the fastest. I have force a merge join in the query on the test environment and then it runs like before (1,5 minute instead of 2,5 hours). I have done this using 'OPTION (merge join)'. I have run the same query on the development environment, where it runs 1,5 minute, and forced a nested loop join with (OPTION loop join). The query runs very slow there as well. So I can reproduce the issue it seems. 

    Now, the problem is the solution for this. We use the Ola Hallengren scripts and I have updated the statistics with this script manually. (it is scheduled once per week). This did not help. We suspect that the problem occured since we have added like 40 + records to a certain dimension table from the model, which contains companynumbers. Total records in test is 96 and in dev about 46. I can't get my finger behind the issue. The cause is probably the number of records in the table, but I don't know why SQL uses that execution plan. I have also compared al the indexes of the involved tables and they are exactly the same.

    I have also cleared the plan cache with DBCC FREEPROCCACHE. You can see that it generates a new query plan if I clear it. First I clear the plan cache, then I query the plan cache for a keyword in the query. No result. Next I execute the query (and manually stop it, because takes too long) and query the plan cache again. There is now a query plan in the cache for the query. I clear the cache again and query the plan cache again. Then the plan is gone.

    So for some reason, SQL thinks it should generate this query plan, which is very slow.

    • Modificato Niels Br venerdì 1 giugno 2018 12:41
    venerdì 1 giugno 2018 11:48