---

Diving into the world of SQL Server, we often bump into a little hiccup known as “Forwarded Fetches/sec,” especially when we’re dealing with TempDB and tables that are just heaps of data. Imagine every time you try to update a row, it decides to play musical chairs and move to a new spot. This not only makes it harder to find but also slows everything down because it needs extra effort to be accessed. So, let’s roll up our sleeves and tackle how to smooth out these bumps with some savvy strategies and T-SQL magic.

Getting to the Heart of TempDB Troubles

Think of TempDB as the busy bee of SQL Server, buzzing around carrying temporary stuff for user objects, internal operations, and whatnot. But, when it gets too crowded, things start to slow down. Let’s start our detective work here:

How to Spot TempDB Drama

First off, let’s see which queries are making TempDB sweat. We can peek into SQL Server’s secret diary, the Dynamic Management Views (DMVs), to catch the culprits:

SELECT session_id, user_objects_alloc_page_count, internal_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE (user_objects_alloc_page_count + internal_objects_alloc_page_count) > 0
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC;

This snippet is like saying, “Show me who’s hogging all the space!” and it points us to the queries that need a closer look.

Finding the Needle in the Heaps

Now, onto heaps – the tables that decided they’re too cool for clustered indexes. This can lead to a mess when rows start playing hide and seek. Here’s how to find those sneaky heaps:

SELECT 
    OBJECT_NAME(OBJECT_ID) AS TableName,
    forwarded_record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE 
    index_id = 0 AND forwarded_record_count > 0
ORDER BY 
    forwarded_record_count DESC;

We’re looking for tables that are a bit too forward with their fetches. Once we’ve got our list, it’s time for action.

Fixing the Fetch Fiasco

For TempDB:

Found a query that’s a bit too greedy with TempDB? Time to trim its nails. Maybe it’s using a huge temporary table that could use some indexing love:

CREATE INDEX idx_temp_example ON #TempExample(Col1, Col2);

This can make navigating through the temp table a breeze, lightening the load on TempDB.

For Heaps:

Stumbled upon a heap that’s a hot mess of forwarded fetches? We can either give it some structure with a clustered index or give it a good old rebuild:

Clustered Index Style:

CREATE CLUSTERED INDEX CIX_YourTableName ON YourTableName(YourColumn);

Pick a column that makes sense for your data and queries, and turn that heap into a well-organized table.

Heap Rebuild Mode:

ALTER TABLE YourTableName REBUILD;

This is like giving your table a spa day. It comes out refreshed, compact, and free of those pesky forwarded records.

Wrapping It Up

Forwarded fetches might seem like small potatoes, but in the grand scheme of SQL Server performance, they can be a real drag, especially with TempDB and heaps. By getting down and dirty with DMVs to root out the problem areas and applying some targeted fixes, we can keep our databases zipping along happily. Remember, every database has its quirks, so tweak these tips to fit your unique setup and watch your performance worries melt away.

---