Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have just set up fresh installation of SQL Server 2012 on a new server and I'm experiencing very strange performance problems. Long story short, for some reason not including execution plan makes my queries very slow. For example following query

INNER JOIN TableB on TableA.Id = TableB.Id 
WHERE TableA.Id = '15c3a2f5-777a-4ac5-8472-dc525ee299a7'

takes approximately 500ms to execute (tested directly on SSMS and also with Entity Framework). However, if I include execution plan or simply use command SET XML STATISTICS ON makes aforementioned query to run in less than 5ms. Also, same query with same data and same results are working just fine on old server (without 500ms overhead).

Statistic auto creation and auto update are both on (following queries both return '1')

select databasepropertyex('MyDatabase', 'IsAutoCreateStatistics')
select databasepropertyex('MyDatabase', 'IsAutoUpdateStatistics')

I have also tried clearing cached plans and data (with following commands) but it does not help.


Also, running two queries in same batch only adds overhead once, not twice, ie:

SELECT ... ;
SELECT ... ;
// total time approx 510ms

// total time approx 1010ms

There is similar problem posted on stackoverflow 4 years ago but it does not give working solution.

share|improve this question

closed as unclear what you're asking by Paul White, Phil, RolandoMySQLDBA, RLF, dezso May 6 '15 at 14:16

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

Have you tried updating the statistics manually? – Brian Bentley Apr 24 '14 at 11:11
What happens if you add OPTION (RECOMPILE) to the query? I suspect the plans being used are different because one got cached for a different set of SET options. – Aaron Bertrand Apr 24 '14 at 11:14
Also if you cleared the plans after the slow and before the fast, you have no idea if they're really the same plan. Clear the cache, then run both queries, then check the cache. Is there one plan or two? – Aaron Bertrand Apr 24 '14 at 13:00
OK, so what happens if you do this in the opposite order? Clear the cache, run the fast query, then run the slow query. Does the one that includes the plan run slower this time? Or is it the same? (As an aside, how much time are you willing to spend troubleshooting a 500ms hit only the first time a query runs after the cache has been cleared?) – Aaron Bertrand Apr 24 '14 at 13:16
@user544511 Can you try running the query with QUERYTRACEON 2335 to see if you get same results. Might be related to difference in execution plans – Kin Apr 24 '14 at 14:15