asked
The same query has different plan for different logins
Hi, we’ve just upgraded to SS2014 and one developer asked me about slow query execution. The query was quite simple, one table, one condition selecting top 1 record with order by. For his quite narrowed user it was running for ages. It must have been statistics, I switched to SA login and updated statistics for that table and the query executed immediately. I thought the problem is resolved but he still complained – query is still slow. Then when I logged with his user I also noticed the same problem, the query was super slow, almost never ending. I checked execution plans when logged SA and later on with his login. Those plans are different. Why execution plan was not refreshed for that user? Any ideas very appreciated :)
The most likely answer is that the two connections have different options set which affects whether the same cached plan can be used. A quick way to check is to run the Profiler with just the 'Existing Connections' bit ticked. That will list out all connections with their options. Find the user's connection and copy the text for that. It will be something like this: -- network protocol: TCP/IP set quoted_identifier on set arithabort on set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language British set dateformat dmy set datefirst 1 set transaction isolation level read committed If you run this once in your code window, it should mean that you get the same plan from the cache that the developer gets, and then you can see what's different in the execution plan. There's a long but excellent article about this by the amazing Erland Sommarskog:
http://www.sommarskog.se/query-plan-mysteries.html
Hi David, thank you for suggestion. I ran that query from my SSMS once with SA privilege and once with his priviliege - I guess connections should be the same. Anyway it seems I found the problem. When I approached to the issue once again query was also executing very slow from SA. (wonder why it was faster after updated statistics before) Now even if I update stats it still executes endlessly. As I mentioned we upgraded to SS2014, so I checked how the query would execute with old CE algorithm by applying trace flag OPTION (QUERYTRACEON 9481). Query executed immediatly.
19 People are following this question.
