Heaps in SQL Server: Part 3 Nonclustered Indexes - Simple Talk

Heaps in SQL Server: Part 3 Nonclustered Indexes - Simple Talk

Uwe Ricken continues his series on heaps. This time he demonstrates a common scenario where the query against a heap is faster than a clustered index.

The series so far:

  1. Heaps in SQL Server: Part 1 The Basics
  2. Heaps in SQL Server: Part 2 Optimizing Reads
  3. Heaps in SQL Server: Part 3 Nonclustered Indexes

The previous article Heaps in SQL Server: Part 2 Optimizing Reads described how performance could be optimized for selecting data from a heap. This article describes the possibility of achieving optimal query times for heaps with the help of nonclustered indexes.

Nonclustered Index

A nonclustered index is an index structure that is separate from the data in the table. In this way, data can be found faster than with a search of the underlying table. In general, nonclustered indexes are created to improve the performance of frequently used queries that are not covered by the clustered index or heap.

Demonstration

Since a heap does not sort data according to a key attribute, a nonclustered index can only form a reference by using the position of the data record in the heap. The position of a data record in a heap is determined by three pieces of information:

  • File number
  • Data page
  • Slot

These three pieces of information are stored as a reference in each Nonclustered index for the actual key of the index.

To follow along with the demos, see the article Heaps in SQL Server: Part 2 Optimizing Reads. A table with approximately 4,000,000 data records is used.

This table is very often accessed by users to display orders for a specific period. Since there is no index on the [OrderDate] attribute, a table scan must always be carried out.

SELECT * FROM dbo . CustomerOrderList

WHERE OrderDate = '20081220'

OPTION ( QUERYTRACEON 9130 );

GO

A table scan is used, as shown in the execution plan in Figure 1.

Figure 1: A TABLE SCAN is used for 208 records

The table scan may be sufficient on fast systems (0.716 seconds), and the programmer might be inclined to accept this time interval.

Figure 2: more than 1.6 seconds used on the CPU and 0.716 seconds elapsed

The high CPU time is due to the fact that the query uses parallel execution shown in Figure 3.

Figure 3: A parallel plan

Unfortunately, the following things are often disregarded, which are relevant regardless of the time:

  • The query parallelizes and consumes the CPUs configured for MAXDOP!
  • A [SCH-S] lock is kept on the table during the runtime! (See this article)
  • What happens if not only one user runs the query, but there is also a web client running thousands of queries in parallel?

For the reasons mentioned above, it is advisable to optimize the query. To optimize the query, a Nonclustered index is created for the [OrderDate] attribute.

CREATE NONCLUSTERED INDEX nix_CustomerOrderList_OrderDate

ON dbo . CustomerOrderList ( OrderDate );

GO

If you rerun the same query as in the previous example, you will get the following improvements shown in Figure 4:

development homepage sql prompt sql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

How long does it take to develop/build an app?

This article covers A-Z about the mobile and web app development process and answers your question on how long does it take to develop/build an app.

Developer Career Path: To Become a Team Lead or Stay a Developer?

For a developer, becoming a team leader can be a trap or open up opportunities for creating software. Two years ago, when I was a developer, ... by Oleg Sklyarov, Fullstack Developer at Skyeng company

Welcome Back the T-SQL Debugger with SQL Complete – SQL Debugger

Debug SQL stored procedures and develop your SQL database project with dbForge SQL Complete, a new add-in for Visual Studio and SSMS. When you develop large chunks of T-SQL code with the help of the SQL Server Management Studio tool, it is essential to test the “Live” behavior of your code by making sure that each small piece of code works fine and being able to allocate any error message that may cause a failure within that code.

Hire Dedicated SQL Developer

Looking for an SQL Developer to process your data? **[Hire Dedicated SQL Developer](https://hourlydeveloper.io/hire-dedicated-sql-developer/ "Hire Dedicated SQL Developer")** from **[HourlyDeveloper.io](https://hourlydeveloper.io/...