Latest Contributions by ThomasColson

There are about as many opinions and resources on SQL Spatial Index Tuning as there are on why the use of a Sequential GUID is good or bad. There's The Black Art Of Spatial Index Tuning In SQL Server | boomphisto , ArcGIS Help 10.1, Spatial Indexing Overview , Spatial Indexing: From 4 Days to 4 Hours - CSS SQL Server Engineers - Site Home - MSDN Blogs, sp_help_spatial_geography_histogram and Indexing geography data in SQL Server Denali | Alastair Aitchison , Basic Multi-Level Grids - Isaac @ MSDN - Site Home - MSDN Blogs , http://social.technet.microsoft.com/wiki/contents/articles/9694.tuning-spatial-point-data-queries-in-sql-server-2012.asp… ,  and of course, if you've ever struggled with a poorly-performing spatial index and posted about it on the internet, chances are you got a reply from this guy. Unfortunately, most of us are not database programmers, nor do we dream in SQL syntax. SQL is like learning French by reading a Chinese dictionary. I don't know about you, but I can't make heads or tails of one single line of SQL code. I'm happy to copy other peoples SQL snippets and hack them up 'till I get them to work. I particularly love this statement from ArcGIS Help: "If you create your data through ArcGIS for Desktop, the spatial grid index is calculated for you." That's like saying "If you put your key in the ignition, your car will drive to the store, get milk, and NOT kill a raccoon on the way". Unless your a Massachusetts driver, there are obviously a few things you have to tweak, such as turn the steering wheel and press the gas paddle in endlessly unique combinations in order to get to the milk store and swerve to avoid the raccoon. The same goes for SQL Spatial Indexes in a SDE Database. Just because the software enables them out of the box by no means implies that the index is optimized for your particular data environment. Out of the box, ArcGIS creates a spatial index with 16 Cells Per Object with all four levels set to Medium Grid Levels. Notice how the storage type is Goemetry, which some of you are discovering is the "New" ESRI default storage format. Let's see how the "Out of the Box" spatial index performs. One way to test a spatial index "Quick and Easy" is with the Spatial Index Stored Procedures. CREATE SPATIAL INDEX --THIS IS THE DEFAULT NAME OF THE INDEX --CREATED BY ARCGIS [S1169_idx] ON --AND THIS IS THE NAME OF TABLE --SUPPLIED BY THE USER IN ARCGIS --AT CREATION TIME [dbo].[TEST_GEOM] (   [SHAPE] )USING  GEOMETRY_GRID WITH (BOUNDING_BOX =(227166.13, 3925740.74, 314851.6915, 3968047.64), GRIDS =( LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, --DROP_EXISTING = ON ALLOWS US TO DO THIS ALL --DAY WITHOUT MESSING WITH DROPPING THE IDX DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] GO DECLARE @geom geometry --THE POLYGON IS AN AREA SMALLER THAN THE BOUNDING BOX 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)jjj exec sp_help_spatial_geometry_index 'TEST_GEOM', 'S1169_idx', 1, @geom There's a WHOLE lot of output. The first thing my eye spies is Primary_Filter_Efficiency 19.634703196347 and Internal_Filter_Efficiency 0 I'm no expert on SQL or spatial indexes, but I don't think those numbers look good. No wonder my draw times are slow, spatial queries are sluggish, and in general, the database is performing horribly. Every single ArcGIS-created table has the same index parameters! Just for giggle, lets change the cells per object by altering just this in the above code snippet: CELLS_PER_OBJECT = 4096 And look at the increase in efficiencies! Internal_Filter_Efficiency 76.7441860465116 Primary_Filter_Efficiency 91.4893617021277 Again, I'm claiming to be no expert at SQL Spatial Index Tuning, but I think I'm on to something here. As it turns out, my test data consists of "Very Complex Line Strings", which, if you're not living under the GIS rock, that's what all of your data is. Coincidentally, using value of 8192 for Cells Per Object in this scenario is a good starting point. But there's a lot (lots and lots) of values between 16 and 8092, and then there's all the permutations of Low, Medium, and High that could be tested to determine the spatial index that is likely to give you the best performance most of the time. What if there was a way to automatically test spatial index settings and magically determine which parameters best fit your data scenario? Enter the SDE Hacker.... I stumbled across this geospatial - Selecting a good SQL Server 2008 spatial index with large polygons - Stack Overflow  post where someone had posted a SQL stored procedure that will loop through Cell Sizes and Grid Levels to report Spatial Query results for every permutation (user supplied). Being the tinker that I am, I of course broke the code trying to add a bunch of output. Finally got it working. Here it is: --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 DATBASE GO CREATE 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 ) 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  GEOMETRY_GRID   WITH   (   --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!   --MAKE SURE YOU EDIT THE BOUNDING BOX TO BE EXACTLY EQUAL TO THE BOUNDING   --BOX OF YOUR SPATIAL TABLE   --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!   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   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))   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 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   (   '   DECLARE @geom geometry   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 GEOMETRY VARIABLE BELOW TO REPRESENT A POLYGON   --THAT IS WITHIN YOUR BOUNDING BOX   --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!   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)   --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!   --MAKE SURE YOU SPECIFY THE NAME OF YOUR SPATIAL TABLE   --AND THE NAME OF THE SPATIAL INDEX   -- IN THE sp_help_spatial_geometry_index_xml VARIABLES   --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!   exec sp_help_spatial_geometry_index_xml TEST_GEOM , S1169_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 The hacking that I did involved using the xml output of the index help stored procedure to write some more descriptive results to the output in addition to query time. The stored procedure is executed like so: DECLARE @BOUNDING VARCHAR(MAX) SET @BOUNDING = 'GEOMETRY::STGeomFromText(''POLYGON ((226805.072 3975572.527, 318101.215 3975985.165, 317894.896 3922239.073, 225360.839  3926571.772 , 226805.072 3975572.527))'', 0)' DECLARE @QUERY VARCHAR(MAX) SET @QUERY = '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_tune_spatial_index 'TEST_GEOM', 'S1169_idx', 4096, 4096, @BOUNDING, @QUERY GO In this example I'm only testing one Cell Size: 4096, but you could use any value range such as 8, 16. Results can be nicely reviewed with the following: SELECT      perm_id as 'Permutation #'       ,num_cells 'Cells Per Object'       ,permut as 'Grids'       ,g1t1 as 'ms to query entire geometry (Level 1)'       ,g1t2 as 'ms to query entire geometry (Level 2)'       ,g1t3 as 'ms to query entire geometry (Level 3)'       ,g1t4 as 'ms to query entire geometry (Level 4)'       ,g2t1 as 'ms to execute spatial query (Level 1)'       ,g2t2 as 'ms to execute spatial query (Level 2)'       ,g2t3 as 'ms to execute spatial query (Level 3)'       ,g2t4 as 'ms to execute spatial query (Level 4)'       ,PF_EFF as 'Primary Filter Efficiency'       ,IF_EFF as 'Internal Filter Efficiency'       ,GRIDL1 as 'Grid Size Level 1'       ,GRIDL2 as 'Grid Size Level 2'       ,GRIDL3 as 'Grid Size Level 3'       ,GRIDL4 as 'Grid Size Level 4'       ,TPIR as 'Total Primary Index Rows'       ,TPIP as 'Total Primary Index Pages'       ,ANOIRPBR as 'Average Number of Index Rows Per Base Row'       ,TNOOCILFQ as 'Total Number of Object Cells in Level 0 For Query Sample'    ,TNOOCIL3FQ as 'Total Number of Object Cells in Level 3 For Query Sample'    ,TNOOCIL4FQ as 'Total Number of Object Cells in Level 4 For Query Sample'       ,TNOOCIL0II as 'Total Number of Object Cells In Level 0 In Index'       ,TNOOCIL4II as 'Total Number of Object Cells In Level 4 In Index'    ,TNOIOIL3FQ as 'Total Number Of Interior ObjectCells In Level 3 For QuerySample'    ,TNOIOIL4FQ as 'Total Number Of Interior ObjectCells In Level 4 For QuerySample'    ,ITTCNTLGP as 'Interior To Total Cells Normalized To Leaf Grid Percentage'    ,INTTTCNTLGP as 'Intersecting To Total Cells Normalized To Leaf Grid Percentage'    ,BTTCNTLGP as 'Border To Total Cells Normalized To Leaf Grid Percentage'    ,ACPONTLGP as 'Average Cells Per Object Normalized To Leaf Grid'    ,AOPG as 'Average Objects PerLeaf GridCell'    ,NORSBPF as 'Number Of Rows Selected By Primary Filter'    ,NORSBIF as 'Number Of Rows Selected By Internal Filter'    ,NOTSFIC as 'Number Of Times Secondary Filter Is Called'    ,NORO as 'Number Of Rows Output'    ,PORNBPF as 'Percentage Of Rows NotSelected By Primary Filter'    ,POPFRSBIF as 'Percentage Of Primary Filter Rows Selected By Internal Filter' FROM spat_idx_test_result ORDER BY PF_EFF Permutation # Cells Per Object Grids ms to query entire geometry (Level 1) ms to query entire geometry (Level 2) ms to query entire geometry (Level 3) ms to query entire geometry (Level 4) ms to execute spatial query (Level 1) ms to execute spatial query (Level 2) ms to execute spatial query (Level 3) ms to execute spatial query (Level 4) Primary Filter Efficiency Internal Filter Efficiency Grid Size Level 1 Grid Size Level 2 Grid Size Level 3 Grid Size Level 4 Total Primary Index Rows Total Primary Index Pages Average Number of Index Rows Per Base Row Total Number of Object Cells in Level 0 For Query Sample Total Number of Object Cells in Level 3 For Query Sample Total Number of Object Cells in Level 4 For Query Sample Total Number of Object Cells In Level 0 In Index Total Number of Object Cells In Level 4 In Index Total Number Of Interior ObjectCells In Level 3 For QuerySample Total Number Of Interior ObjectCells In Level 4 For QuerySample Interior To Total Cells Normalized To Leaf Grid Percentage Intersecting To Total Cells Normalized To Leaf Grid Percentage Border To Total Cells Normalized To Leaf Grid Percentage Average Cells Per Object Normalized To Leaf Grid Average Objects PerLeaf GridCell Number Of Rows Selected By Primary Filter Number Of Rows Selected By Internal Filter Number Of Times Secondary Filter Is Called Number Of Rows Output Percentage Of Rows NotSelected By Primary Filter Percentage Of Primary Filter Rows Selected By Internal Filter 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 6 4096 LLMH 90 66 70 66 46 16 30 23 68.2539682539683 69.7674418604651 16 16 64 256 21278 66 10 1 110 3983 2 21276 72 3559 89.3547577203113 10.5699221692192 0.0753201104694954 2.0425641025641 0.48958071805172 63 30 33 43 96.7692307692308 47.6190476190476 70 4096 HMHL 156 136 130 146 50 33 36 33 68.2539682539683 69.7674418604651 256 64 256 16 79767 239 40 1 3980 6 2 79765 3559 NULL 0 0 0 0 0 63 30 33 43 96.7692307692308 47.6190476190476 71 4096 HMHM 233 226 203 166 43 40 40 66 68.2539682539683 69.7674418604651 256 64 256 64 157432 472 80 1 3980 5 2 157410 3559 NULL 0 0 0 0 0 63 30 33 43 96.7692307692308 47.6190476190476 72 4096 HMHH 376 310 263 266 53 63 73 56 68.2539682539683 69.7674418604651 256 64 256 256 306611 915 157 1 3981 5 2 306176 3559 NULL 0 0 0 0 0 63 30 33 43 96.7692307692308 47.6190476190476 52 4096 MHHL 156 116 133 140 46 33 43 40 68.2539682539683 69.7674418604651 64 256 256 16 79767 239 40 1 3980 6 2 79765 3559 NULL 0 0 0 0 0 63 30 33 43 96.7692307692308 47.6190476190476 53 4096 MHHM 243 216 210 186 43 43 43 33 68.2539682539683 69.7674418604651 64 256 256 64 157432 472 80 1 3980 5 2 157410 3559 NULL 0 0 0 0 0 63 30 33 43 96.7692307692308 47.6190476190476 54 4096 MHHH 366 256 263 266 56 43 56 50 68.2539682539683 69.7674418604651 64 256 256 256 306611 915 157 1 3981 5 2 306176 3559 NULL 0 0 0 0 0 63 30 33 43 96.7692307692308 47.6190476190476 30 4096 MLLH 86 63 70 66 40 26 30 20 68.2539682539683 69.7674418604651 64 16 16 256 21278 66 10 1 102 3994 2 21276 72 3429 85.8537806710065 14.07110665999 0.0751126690035053 2.04820512820513 0.488232348522784 63 30 33 43 96.7692307692308 47.6190476190476 12 4096 LMLH 83 73 63 63 43 23 26 20 70.4918032786885 72.0930232558139 16 64 16 256 21278 66 10 1 95 3999 2 21276 72 3331 83.295823955989 16.6291572893223 0.0750187546886722 2.05076923076923 0.487621905476369 61 31 30 43 96.8717948717949 50.8196721311475 26 4096 LHHM 156 140 143 140 50 40 40 30 78.1818181818182 41.8604651162791 16 256 256 64 79767 239 40 1 4008 77 2 79765 3576 NULL 0 0 0 0 0 55 18 37 43 97.1794871794872 32.7272727272727 27 4096 LHHH 153 143 170 163 56 36 46 30 78.1818181818182 41.8604651162791 16 256 256 256 157432 472 80 1 4013 72 2 157420 3576 NULL 0 0 0 0 0 55 18 37 43 97.1794871794872 32.7272727272727 25 4096 LHHL 116 86 83 80 43 36 40 33 78.1818181818182 41.8604651162791 16 256 256 16 40769 123 20 1 3996 90 2 40767 3576 NULL 99.8429483823683 0.155306599657976 0.00174501797368513 29.3876923076923 0.03402785048686 55 18 37 43 97.1794871794872 32.7272727272727 43 4096 MMHL 76 63 63 63 33 30 40 33 78.1818181818182 41.8604651162791 64 64 256 16 40769 123 20 1 4003 83 2 40767 3576 24 28.9156626506024 69.8795180722892 1.20481927710843 0.0425641025641026 23.4939759036145 55 18 37 43 97.1794871794872 32.7272727272727 44 4096 MMHM 153 143 133 126 43 40 43 30 78.1818181818182 41.8604651162791 64 64 256 64 79767 239 40 1 4008 77 2 79765 3576 NULL 0 0 0 0 0 55 18 37 43 97.1794871794872 32.7272727272727 45 4096 MMHH 233 223 223 176 43 40 50 30 78.1818181818182 41.8604651162791 64 64 256 256 157432 472 80 1 4013 72 2 157420 3576 NULL 0 0 0 0 0 55 18 37 43 97.1794871794872 32.7272727272727 39 4096 MMLH 106 96 90 96 43 20 33 20 84.3137254901961 74.4186046511628 64 64 16 256 40769 123 20 1 332 3749 2 40767 229 3355 89.4905308082155 9.89597225926914 0.613496932515337 1.9225641025641 0.520138703654308 51 32 19 43 97.3846153846154 62.7450980392157 33 4096 MLMH 110 93 90 96 43 20 33 26 84.3137254901961 74.4186046511628 64 16 64 256 40769 123 20 1 425 3669 2 40767 325 3227 87.9531207413464 11.4200054510766 0.626873807576996 1.88153846153846 0.531479967293541 51 32 19 43 97.3846153846154 62.7450980392157 79 4096 HHHL 246 206 173 176 46 40 46 26 84.3137254901961 74.4186046511628 256 256 256 16 157433 472 80 1 3667 NULL 2 157386 3291 NULL 25.0673623475421 74.9250207085662 0.00761694389168706 107.722051282051 0.0092831503679936 51 32 19 43 97.3846153846154 62.7450980392157 80 4096 HHHM 380 350 293 246 43 43 46 46 84.3137254901961 74.4186046511628 256 256 256 64 306612 915 157 1 3667 NULL 2 305697 3291 NULL 0 0 0 0 0 51 32 19 43 97.3846153846154 62.7450980392157 81 4096 HHHH 550 486 430 516 60 60 50 56 84.3137254901961 74.4186046511628 256 256 256 256 560683 1671 287 1 3667 NULL 2 556594 3291 NULL 60.2064107090026 39.7752950306786 0.0182942603187489 717.613333333333 0.0013935081102172 51 32 19 43 97.3846153846154 62.7450980392157 57 4096 HLLH 113 96 90 90 46 23 33 16 84.3137254901961 74.4186046511628 256 16 16 256 40769 123 20 1 331 3749 2 40767 229 3339 89.0637503334222 10.3227527340624 0.613496932515337 1.9225641025641 0.520138703654308 51 32 19 43 97.3846153846154 62.7450980392157 9 4096 LLHH 113 90 83 73 36 20 30 26 84.3137254901961 74.4186046511628 16 16 256 256 40769 123 20 1 430 3653 2 40767 325 3291 90.0903367095538 9.28004379961675 0.629619490829455 1.87333333333333 0.533807829181495 51 32 19 43 97.3846153846154 62.7450980392157 1 4096 LLLL 53 36 36 36 26 16 10 13 84.3137254901961 74.4186046511628 16 16 16 16 4230 15 2 1 6 358 2 4228 6 229 0 0 0 0 0 51 32 19 43 97.3846153846154 62.7450980392157 15 4096 LMMH 110 83 83 80 46 26 36 26 84.3137254901961 76.7441860465116 16 64 64 256 40769 123 20 1 403 3681 2 40767 325 2890 78.5112741102961 20.8638956805216 0.624830209182287 1.88769230769231 0.529747351263244 51 33 18 43 97.3846153846154 64.7058823529412 21 4096 LHLH 106 96 90 80 40 20 30 20 84.3137254901961 76.7441860465116 16 256 16 256 40769 123 20 1 313 3776 2 40767 229 3073 81.3824152542373 18.0084745762712 0.609110169491525 1.93641025641026 0.516419491525424 51 33 18 43 97.3846153846154 64.7058823529412 22 4096 LHML 90 70 73 73 53 23 40 26 91.4893617021277 93.0232558139535 16 256 64 16 21278 66 10 1 986 2342 2 21276 986 1320 56.3620836891546 43.5098206660974 0.128095644748079 1.20102564102564 0.832621690862511 47 40 7 43 97.5897435897436 85.1063829787234 23 4096 LHMM 110 100 90 90 53 36 40 33 91.4893617021277 76.7441860465116 16 256 64 64 40769 123 20 1 1142 2941 2 40767 986 2147 73.0023801428086 26.1475688541313 0.850051003060184 1.50820512820513 0.663039782386943 47 33 14 43 97.5897435897436 70.2127659574468 24 4096 LHMH 156 143 143 123 60 30 43 33 91.4893617021277 76.7441860465116 16 256 64 256 79767 239 40 1 1196 2868 2 79765 986 2161 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 16 4096 LMHL 90 73 70 73 50 26 40 26 91.4893617021277 93.0232558139535 16 64 256 16 21278 66 10 1 1370 2342 2 21276 1370 1320 56.3620836891546 43.5098206660974 0.128095644748079 1.20102564102564 0.832621690862511 47 40 7 43 97.5897435897436 85.1063829787234 17 4096 LMHM 103 96 83 73 46 26 33 26 91.4893617021277 76.7441860465116 16 64 256 64 40769 123 20 1 1529 2565 2 40767 1370 1797 70.0584795321637 29.3567251461988 0.584795321637427 1.31538461538461 0.760233918128655 47 33 14 43 97.5897435897436 70.2127659574468 18 4096 LMHH 156 146 120 103 50 33 43 30 91.4893617021277 76.7441860465116 16 64 256 256 79767 239 40 1 1583 2512 2 79765 1370 1854 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 19 4096 LHLL 76 56 56 53 40 16 33 23 91.4893617021277 76.7441860465116 16 256 16 16 11543 37 5 1 229 932 2 11541 229 420 45.0643776824034 54.7210300429185 0.214592274678112 0.477948717948718 2.09227467811159 47 33 14 43 97.5897435897436 70.2127659574468 20 4096 LHLM 80 70 70 70 46 23 30 26 91.4893617021277 93.0232558139535 16 256 16 64 21278 66 10 1 229 3462 2 21276 229 2440 70.4794916233391 29.4338532640092 0.0866551126516464 1.77538461538461 0.563258232235702 47 40 7 43 97.5897435897436 85.1063829787234 2 4096 LLLM 60 40 36 40 36 20 26 26 91.4893617021277 76.7441860465116 16 16 16 64 6617 22 3 1 6 1241 2 6615 6 986 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 3 4096 LLLH 73 56 56 60 50 20 30 23 91.4893617021277 46.5116279069767 16 16 16 256 11543 37 5 1 9 4052 2 11541 6 3592 88.6475814412636 11.1056268509378 0.246791707798618 2.07794871794872 0.481243830207305 47 20 27 43 97.5897435897436 42.5531914893617 4 4096 LLML 56 40 43 40 33 16 20 16 91.4893617021277 76.7441860465116 16 16 64 16 6617 22 3 1 72 473 2 6615 72 218 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 5 4096 LLMM 73 56 60 56 43 30 30 23 91.4893617021277 76.7441860465116 16 16 64 64 11543 37 5 1 72 1524 2 11541 72 1012 66.4041994750656 33.4645669291339 0.131233595800525 0.781538461538462 1.27952755905512 47 33 14 43 97.5897435897436 70.2127659574468 10 4096 LMLL 63 40 36 36 26 20 20 16 91.4893617021277 76.7441860465116 16 64 16 16 6617 22 3 1 72 473 2 6615 72 218 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 11 4096 LMLM 73 56 53 53 40 30 30 16 91.4893617021277 76.7441860465116 16 64 16 64 11543 37 5 1 72 1524 2 11541 72 1012 66.4041994750656 33.4645669291339 0.131233595800525 0.781538461538462 1.27952755905512 47 33 14 43 97.5897435897436 70.2127659574468 7 4096 LLHL 73 60 56 56 40 23 33 20 91.4893617021277 76.7441860465116 16 16 256 16 11543 37 5 1 325 932 2 11541 325 420 45.0643776824034 54.7210300429185 0.214592274678112 0.477948717948718 2.09227467811159 47 33 14 43 97.5897435897436 70.2127659574468 8 4096 LLHM 90 66 73 66 40 23 30 23 91.4893617021277 93.0232558139535 16 16 256 64 21278 66 10 1 325 3462 2 21276 325 2440 70.4794916233391 29.4338532640092 0.0866551126516464 1.77538461538461 0.563258232235702 47 40 7 43 97.5897435897436 85.1063829787234 58 4096 HLML 93 76 70 76 53 26 40 30 91.4893617021277 93.0232558139535 256 16 64 16 21278 66 10 1 986 2342 2 21276 986 1320 56.3620836891546 43.5098206660974 0.128095644748079 1.20102564102564 0.832621690862511 47 40 7 43 97.5897435897436 85.1063829787234 59 4096 HLMM 116 93 103 93 56 26 43 26 91.4893617021277 76.7441860465116 256 16 64 64 40769 123 20 1 1158 2931 2 40767 986 2272 77.5162060730126 21.9720232002729 0.511770726714432 1.50307692307692 0.665301944728761 47 33 14 43 97.5897435897436 70.2127659574468 60 4096 HLMH 156 143 143 143 60 36 46 30 91.4893617021277 76.7441860465116 256 16 64 256 79767 239 40 1 1216 2869 2 79765 986 2486 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 61 4096 HLHL 116 100 96 100 53 33 50 26 91.4893617021277 46.5116279069767 256 16 256 16 40769 123 20 1 4046 41 2 40767 3592 24 58.5365853658536 39.0243902439024 2.4390243902439 0.021025641025641 47.5609756097561 47 20 27 43 97.5897435897436 42.5531914893617 62 4096 HLHM 150 140 140 143 56 33 43 26 91.4893617021277 46.5116279069767 256 16 256 64 79767 239 40 1 4044 43 2 79765 3592 NULL 0 0 0 0 0 47 20 27 43 97.5897435897436 42.5531914893617 63 4096 HLHH 156 146 146 146 36 33 46 33 91.4893617021277 46.5116279069767 256 16 256 256 157432 472 80 1 4047 35 2 157420 3592 NULL 0 0 0 0 0 47 20 27 43 97.5897435897436 42.5531914893617 64 4096 HMLL 63 56 56 53 33 20 36 30 91.4893617021277 93.0232558139535 256 64 16 16 21278 66 10 1 218 2342 2 21276 218 1320 56.3620836891546 43.5098206660974 0.128095644748079 1.20102564102564 0.832621690862511 47 40 7 43 97.5897435897436 85.1063829787234 65 4096 HMLM 116 100 86 73 40 30 33 23 91.4893617021277 76.7441860465116 256 64 16 64 40769 123 20 1 376 3648 2 40767 218 2876 78.8377192982456 20.7510964912281 0.411184210526316 1.87076923076923 0.534539473684211 47 33 14 43 97.5897435897436 70.2127659574468 66 4096 HMLH 156 116 106 113 43 36 40 23 91.4893617021277 76.7441860465116 256 64 16 256 79767 239 40 1 442 3581 2 79765 218 3089 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 67 4096 HMML 113 103 100 103 53 33 40 33 91.4893617021277 76.7441860465116 256 64 64 16 40769 123 20 1 1305 2719 2 40767 1012 1855 68.2236116219198 31.5189407870541 0.257447591026112 1.39435897435897 0.717175432144171 47 33 14 43 97.5897435897436 70.2127659574468 68 4096 HMMM 153 123 106 143 53 36 43 26 91.4893617021277 76.7441860465116 256 64 64 64 79767 239 40 1 1445 2579 2 79765 1012 1964 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 69 4096 HMMH 236 190 163 146 43 40 46 40 91.4893617021277 76.7441860465116 256 64 64 256 157432 472 80 1 1504 2518 2 157420 1012 2231 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 73 4096 HHLL 113 93 90 83 50 36 63 36 91.4893617021277 76.7441860465116 256 256 16 16 40769 123 20 1 692 3079 2 40767 420 2131 69.2107827216629 30.5618707372524 0.227346541084768 1.57897435897436 0.633322507307567 47 33 14 43 97.5897435897436 70.2127659574468 74 4096 HHLM 120 96 90 93 40 36 43 33 91.4893617021277 76.7441860465116 256 256 16 64 79767 239 40 1 852 2918 2 79765 420 2300 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 75 4096 HHLH 236 220 210 210 53 46 53 30 91.4893617021277 76.7441860465116 256 256 16 256 157432 472 80 1 909 2862 2 157419 420 2512 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 76 4096 HHML 170 146 146 143 50 30 33 33 91.4893617021277 93.0232558139535 256 256 64 16 79767 239 40 1 3433 338 2 79765 2440 229 64.6892655367232 30.7909604519774 4.51977401129944 0.181538461538462 5.50847457627119 47 40 7 43 97.5897435897436 85.1063829787234 77 4096 HHMM 236 220 220 200 56 36 36 36 91.4893617021277 93.0232558139535 256 256 64 64 157432 472 80 1 3448 323 2 157410 2440 224 57.8811369509044 25.5813953488372 16.5374677002584 0.198461538461538 5.03875968992248 47 40 7 43 97.5897435897436 85.1063829787234 78 4096 HHMH 360 353 336 353 60 40 50 33 91.4893617021277 93.0232558139535 256 256 64 256 306611 915 157 1 3456 314 2 306171 2440 256 0 0 0 0 0 47 40 7 43 97.5897435897436 85.1063829787234 13 4096 LMML 66 60 56 50 43 26 30 23 91.4893617021277 76.7441860465116 16 64 64 16 11543 37 5 1 325 932 2 11541 325 420 45.0643776824034 54.7210300429185 0.214592274678112 0.477948717948718 2.09227467811159 47 33 14 43 97.5897435897436 70.2127659574468 14 4096 LMMM 86 73 66 66 43 30 33 23 91.4893617021277 93.0232558139535 16 64 64 64 21278 66 10 1 325 3462 2 21276 325 2440 70.4794916233391 29.4338532640092 0.0866551126516464 1.77538461538461 0.563258232235702 47 40 7 43 97.5897435897436 85.1063829787234 55 4096 HLLL 76 56 50 60 43 26 30 26 91.4893617021277 76.7441860465116 256 16 16 16 11543 37 5 1 229 932 2 11541 229 420 45.0643776824034 54.7210300429185 0.214592274678112 0.477948717948718 2.09227467811159 47 33 14 43 97.5897435897436 70.2127659574468 56 4096 HLLM 90 73 66 63 43 26 30 16 91.4893617021277 93.0232558139535 256 16 16 64 21278 66 10 1 229 3462 2 21276 229 2440 70.4794916233391 29.4338532640092 0.0866551126516464 1.77538461538461 0.563258232235702 47 40 7 43 97.5897435897436 85.1063829787234 34 4096 MLHL 90 70 76 70 53 33 33 23 91.4893617021277 93.0232558139535 64 16 256 16 21278 66 10 1 1370 2342 2 21276 1370 1320 56.3620836891546 43.5098206660974 0.128095644748079 1.20102564102564 0.832621690862511 47 40 7 43 97.5897435897436 85.1063829787234 35 4096 MLHM 106 103 93 83 43 33 40 33 91.4893617021277 76.7441860465116 64 16 256 64 40769 123 20 1 1548 2547 2 40767 1370 1936 76.010993325481 23.4000785237534 0.588928150765607 1.30615384615385 0.765606595995289 47 33 14 43 97.5897435897436 70.2127659574468 36 4096 MLHH 153 143 143 140 56 36 36 30 91.4893617021277 76.7441860465116 64 16 256 256 79767 239 40 1 1597 2480 2 79765 1370 2049 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 37 4096 MMLL 70 56 53 46 40 23 30 26 91.4893617021277 76.7441860465116 64 64 16 16 11543 37 5 1 229 932 2 11541 229 420 45.0643776824034 54.7210300429185 0.214592274678112 0.477948717948718 2.09227467811159 47 33 14 43 97.5897435897436 70.2127659574468 38 4096 MMLM 86 63 60 56 30 20 33 20 91.4893617021277 93.0232558139535 64 64 16 64 21278 66 10 1 229 3462 2 21276 229 2440 70.4794916233391 29.4338532640092 0.0866551126516464 1.77538461538461 0.563258232235702 47 40 7 43 97.5897435897436 85.1063829787234 28 4096 MLLL 56 40 40 36 26 20 20 13 91.4893617021277 76.7441860465116 64 16 16 16 6617 22 3 1 72 473 2 6615 72 218 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 29 4096 MLLM 76 60 56 56 43 16 30 20 91.4893617021277 76.7441860465116 64 16 16 64 11543 37 5 1 72 1524 2 11541 72 1012 66.4041994750656 33.4645669291339 0.131233595800525 0.781538461538462 1.27952755905512 47 33 14 43 97.5897435897436 70.2127659574468 40 4096 MMML 86 76 83 63 60 30 40 30 91.4893617021277 93.0232558139535 64 64 64 16 21278 66 10 1 986 2342 2 21276 986 1320 56.3620836891546 43.5098206660974 0.128095644748079 1.20102564102564 0.832621690862511 47 40 7 43 97.5897435897436 85.1063829787234 41 4096 MMMM 116 83 76 70 46 30 40 33 91.4893617021277 76.7441860465116 64 64 64 64 40769 123 20 1 1175 2912 2 40767 986 2388 82.0054945054945 17.4793956043956 0.51510989010989 1.49333333333333 0.669642857142857 47 33 14 43 97.5897435897436 70.2127659574468 42 4096 MMMH 163 143 143 133 50 36 43 33 91.4893617021277 76.7441860465116 64 64 64 256 79767 239 40 1 1214 2869 2 79765 986 2454 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 31 4096 MLML 73 56 60 50 43 23 33 20 91.4893617021277 76.7441860465116 64 16 64 16 11543 37 5 1 325 932 2 11541 325 420 45.0643776824034 54.7210300429185 0.214592274678112 0.477948717948718 2.09227467811159 47 33 14 43 97.5897435897436 70.2127659574468 32 4096 MLMM 86 63 73 63 40 16 33 20 91.4893617021277 93.0232558139535 64 16 64 64 21278 66 10 1 325 3462 2 21276 325 2440 70.4794916233391 29.4338532640092 0.0866551126516464 1.77538461538461 0.563258232235702 47 40 7 43 97.5897435897436 85.1063829787234 46 4096 MHLL 93 76 70 63 40 33 33 36 91.4893617021277 93.0232558139535 64 256 16 16 21278 66 10 1 218 2342 2 21276 218 1320 56.3620836891546 43.5098206660974 0.128095644748079 1.20102564102564 0.832621690862511 47 40 7 43 97.5897435897436 85.1063829787234 47 4096 MHLM 110 96 90 100 46 20 36 33 91.4893617021277 76.7441860465116 64 256 16 64 40769 123 20 1 383 3640 2 40767 218 2927 80.4120879120879 19.1758241758242 0.412087912087912 1.86666666666667 0.535714285714286 47 33 14 43 97.5897435897436 70.2127659574468 48 4096 MHLH 163 133 140 120 50 33 40 33 91.4893617021277 76.7441860465116 64 256 16 256 79767 239 40 1 442 3581 2 79765 218 3089 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 49 4096 MHML 116 86 93 100 50 30 36 26 91.4893617021277 76.7441860465116 64 256 64 16 40769 123 20 1 1305 2719 2 40767 1012 1855 68.2236116219198 31.5189407870541 0.257447591026112 1.39435897435897 0.717175432144171 47 33 14 43 97.5897435897436 70.2127659574468 50 4096 MHMM 156 120 133 116 43 40 43 40 91.4893617021277 76.7441860465116 64 256 64 64 79767 239 40 1 1436 2588 2 79765 1012 1894 0 0 0 0 0 47 33 14 43 97.5897435897436 70.2127659574468 51 4096 MHMH 233 223 180 220 63 30 43 26 91.4893617021277 76.7441860465116 64 256 64 256 157432 472 80 1 1494 2530 2 157420 1012 2084 82.3715415019763 16.5612648221344 1.06719367588933 1.2974358974359 0.770750988142292 47 33 14 43 97.5897435897436 70.2127659574468 This post doesn't even scratch the surface of Spatial Index Tuning. For example, if you're using SQL 2012, you could set the index to Auto Grid, which gives you 8 Cell Levels and supposedly is a good marriage for ArcGIS. You also want to look at other performance factors such as Internal Filter Efficiency. There's a lot of Witch Magic and Snake Oil Medicine to Spatial Index Tuning. Hope this blog post gets you started in the right direction! ... View more

Following the instructions in Updating your hosted feature service for 10.2 | ArcGIS Blog , as well as some of the user replies, I cannot get update.py to update an AGOL FS. Works fine when publishing from ArcMap, fails when using the python script with this error: Starting Feature Service publish process found Feature Service : f4c43de7a4964371b1fbba68eaa15cfb found Service Definition : d4333434f3c34a75b14b5d42fe811345 Created X:\GIS_Final\data\basedata\basemap\Data\Working\Python_Automated_Scripts \UPDATE_AGOL\tempDir\GRSM_TRAIL_INVENTORY.sd Traceback (most recent call last):   File "update.py", line 294, in <module>     if upload(finalSD, tags, description):   File "update.py", line 171, in upload     response = requests.post(url, files=filesUp);   File "C:\Python27\ArcGIS10.2\Lib\site-packages\requests\api.py", line 109, in post     return request('post', url, data=data, json=json, **kwargs)   File "C:\Python27\ArcGIS10.2\Lib\site-packages\requests\api.py", line 50, in r equest     response = session.request(method=method, url=url, **kwargs)   File "C:\Python27\ArcGIS10.2\Lib\site-packages\requests\sessions.py", line 451 , in request     prep = self.prepare_request(req)   File "C:\Python27\ArcGIS10.2\Lib\site-packages\requests\sessions.py", line 382 , in prepare_request     hooks=merge_hooks(request.hooks, self.hooks),   File "C:\Python27\ArcGIS10.2\Lib\site-packages\requests\models.py", line 296, in prepare     self.prepare_body(data, files, json)   File "C:\Python27\ArcGIS10.2\Lib\site-packages\requests\models.py", line 444, in prepare_body     (body, content_type) = self._encode_files(files, data)   File "C:\Python27\ArcGIS10.2\Lib\site-packages\requests\models.py", line 150, in _encode_files     fdata = fp.read() MemoryError Testing the requests library checks out ok: python test_requests. py ................................................................................ ................................................................ ---------------------------------------------------------------------- Ran 144 tests in 22.476s OK as does a check of openssl: python -m pip install pyopenssl Requirement already satisfied (use --upgrade to upgrade): pyopenssl in c:\python 27\arcgis10.2\lib\site-packages Requirement already satisfied (use --upgrade to upgrade): six>=1.5.2 in c:\pytho n27\arcgis10.2\lib\site-packages (from pyopenssl) Requirement already satisfied (use --upgrade to upgrade): cryptography>=0.7 in c :\python27\arcgis10.2\lib\site-packages (from pyopenssl) Requirement already satisfied (use --upgrade to upgrade): setuptools in c:\pytho n27\arcgis10.2\lib\site-packages (from cryptography>=0.7->pyopenssl) Requirement already satisfied (use --upgrade to upgrade): enum34 in c:\python27\ arcgis10.2\lib\site-packages (from cryptography>=0.7->pyopenssl) Requirement already satisfied (use --upgrade to upgrade): pyasn1 in c:\python27\ arcgis10.2\lib\site-packages (from cryptography>=0.7->pyopenssl) Requirement already satisfied (use --upgrade to upgrade): idna in c:\python27\ar cgis10.2\lib\site-packages (from cryptography>=0.7->pyopenssl) Requirement already satisfied (use --upgrade to upgrade): ipaddress in c:\python 27\arcgis10.2\lib\site-packages (from cryptography>=0.7->pyopenssl) Requirement already satisfied (use --upgrade to upgrade): cffi>=0.8 in c:\python 27\arcgis10.2\lib\site-packages (from cryptography>=0.7->pyopenssl) Requirement already satisfied (use --upgrade to upgrade): pycparser in c:\python 27\arcgis10.2\lib\site-packages (from cffi>=0.8->cryptography>=0.7->pyopenssl) Has anyone gotten http://blogs.esri.com/esri/arcgis/2014/01/24/updating-your-hosted-feature-service-for-10-2/#comment-7801  to work? ... View more