Take the 2-minute tour ×
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.

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

SELECT * FROM TableA 
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.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

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

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

SELECT ...;
GO
SELECT ...;
// 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
1  
Have you tried updating the statistics manually? –  Brian Bentley Apr 24 at 11:11
5  
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 at 11:14
1  
Do you mean the graphical plan looked the same? Did you compare all of the estimated/actual values, all of the values in the plan attributes DMV? We may have differing definitions of "identical"... –  Aaron Bertrand Apr 24 at 12:55
2  
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 at 13:00
3  
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 at 13:16

2 Answers 2

If you run the first query twice in a row is the 2nd execution quicker?

If so then the reason it is slow at first is because the execution plan is not cached for the first execution. Clearing the chache with DBCC FREEPROCCACHE will cause the problem to happen again.

share|improve this answer
    
It's only faster if it is not run in separate batch. Also, plans should be cached between batches so... –  user544511 Apr 24 at 9:38

What about following sql_statement_recompile extended event and checking recompile_cause field?

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.