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?
If you ask any SQL Server DBAs about cursors, they will tell you it’s a big no-no. If you search for T-SQL best practices on the web, in all the results that you will find, there will always be a section similar to this: “Avoid using cursors as much as you can”. I agree that in many cases cursors should be avoided and can be replaced by more efficient set-based programming alternatives. But, in some cases, using a cursor is the only option or the most efficient option. Otherw