Hypothetical Index for SQL Server Performance Tuning

The estimated reading time for this post is 3 minutes

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:-

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

 

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

SELECT ID,Padding FROM dbo.TempData WHERE ID = 500;
SELECT ID FROM dbo.TempData WHERE ID = 500 ;
SELECT * FROM dbo.TempData WHERE ID = 500;

 

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.

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 ;

 

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.

-- 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);

 

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

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;

 

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

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

2 Comments on "Hypothetical Index for SQL Server Performance Tuning"

Notify of
avatar
1000
Sort by:   newest | oldest | most voted
Johan Sebastian Max
Guest

your code (DBCC AUTOPILOT(0,2,389576426,4); Where did the number 4 come from ?

wpDiscuz