One of our main processes was performing badly.
During the investigation, we found that most of the time is wasted on creating temporary tables, and indexes on temporary tables.
We started checking a few of the regular suspects:
1. Trace flag 1118 was on.
2. Tempdb was spread across 8 files equally sized, so there shouldn’t be any big problem with GAM, SGAM and PFS.
3. Looking at performance monitor, we didn’t see any high latency on the tempdb disks.
So what could go wrong?
We found that SQL Server 2008 R2 cumulative update 6 handles this problem.
More specifically, it holds a fix to this issue.
Once we deployed the fix, performance went down from 700ms per execution to under 100ms per execution. Impressive.
Comments