Hypothetical indexes, making it easy to use…
Hi there, yes, I know, it’s being a while since I wrote something here… So, sorry about that.
Yesterday the guy I’m a big fan (if you already join one of my SQL trainings you know that ) Query Optimizer Guru Paul White (blog|twitter) answered a question on twitter (#sqlhelp hash tag) and he mentioned a post I wrote some time ago about hypothetical indexes on SQL Server. That is a very nice feature but unfortunately is not pretty to use because it requires you to use DBCC AUTOPILOT and set AUTOPILOT to ON…
The parameters you have to use are not straightforward to find and may discourage you to use it… I’ve created a procedure to make it a little easier to use.
Originally I created this procedure after a student question about how to make it easier to use hypothetical indexes on SQL Server. So I thought you may like it.
Unfortunately it relies on a CLR stored procedure to SET the AUTOPILOT, but if you don’t mind to use it in a develop environment (which is something normal to do) then you can use it, following is the CLR code, and if you are interested you can download the project code here:
-- CLR Proc /* using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_GetAutoPilotShowPlan ( SqlString SQL, out SqlXml PlanXML ) { //Prep connection SqlConnection cn = new SqlConnection("Context Connection = True"); //Set command texts SqlCommand cmd_SetAutoPilotOn = new SqlCommand("SET AUTOPILOT ON", cn); SqlCommand cmd_SetAutoPilotOff = new SqlCommand("SET AUTOPILOT OFF", cn); SqlCommand cmd_input = new SqlCommand(SQL.ToString(), cn); if (cn.State != ConnectionState.Open) { cn.Open(); } //Run AutoPilot On cmd_SetAutoPilotOn.ExecuteNonQuery(); //Run input SQL SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd_input; ds.Tables.Add(new DataTable("Results")); ds.Tables[0].BeginLoadData(); da.Fill(ds, "Results"); ds.Tables[0].EndLoadData(); //Run AutoPilot Off cmd_SetAutoPilotOff.ExecuteNonQuery(); if (cn.State != ConnectionState.Closed) { cn.Close(); } //Package XML as output System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument(); //XML is in 1st Col of 1st Row of 1st Table xmlDoc.InnerXml = ds.Tables[0].Rows[0][0].ToString(); System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xmlDoc); PlanXML = new SqlXml(xnr); } }; */
Following is the code to compile it on SQL Server and to create another procedure to simulate the hypothetical indexes:
-- Publishing Assembly IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'CLR_ProjectAutoPilot') BEGIN IF OBJECT_ID('st_CLR_GetAutoPilotShowPlan') IS NOT NULL DROP PROC st_CLR_GetAutoPilotShowPlan DROP ASSEMBLY CLR_ProjectAutoPilot END GO CREATE ASSEMBLY CLR_ProjectAutoPilot FROM 'C:\ProjectAutoPilot\ProjectAutoPilot\bin\Release\ProjectAutoPilot.dll' WITH PERMISSION_SET = SAFE GO CREATE PROCEDURE st_CLR_GetAutoPilotShowPlan (@Query NVarChar(MAX), @ShowPlan XML OUTPUT) AS EXTERNAL NAME CLR_ProjectAutoPilot.StoredProcedures.CLR_GetAutoPilotShowPlan GO IF OBJECT_ID('st_TestHipotheticalIndexes', 'p') IS NOT NULL DROP PROC dbo.st_TestHipotheticalIndexes GO CREATE PROCEDURE dbo.st_TestHipotheticalIndexes (@SQLIndex NVarChar(MAX), @Query NVarChar(MAX)) AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRAN DECLARE @CreateIndexCommand NVarChar(MAX), @IndexName NVarChar(MAX), @TableName NVarChar(MAX), @SQLIndexTMP NVarChar(MAX), @SQLDropIndex NVarChar(MAX), @SQLDbccAutoPilot NVarChar(MAX), @i Int, @QuantityIndex Int, @Xml XML IF SubString(@SQLIndex, LEN(@SQLIndex), 1) <> ';' BEGIN RAISERROR ('Last caracter in the index should be ;', -- Message text. 16, -- Severity. 1 -- State. ); END SET @SQLDropIndex = ''; SET @QuantityIndex = LEN(@SQLIndex) - LEN(REPLACE(@SQLIndex, ';', '')) SELECT @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex)) SET @i = 0 WHILE @i < @QuantityIndex BEGIN SET @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex)) SET @CreateIndexCommand = SUBSTRING(@SQLIndexTMP, 0, CharIndex(' ON ',@SQLIndexTMP)) SET @IndexName = REVERSE(SubString(REVERSE(@CreateIndexCommand), 0, CharIndex(' ', REVERSE(@CreateIndexCommand)))) SET @TableName = SUBSTRING(REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''), 0, CharIndex(' ', REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''))) IF ISNULL(@TableName,'') = '' SET @TableName = SUBSTRING(REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''), 0, CharIndex('(', REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''))) SET @SQLIndex = REPLACE(@SQLIndex, @SQLIndexTMP + ';', '') --SELECT @SQLIndex, @SQLIndexTMP, @CreateIndexCommand, @TableName, @IndexName -- Creating hypotetical index IF CharIndex('WITH STATISTICS_ONLY =', @SQLIndexTMP) = 0 BEGIN SET @SQLIndexTMP = @SQLIndexTMP + ' WITH STATISTICS_ONLY = -1' END -- PRINT @SQLIndexTMP EXEC (@SQLIndexTMP) -- Creating query to drop the hypotetical index SELECT @SQLDropIndex = @SQLDropIndex + 'DROP INDEX ' + @TableName + '.' + @IndexName + '; ' -- PRINT @SQLDropIndex -- Executing DBCC AUTOPILOT SET @SQLDbccAutoPilot = 'DBCC AUTOPILOT (0, ' + CONVERT(VarChar, DB_ID()) + ', '+ CONVERT(VarChar, OBJECT_ID(@TableName),0) + ', ' + CONVERT(VarChar, INDEXPROPERTY(OBJECT_ID(@TableName), @IndexName, 'IndexID')) + ')' EXEC (@SQLDbccAutoPilot) --PRINT @SQLDbccAutoPilot SET @i = @i + 1 END -- Executing Query DECLARE @PlanXML xml EXEC st_CLR_GetAutoPilotShowPlan @Query = @Query, @ShowPlan = @PlanXML OUT SELECT @PlanXML -- Droping the indexes EXEC (@SQLDropIndex) COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN -- Execute error retrieval routine. SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH; END GO
The proc st_TestHipotheticalIndexes expect two input parameters:
- @SQLIndex: Here you should specify the command to create the index you and to try (the hypothetical indexes), if you want to try more than one index, just call it separating many “create index” commands by coma. For instance:
@SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);',
- @Query: Here you should write the query you want to try.
Here is a sample of how to call it on :
-- Sample 1 EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix ON Order_Details(Quantity);', @Query = 'SELECT * FROM Order_Details WHERE Quantity < 1'
The results of the query above is an XML with the query plan considering the suggested index:
Another sample:
— Sample 2
-- Sample 2 EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);', @Query = 'SELECT p.ProductName, p.UnitPrice, s.CompanyName, s.Country, od.quantity FROM Products as P INNER JOIN Suppliers as S ON P.SupplierID = S.SupplierID INNER JOIN order_details as od ON p.productID = od.productid WHERE P.CategoryID in (1,2,3) AND P.Unitprice < 20 AND S.Country = ''uk'' AND od.Quantity < 90'
Yep, now it is easier… Let me know what do you think and please don’t mind on the crappy code in the proc to get the tablename, indexname…
Have fun…