Basic concepts of Performance Tuning

6 min readApr 1, 2020

In this article, we discussed the basic important concepts of performance tuning.

Unique key improve table scan performance

The table scan scans the records row by row until it finds the records. After finding the records, It gives to the user.
Unique keys improve table scan performance also logical reads are decreased when we created a unique key. Every table should have unique keys (Primary Key or Unique Key).
Logical or Physical operator associated with unique keys always the performance increases. The performance will increase when there is a unique key.

Choose a table scan for small records and seek-scan for numerous records.

TABLE SCAN:-

The table scan scans the records row by row. So it can be difficult to search for numerous records.

SEEK SCAN:-

The Seek-scan works on the index it uses the B-Tree structure to search the value.
When we use seek-scan on a small table then performance will decrease because searching is difficult in B-Tree structure.

B-TREE:-

Indexes are used to retrieve data from the database by using B-Tree to make our search faster.

If data from 1 to 50 and we want to search 44. The B-Tree divided data into two sections that are 1 to 30 and 31 to 50.
Table scan scans row by row until it finds 44 and gets the value. It scans 43 records.
The seek-scan scans the value 44 is greater than 30 therefore, the first section 1 to 30 is skipped only 31 to 50 is searched and gets the value. That’s why seek-scan good when table/records are huge.

Use covering index to reduce RID (Row Identifier) lookup

A RID is a lookup it a physical operator. The lookup occurs when a non clustered index created on a table.

INDEX:-

CLUSTERED INDEX:

The Leaf node of a clustered index has actual records.
When we create a clustered index on a table then the row id (RID) of non clustered index points to the clustered index key and by using key search the value.

NON-CLUSTERED INDEX:

The Leaf node of the Non-clustered index does not point to the actual data. It points to the leaf node of the clustered index and then fetches the records.
If a table has a non-clustered index, then row id (RID) points to the heap table. When the table does not have indexes that table termed as heap table. Row id (RID) points to heap table so if we search value 5 it will go to heap table and search row by row finally get the value.

ELIMINATE RID/KEY LOOKUP:

The problem is in the lookup jump. The non-clustered index leaf node contains the row id(RID) and this RID needs to jump to the heap table or the clustered index key. This jump requires additional effort and hence it decreases the performance of our query.
RID lookup eliminated by using a covering index. There are two ways of creating a covering index.

COMPOSITE KEYS:

A key that has more than one attribute is known as a composite key.
In Composite key, we join clustered and non clustered keys so the data of clustered keys included in non-clustered keys and lookup is eliminated.

INCLUDE KEYWORD:

Include is the best way of covering index because:-

We cannot create the composite key on data types such as varchar(max), XML. So if we have huge data types, we cannot create a composite index.
The size of the index increases the key size which further impacts performance.
It can exceed the constraint of the index key size of 900 bytes.

Keep index size as small as possible

How SQL Server stores the data?

The top-level have MDF and LDF file that is physical files. These files divided into Extent.
Extent is a logical grouping of pages so extent is divide into pages inside pages is our actual data row.
Pages are a fundamental unit of storage. Pages are the actual objects in which our data or SQL is stored.
The page is approximately 8 KB in size. The table has 100 rows so the first 50 rows may move into Page 1 and the second 50 rows go to Page no 2 depending on how much is row size. That means 50 rows fit into 8 KB pages.
The table has 100 records and the total size of 100 records is 8 KB. So SQL Server browses that page and browses that records. It doesn’t have to jump on other pages.
These 100 records are pushed into two different pages that SQL Server has to make the jump between to fetch the records.
Whenever we create an index of int data type that means the pages required will be less the time of increases the size of our index.

When we choose int data type the number of pages is less and when we choose decimal/numeric data types the SQL Server pages are increases also increases of performance.
In fig. shows that whenever we created an index on int data type as compare to decimal data type number of pages is increased and because the number of the page increased the SQL Server engine has to make an extra jump between pages to fetch data.

Use numeric as compared to text data type

Arithmetic searches are faster than compared to string searches.
If we demonstrate on data type then we will see the logical reads are less when we use int data type.
When we set data type as nvarchar then logical reads are more than compare to the int data type.

Use indexed view for aggregated SQL Queries

INDEXED VIEW:

The indexed view is a virtual table that represents the output of a select statement.
A table is a persistence. The result set is persisted on the hard disk which can save a lot of overheads.

The data is persistence so the SQL engine does not have again and again gets data from the base table. It takes data from indexed virtual tables and gives to end-users because of this we save a lot of overheads.
In SQL Server creates a table and do the following steps and compare all the readings then conclude that what situation indexed views are good and bad.

Use index views when where our table is not transactional and we have a lot of aggregation, summation information we want to display.
Do not use indexed views when the base table has high transactions probably use a clustered index.

If you are new to SQL Server start with the following must-watch video:

Written by Vaishali