The Recording
The Panelists
- Kevin Feasel
- Tom Norman
- Tracy Boggiano
- Mala Mahadevan
Notes: Questions and Topics
The DBA as Gatekeeper
Our first topic was all about the DBA as a gatekeeper. Kenneth Fisher’s blog post inspired the discussion. We got into a fairly detailed discussion on what gatekeeping means, where it makes sense, and where it doesn’t. Mike in chat summed it up with an excellent analogy: gatekeepers guard gates, not walls. In other words, “No” can be a viable answer, but can’t be the only answer.
The Case for Heap Tables
From there, Mala gave us the second topic of the night: is there a place for heap tables? tg came in quickly with “Yes, in a museum.”
The general consensus is that yes, there are cases for heap tables, but they are rare: rare enough that we’re talking 1% or fewer of user tables across an environment. But there are some good cases:
- Tiny tables which fit on one page, such as enumeration tables. There’s no benefit to a clustered index when you’ve only got one page.
- “Write-only” tables, such as log tables, can potentially be faster with heaps than with clustered indexes. That’s not guaranteed and advice can vary based on the version of SQL Server we’re talking about (where later versions make it less likely that you want to use a heap), but it’s possible.
- Temporary tables. It may sound like a bit of cheating for me to include these, but “temporary” can include “temporary permanent” tables: non-temp tables which you don’t expect to be around for very long and aren’t used in your application.
- If I need to perform a full scan of the table every time I query it—if I actually need every record and don’t look for ranges or individual rows—then it might make sense to leave the table as a heap.
- In Azure Synapse Analytics SQL pools, we have three options: clustered columnstore index, clustered index, and heap. Clustered columnstore indexes are recommended for fact-like tables (lots of numeric values, no long strings) with at least 60 million rows. Clustered indexes are recommended for cases when you perform a single-row lookup. And heaps are recommended in other cases: small data sets supporting scans rather than point lookups.
Mala’s Book Corner
Mala has two book recommendations for us: