Optimization Of Queries In SQL Server - HEAP TABLES

One of the steps to optimize and efficiently retrieve queries in SQL Server is to define the correct indexes. Indexes provide fast access to the result of a query (SELECT).

Before looking at the values of indexes, it is necessary to understand how the indexes affect the tables and based on which algorithm they create the basis for fast retrieval of information from the database.

If we have not explicitly specified an index creation command on the table, with not used constraints (except not null) and Primary key, then it creates special tables called HEAP tables.

HEAP is a form of table without an index and the arrangement of data in the table in an arbitrary way.

Physically, SQL stores data in pages, not tables. The 8 pages together are called an EXTENT, each being 8 kb in size. Pages in HEAP tables are arranged in an arbitrary order. HEAP tables provide data management in the table through IAM (Index Allocation map).

When a new table is created, a new page is not allocated for it. The fact that SQL is based on set theory is precise because data is placed in pages in an arbitrary order. As we add the first row, 2 pages are created automatically. One stores the data and the other is the system table called IAM (Index Allocation MAP). An IAM is a bitmap file that tells which page belongs to which object.

In addition, HEAP tables contain several additional system tables, one of which is called PFS (Page Free SPACE). PFS tells us what percentage of free space is left on the page.

HEAP tables are mainly suitable for tables with small amounts of data. Finding large data creates many problems because HEAP tables provide data search based on "TABLE SCAN", which causes the entire table to be scanned to find only the information in the table. The interest rate of PFS works as follows:

1) The page is empty

2) Page is 1-50% full

3) The page is between 51-80% full.

4) The page is between 81-95% full

5) The page is between 96-100% full.

You may be thinking, what is the point of knowing these things? Let me explain it in simple language. Imagine you have created a new table and added data to the table. Your data is in rows, which are in pages, and pages are in extents. As a result of entering information on the first page, let's imagine that the page is 53% full. But since PFS sees it as 51-80% rather than 53%, if the next input is more than 20% (if 51-80% is full then PFS takes 80% I and measures that the next input is from 100-80=20%) not big) then a new page is created, and 47% of our first existing page is empty!!

The following code example allows us to create a HEAP table, and as a result of the query, we see that the table is indeed organized based on the HEAP algorithm.

CREATE DATABASE OptimizeDb
Go
USE OptimizeDb
GO
CREATE SCHEMA HEAP AUTHORIZATION dbo;
GO

CREATE TABLE HEAP.MyHeapTable
(Id int not null, --without primary key
Name nvarchar(30) not null,
Surname nvarchar(40) not null,
Age tinyint not null,
Email varchar(40) not null)

GO

SELECT OBJECT_NAME(idx.object_id) as 'tableName',
idx.type_desc FROM sys.indexes as idx
WHERE object_id=OBJECT_ID('HEAP.MyHeapTable');

INSERT INTO HEAP.MyHeapTable(Id, Name, Surname,Age,Email)
VALUES(1,'Ichigo','Kurasaki',21,'[email protected]'),
(2,'Soske','Ayzen',37,'[email protected]'),
(3,'Kenpachi','Zaraki',43,'[email protected]');

GO

SELECT * FROM HEAP.MyHeapTable
WHERE Id=3

Our survey provided information based on the "Table Scan" algorithm because it appealed to the Heap TABLE, and the table did not have a specific index. Table Scan reduces speed in search of great data. It should be noted that the creation of indexes does not guarantee that we will reach the data fast. If the SARG (Search Arguments) is not guaranteed correctly, there is not much value in creating indexes.