Hypothetical Index for SQL Server Performance Tuning

One of the major difficulties in the process of performance tuning in VLDB is to create index on a table which can drag the tuning process for a long time or in some cases, it does blocking and reject any incoming queries from related applications. basically DBAs create indexes on tables and then compare the query performance, I would like to inform you that expert DBAs or consultants do not create indexes first, they do some fundamental analysis on the query, table(s) and then evaluate the index usage then create the index on the table.
In this post, we would like to introduce you a high level technique. It is called ‘Hypothetical Index’, keep in mind that all the commands mentioned in this post is not officially exposed by Microsoft and you should not use them in your production servers.

ATTENTION: THE WRITER AND FARD SOLUTIONS SDN BHD IS NOT RESPONSIBLE OF ANY DAMAGE BY RUNNING THESE COMMANDS ON YOUR SQL SERVER DATABASES.

Hypothetical index is a kind of index that does not store any data physically except the structure of the index such as index columns, and it is only accessible and use by SQL Server Optimizer Engine, hypothetical indexes can be created with/without statistics.

For the starting point, lets create a table called ‘TempData’ in tempdb database by using following script:-

[codesyntax lang=”tsql”]

Use tempdb;
Go
Create Table dbo.TempData (ID bigint Identity(1,1),Padding binary(8) Default 0xFF, CPadding Char(2) Default 'AA');
Go
Insert Into dbo.TempData Default Values
Go 1000

[/codesyntax]

 

In the next step we need to take a look at the ‘Execution Plans’ of the following queries:-

[codesyntax lang=”tsql”]

Select ID,Padding From dbo.TempData Where ID = 500;
Select ID From dbo.TempData Where ID = 500 ;
Select * from dbo.TempData Where ID = 500;

[/codesyntax]

 

Hypothetical Index Fig 1

As you can observe from above figure, every single query uses ‘Table Scan’ physical operation to retrieve data from ‘TempData’ table, this operation is costly for SQL Server and the query. Now it is time to create proper index to support every single queries, but due to the huge size of the database and ‘TempData’ table, it will take long time to complete. In this situation we are going to use ‘Hypothetical Index’, the following script creates three (3) indexes as FARD_IX, FARD_IX_1 and FARD_CIX on ‘TempData’ table.

[codesyntax lang=”tsql”]

Create Index FARD_IX on dbo.TempData (ID) INCLUDE (PADDING) With Statistics_Only ;
Create Index FARD_IX_1 on dbo.TempData (ID) With Statistics_Only ;
Create Clustered Index FARD_CIX on dbo.TempData (ID) With Statistics_Only ;

[/codesyntax]

 

Creating above indexes only takes less than 1 second (It might be different in your environment) which is much faster than normal indexes. At the next step, we are going to use those indexes to find the optimal ‘Execution Plan’ for our queries.

At this point, we are in need to use ‘DBCC AUTOPILOT’ command to instruct SQL Server Optimizer Engine to use Hypothetical Indexes in the Estimated Execution Plans. By using this command, you will see the use of index before you physically create it on the database.

The following script sets SQL Server Optimizer Engine to use Hypothetical Indexes on ‘TempData’ table in Tempdb database.

[codesyntax lang=”tsql”]

-- DBCC AUTOPILOT (TYPE_ID,DB_ID,OBJECT_ID,INDEX_ID);
DBCC AUTOPILOT(0,2,389576426,4);
DBCC AUTOPILOT(0,2,389576426,5);
DBCC AUTOPILOT(6,2,389576426,6);

[/codesyntax]

 

At the final step, you need to use SET AUTOPILOT ON before and SET AUTOPILOT OFF after queries to view the Estimated Execution Plans.

[codesyntax lang=”tsql”]

SET AUTOPILOT ON;
Go
Select ID,Padding From dbo.TempData Where ID = 500;
Select ID From dbo.TempData Where ID = 500 ;
Select * from dbo.TempData Where ID = 500;
Go
SET AUTOPILOT OFF;

[/codesyntax]

 

Below figure shows the Estimated Execution Plans for every single queries after creating physical indexes, actually we did not create the indexes physically.

Hypothetical Index Fig 3Author: Hamid Jabarpour Fard

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan