SQL Server Execution plans
-
Once SQL server has come up with an execution plan are there different ways
of putting it into effect? What I am asking is :
Is there more than one way of executing an execution plan!?!
The reason why I ask is:
a) Sometimes a certain piece of T-SQL results in an execution plan and
completes in 3 or 4 seconds.
b) When exactly the same T-SQL is run at a later date it results in exactly
the same execution plan but completes in 150/200 secs.
I have tried all the normal troubleshooting methodologies looked at stats, defrag, blocks, deadlocks etc etc etc.
All I really want to know is is there more than 1 way of completing an
execution plan? Is there anything intelligent under the optimiser that is
capable of making decisions on the fly about how it is going to execute the
plan? Or once you have an execution plan is that it - SQL will only follow
the execution plan till it completes or errors?
-
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=177917
It sound like blocking too.
Using hints will force SQL Server to run in different execution plan.
-
Basically, yes. Once the optimizer has decided upon a plan, it will be used.
Now, the interesting part is how a plan gets to be decided upon. Just because a certain plan is chosen doesn't necessarily mean that it's the most optimal. (confusing, eh?)
This is a phenomenon that can happen with multi-statement procedures - one plan may be reused, but depending on how code branches, that plan may not be the best at all times.
From your example, you say 'the exact same T-SQL', and there is a great discrepancy in the percieved time it completes.
Now, are you positive that it is exactly the same statements? That is, no differences in parameters and such?
If it indeed is the exact same statements issued, then I'd really look at other things for the great time differences, like locking, blocking etc...
/Kenneth
-
Not only is the T-SQL exactly the same but the execution plan is exactly the same including paths, estimates etc. This is what has lead me to question wether there is something under the hood which is running the plan differently.
No blocking occuring. Could SQL get in a muddle about how it is going to go about locking the resources (the T-SQL is dml) .
Does SQL have a runtime locking strategy and therefore could the difference between the two runs be explained by SQL locking at different levels?
-
As Kenneth hints, I would assume that there are actually differences in the parameters that are used for the differing executions. If so, then the problem is most probably skewed data. The executionplan is first created for some parameter, resulting in a fast execution. But then when the same plan is used for a completely different parameter, that same plan is not really the optimal plan anymore and you would want to recompile the plan.
-
Hm, I hadn't seen your last post when I wrote mine. But I will let it stay if it is still valid.
-
No difference in the SQL, parameters or execution plan. I don't think the execution plan is the problem here. I think a part of the architecture that takes over after the optimiser has finished creating the plan is at fault. This is how it works as I understand it:
1. T-SQL sent by client
2. Optimiser looks at it and comes up with a plan
3. Query Executor (and whatever else in the architecture does this) executes plan - either quickly (3 or 4 seconds) or mega syrupy slow (up to 200)
The optimiser at stage 2. always comes up with the same plan. it appears to me that the Stage 3 components are executing the plan differently. Is this a possibility?
-
The posters above are assuming that the TSQL we are talking about here is a stored procedure, is that true?
There is also another fact. Even if the execution plan is the same but you have index fragmentation your speed is going to be slowed down on days that are away from the lates rebuild/defrag
just my $0.02
* Noel -
It's a batch that calls stored procedures. The cause is not fragmented indexes or stats because a series of executions will run at 3 or 4 seconds. Then a series of executions will run at 150 (+- 6) seconds. Then it will drop down to 3 or 4 seconds. All without me even mentioning 'dbcc dbreindex' to query analyzer !
-
If that is the case, Are you sure nothing else is running on that server when the execution time is longer ?
* Noel -
I know other things are running on the server but it is SQL that is in a twist. It CPU spikes to 100% for the entire duration of the execution. It is not contending for the hardware with other programs. Other SPIDs are in the database but they are not blocking or being blocked. They are also present when execution is fast. During both fast/slow the other SPIDs are showing minimal activity.
I am almost 100% sure this is a SQL execution problem that occurs after the plan has been decided on.
-
What I meant was other thing runing concurrently in SQL server not external programs.
I would try two approaches.
1. Convert the batch that call those stored procedures into a separate stored procedure (I am assuming your batch does not call more or less sp depending on the data) - see if it improves
2. Setup a trace filtering for long running queries to see if you can trap something that was not supposed to be executing at that time.
The last thing I have used in a similar situation to discover that there was a "power user" downloading the full Order history table at one client as I was tunning a long running process
hth
* Noel -
Hmmm... CPU at 100% you say... That's not 'normal'... Is it spiking to 100% on both occasions? Fast and slow runs? If only on the long runs, then perhaps what you're experiencing is wait-times.
Parallellization.. Is it on? Is it used in the plan? If on, try turning it off and see what happens.
How large is the table(s) involved? How many rows are being processed/accessed in this 'problem query'?
It's a batch... Have you looked at recompilations? Sometimes recompiles in mid-proc can take up cosiderable resources..
Well.. can't do much more than speculate without knowing more about the stuff involved.
/Kenneth
-
...Big post somehow lost by Internet Explorer where I answered all the questions...
Anway, the long and the short of it is what I am hoping to get out of this forum is not a fix (although this would be nice!) but an answer to :
Is there more than one way of executing an execution plan!?!
For example, does SQL have different locking strategies which could explain the performance difference? Both strategies could fulfill the execution plan but one could result in poor performance.
As I said before, my understanding of what the problem is:
1. T-SQL sent by client
2. Optimiser looks at it and comes up with a plan (same plan both fast and slow times)
3. Query Executor (and whatever else in the architecture does this) executes plan - either quickly (3 or 4 seconds) or mega syrupy slow (up to 200)
The optimiser at stage 2 always comes up with the same plan. It appears to me that the Stage 3 components are executing the plan differently. Is this a possibility? I have all but ruled out any concurrency issues like blocking etc. It appears to be an internal switch that is thrown. When it is on, the SQL is executed very slowly, then it switches off and the SQL executes quickly. Have a look at this log- it illustrates what I am saying:
/qmh/cases/0063/case063792 Created (Time Taken to Complete: 3.7818792 seconds), Date:25/04/2005 00:00:00 (16:6)
/qmh/cases/0063/case063794 Created (Time Taken to Complete: 4.150748 seconds), Date:25/04/2005 00:00:00 (16:25)
/qmh/cases/0063/case063797 Created (Time Taken to Complete: 3.6707235 seconds), Date:25/04/2005 00:00:00 (16:58)
/qmh/cases/0063/case063798 Created (Time Taken to Complete: 196.4118182 seconds), Date:26/04/2005 00:00:00 (11:24)
/qmh/cases/0063/case063800 Created (Time Taken to Complete: 220.7551662 seconds), Date:26/04/2005 00:00:00 (11:32)
/qmh/cases/0063/case063801 Created (Time Taken to Complete: 207.3445046 seconds), Date:26/04/2005 00:00:00 (11:37)
/qmh/cases/0063/case063802 Created (Time Taken to Complete: 200.6473873 seconds), Date:26/04/2005 00:00:00 (11:46)
/qmh/cases/0063/case063803 Created (Time Taken to Complete: 4.9977411 seconds), Date:26/04/2005 00:00:00 (11:46)
/qmh/cases/0063/case063805 Created (Time Taken to Complete: 4.103968 seconds), Date:26/04/2005 00:00:00 (12:9)
/qmh/cases/0063/case063807 Created (Time Taken to Complete: 5.028144 seconds), Date:26/04/2005 00:00:00 (12:15)
The bold entries are obviously slow times! So in summary, is there more than one way of executing an execution plan? (i appreciate all this help btw
) -
What internal switch are you referring to? Can you post the T-SQL statement and execution plan?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply