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.