Free TempDB allocation performance booster

STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

Free TempDB allocation performance booster

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.

#performance #tempdb

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle