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;
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.
Author: Hamid Jabarpour Fard