Witch Magic, Snake Oil Medicine, and Spatial Index Tuning: The Geography Version

ThomasColson · ‎12-24-2015

In response to https://community.esri.com/migration-blogpost/54807

which adds some bling and zing to your SDE database, I've gotten a few comments regarding doing the same type of tuning with the Geography Storage type. Easy Cheesy, just note that this alteration simply replaces geometry with geography, and removes the bounding box from the index creation. Happy tuning!

--ORIGINAL SQL CODE FROM  
--http://stackoverflow.com/users/2250424/greengeo  
--MODIFIFIED TO INCLUDE SPATIAL INDEX HELP OUTPUT  
--IN TABLE  
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  
  --LOOK FOR THE EXCLAMATION POINTS IN THIS CODE  
  --THERE ARE SEVERAL USER-SUPPLIED VARIABLES THAT MUST  
  --BE INPUT FOR THIS SP TO WORK  
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  
  
  
USE   [GPS_COLLAR]
GO  
  
  
  
ALTER PROCEDURE dbo.sp_tune_spatial_index  
(  
  @tabnm VARCHAR(MAX), -- This parameter stores the name of the spatial table for which you are tuning the index  
  @idxnm VARCHAR(MAX), -- This parameter stores the name of the spatial index of the named table  
  @min_cells_per_obj INT, -- Minimum Cells Per Object to test on. Suggested to start at 2.  
  @max_cells_per_obj INT, -- Maximum Cells Per Object to test on.  
  
  /* The test requires two geography instances to use in test query 1 and 2.  
  The first one should cover the area of default extent. The second should  
  cover an area roughly the size of the area shown when zoomed in, panning  
  around. It is required that the variable store a string that will create  
  the geography instance since this will be done within the procedure and   
  cannot be a variable of type: GEOGRAPHY. The SRID of these instances must  
  match that of the table you are testing. */  
  @testgeom1 VARCHAR(MAX), -- This parameter stores the first geography instance creation string that will be used in the test  
  @testgeom2 VARCHAR(MAX) -- This parameter stores the second geography instance creation string that will be used in the test  
  
  
  
)  
  
AS  
  
SET NOCOUNT ON;  
  
/* Prior to running this procedure, two tables are required. These tables are   
  created here to prepare for running the procedure. */  
  
PRINT 'Checking for required tables...'  
IF EXISTS(SELECT 1 FROM sysobjects WHERE name IN ('cell_opt_perm', 'spat_idx_test_result'))  
  BEGIN  
  PRINT '... The "cell_opt_perm" and "spat_idx_test_result" tables exist.'  
  END  
ELSE  
BEGIN  
  PRINT '... Creating "cell_opt_perm" and "spat_idx_test_result" tables.'  
  CREATE TABLE cell_opt_perm(  
  [perm_id] [smallint] NOT NULL,  
  [permutation] [nvarchar](4) NOT NULL,  
  [level1] [nvarchar](6) NOT NULL,  
  [level2] [nvarchar](6) NOT NULL,  
  [level3] [nvarchar](6) NOT NULL,  
  [level4] [nvarchar](6) NOT NULL  
  )  
  
  INSERT INTO cell_opt_perm ([perm_id], [permutation], [level1], [level2], [level3], [level4])  
  VALUES (1,'LLLL','LOW','LOW','LOW','LOW'),  
  (2,'LLLM','LOW','LOW','LOW','MEDIUM'),  
  (3,'LLLH','LOW','LOW','LOW','HIGH'),  
  (4,'LLML','LOW','LOW','MEDIUM','LOW'),  
  (5,'LLMM','LOW','LOW','MEDIUM','MEDIUM'),  
  (6,'LLMH','LOW','LOW','MEDIUM','HIGH'),  
  (7,'LLHL','LOW','LOW','HIGH','LOW'),  
  (8,'LLHM','LOW','LOW','HIGH','MEDIUM'),  
  (9,'LLHH','LOW','LOW','HIGH','HIGH'),  
  (10,'LMLL','LOW','MEDIUM','LOW','LOW'),  
  (11,'LMLM','LOW','MEDIUM','LOW','MEDIUM'),  
  (12,'LMLH','LOW','MEDIUM','LOW','HIGH'),  
  (13,'LMML','LOW','MEDIUM','MEDIUM','LOW'),  
  (14,'LMMM','LOW','MEDIUM','MEDIUM','MEDIUM'),  
  (15,'LMMH','LOW','MEDIUM','MEDIUM','HIGH'),  
  (16,'LMHL','LOW','MEDIUM','HIGH','LOW'),  
  (17,'LMHM','LOW','MEDIUM','HIGH','MEDIUM'),  
  (18,'LMHH','LOW','MEDIUM','HIGH','HIGH'),  
  (19,'LHLL','LOW','HIGH','LOW','LOW'),  
  (20,'LHLM','LOW','HIGH','LOW','MEDIUM'),  
  (21,'LHLH','LOW','HIGH','LOW','HIGH'),  
  (22,'LHML','LOW','HIGH','MEDIUM','LOW'),  
  (23,'LHMM','LOW','HIGH','MEDIUM','MEDIUM'),  
  (24,'LHMH','LOW','HIGH','MEDIUM','HIGH'),  
  (25,'LHHL','LOW','HIGH','HIGH','LOW'),  
  (26,'LHHM','LOW','HIGH','HIGH','MEDIUM'),  
  (27,'LHHH','LOW','HIGH','HIGH','HIGH'),  
  (28,'MLLL','MEDIUM','LOW','LOW','LOW'),  
  (29,'MLLM','MEDIUM','LOW','LOW','MEDIUM'),  
  (30,'MLLH','MEDIUM','LOW','LOW','HIGH'),  
  (31,'MLML','MEDIUM','LOW','MEDIUM','LOW'),  
  (32,'MLMM','MEDIUM','LOW','MEDIUM','MEDIUM'),  
  (33,'MLMH','MEDIUM','LOW','MEDIUM','HIGH'),  
  (34,'MLHL','MEDIUM','LOW','HIGH','LOW'),  
  (35,'MLHM','MEDIUM','LOW','HIGH','MEDIUM'),  
  (36,'MLHH','MEDIUM','LOW','HIGH','HIGH'),  
  (37,'MMLL','MEDIUM','MEDIUM','LOW','LOW'),  
  (38,'MMLM','MEDIUM','MEDIUM','LOW','MEDIUM'),  
  (39,'MMLH','MEDIUM','MEDIUM','LOW','HIGH'),  
  (40,'MMML','MEDIUM','MEDIUM','MEDIUM','LOW'),  
  (41,'MMMM','MEDIUM','MEDIUM','MEDIUM','MEDIUM'),  
  (42,'MMMH','MEDIUM','MEDIUM','MEDIUM','HIGH'),  
  (43,'MMHL','MEDIUM','MEDIUM','HIGH','LOW'),  
  (44,'MMHM','MEDIUM','MEDIUM','HIGH','MEDIUM'),  
  (45,'MMHH','MEDIUM','MEDIUM','HIGH','HIGH'),  
  (46,'MHLL','MEDIUM','HIGH','LOW','LOW'),  
  (47,'MHLM','MEDIUM','HIGH','LOW','MEDIUM'),  
  (48,'MHLH','MEDIUM','HIGH','LOW','HIGH'),  
  (49,'MHML','MEDIUM','HIGH','MEDIUM','LOW'),  
  (50,'MHMM','MEDIUM','HIGH','MEDIUM','MEDIUM'),  
  (51,'MHMH','MEDIUM','HIGH','MEDIUM','HIGH'),  
  (52,'MHHL','MEDIUM','HIGH','HIGH','LOW'),  
  (53,'MHHM','MEDIUM','HIGH','HIGH','MEDIUM'),  
  (54,'MHHH','MEDIUM','HIGH','HIGH','HIGH'),  
  (55,'HLLL','HIGH','LOW','LOW','LOW'),  
  (56,'HLLM','HIGH','LOW','LOW','MEDIUM'),  
  (57,'HLLH','HIGH','LOW','LOW','HIGH'),  
  (58,'HLML','HIGH','LOW','MEDIUM','LOW'),  
  (59,'HLMM','HIGH','LOW','MEDIUM','MEDIUM'),  
  (60,'HLMH','HIGH','LOW','MEDIUM','HIGH'),  
  (61,'HLHL','HIGH','LOW','HIGH','LOW'),  
  (62,'HLHM','HIGH','LOW','HIGH','MEDIUM'),  
  (63,'HLHH','HIGH','LOW','HIGH','HIGH'),  
  (64,'HMLL','HIGH','MEDIUM','LOW','LOW'),  
  (65,'HMLM','HIGH','MEDIUM','LOW','MEDIUM'),  
  (66,'HMLH','HIGH','MEDIUM','LOW','HIGH'),  
  (67,'HMML','HIGH','MEDIUM','MEDIUM','LOW'),  
  (68,'HMMM','HIGH','MEDIUM','MEDIUM','MEDIUM'),  
  (69,'HMMH','HIGH','MEDIUM','MEDIUM','HIGH'),  
  (70,'HMHL','HIGH','MEDIUM','HIGH','LOW'),  
  (71,'HMHM','HIGH','MEDIUM','HIGH','MEDIUM'),  
  (72,'HMHH','HIGH','MEDIUM','HIGH','HIGH'),  
  (73,'HHLL','HIGH','HIGH','LOW','LOW'),  
  (74,'HHLM','HIGH','HIGH','LOW','MEDIUM'),  
  (75,'HHLH','HIGH','HIGH','LOW','HIGH'),  
  (76,'HHML','HIGH','HIGH','MEDIUM','LOW'),  
  (77,'HHMM','HIGH','HIGH','MEDIUM','MEDIUM'),  
  (78,'HHMH','HIGH','HIGH','MEDIUM','HIGH'),  
  (79,'HHHL','HIGH','HIGH','HIGH','LOW'),  
  (80,'HHHM','HIGH','HIGH','HIGH','MEDIUM'),  
  (81,'HHHH','HIGH','HIGH','HIGH','HIGH')  
  
  CREATE TABLE spat_idx_test_result(  
  [perm_id] [int] NOT NULL,  
  [num_cells] [int] NOT NULL,  
  [permut] [nvarchar](4) NOT NULL,  
  [g1t1] [bigint] NULL,  
  [g1t2] [bigint] NULL,  
  [g1t3] [bigint] NULL,  
  [g1t4] [bigint] NULL,  
  [g2t1] [bigint] NULL,  
  [g2t2] [bigint] NULL,  
  [g2t3] [bigint] NULL,  
  [g2t4] [bigint] NULL,  
  [PF_EFF][float] NULL,  
  [IF_EFF][float] NULL,  
  [GRIDL1] [int] NULL,  
  [GRIDL2] [int] NULL,  
  [GRIDL3] [int] NULL,  
  [GRIDL4] [int] NULL,  
  [TPIR] [bigint] NULL,  
  [TPIP] [bigint] NULL,  
  [ANOIRPBR] [bigint] NULL,  
  [TNOOCILFQ] [bigint] NULL,  
  [TNOOCIL3FQ] [bigint] NULL,  
  [TNOOCIL4FQ] [bigint] NULL,  
  [TNOOCIL0II] [bigint] NULL,  
  [TNOOCIL4II] [bigint] NULL,  
  [TNOIOIL3FQ] [bigint] NULL,  
  [TNOIOIL4FQ] [bigint] NULL,  
  [ITTCNTLGP] [float] NULL,  
  [INTTTCNTLGP] [float] NULL,  
  [BTTCNTLGP] [float] NULL,  
  [ACPONTLGP] [float] NULL,  
  [AOPG] [float] NULL,  
  [NORSBPF] [bigint] NULL,  
  [NORSBIF] [bigint] NULL,  
  [NOTSFIC] [bigint] NULL,  
  [NORO] [bigint] NULL,  
  [PORNBPF] [float] NULL,  
  [POPFRSBIF] [float] NULL  
  
  
  )  
  
  INSERT INTO dbo.spat_idx_test_result  
  VALUES (0,16,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)  
END  
  
  
/* delete all rows from "spat_idx_test_result" table. This makes it ready to stuff in new results.  
  !!!WARNING!!! if your test was interupted, the table will be cleared out and the test will  
  begin from the beginning. You could try to modify this to start where you left off but  
  I didn't have time and this worked well enough for me. */  
DELETE FROM spat_idx_test_result  
  WHERE perm_id != 0  
  
/* set up counters */  
DECLARE @a1 INT  
DECLARE @a2 INT  
DECLARE @a3 INT  
DECLARE @a4 INT  
  
/* set up variables to hold high/medium/low values and permutation to use in rebuilding  
   the spatial index and recording stats */  
DECLARE @lev1 VARCHAR(6)  
DECLARE @lev2 VARCHAR(6)  
DECLARE @lev3 VARCHAR(6)  
DECLARE @lev4 VARCHAR(6)  
DECLARE @permut VARCHAR(6)  
DECLARE @num_cell VARCHAR(4)  
DECLARE @time_str VARCHAR(20)  
DECLARE @perm_id VARCHAR(20)  
DECLARE @x xml  
DECLARE @pf_eff FLOAT  
  
  
  
/* create variables to hold timestamps for beginning and ending of test queries */  
DECLARE @start_t DATETIME  
DECLARE @end_t DATETIME  
DECLARE @elapse_t INT  
  
  
  
/* begin looping through cell option permutations */  
SET @a1 = @min_cells_per_obj  
WHILE @a1 <= @max_cells_per_obj  
  BEGIN  
  SET @a2 = 1  
  PRINT 'Started Testing for ' +CAST(@a1 AS VARCHAR(10)) +' cells per object'  
  WHILE @a2 < 82  
  BEGIN  
  SELECT @lev1 = level1, @lev2 = level2, @lev3 = level3, @lev4 = level4 FROM cell_opt_perm WHERE perm_id = @a2  
  SET @permut = '''' +(SELECT permutation FROM cell_opt_perm WHERE perm_id = @a2) +''''  
  
  
  EXEC  
  ('  
  CREATE SPATIAL INDEX ' +@idxnm +' ON ' +@tabnm +'   
  (  
  [SHAPE]  
  )  
  USING  GEOGRAPHY_GRID   
  WITH  
  (  
  
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  
  --MAKE SURE YOU EDIT THE BOUNDING BOX TO BE EXACTLY EQUAL TO THE BOUNDING  
  --BOX OF YOUR SPATIAL TABLE   
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  
  
  GRIDS =(LEVEL_1 = ' +@lev1 +' ,LEVEL_2 = ' +@lev2 +' ,LEVEL_3 = ' +@lev3 +' ,LEVEL_4 = ' +@lev4 +' ),   
  CELLS_PER_OBJECT = ' +@a1 +' ,  
  PAD_INDEX  = OFF,  
  SORT_IN_TEMPDB = OFF,  
  DROP_EXISTING = ON,  
  ALLOW_ROW_LOCKS  = ON,  
  ALLOW_PAGE_LOCKS  = ON,  
  FILLFACTOR = 100  
  )  
  ON [PRIMARY]  
  '  
  )  
  
  
  PRINT 'Re-built index to ' +@permut  
  SET @a3 = 1  
  SET @a4 = 1  
  WHILE @a3 < 5  
  BEGIN  
  SET @start_t = GETDATE()  
  EXEC  
  (  
  'CREATE TABLE #tmp_tab (shp GEOGRAPHY)  
  DECLARE @g1 GEOGRAPHY  
  SET @g1 = ' +@testgeom1 +'  
  INSERT #tmp_tab (shp)  
  SELECT  
  r.Shape AS shp  
  FROM  
  ' +@tabnm +' r  
  WHERE  
  r.SHAPE.STIntersects(@g1) = 1  
  DROP TABLE #tmp_tab'  
  )  
  SET @end_t = GETDATE()  
  SET @elapse_t = (SELECT DATEDIFF(MS, @start_t, @end_t))  
  SET @num_cell = CAST(@a1 AS VARCHAR(6))  
  SET @time_str = CAST(@elapse_t AS VARCHAR(20))  
  IF @a3 = 1  
  BEGIN  
  IF (SELECT TOP 1 perm_id FROM spat_idx_test_result) IS NULL  
  BEGIN  
  SET @perm_id = 1  
  
  
  END  
  ELSE  
  BEGIN  
  SET @perm_id = CAST((SELECT MAX(perm_id+1) FROM spat_idx_test_result) AS VARCHAR(20))  
  END  
  
  
  EXEC  
  
  
  (  
  '  
  
  
  INSERT INTO spat_idx_test_result (perm_id, num_cells, permut, g1t' +@a3 +')  
  VALUES (' +@perm_id +', ' +@num_cell +', ' +@permut +', ' +@time_str +')'  
  )  
  END  
  ELSE  
  
  
  EXEC  
  (  
  '  
  
  UPDATE spat_idx_test_result  
  SET  
  num_cells = ' +@num_cell +',  
  permut = ' +@permut +',  
  g1t' +@a3 +' = ' +@time_str +'  
  WHERE perm_id = ' +@perm_id   
  
  
  )  
  SET @a3 = @a3 + 1  
  END  
  WHILE @a4 < 5  
  BEGIN  
  
  SET @start_t = GETDATE()  
  EXEC  
  (  
  'CREATE TABLE #tmp_tab (shp GEOGRAPHY)   
  DECLARE @g2 GEOGRAPHY  
  SET @g2 = ' +@testgeom2 +'  
  INSERT #tmp_tab (shp)  
  SELECT  
  r.Shape AS shp  
  FROM  
  ' +@tabnm +' r  
  WHERE  
  r.SHAPE.STIntersects(@g2) = 1  
  DROP TABLE #tmp_tab'  
  )  
  SET @end_t = GETDATE()  
  SET @elapse_t = (SELECT DATEDIFF(MS, @start_t, @end_t))  
  SET @num_cell = CAST(@a1 AS VARCHAR(6))  
  SET @time_str = CAST(@elapse_t AS VARCHAR(20))  
  EXEC  
  (  
  '  
  DECLARE @geom geography  
  DECLARE @x xml  
  DECLARE @PFVALUE float  
  DECLARE @IFVALUE float  
  DECLARE @GRIDL1VALUE int  
  DECLARE @GRIDL2VALUE int  
  DECLARE @GRIDL3VALUE int  
  DECLARE @GRIDL4VALUE int  
  DECLARE @TPIRVALUE bigint  
  DECLARE @TPIPVALUE bigint  
  DECLARE @ANOIRPBRVALUE bigint  
  DECLARE @TNOOCILFQVALUE bigint  
  DECLARE @TNOOCIL0IIVALUE bigint  
  DECLARE @TNOOCIL4IIVALUE bigint  
  DECLARE @TNOOCIL3FQVALUE bigint  
  DECLARE @TNOOCIL4FQVALUE bigint  
  DECLARE @TNOIOIL3FQVALUE bigint  
  DECLARE @TNOIOIL4FQVALUE bigint  
  DECLARE @ITTCNTLGPVALUE float  
  DECLARE @INTTTCNTLGPVALUE float  
  DECLARE @BTTCNTLGPVALUE float  
  DECLARE @ACPONTLGPVALUE float  
  DECLARE @AOPGVALUE float  
  DECLARE @NORSBPFVALUE bigint  
  DECLARE @NORSBIFVALUE bigint  
  DECLARE @NOTSFICVALUE bigint  
  DECLARE @NOROVALUE bigint  
  DECLARE @PORNBPFVALUE float  
  DECLARE @POPFRSBIFVALUE float  
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  
  --MAKE SURE YOU EDIT GEOGRAPHY VARIABLE BELOW TO REPRESENT A POLYGON  
  --THAT IS WITHIN YOUR BOUNDING BOX  
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  
  SET @geom = geography::STPolyFromText(''POLYGON ((-83.883490 35.440092, -83.896426 35.715280, -83.121432 35.787017, -83.124960 35.510653, -83.883490 35.440092))'', 4269)
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  
  --MAKE SURE YOU SPECIFY THE NAME OF YOUR SPATIAL TABLE  
  --AND THE NAME OF THE SPATIAL INDEX  
  -- IN THE sp_help_spatial_geography_index_xml VARIABLES  
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  
  exec sp_help_spatial_geography_index_xml GRSM_TRAILS , GRSM_TRAILS_IDX , 1, @geom, @x output  
  SET @PFVALUE =  @x.value(''(/Primary_Filter_Efficiency/text())[1]'', ''float'')  
  SET @IFVALUE =  @x.value(''(/Internal_Filter_Efficiency/text())[1]'', ''float'')  
  SET @GRIDL1VALUE =  @x.value(''(/Grid_Size_Level_1/text())[1]'', ''int'')  
  SET @GRIDL2VALUE =  @x.value(''(/Grid_Size_Level_2/text())[1]'', ''int'')  
  SET @GRIDL3VALUE =  @x.value(''(/Grid_Size_Level_3/text())[1]'', ''int'')  
  SET @GRIDL4VALUE =  @x.value(''(/Grid_Size_Level_4/text())[1]'', ''int'')  
  SET @TPIRVALUE =  @x.value(''(/Total_Primary_Index_Rows/text())[1]'', ''bigint'')  
  SET @TPIPVALUE =  @x.value(''(/Total_Primary_Index_Pages/text())[1]'', ''bigint'')  
  SET @ANOIRPBRVALUE =  @x.value(''(/Average_Number_Of_Index_Rows_Per_Base_Row/text())[1]'', ''bigint'')  
  SET @TNOOCILFQVALUE =  @x.value(''(/Total_Number_Of_ObjectCells_In_Level0_For_QuerySample/text())[1]'', ''bigint'')  
  SET @TNOOCIL0IIVALUE =  @x.value(''(/Total_Number_Of_ObjectCells_In_Level0_In_Index/text())[1]'', ''bigint'')  
  SET @TNOOCIL4IIVALUE =  @x.value(''(/Total_Number_Of_ObjectCells_In_Level4_In_Index/text())[1]'', ''bigint'')  
  SET @TNOOCIL3FQVALUE =  @x.value(''(/Total_Number_Of_ObjectCells_In_Level3_For_QuerySample/text())[1]'', ''bigint'')  
  SET @TNOOCIL4FQVALUE =  @x.value(''(/Total_Number_Of_ObjectCells_In_Level4_For_QuerySample/text())[1]'', ''bigint'')  
  SET @TNOIOIL3FQVALUE =  @x.value(''(/Total_Number_Of_Interior_ObjectCells_In_Level3_For_QuerySample/text())[1]'', ''bigint'')  
  SET @TNOIOIL4FQVALUE =  @x.value(''(/Total_Number_Of_Interior_ObjectCells_In_Level4_For_QuerySample/text())[1]'', ''bigint'')  
  SET @ITTCNTLGPVALUE =  @x.value(''(/Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage/text())[1]'', ''float'')  
  SET @INTTTCNTLGPVALUE =  @x.value(''(/Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage/text())[1]'', ''float'')  
  SET @BTTCNTLGPVALUE =  @x.value(''(/Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage/text())[1]'', ''float'')  
  SET @ACPONTLGPVALUE =  @x.value(''(/Average_Cells_Per_Object_Normalized_To_Leaf_Grid/text())[1]'', ''float'')  
  SET @AOPGVALUE =  @x.value(''(/Average_Objects_PerLeaf_GridCell/text())[1]'', ''float'')  
  SET @NORSBPFVALUE =  @x.value(''(/Number_Of_Rows_Selected_By_Primary_Filter/text())[1]'', ''bigint'')  
  SET @NORSBIFVALUE =  @x.value(''(/Number_Of_Rows_Selected_By_Internal_Filter/text())[1]'', ''bigint'')  
  SET @NOTSFICVALUE =  @x.value(''(/Number_Of_Times_Secondary_Filter_Is_Called/text())[1]'', ''bigint'')  
  SET @NOROVALUE =  @x.value(''(/Number_Of_Rows_Output/text())[1]'', ''bigint'')  
  SET @PORNBPFVALUE =  @x.value(''(/Percentage_Of_Rows_NotSelected_By_Primary_Filter/text())[1]'', ''float'')  
  SET @POPFRSBIFVALUE =  @x.value(''(/Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter/text())[1]'', ''float'')  
  
  
  UPDATE spat_idx_test_result  
  SET  
  num_cells = ' +@num_cell +',  
  permut = ' +@permut +',  
  g2t' +@a4 +' = ' +@time_str +',  
  PF_EFF = @PFVALUE,  
  IF_EFF = @IFVALUE,  
  GRIDL1 = @GRIDL1VALUE,  
  GRIDL2 = @GRIDL2VALUE,  
  GRIDL3 = @GRIDL3VALUE,  
  GRIDL4 = @GRIDL4VALUE,  
  TPIR = @TPIRVALUE,  
  TPIP = @TPIPVALUE,  
  ANOIRPBR = @ANOIRPBRVALUE,  
  TNOOCILFQ = @TNOOCILFQVALUE,  
  TNOOCIL0II = @TNOOCIL0IIVALUE,  
  TNOOCIL4II = @TNOOCIL4IIVALUE,  
  TNOOCIL3FQ =  @TNOOCIL3FQVALUE,  
  TNOOCIL4FQ =  @TNOOCIL4FQVALUE,  
  TNOIOIL3FQ = @TNOIOIL3FQVALUE,  
  TNOIOIL4FQ = @TNOIOIL4FQVALUE,  
  ITTCNTLGP = @ITTCNTLGPVALUE,  
  INTTTCNTLGP = @INTTTCNTLGPVALUE,  
  BTTCNTLGP = @BTTCNTLGPVALUE,  
  ACPONTLGP = @ACPONTLGPVALUE,  
  AOPG = @AOPGVALUE,  
  NORSBPF = @NORSBPFVALUE,  
  NORSBIF = @NORSBIFVALUE,  
  NOTSFIC = @NOTSFICVALUE,  
  NORO = @NOROVALUE,  
  PORNBPF = @PORNBPFVALUE,  
  POPFRSBIF = @POPFRSBIFVALUE  
  WHERE perm_id = ' +@perm_id  
  )  
  SET @a4 = @a4 + 1  
  END  
  SET @a2 = @a2 + 1  
  END  
  SET @a1 = @a1 + 1  
  
  END  
PRINT 'Testing of ' +@tabnm +' spatial index: ' +@idxnm +' is complete!'  
GO

And execute like so:

DECLARE @BOUNDING VARCHAR(MAX)   
SET @BOUNDING = 'geography::STPolyFromText(''POLYGON ((-83.883490 35.440092, -83.896426 35.715280, -83.121432 35.787017, -83.124960 35.510653, -83.883490 35.440092))'', 4269)'  
  
DECLARE @QUERY VARCHAR(MAX)   
SET @QUERY = 'geography::STPolyFromText(''POLYGON ((-83.883490 35.440092, -83.896426 35.715280, -83.121432 35.787017, -83.124960 35.510653, -83.883490 35.440092))'', 4269)'    
  
EXEC sp_tune_spatial_index 'GRSM_TRAILS', 'GRSM_TRAILS_IDX', 4096, 4096, @BOUNDING, @QUERY   
GO