March 6, 2019 at 1:37 am
Hi,
This is Part 2 of Slow in the Application, Fast in SSMS. How to force the execution plan generated by SMSS.
In summary, the same query is performing slow in the application and fast in SSMS. I can see that the execution plans are different and that would explain why it is slow in the application. The queries are parametrized, the parameters and the attributes are the same. The only difference I can find is the query triggered by the application is using a cursor.
How does a cursor affect the optimizer ? The difference between both execution plan is the first index seek. The bad execution plan starts with an index seek on 3 common value. It reads 1/3 of the table, which represents 90 000 rows. Then the data goes to the next the next operator which is a nested loop. The good execution starts an index seek (different index) on a uncommon value and reads 1 rows. It makes the whole difference in term of logical reads.
I am not trying fix the execution plan at this point, but I am keen to understand why a cursor can make a difference.
Does it make sense ?
Thank you
March 6, 2019 at 5:25 am
So, without the plans in hand to compare them, everything I'm writing is speculation.
First up, we're attempting to compare the execution of two radically different queries, one with a cursor and one without. Can this seriously impact the performance of the query? Yes. Depending on what's going on, absolutely the query performance can be affected.
Now, I think I'm getting from your question that, in fact, you're seeing two different execution plans for the SELECT statement of the cursor, one when the cursor is run, and one when you just run the query. This could come from two different, but related, causes. First, you're not using the same parameter values in the two queries. Due to parameter sniffing, it's possible that your data distribution results in plan for one value and one plan for another value. Or, you're testing the cursor by running a procedure with parameters (and sniffing) while comparing it to a batch run through SSMS using a local variable (no sniffing except in the case of recompiles). If that speculation is correct, then the difference in plans is down to the sniffed value of a parameter versus the average value used when you have a local variable (again, unless the statement recompiles, then the local variable can be sniffed). For giggles you could put the RECOMPILE hint in the local batch to see if it changes the plan to match what you're seeing from the procedure (with parameter sniffing).
Or, I'm way off base and don't understand your situation because I can't see the queries or the plans.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 6, 2019 at 9:42 am
Gamleur84 - Wednesday, March 6, 2019 1:37 AMHi,This is Part 2 of Slow in the Application, Fast in SSMS. How to force the execution plan generated by SMSS.
In summary, the same query is performing slow in the application and fast in SSMS. I can see that the execution plans are different and that would explain why it is slow in the application. The queries are parametrized, the parameters and the attributes are the same. The only difference I can find is the query triggered by the application is using a cursor.
How does a cursor affect the optimizer ? The difference between both execution plan is the first index seek. The bad execution plan starts with an index seek on 3 common value. It reads 1/3 of the table, which represents 90 000 rows. Then the data goes to the next the next operator which is a nested loop. The good execution starts an index seek (different index) on a uncommon value and reads 1 rows. It makes the whole difference in term of logical reads.
I am not trying fix the execution plan at this point, but I am keen to understand why a cursor can make a difference.
Does it make sense ?
Thank you
A part of the problem is that the application is probably using different connection settings than SSMS. For example, SSMS defaults to using "SET ARITHABORT" to ON whereas the application is probably defaulting to it being OFF. The same is particularly true of the ANSI NULL settings (as has sparked a great amount of controversy in the development world because MS wants to make all connections have them ON with no option to revert to OFF).
--Jeff Moden
March 6, 2019 at 1:23 pm
Jeff Moden - Wednesday, March 6, 2019 9:42 AMA part of the problem is that the application is probably using different connection settings than SSMS. For example, SSMS defaults to using "SET ARITHABORT" to ON whereas the application is probably defaulting to it being OFF. The same is particularly true of the ANSI NULL settings (as has sparked a great amount of controversy in the development world because MS wants to make all connections have them ON with no option to revert to OFF).
Additionally - if I recall the other thread properly - the difference appears to be that the query coming from that application is execute using sp_cursor_prepare (in other words - as a cursor) whereas from SSMS it is not executed that way.
So - the question I have is how is that code called from within the application? What is the actually application code that is setting up this query and calling it? Is it python, java, c# - something else? My guess is that no only is there ANSI setting issues - but also application code issues.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 11, 2019 at 9:14 pm
Thank for for all response. Finally I was able to get the same performance in SMSS and the application. I different was the values passed in parameters. I initially captured the query in SQL Profiler and for some reason the value shown were incorrect. Maybe because I was not reading the trace correctly due to the fact this is is executed in a cursor and that seems to be a bit more difficult to track. Anyway, I figured out the correct values and got the same performance issue once I put the correct value in the query in SMSS.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply