question

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