I know, at least theoretically, that heap tables can be partitioned. As I learn more about table partitioning, I realise that the use case is very limited. Similarly, the use case for heaps in SQL Server is also very limited. Whenever I intersect my list of heap use cases and partitioning use cases, I find myself with an empty list. Are there any sets of circumstances where a partitioned heap would appear strongly desirable?

We experienced a surge in G1 Old Gen consumption due to org.apache.cassandra.concurrent.SEPExecutor according to eclipse memory analyser. This is the problem suspect from the leak detector:

One instance of org.apache.cassandra.concurrent.SEPExecutor loaded by jdk.internal.loader.ClassLoaders$AppClassLoader @ 0x300001568 occupies 12,242,915,256 (72.38%) bytes. 
Thread io.netty.util.concurrent.FastThreadLocalThread @ 0x30a2197e8 MutationStage-30 has a local variable or reference to org.apache.cassandra.concurrent.SEPExecutor @ 0x371d3d790 which is on the shortest path to org.apache.cassandra.concurrent.SEPExecutor @ 0x371d3d790. The thread io.netty.util.concurrent.FastThreadLocalThread @ 0x30a2197e8 MutationStage-30 keeps local variables with total size 1,064,504 (0.01%) bytes.
Significant stack frames and local variables
org.apache.cassandra.concurrent.SEPWorker.run()V (SEPWorker.java:120)
org.apache.cassandra.concurrent.SEPExecutor @ 0x371d3d790 retains 12,242,915,256 (72.38%) bytes

Any ideas what could have caused this? Thanks, Chris.

Background I have two virtual machines set up on Azure: * Standard E48ds v5 * Standard E16ds v5 Both machines are running SQL Server 2019, and I have a specific query that retrieves data from a view. The body of the view consists solely of heaps (+ 1 small table from different DB). However, I’ve noticed that executing the same query on these two different VM configurations results in significantly different execution plans, and the plan generated by the E16ds v5 appears to be more efficient. Plan on Standard E16ds v5 (query finished in 13 minutes): https://www.brentozar.com/pastetheplan/?id=B11RudaV1x Plan on Standard E48ds v5 (query was running 1h 20 minutes and not finished - live execution plan was retrieved) https://www.brentozar.com/pastetheplan/?id=rytCY_aNyl 1. Why we have such difference ?

[This question](https://dba.stackexchange.com/questions/28370/what-are-valid-usage-scenarios-for-heap-tables) is the highest rated question on this website about heaps. It asks about the use case of heaps. However, I believe that very large heaps (e.g. thousands of gigabytes) are a special case that deserves its own question. As the size of your clustered index grows, so too does the [clustered index penalty](https://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key) i.e. the number of logical reads required to get the remaining keys from your non-clustered indexes. The same is not true of a heap. It is my belief that heaps are unfairly dismissed because too many people either learned all of their database design from data warehouses (where primary key scans, and therefore clustered indexes, are generally a good idea) or have never worked on a database large enough to feel the cost of having a _really_ big table. This gives me my question: **Do well-indexed very large heaps have practical use cases that are not found in well-indexed very large clustered tables?** For fear of making this question too broad, let the following constraints apply. Assume: * A 1 TB heap * A Standard Edition box * An OLTP environment (_Not_ a data warehouse). Cumulatively, this means that you are limited to 128 GB of buffer pool RAM and will not regularly do large scans on the primary key. Reading the whole heap from disk is going to be miserable, so any answers about using the table as a staging or backup-only table should not apply.

I believe I am a little confused about what exactly a database page actually is, and perhaps also database heaps, and how they relate to each other. From my understanding; > A database heap is a table which stores database pages, without using a clustered index. > Pages are more like the physical I/O representation of data. They exist to store records. They have fixed sizes and are thought of as a basic unit of I/O operation. They often contain multiple database rows. I get that rows, (database) pages and (database) heaps are all abstract database concepts (for lack of a better description), but pages appear to be a more special case in that they "bridge" the final gap between the concept of a database as an abstraction of data, and the database and its data as a physical representation on the underlying physical disk. But the fact that this "final" physical "bridge" exists within another abstract database concept is really confusing me. I would have expected this "bridge" to be the most bottom layer of all the database abstraction concepts. Basically the last thing that contains all other database abstractions. I guess the only way I can understand this is if; - There exists another abstraction which persists the remaining database abstractions (what persists heap tables to disk, and whatever contains heap tables?) - Pages also persist the rows of heap tables, somewhere, which means pages persisting pages? Or am I way off in my understanding? I am still learning, so while a technical deep dive is appreciated, a more simplified explanation at this stage would be enough.

I work with an enterprise application (code not available) that currently uses heaps. It uses a terrible pseudo-sequential varchar key that ends up not being sequential due to string sorting not taking length into account (ex. ABCD is sorted after ABC but before BCD). For SELECT performance, we still have covering non-clustered indexes on the key column (among many other non-clustered indexes for other columns). My first question is, given that INSERT, UPDATE, and DELETE (rare) still need to update both the heap and the non-clustered index, is there any performance benefit in using the heap, or would a clustered index (even on this non-sequential key) have the same 'relative' performance? My reasoning is that while, yes, inserts into the heap are faster for non-sequential keys, the non-clustered index will also require updating which would take (I believe) an equivalent time - but at least all of the other benefits of having a clustered index will be available. My second question is what will fragmentation look like with such a pseudo-sequential key? Will SQL-Server continually fragment the clustered index? Or is the fact that inserts are mostly sequential helpful to keep fragmentation low?

I clearly have a misconception, but no amount of consulting documentation has solved it. Consider the following argument: 1. We generally worry about joining to heaps, because sorting them is often more expensive than having a clustered index. 2. Hash joins require no sorts. 3. From the above, we conclude that you shouldn't worry about joining to heaps if you can always get (or force) a hash join. The popular disdain for heaps suggests that the above is wrong, but where is it wrong?

> Inspired by this post: https://twitter.com/#!/SQLChicken/status/102930436795285505 Heaps: Are they considered an index structure or are they strictly a table structure without index?

Database Engines like MS SQL Server , PostgreSQL etc uses HEAP to store table data while using B-tree to store index data. Time complexity for most operations in B-tree is **O(log n)** while for HEAP it is **O(n)**. Even though B-tree is efficient by time factor why do databases still using HEAP(by default) over B-tree to store table data?

I know a HEAP is a table without a clustered index. But I'm not quite clear on the following definition from sys.indexes. I understand that this system view contains row per index of a tabular object. But I get confused when I read, *"contains a row per index or heap."* ## Question Why is the document using the word heap in the following definition, why per index **or** heap? >Contains a row per index or heap of a tabular object. Ref: sys.indexes

I have two clusters setup for failover. However, from some time ago, I keep getting error Exception code: 0xc0000374 Then SQL SERVER failover happens. When failover happens, sometimes the secondary node is able to successfully receive failover. Other times, secondary node is unable to start SQL SERVER with following error: Exception code: 0xc0000374. This is happening almost every day now at random times. Researching the exception code, I found its heap corruption issue. Is there any way to debug or find out the cause of this issue or fix the problem?

Irrespective of the where clause; When table is heap (no clustered indexes) then does a select query make sql server to lock all rows until it has finished reading all the rows? Where as in case of table with clustered index, sql server will lock the rows one by one and release them immediately after that row is read? Any reference links on this topic will be really helpful.

I'm just looking to understand why this is happening, and my Google searches were failing me. We are on SQL Server 2016 SP1. This is the situation: Vendor table that manages IDs by keeping track of current values for each table. A function can be called to return a block of IDs if you are doing an insert. So we set up a temp table by selecting from the real table using select into (we are cloning a set of data to be reinserted with a different property set). Then we call the function and get new ids for the number of records (it just returns the max ID, so we do some math to get the next id). Then we update the table as such: update #temp set @nextId = Id = @nextId + 1 with the expectation that it will increment by one for each record and set the ids. Instead, the same ID was set for every 4 records, then it would increment and the next 4 get the next id, etc. Why every 4 records? What went wrong? Even more fun, if we put a clustered index on the table, everything works correctly. I'm sure it has to do with the table being a heap...but not sure why. Link to plan .

As I understand, a heap is an unordered object. When you insert a record, SQL Server use IAM page to get the pages that belong to this heap, and use PFS page to find a particular one which has enough space to accommodate this record and insert into it. When you create a clustered index on it, it becomes a clustered table and the clustered index itself becomes the table. But as the clustered index and the original heap are two different structures, does SQL Server create a new structure (the clustered index) and moves everything from the help to the new structure and then drops the heap? There are a lot of stuffs that can be defined on a table, like triggers, constrains, permissions etc. If my assumption is true, that means SQL Server also moves all these stuff to the new structure. I didn't find any related information in the documentation. Is my understanding correct?

I have a temp table which I update many times based on joins (each time almost all the rows). I have kept it as a heap since if I don't get an inner loop join then after each update I get a sort in the execution plan. And also the other tables which I join on need to have a unique index if I want to get the correct join (i.e. so I don't get a sort after the join). This answer helps explain the problem: https://dba.stackexchange.com/questions/47134/updating-a-table-efficiently-using-join/47164#47164 This was just an explanation for why the table is a heap. Now my question is: how much do these forwarded records hurt the performance of my SP? Say I have 10 updates and with each update I get more forwarded records. Should I rebuild the heap temp table after say five updates? How can I figure out how many times to rebuild the heap? So basically my question is how to deal with a table that gets updated many times and with each update basically all rows for a particular column get updated. This is a reporting query. If I keep the table has a heap I get extra IO and if I create a clustered index I need to get all the statements to do an inner loop join and even if I achieve this I still will get page splits... I have not tried this yet but I am thinking to change the string columns to fixed length columns in order to avoid forwarded records, but that seems to increase the IO also, at least at first...

On Postgresql 12, given: postgres=# \d bip Table "public.bip" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | not null | Indexes: "foo_idx_a" hash (a) I then ran a explain select ...: postgres=# explain select * from bip where a = 42; QUERY PLAN ------------------------------------------------------------------------- Bitmap Heap Scan on bip (cost=4.10..14.80 rows=13 width=4) Recheck Cond: (a = 42) -> Bitmap Index Scan on foo_idx_a (cost=0.00..4.10 rows=13 width=0) Index Cond: (a = 42) (4 rows) What is the meaning of the Recheck Cond: (a = 42) in the EXPLAIN output?

Problem: -------- A huge table is causing to a critical performance issue on a very critically important production database (MS SQL Server 2016 SP1 Standard Edition). Facts about the problematic Table : ----------------------------------- - Heap Table without index. - 450 GB of Size. - More than 190 million records. - More than 900 MB/s Physical I/O activity every time a simple SELECT Query executes upon. Worst if the query includes WHERE clause. - Queries executed upon this table are causing 4-5 minutes delays on the application UI. Suggested solution by the application/system manufacturer: ---------------------------------------------------------- - Convert the Heap Table to a Clustered Index Table. Suggested performance tuning steps with respect to suggested solution: ---------------------------------------------------------------------- - Truncate db table (agreed with customer to delete records older than 1 year). - Don't Delete Records. Copy records created in last 1 year to a temp table. Truncate the original table, and then INSERT the records you have copied from the temp table to the emptied original table. - Make a de-fragmentation on the db table. - Migrate the db table from heap to clustered table. - Create a Clustered Index. - Create a maintenance job and schedule it to re-build the clustered index periodically. My question to community: ------------------------- How would you improve the performance of such a problematic table? What would you add to or remove from the suggested performance tuning steps? Much appreciated and thanks a lot in advance. Edited from here on: -------------------- OK, I didn't want to reply to all comments 1 by 1. So I'm editing the question and adding some more information. 1st : I am not an expert DBA. I would say an advanced beginner. That's why I am here to learn from the community as much as I can. 2nd : We just got this customer 2 weeks ago, and I have not got full access to the environment myself. This information (facts) about the problem, about the table is directly from the customer's sys admins. 3rd : There for, I have no idea why the table is Heap in the first hand, without an index, and they let it to grow this much without any partitioning and what so ever. 4th : Suggested Solution and Suggested Steps are not my own but directly from the manufacturer of the whole system (it's a well know Unified Communications System and the manufacturer is the best in this area. I guess you all know which one). 5th : I will hopefully have a full access to the environment next week and before that I just wanted to share what I got in hand and try to get as much opinion/suggestion as I could prior to going into this mess. 6th : Because unfortunately I am the one who is going to take this mess from here and try to make it better and smoother working mess. Thanks to everyone who already spare some time and replied. I already got some insight. And I will definitely share more info as soon as I have more in hand.

I have some tables used in reporting and some of them grow huge with the daily ETL so I have implemented som jobs that delete rows more that x days old. E.g. everyday 10% new data gets added and I delete 10% of the oldest rows in another job. Do I need to do something for efficiency? Some tables are heaps with nonclustered indexes and some also have clustered indexes. E.g. to I need to rebuild the tables in case of heaps or rebuild index in case of clustered index on the table? If so how often? Most tables are only used once per day and when all joins and calculations are done they get extracted for visualization in a non-live manner. I know a little about index fragmentation and heap fragmentation and that one can query fragmentation in percent. How much do I need to worry about the above scenario?

What are the benefits of not having a clustered index on a table in SQL Server? Will: SELECT * INTO TABLE_A FROM TABLE_B Be faster if TABLE_A is a heap? Which operation(s) will benefit if the table is a heap? I am quite sure UPDATEs and DELETEs will benefit from a clustered index. What about INSERTs? My understanding is that INSERT "might" benefit from the table being a heap, both in term of speed but also other resources and hardware (I/O, CPU, memory and storage...). What is the most scarce resource in terms of hardware? In terms of storage is a heap going to occupy less space? Is disk storage not the least expensive resource? If so is it rational to keep table as heap in order to save disk space? How will a heap affect CPU and I/O with SELECT, INSERT, UPDATE and DELETE? What cost goes up when table is a heap and we SELECT, UPDATE and DELETE from it?

Recently read that rebuilding a heap online in SQL Server Enterprise is single threaded and does not go parallel. My question is: If you try to rebuild online just non-clustered indexes on a heap table with those also be single threaded or can those go parallel? ### Background I am trying to find the best place to actually test it, but right now I have limited hardware available to me. I am new to the company and I am not advocating a heap, I am trying to steer them towards clustered indexes, but they have random GUIDs everywhere and lots of the developers think clustered indexes will hurt performance. I am trying to educate them on the advantages of using clustered indexes rather than heaps and one of those components is index maintenance. We had one stored procedure that was doing over 27 million logical reads. I went in and looked at fragmentation on one of the key table scans, rebuilt the heap and logical reads went down to 560,000. Just looking at forwarded records and fragmentation I would not have even rebuilt that table through a scripted job. Trying to make them understand if we are gonna keep heaps we need to perform regular maintenance on these things. The cost of this maintenance is higher for rebuilding heaps than having clustered indexes. That is really where my question about single threaded is coming from. Sorry to be long winded but thought would help explain question.