x

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

query1ep.sqlplan (109.5 kB)
more ▼

asked Oct 09, 2014 at 06:35 PM in Default

avatar image

yossim
31 1 4 6

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

query2ep.txt (7.5 kB)
Oct 10, 2014 at 02:06 AM yossim

You'll have to update your statistics more frequently to take into account the growing data set.

Oct 10, 2014 at 10:59 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Oct 09, 2014 at 09:39 PM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

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?

Oct 10, 2014 at 06:30 PM yossim
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x311
x61
x47
x14

asked: Oct 09, 2014 at 06:35 PM

Seen: 575 times

Last Updated: Oct 14, 2014 at 01:58 PM

Copyright 2017 Redgate Software. Privacy Policy