locked
How to display limited properties from output of sp_help_spatial_geometry_index? RRS feed

  • Question

  • I am using https://msdn.microsoft.com/en-us/library/cc627425.aspx as such:

    DECLARE @geom geometry
    SET @geom = GEOMETRY::STGeomFromText('POLYGON ((247804.201 3943957.896, 29932.568 3943963.210, 247671.344 3942876.441, 247684.630 3943652.325,247804.201 3943957.896))', 26917)
    exec sp_help_spatial_geometry_index 'TEST_GEOM', 'S1169_idx', 1, @geom

    which returns the obvious:

    propname	propvalue
    Base_Table_Rows	1950
    Bounding_Box_xmin	227166.13
    Bounding_Box_ymin	3925740.74
    Bounding_Box_xmax	314851.6915
    Bounding_Box_ymax	3968047.64
    Grid_Size_Level_1	16
    Grid_Size_Level_2	16
    Grid_Size_Level_3	16
    Grid_Size_Level_4	16
    Cells_Per_Object	4097
    Total_Primary_Index_Rows	4230
    Total_Primary_Index_Pages	15
    Average_Number_Of_Index_Rows_Per_Base_Row	2
    Total_Number_Of_ObjectCells_In_Level0_For_QuerySample	1
    Total_Number_Of_ObjectCells_In_Level0_In_Index	2
    Total_Number_Of_ObjectCells_In_Level3_For_QuerySample	6
    Total_Number_Of_ObjectCells_In_Level4_For_QuerySample	358
    Total_Number_Of_ObjectCells_In_Level4_In_Index	4228
    Total_Number_Of_Interior_ObjectCells_In_Level3_For_QuerySample	6
    Total_Number_Of_Interior_ObjectCells_In_Level4_For_QuerySample	229
    Total_Number_Of_Intersecting_ObjectCells_In_Level4_For_QuerySample	129
    Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index	4228
    Total_Number_Of_Border_ObjectCells_In_Level0_For_QuerySample	1
    Total_Number_Of_Border_ObjectCells_In_Level0_In_Index	2
    Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage	0.0
    Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage	0.0
    Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage	0.0
    Average_Cells_Per_Object_Normalized_To_Leaf_Grid	0.0
    Average_Objects_PerLeaf_GridCell	0.0
    Number_Of_SRIDs_Found	1
    Width_Of_Cell_In_Level1	21921.390375
    Width_Of_Cell_In_Level2	5480.34759375
    Width_Of_Cell_In_Level3	1370.0868984375
    Width_Of_Cell_In_Level4	342.521724609375
    Height_Of_Cell_In_Level1	10576.725
    Height_Of_Cell_In_Level2	2644.18124999999
    Height_Of_Cell_In_Level3	661.045312499999
    Height_Of_Cell_In_Level4	165.261328125
    Area_Of_Cell_In_Level1	231856517.614021
    Area_Of_Cell_In_Level2	14491032.3508763
    Area_Of_Cell_In_Level3	905689.521929771
    Area_Of_Cell_In_Level4	56605.5951206107
    CellArea_To_BoundingBoxArea_Percentage_In_Level1	6.25
    CellArea_To_BoundingBoxArea_Percentage_In_Level2	0.390625
    CellArea_To_BoundingBoxArea_Percentage_In_Level3	0.0244140625
    CellArea_To_BoundingBoxArea_Percentage_In_Level4	0.00152587890625
    Number_Of_Rows_Selected_By_Primary_Filter	51
    Number_Of_Rows_Selected_By_Internal_Filter	32
    Number_Of_Times_Secondary_Filter_Is_Called	19
    Number_Of_Rows_Output	43
    Percentage_Of_Rows_NotSelected_By_Primary_Filter	97.3846153846154
    Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter	62.7450980392157
    Internal_Filter_Efficiency	74.4186046511628
    Primary_Filter_Efficiency	84.3137254901961

    What I'd like to do, is retrieve ONLY Primary_Filter_Efficiency, reason, I'm testing some permutations of Grid Levels and Cells Per Object, and I'd like to write Primary_Filter_Efficiency to a results table each time I test the spatial index. Is there a way to execute sp_help_spatial_geometry_index so that it ONLY returns Primary_Filter_Efficiency?

    Monday, July 6, 2015 6:31 PM

Answers

  • No. You can get a more limited subset of properties by using 0 instead of 1 as the third parameter, but those are your two choices.

    If you want a single of a few properties, the easiest way would be to use the variant that returns XML and then use XQuery to get the exact properties you want. I used to have some code to do this and store each iteration's relevant properties (along with the values of the tuning parameters used for that run) in a temp or permanent table, but it's really trivial code to write.   

    BTW, based on your example output, don't forget to look at the internal filter efficiency too. Although there is an expense to do the key lookup per-row (which happens even if the internal filter will be used), the internal filter saves you from doing the actual STIntersects, which is a big deal, and also something that the estimated query plan iterators tend to underestimate (i.e. it's a bigger deal than the query optimizer thinks it is). Of course, the query optimizer can get some estimated numbers that are wildly off for spatial index plans, as it has no way to know how good your spatial index is in each case.

    If you're using polygons (it looks like you are) don't forget to choose a number of different query samples, chosen to reflect the range of STArea values of the polygons.

    Hope this helps, Cheers, Bob

    • Marked as answer by tpcolson Monday, July 6, 2015 8:32 PM
    Monday, July 6, 2015 8:03 PM

All replies

  • No. You can get a more limited subset of properties by using 0 instead of 1 as the third parameter, but those are your two choices.

    If you want a single of a few properties, the easiest way would be to use the variant that returns XML and then use XQuery to get the exact properties you want. I used to have some code to do this and store each iteration's relevant properties (along with the values of the tuning parameters used for that run) in a temp or permanent table, but it's really trivial code to write.   

    BTW, based on your example output, don't forget to look at the internal filter efficiency too. Although there is an expense to do the key lookup per-row (which happens even if the internal filter will be used), the internal filter saves you from doing the actual STIntersects, which is a big deal, and also something that the estimated query plan iterators tend to underestimate (i.e. it's a bigger deal than the query optimizer thinks it is). Of course, the query optimizer can get some estimated numbers that are wildly off for spatial index plans, as it has no way to know how good your spatial index is in each case.

    If you're using polygons (it looks like you are) don't forget to choose a number of different query samples, chosen to reflect the range of STArea values of the polygons.

    Hope this helps, Cheers, Bob

    • Marked as answer by tpcolson Monday, July 6, 2015 8:32 PM
    Monday, July 6, 2015 8:03 PM
  • Thanks Bob, it seems XML was the trick: 

    DECLARE @geom geometry
    DECLARE @x xml
    DECLARE @VALUE float;
    SET @geom = GEOMETRY::STGeomFromText('POLYGON ((247804.201 3943957.896, 29932.568 3943963.210, 247671.344 3942876.441, 247684.630 3943652.325,247804.201 3943957.896))', 26917)
    exec sp_help_spatial_geometry_index_xml 'TEST_GEOM', 'S1169_idx', 1, @geom, @x output
    SET @VALUE = @x.value('(/Primary_Filter_Efficiency/text())[1]', 'float')
    PRINT @VALUE

    Right now I'm just trying to get this to work: I'm trying to alter  https://gist.github.com/anonymous/5322650 to write sp_help_spatial_geometry_index_xml to the output table. I intend to use most, if not all, of the sp_help_spatial_geometry_index_xml output parameters, but for now, I just want to get  Primary_Filter_Efficiency working. Here's what I have so far, but I'm convinced I'm putting the exec sp in the wrong place, as the output table doesn't correlate to truth:

    USE GRSM
    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 geometry 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 geometry instance since this will be done within the procedure and 
    		cannot be a variable of type: GEOMETRY. The SRID of these instances must
    		match that of the table you are testing. */
    	@testgeom1			VARCHAR(MAX),	-- This parameter stores the first geometry instance creation string that will be used in the test
    	@testgeom2			VARCHAR(MAX),	-- This parameter stores the second geometry instance creation string that will be used in the test
    	@geom1			    GEOMETRY	-- This parameter stores the first geometry 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
    	)
    	
    	INSERT INTO dbo.spat_idx_test_result
    	VALUES (0,16,0,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  GEOMETRY_GRID 
    						WITH
    							(
    								BOUNDING_BOX =(227166.13, 3925740.74, 314851.6915, 3968047.64),
    								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
    				EXEC sp_help_spatial_geometry_index_xml @tabnm, @idxnm, 1, @geom1, @x output
    				SET @pf_eff = @x.value('(/Primary_Filter_Efficiency/text())[1]', 'float')
    				
    				SET @a3 = 1
    				SET @a4 = 1
    				WHILE @a3 < 5
    					BEGIN
    						SET @start_t = GETDATE()
    
    						EXEC
    							(
    								'CREATE TABLE #tmp_tab (shp GEOMETRY)
    								DECLARE @g1 GEOMETRY
    								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))
    						SET @pf_eff = @x.value('(/Primary_Filter_Efficiency/text())[1]', 'float')
    						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 +',PF_EFF)
    										VALUES (' +@perm_id +', ' +@num_cell +', ' +@permut +', ' +@time_str +', ' +@pf_eff +')'
    									)
    							END
    						ELSE
    
    							EXEC
    								(
    									'UPDATE spat_idx_test_result
    									SET
    									num_cells = ' +@num_cell +',
    									permut = ' +@permut +',
    									g1t' +@a3 +' = ' +@time_str +',
    									PF_EFF = ' +@pf_eff +'
    									WHERE perm_id = ' +@perm_id 
    									
    									
    								)
    						SET @a3 = @a3 + 1
    					END
    				WHILE @a4 < 5
    					BEGIN
    						SET @start_t = GETDATE()
    						EXEC
    							(
    								'CREATE TABLE #tmp_tab (shp GEOMETRY) 
    								DECLARE @g2 GEOMETRY
    								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
    							(
    								'UPDATE spat_idx_test_result
    								SET
    								num_cells = ' +@num_cell +',
    								permut = ' +@permut +',
    								g2t' +@a4 +' = ' +@time_str +'
    								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

    Executing 

    DECLARE @BOUNDING VARCHAR(MAX) 
    SET @BOUNDING = 'GEOMETRY::STGeomFromText(''POLYGON ((225360.839 3969589.277, 313459.038 3968248.203 , 317275.939 3922032.754, 224638.723 3927397.048,225360.839 3969589.277))'', 26917)'
    
    DECLARE @QUERY VARCHAR(MAX) 
    SET @QUERY = 'GEOMETRY::STGeomFromText(''POLYGON ((247804.201 3943957.896, 249932.568 3943963.210, 247671.344 3942876.441, 247684.630 3943652.325,247804.201 3943957.896))'', 26917)'
    
    DECLARE @geom1 GEOMETRY
    SET @geom1 = GEOMETRY::STGeomFromText('POLYGON ((247804.201 3943957.896, 249932.568 3943963.210, 247671.344 3942876.441, 247684.630 3943652.325,247804.201 3943957.896))', 26917)
    
    EXEC sp_tune_spatial_index 'TEST_GEOM', 'S1169_idx', 4096, 4097, @BOUNDING, @QUERY, @geom1
    GO

    Results in

    perm_id	num_cells	permut	g1t1	g1t2	g1t3	g1t4	g2t1	g2t2	g2t3	g2t4	PF_EFF
    0	16	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
    1	4096	LLLL	63	53	83	73	16	6	6	6	90.4762
    2	4096	LLLM	103	96	100	93	30	13	10	13	93.1373
    3	4096	LLLH	130	96	96	96	30	13	13	13	97.9381
    4	4096	LLML	103	86	110	90	30	10	10	10	93.1373
    5	4096	LLMM	120	110	93	93	30	13	13	13	97.9381
    6	4096	LLMH	133	106	103	96	36	20	20	20	100
    7	4096	LLHL	116	116	110	113	26	10	10	13	97.9381
    8	4096	LLHM	130	120	113	133	33	16	16	16	100
    9	4096	LLHH	120	100	126	140	43	26	23	23	100
    10	4096	LMLL	96	96	93	93	26	13	10	10	93.1373
    11	4096	LMLM	123	110	100	100	30	13	13	13	97.9381
    12	4096	LMLH	100	116	113	126	36	20	23	23	100
    13	4096	LMML	106	86	96	96	26	10	13	10	97.9381
    14	4096	LMMM	116	93	100	133	30	16	16	16	100
    15	4096	LMMH	240	166	153	130	40	23	23	20	100
    16	4096	LMHL	113	120	103	103	30	13	16	16	100
    17	4096	LMHM	123	130	123	143	43	23	20	16	100
    18	4096	LMHH	176	176	196	176	46	30	30	30	100
    19	4096	LHLL	126	100	83	83	30	13	13	10	97.9381
    20	4096	LHLM	110	116	106	103	33	16	16	20	100
    21	4096	LHLH	143	116	130	126	40	23	23	20	100
    22	4096	LHML	110	110	106	106	30	16	16	16	100
    23	4096	LHMM	143	133	130	140	43	26	23	23	100
    24	4096	LHMH	150	186	163	170	43	26	23	26	100
    25	4096	LHHL	150	130	113	133	43	23	26	26	100
    26	4096	LHHM	146	193	183	180	46	30	30	33	100
    27	4096	LHHH	236	260	246	246	50	36	33	33	97.9381
    28	4096	MLLL	96	76	86	93	26	10	10	13	93.1373
    29	4096	MLLM	120	116	106	93	26	10	13	13	97.9381
    30	4096	MLLH	120	113	123	120	36	23	20	20	100
    31	4096	MLML	103	96	86	103	30	10	10	10	97.9381
    32	4096	MLMM	136	130	136	116	33	20	16	16	100
    33	4096	MLMH	103	136	183	140	40	23	23	20	100
    34	4096	MLHL	130	113	116	110	30	16	16	16	100
    35	4096	MLHM	120	136	110	123	40	26	23	23	100
    36	4096	MLHH	173	153	150	183	43	26	26	30	100
    37	4096	MMLL	126	110	100	93	26	10	13	13	97.9381
    38	4096	MMLM	140	106	103	113	33	13	16	20	100
    39	4096	MMLH	150	123	143	136	40	23	23	23	100
    40	4096	MMML	136	110	110	123	33	16	16	16	100
    41	4096	MMMM	116	140	110	120	36	23	23	23	100
    42	4096	MMMH	186	200	193	173	43	30	26	26	100
    43	4096	MMHL	143	153	143	133	43	26	26	30	100
    44	4096	MMHM	183	186	170	186	46	30	26	26	100
    45	4096	MMHH	236	260	243	250	50	36	33	33	97.9381
    46	4096	MHLL	133	123	106	120	33	20	20	20	100
    47	4096	MHLM	110	130	130	156	40	23	26	23	100
    48	4096	MHLH	183	183	190	203	43	26	23	26	100
    49	4096	MHML	133	143	123	153	43	23	26	26	100
    50	4096	MHMM	183	180	183	180	46	30	33	30	100
    51	4096	MHMH	216	250	276	243	53	33	30	33	97.9381
    52	4096	MHHL	170	166	170	190	50	36	33	36	100
    53	4096	MHHM	250	256	253	256	56	33	33	36	100
    54	4096	MHHH	410	283	266	273	36	33	36	33	100
    55	4096	HLLL	100	100	100	110	23	10	6	10	97.9381
    56	4096	HLLM	120	126	113	126	53	16	16	16	100
    57	4096	HLLH	143	160	136	140	40	26	23	23	100
    58	4096	HLML	113	130	116	103	30	16	16	16	100
    59	4096	HLMM	130	136	123	126	40	23	23	20	100
    60	4096	HLMH	190	183	190	180	46	26	26	23	100
    61	4096	HLHL	110	130	136	133	43	30	26	30	100
    62	4096	HLHM	186	173	176	160	43	26	30	30	100
    63	4096	HLHH	220	250	236	236	43	30	33	33	97.9381
    64	4096	HMLL	146	106	113	116	30	16	16	16	100
    65	4096	HMLM	163	133	140	140	40	26	26	23	100
    66	4096	HMLH	193	176	170	193	43	26	26	26	100
    67	4096	HMML	153	143	126	133	43	26	26	26	100
    68	4096	HMMM	163	176	193	180	50	30	30	30	100
    69	4096	HMMH	246	246	266	250	50	33	33	33	97.9381
    70	4096	HMHL	193	190	166	173	53	36	30	33	100
    71	4096	HMHM	266	246	280	246	53	43	36	40	100
    72	4096	HMHH	386	396	386	393	63	50	40	46	100
    73	4096	HHLL	120	126	136	133	46	30	26	23	100
    74	4096	HHLM	180	140	150	143	30	20	16	20	100
    75	4096	HHLH	230	236	256	260	50	33	30	33	97.9381
    76	4096	HHML	166	176	193	183	50	33	33	30	100
    77	4096	HHMM	253	263	270	256	56	40	33	36	100
    78	4096	HHMH	386	380	396	270	40	33	36	36	100
    79	4096	HHHL	190	260	253	236	60	43	43	43	100
    80	4096	HHHM	366	420	390	383	60	43	36	40	100
    81	4096	HHHH	560	476	483	476	73	50	53	50	100
    82	4097	LLLL	96	96	106	106	30	13	13	13	90.4762
    83	4097	LLLM	100	86	76	90	26	10	10	13	93.1373
    84	4097	LLLH	130	103	100	113	30	16	13	13	97.9381
    85	4097	LLML	116	96	73	93	26	10	10	13	93.1373
    86	4097	LLMM	110	93	93	80	26	13	13	13	97.9381
    87	4097	LLMH	100	106	100	110	36	23	20	20	100
    88	4097	LLHL	103	103	96	100	30	13	13	13	97.9381
    89	4097	LLHM	113	123	126	136	33	16	13	16	100
    90	4097	LLHH	153	140	146	123	40	23	23	23	100
    91	4097	LMLL	93	100	120	103	26	10	10	10	93.1373
    92	4097	LMLM	120	86	96	120	26	13	13	13	97.9381
    93	4097	LMLH	130	93	100	106	36	20	23	20	100
    94	4097	LMML	83	66	90	63	26	13	16	13	97.9381
    95	4097	LMMM	90	80	86	80	20	10	13	10	100
    96	4097	LMMH	113	146	126	113	40	26	23	23	100
    97	4097	LMHL	160	190	103	126	33	13	13	13	100
    98	4097	LMHM	120	133	126	126	43	23	20	26	100
    99	4097	LMHH	186	166	186	200	43	30	26	26	100
    100	4097	LHLL	86	96	76	86	26	10	13	10	97.9381
    101	4097	LHLM	120	106	100	113	30	16	16	16	100
    102	4097	LHLH	110	120	133	116	40	23	23	23	100
    103	4097	LHML	160	113	100	103	30	16	16	16	100
    104	4097	LHMM	123	110	103	86	23	13	13	13	100
    105	4097	LHMH	213	180	220	173	46	30	26	26	100
    106	4097	LHHL	136	123	166	126	43	30	26	26	100
    107	4097	LHHM	206	136	126	163	46	30	30	30	100
    108	4097	LHHH	240	253	193	253	53	33	30	33	97.9381
    109	4097	MLLL	86	106	100	96	26	10	10	10	93.1373
    110	4097	MLLM	116	83	103	90	30	13	13	13	97.9381
    111	4097	MLLH	110	120	96	113	36	20	20	20	100
    112	4097	MLML	123	86	106	116	30	13	10	10	97.9381
    113	4097	MLMM	106	123	116	116	33	20	16	16	100
    114	4097	MLMH	140	140	126	136	40	23	23	23	100
    115	4097	MLHL	130	113	110	120	33	16	16	16	100
    116	4097	MLHM	143	123	140	130	40	26	20	20	100
    117	4097	MLHH	166	170	163	190	43	26	26	26	100
    118	4097	MMLL	123	110	103	103	23	10	10	10	97.9381
    119	4097	MMLM	110	113	103	113	33	16	16	13	100
    120	4097	MMLH	150	146	123	130	40	20	26	23	100
    121	4097	MMML	113	100	110	116	33	16	16	13	100
    122	4097	MMMM	130	140	116	136	40	26	23	23	100
    123	4097	MMMH	150	156	176	173	40	30	26	26	100
    124	4097	MMHL	153	146	133	136	40	26	26	26	100
    125	4097	MMHM	163	170	180	176	43	30	30	26	100
    126	4097	MMHH	300	246	263	260	50	33	30	33	97.9381
    127	4097	MHLL	130	116	116	126	30	16	16	13	100
    128	4097	MHLM	163	130	113	140	40	23	23	23	100
    129	4097	MHLH	160	170	186	183	43	26	26	23	100
    130	4097	MHML	140	146	130	143	43	26	30	26	100
    131	4097	MHMM	170	170	176	180	40	26	30	30	100
    132	4097	MHMH	270	243	270	243	50	33	30	30	97.9381
    133	4097	MHHL	143	173	186	173	50	33	30	33	100
    134	4097	MHHM	260	260	273	236	56	40	33	40	100
    135	4097	MHHH	316	386	356	396	63	43	46	40	100
    136	4097	HLLL	130	96	106	96	30	10	13	13	97.9381
    137	4097	HLLM	120	106	96	166	36	20	20	16	100
    138	4097	HLLH	160	150	133	146	36	23	23	26	100
    139	4097	HLML	100	113	110	90	30	13	13	16	100
    140	4097	HLMM	160	136	146	133	40	26	26	26	100

    All those repeat values, I know to be false. 

    I know there's a lot of snake tails and witch magic in spatial index tuning. Using ESRI GIS Server as a front-end to SQL Spatial, and thousands of spatial tables, I really need to come up with a way to "Better Tune" a lot of indexes as opposed to making one spatial index perfect. 

    Once I know I can get the stored proc to write the correct Primary_Filter_Efficiency for each permutation to the results table, I agree with you, some more queries should and can be easily added to the stored proc to give more robust results. 

    Monday, July 6, 2015 8:23 PM
  • If you're using SQL Server 2012 don't forget the auto-grid spatial index. It was added specifically as a "nice default" (MMMM is not a nice default) spatial index for folks with software products that use an "add a spatial index" checkbox when defining tables. That said, it's physically larger than all others for anything but points because it has more hierarchical levels (it's HLLLLLLL), but beats maybe 75% or more of the "standard" permutations.

    I think you can cut down on the number of permutations to save time. I think I started with 6-8 different ones rather than 81. It really depends on what your spatial features look like. Start by looking at your spatial features with some descriptive queries using STArea and STNumPoints. As STNumPoints increases, spatial query time increases. 

    And again, don't forget to look at Internal Filter Efficiency for polygon features/query samples, it's a nice optimization on the model. Finally remember, the point of SQL Server's spatial index is candidate elimination. A good query for optimization will allow a good spatial index to eliminate 90-98% of the candidate rows.

    Hope this helps, Cheers, Bob 

    Monday, July 6, 2015 9:44 PM