Questions tagged [heap]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
2
votes
1answer
111 views

What does SQL Server use to do a count(*) on a table?

Internally, how does SQL Server determine the result for a select(*)? Is it different between a heap and a clustered index? Is it able to do smart things with the clustered index to not have to load ...
4
votes
1answer
82 views

Huge Heap Table and table compression on SQL Server 2016

My database size is huge, and recently I noticed that a new table added few months ago is the culprit. Here is the table script. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]....
2
votes
2answers
176 views

Postgres heap vs SQL Server clustered index

I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres. Can someone share their ...
2
votes
1answer
276 views

Fragmentation Level for Heaps

I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and ...
0
votes
1answer
166 views

Does the Fill Factor setting affect heaps / heap tables with non-clustered indexes?

I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/ In it, she briefly ...
1
vote
1answer
886 views

Bitmap Heapscan is slow

I have 2 tables: mailings and form_responses. The form_responses table has a foreign key with the mailings table, so a mailing has one-to-many form_responses. What I want to do is calculate mailing ...
8
votes
1answer
625 views

Does RE-BUILDing a HEAP cause downtime?

This is a bit of an embarrassing question, and I can't believe I've missed this for so many years. I have a vendor third-party database that has 401 heap tables. I've recently got into using Brent ...
2
votes
2answers
75 views

in place updates causes forwarded records

I am well aware what a forwarded record within a heap is. Since I want to keep forwarded records at 0, we decided to update only on columns that could not be extended. Recently on my system I ...
-3
votes
1answer
306 views

Fix SQL Table with *BOTH* Heap AND Clustered Index

Somehow, we have a SQL Server Table with both a HEAP and a CLUSTERED index on a table. Is there a way to fix this? e.g. delete the HEAP index by its object ID? If we drop the Clustered Index, it will ...
0
votes
1answer
681 views

MSSQL: High number of logical reads when inserting into fragmented heap

I'm inserting a batch of rows - using INSERT INTO tblUploadBanking SELECT FROM ... - into a table, which is a heap and have 3 narrow NC indexes. When I execute the query from SSMS with SET ...
3
votes
2answers
750 views

DELETE on a heap table

Is there any way to deallocate empty pages on a heap table during a delete operation other than putting an exclusive lock on the table or rebuilding it? None of these options are acceptable for my ...
2
votes
0answers
31 views

How much does a query of deletion costs in terms of operations when the attribute is a key or an attribute?

How much does the following query costs in terms of operations when the attribute is a key or an attribute ? DELETE FROM R WHERE ATTRIBUTE = 50 We assume we have R(K,A,B,other) a relation with ...
4
votes
1answer
65 views

How much does an ordered query of an attribute costs in terms of operations when the attribute is a key or an attribute?

How much does the following query costs in terms of operations when the attribute is a key or an attribute ? SELECT Attribute FROM R WHERE Attribute = 50 ORDER BY Attribute We assume we ...
0
votes
1answer
477 views

Index and Heap table storage on disk

I have a few questions on tables and Indexed tables and its storage, Here is my first image where i have taken an example with Northwind database and Table name is Orders. Orders Table is with 9 ...
1
vote
2answers
411 views

Converting Heap to MyISAM issue on temp table defined as innodb

I was having an issue on the aws aurora brand of mysql 5.6. I was creating temp tables with the memory engine from some selects ... ie create temporary table mytemp table engine memory as select * ...
3
votes
2answers
564 views

SQL Server heap tables fragmentation monitoring

As a SQL Server DBA I have maintenance jobs to regularly rebuild/reorganize indexes. Recently, I ask myself about table fragmentation in SQL Server. Then I have read article regarding heap tables and ...
13
votes
2answers
761 views

Compression on a heap

The follow is a paragraph from Microsoft Docs: New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and ...
7
votes
2answers
1k views

Is heap a table without indexes at all or without a clustered index?

BOL seems to define a heap as a table without a clustered index. But many online posts seem to equate a heap to a table without any kind of indexes. Is there any subtlety I am not aware of? Thanks
9
votes
1answer
9k views

How to lower HEAP Fragmentation in SQL Server?

i recently found out that one heap table had more than 70% fragmentation. So i decided to do a ALTER TABLE dbo.myTable REBUILD Funny enough, afterwards i had 20% fragmentation. There was no write ...
7
votes
3answers
1k views

Would a table benefit if it was a heap

I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always ...
3
votes
1answer
96 views

calculating the total number of rows in a table - fails when it is a heap - is there any fix?

I have a procedure that I use to find out how big is a table. select * from sys.objects where name = 'WMS_SLALoad' -- to get the object_id of my table -- in this case it is 2041058307 SELECT o....
19
votes
4answers
5k views

Should the index on an identity column be nonclustered?

For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column? The reason is other indexes will be created for queries. A query which uses a ...
1
vote
0answers
137 views

Database fragmentation and heap SQL Server

We have critical performance issues with our production database (its around 300 gb size) We have run the following script for a few huge tables : DECLARE @db_id SMALLINT; DECLARE @object_id INT; ...
3
votes
1answer
383 views

Heap having high fragmentation

Does high fragmentation on a heap impact query performance? Can I rebuild the heap with ALTER TABLE REBUILD to get rid of the fragmentation? This is a staging table used for reporting. It does have ...
5
votes
1answer
3k views

Query shows SELECT 0

I'm looking through the queries being executed by a third party application. The application was flagged as being slow by some of our end-users. It seems to INSERT records into a HEAP, then DELETE ...
4
votes
1answer
1k views

Table Data Space Used Confusion

I have a heap table with around 700k rows. The rowsize is 34 bytes: (1x BIGINT, 2x SMALLINT, 2x BIT, 4X INT, 1x SMALLDATETIME). Why does Data Space Used say this table is taking up nearly 4000000KB (...
2
votes
1answer
2k views

Moving a heap between filegroups

I would like to move the tables in my database from one filegroup (Primary) to a another (a new one). I can find lots of advice on this concerning tables with clustered indexes but all these tables in ...
31
votes
3answers
8k views

What are valid usage scenarios for HEAP tables?

I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and ...
6
votes
2answers
1k views

How do I automate converting heaps into clustered indexes?

I have around 40 tables in one of our production databases that, for varying reasons, where not created with a clustered index. What is the best automated method for converting these heaps? Since I'...
4
votes
2answers
2k views

Heap size too large for data present

A SQL Server 2008 R2 Standard Edition database has a staging table with no indexes, eight varchar(50) and one varchar(255) columns, and 162,676 rows. I'd expect that this table shouldn't be much over ...
6
votes
1answer
1k views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
4
votes
1answer
227 views

SQL Server - is it ok to store link table as a heap?

I'm trying to understand if it's a good practice to have non-clustered PK on link table: CREATE TABLE table1_table2(table1_id INT NOT NULL, table2_id INT NOT NULL, CONSTRAINT PK_T1_T2 PRIMARY KEY ...
15
votes
3answers
876 views

Are heaps considered an index structure or are they strictly a table structure without index?

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?