August 10, 2012 at 7:33 am
Different query plans for same statement after changing 'Maximum server memory'
A select statement as part of a SP with join and where clause performed very bad because it used a 'Clustered Index Scan' where it is not necessary. If I change SQL Server configuration for memory 'Maximum server memory' by incrementing it by only 1MB while the SP is running, the QueryOptimizer changes the query plan and takes another order to execute the joins, so that the scan is not necessary and the performance is better.
Here is the query plan with the scan:
Clustered Index Insert(OBJECT: ([tempdb].[dbo].[#oidList]), OBJECT: ([tempdb].[dbo].[#oidList]), OBJECT: ([tempdb].[dbo].[#oidList]), SET: ([#oidList].[oid] = [MBO].[dbo].[Object].[OID] as [obj].[OID],[#oidList].[oidParent] = [MBO].[dbo].[Object].[OIDParent] as [obj].[OIDParent],[#oidList].[oidAdmin] = [MBO].[dbo].[Object].[OIDAdmin] as [obj].[OIDAdmin],[#oidList].[lev] = RaiseIfNullInsert([@level]),[#oidList].[cid] = [MBO].[dbo].[Object].[CID] as [obj].[CID],[#oidList].[grp] = #searchTree.[grp] as [st].[grp],[#oidList].[oidGrp] = [Expr1011],[#oidList].[type] = [Expr1012]))
|--Compute Scalar(DEFINE: ([Expr1011]=(0), [Expr1012]=(0)))
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Nested Loops(Left Anti Semi Join, WHERE: (#searchTree.[cid] as [st].[cid] IS NULL OR #searchTree.[cid] as [st].[cid]=[cid]))
|--Nested Loops(Inner Join, OUTER REFERENCES: ([obj].[OIDParent]))
| |--Parallelism(Repartition Streams, RoundRobin Partitioning)
!!!============================================================================================================================
| | |--Clustered Index Scan(OBJECT: ([MBO].[dbo].[Object].[IX1_Object] AS [obj]), WHERE: ([MBO].[dbo].[Object].[CID] as [obj].[CID]=[@cid]))
!!!============================================================================================================================
| |--Clustered Index Seek(OBJECT: ([tempdb].[dbo].[#searchTree] AS [st]), SEEK: ([st].[oid]=[MBO].[dbo].[Object].[OIDParent] as [obj].[OIDParent]), WHERE: (#searchTree.[lev] as [st].[lev]=[@level]-(1)) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT: (@tblHideObj))
Here is the query plan (after changing memory configuration while SP is running!):
Clustered Index Insert(OBJECT: ([tempdb].[dbo].[#oidList]), OBJECT: ([tempdb].[dbo].[#oidList]), OBJECT: ([tempdb].[dbo].[#oidList]), SET: ([#oidList].[oid] = [MBO].[dbo].[Object].[OID] as [obj].[OID],[#oidList].[oidParent] = [MBO].[dbo].[Object].[OIDParent] as [obj].[OIDParent],[#oidList].[oidAdmin] = [MBO].[dbo].[Object].[OIDAdmin] as [obj].[OIDAdmin],[#oidList].[lev] = RaiseIfNullInsert([@level]),[#oidList].[cid] = [MBO].[dbo].[Object].[CID] as [obj].[CID],[#oidList].[grp] = #searchTree.[grp] as [st].[grp],[#oidList].[oidGrp] = [Expr1011],[#oidList].[type] = [Expr1012]))
|--Compute Scalar(DEFINE: ([Expr1011]=(0), [Expr1012]=(0)))
|--Top(ROWCOUNT est 0)
|--Nested Loops(Inner Join, OUTER REFERENCES: ([st].[oid]) OPTIMIZED)
|--Nested Loops(Left Anti Semi Join, WHERE: (#searchTree.[cid] as [st].[cid] IS NULL OR #searchTree.[cid] as [st].[cid]=[cid]))
| |--Clustered Index Scan(OBJECT: ([tempdb].[dbo].[#searchTree] AS [st]), WHERE: (#searchTree.[lev] as [st].[lev]=[@level]-(1)))
| |--Clustered Index Scan(OBJECT: (@tblHideObj))
!!!============================================================================================================================
|--Clustered Index Seek(OBJECT: ([MBO].[dbo].[Object].[IX1_Object] AS [obj]), SEEK: ([obj].[OIDParent]=#searchTree.[oid] as [st].[oid]), WHERE: ([MBO].[dbo].[Object].[CID] as [obj].[CID]=[@cid]) ORDERED FORWARD)
!!!============================================================================================================================
'Object' is a table with many rows and no index on CID
'#searchTree' is a temp. table with not many rows that build the selection on the object table.
The interesting part is marked with !!!===============
Did anybody has an explanation for that behaviour or has an idea to solve that problem?
Thanks for every tip!!!
Thomas
August 10, 2012 at 9:21 am
For starters, I am curious what the original memory setting is.
I am also curious as to how long this stored procedure has been in place, and how long the server has been running? If the original plan was created under less than optimal conditions, it may also be less than optimal. Then the plan will pretty much stay in the cache until the server is rebooted or something forces SQL Server to recompile it.
So here is my theory: Restarting the server after making the change to the memory setting cleared the plan cache and forced a recompile of the stored procedure. This recalculated the plan. For some reason SQL Server chose a better route and decided to use an index seek.
There may be a more technical reason, and I may be totally wrong on this. In any case, I would change the setting back, restart SQL Server and see if it still has the index seek. That may eliminate changing the setting as the catalyst for the change. If is goes back to the index scan, then SQL Server thinks it needs that little bit more. Put it back and be really happy such a minor change in available memory can make such an improvement!
August 10, 2012 at 9:37 am
Probably be easier if you saved the execution plans as .sqlpan files and uploaded those. The graphical plan would be much easier to read.
August 10, 2012 at 10:44 am
Changing the max server memory forces invalidates the plan cache. Max server memory is a parameter that the query optimizer uses for evaluating plans. Different plans have different memory grant requirements, for example, a sort may or may not spill to tempdb depending on available memory.
If you change the max server memory back to it's orginal value, do you get the original plan?
If so, you, your query is at a tipping point where the change in max server memory is causing different plans.
If not, then it's probably just a recompilation issue.
August 10, 2012 at 10:45 am
Picky, picky, Lynn! You must learn to read the Matrix in it's original code, how else will you see the Agents? 😀
August 13, 2012 at 7:42 am
... some more info:
- switching back 'Maximum server memory' to old value will NOT change query plan back to old version - the better version with 'Index Seek' is still used
So it is not the memory size to lead to a better plan - it seems to be the reset of the plan cache.
But what can I do to get that optimal plan bay starting the functionality. It is not that complex statement - i think it is obvious to use the Index Seek
for that query - why does SQL Server don't think so?
- the call of that SP is part of a complex functionality in our program - it uses many other database activities. This functionality runs over many minutes
during that i can change the memory configuration to change the query plan. When I start that functionality again (without to restart the program itself)
the old (bad) query plan is used again.
The basic of the query:
The object table has a lot of entries ( > 1 Million). The first selection is a temp. table #searchTree with not much entries (e.g. 200) that has a join with the object table.
Next selection is a special ID (CID) in object table (no index on it) – I am looking for object entries with a special value in CID
So SQL Server first scans all object table entries for that CID and then it joined the result with #searchTree (bad version).
Better would be first to join the #SearchTree and then scan that small result set (so SQL Server will do after clearing plan cache by changing memory settings).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply