0

I have a mirrored SQL Server 2012 database. The first server has a hugh tempdb write IO load when the maximum memory limit is set above 8192 mb.

These 8192 where the original setting for both the first and second server and I can only assume this can be an origin of this wierd behavior. However, when I increase the limit on the second server, the first still causes high io load. Once I set it back to 8192 or lower, tempdb activity zeros.

Also changing the mirroring model between High Performance / High Safety / Monitor has no effect. I have not yet tried to remove the mirror as this will cause an application downtime...

Have I found a bug?

sebdot
  • 101
  • 1
  • 1
    perhaps this would help: http://dba.stackexchange.com/a/54002/10832 – Hannah Vernon Feb 22 '14 at 22:40
  • How many tempdb files you have and any trace flags 1117 and 1118 enabled? They help for proportional tempdb data growth and the later minimize tempdb contention. – Kin Shah Feb 23 '14 at 02:44
  • Those trace flags didn't help. I still get the IO panic when setting max memory to > 8192 mb. – sebdot Feb 24 '14 at 22:37
  • Ok, I found out that the 8192 mb setting is not the point here, setting this value higher only makes the IO horror happening faster. I also found out that the other server is showing the same problem, just later and shorter. I feel so bad. I'll try turning off Snapshot Isolation and Read Committed Snapshot now. – sebdot Feb 25 '14 at 10:20
  • I found the cause. We use Entity Framework in our application. I tracked it down to a Linq statement "from a in b select new Xy() { Test = a.NavProp.Where(c=>c.bla).FirstOrDefault() } " that causes this. It seems that the nested Linq which uses a navigation property makes SQL server assuming that the whole Facebook database shall be in my tempdb now :-) – sebdot Feb 25 '14 at 18:41

0 Answers0