question

yossim avatar image
yossim asked

Why Sql server engine choose bad execution plan

Hi, I have an issue with SQL server execution plan for one of my query. As part of cube processing we are executing SQL queries again FACT tables with date range, I have 2 FACT tables, they both partition daily. Header = ~ 10mil rows Lines = ~ 200 mil rows The queries that executing on a weekly basis are: (the count(*) is only for demonstrate ) SELECT count(*) FROM dbo.InventoryDocumentHeader AS A INNER JOIN dbo.InventoryDocumentLine AS B ON A.InventoryHeaderID = B.InventoryHeaderID and A.DocumentDate=B.DocumentDate WHERE A.DocumentDate >= @fromDate AND A.DocumentDate < @toDate The problem occurs when the last query executing against month that is not full with data. For example: Query1 (full month, around 9 mil rows) SELECT count(*) FROM dbo.InventoryDocumentHeader AS A INNER JOIN dbo.InventoryDocumentLine AS B ON A.InventoryHeaderID = B.InventoryHeaderID and A.DocumentDate=B.DocumentDate WHERE A.DocumentDate >= '2014-09-01' AND A.DocumentDate < '2014-10-01' Query2 (partial month, around 1.3 mil rows) SELECT count(*) FROM dbo.InventoryDocumentHeader AS A INNER JOIN dbo.InventoryDocumentLine AS B ON A.InventoryHeaderID = B.InventoryHeaderID and A.DocumentDate=B.DocumentDate WHERE A.DocumentDate >= '2014-10-01' AND A.DocumentDate < '2014-11-01' Query 1 return ~9mil Query 2 return ~1.3mil I/O statistics : Query 1 Table 'InventoryDocumentHeader'. Scan count 30, logical reads 521, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'InventoryDocumentLine'. Scan count 30, logical reads 11328, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Query2 Table 'InventoryDocumentLine'. **Scan count 65358, logical reads 199349**, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'InventoryDocumentHeader'. Scan count 31, logical reads 761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. We can see that query2 has much more scan and logic reads then on query1, although query2 return less data. The execution plan of query 1 using parallelism The execution plan of query 2 not using parallelism When force plan 1 on query 2 using sp_create_plan_guide The io results show: Query2 Table 'InventoryDocumentHeader'. Scan count 31, logical reads 73, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'InventoryDocumentLine'. Scan count 31, logical reads 2285, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. • Both query1 and query2 execution plan are using partition tables (I checked in the XML and see that only 30 partitions has been scanned - ) • I also try to run sp_updatestats • Sql server version=2012 Ent Can someone explain this behavior ? My assumption is that because of statistics the engine know that query2 has less data and then it choose execution plan without parallelism, but I still cannot understand why there is more I/O on 'InventoryDocumentLine' when execution query2 and less while executing query1 ??? link text
performanceexecution-planstatisticstable partition
query1ep.sqlplan (107.0 KiB)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your answer. I uploaded the Query2EP, (due to its size,more then 512K, need to change the .txt to .rar and then open it) I executed sp_updatestats but it didn't change the execution plan of query2. The plan in query2 has Nested loop. The engine will generate new execution plan only when there will be more rows on that date range (October), But then I will see the same behavior at the new month etc... What do I need to do to solve this issue, ? [link text][1] [1]: /storage/temp/ 1714-query2ep.txt
0 Likes 0 ·
query2ep.txt (7.3 KiB)
You'll have to update your statistics more frequently to take into account the growing data set.
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
It's pretty likely that it has to do with statistics. You're saying that you're retrieving more recent data with Query2 that is incomplete. It's also likely falling outside of the standard set of statistics. In that case, the optimizer, prior to 2014, will assume 1 row. In 2014 it assumes an average of the table, which would probably be better in this case. Since I can't see the query2 execution plan, I have to guess. On a guess, you're probably getting a plan estimated for a lot less rows. In that case, it might choose a loop join or something else rather than the hash join in Query1. In that case, you'll see massive scans since the loop acts pretty much as a cursor.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OK, so as you suggest , I focus on the statistics and ran DBCC SHOW_STATISTICS and I found that the header table statistics was out of date, although I executed sp_updatestats and UPDATE STATISTICS for that specific table. The only way to get this statistics up to date was to execute UPDATE STATISTICS "dbo.TableName" WITH FULLSCAN; Now I know what cause that behavior but still I cannot understand why 1. sp_updatestat didn't fix it? (found the answer http://msdn.microsoft.com/en-us/library/ms187348.aspx) 2. How come the Line table statistics was up to date and the header table not?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.