Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_help_spatial_index_internal

  No additional text.


Syntax
CREATE PROC sys.sp_help_spatial_index_internal
(
	@minx				FLOAT,		-- bounding box minx
	@miny				FLOAT,		-- bounding box miny
	@maxx				FLOAT,		-- bounding box maxx
	@maxy				FLOAT,		-- bounding box maxy
	@gridsize1			INT,		-- level 1 size (16 LOW, 64 MEDIUM, 256 HIGH)
	@gridsize2			INT,		-- level 2 size
	@gridsize3			INT,		-- level 3 size
	@gridsize4			INT,		-- level 4 size
	@cells_per_object	INT,		-- cells per object
	@base_table_rows	BIGINT,		-- number of rows in the base TABLE
	@index_total_pages	BIGINT,		-- optional (number pages in the INDEX)
	@results_card		BIGINT,		-- number of rows selectd
	@internaltabname	SYSNAME,	-- internal TABLE name for the persisted INDEX
	@do_output_xml		TINYINT,	-- OUTPUT report in XML form rather than AS a rowset
	@xml_output		XML OUTPUT,	-- OUTPUT report in XML form rather than AS a rowset
	@verboseoutput		TINYINT		-- OUTPUT all properties
)
AS
BEGIN
	SET NOCOUNT ON
	SET ARITHIGNORE ON
	SET ARITHABORT OFF
	SET ANSI_WARNINGS OFF

	DECLARE @index_total_rows BIGINT,
			@execstr NVARCHAR(2000),
			@param_def NVARCHAR(200);

	-- use a tablevar to maintain tesults. the results are property name value pairs
	-- WITH additional description. currently use FLOAT AS the type of the property, may be
	-- this needs to be a sqlvariant.
	DECLARE @results TABLE (propname NVARCHAR(256), propvalue sql_variant, verbose INT)

	-- size OFF grid for each level along WITH level and size
	DECLARE @gridsizes TABLE (level INT, size INT, leafgrid_normalizer INT)

	INSERT INTO @gridsizes values (1, @gridsize1, @gridsize4*@gridsize3*@gridsize2)
	INSERT INTO @gridsizes values (2, @gridsize2, @gridsize4*@gridsize3)
	INSERT INTO @gridsizes values (3, @gridsize3, @gridsize4)
	INSERT INTO @gridsizes values (4, @gridsize4, 1)

	-- the query groups the rows by depth of the grid which is the last byte of the cell encoding.
	CREATE TABLE #temp_spatial_index_stats(tag NVARCHAR(50), srid INT, level SMALLINT, cell_attributes SMALLINT, total BIGINT);

	IF (@internaltabname is not null)
	BEGIN
		SET @execstr = N'SELECT ''_In_Index'' AS tag, srid, SUBSTRING(cell_id, 5, 1) AS ''level'', cell_attributes, count(*) AS ''total''
			FROM ' + @internaltabname +
			' group by srid, SUBSTRING(cell_id, 5, 1), cell_attributes';

		INSERT INTO #temp_spatial_index_stats execute (@execstr) ;
	END
	
	SET @execstr = N'SELECT ''_For_QuerySample'' AS tag, srid, SUBSTRING(cell_id, 5, 1) AS ''level'', cell_attributes, count(*) AS ''total'' '+
		N' FROM #temp_window_object_tessellation f ' +
		N' group by srid, SUBSTRING(cell_id, 5, 1), cell_attributes';
	INSERT INTO #temp_spatial_index_stats EXEC (@execstr);

	CREATE TABLE #temp_window_object_tessellation_candidates(cell_id BINARY(5), cell_id_end BINARY(5), cell_attributes INT, srid INT);
	CREATE INDEX temp_window_object_tessellation_candidates_index ON
		#temp_window_object_tessellation_candidates(cell_id, cell_id_end);

	-- enumerate self and ancestors
	WITH AncestorCells(depth, cell_id, cell_id_end, cell_attr, srid)
	AS
	(
		-- this part enumerates the self
		SELECT CONVERT(INT, SUBSTRING(cell_id, 5, 1)) AS depth, cell_id, cell_id, cell_attributes, srid
		FROM #temp_window_object_tessellation
		UNION ALL
		-- this part enumerates the ancestors, note the cell attribute mapping
		SELECT depth-1 AS depth,
			CONVERT(BINARY(5), (SUBSTRING(cell_id, 1, (depth-1)) + SUBSTRING(0x0000000000, depth, 5-depth) + CONVERT(BINARY(1), depth-1))),
			CONVERT(BINARY(5), (SUBSTRING(cell_id, 1, (depth-1)) + SUBSTRING(0x0000000000, depth, 5-depth) + CONVERT(BINARY(1), depth-1))),
			CASE WHEN cell_attr = 2 THEN 1 ELSE 0 END,
			srid
		FROM AncestorCells
		WHERE depth >= 2
	)
	INSERT INTO #temp_window_object_tessellation_candidates
	SELECT cell_id, cell_id_end, cell_attr, srid
	FROM AncestorCells
	OPTION (MAXRECURSION 4);

	-- enumerate descendant prefix
	WITH DescendantCells(depth, cell_id_start, cell_id_end, cell_attr, srid)
	AS
	(
		SELECT CONVERT(INT, SUBSTRING(cell_id, 5, 1)) AS depth,
		CONVERT(BINARY(4),
			(
			SUBSTRING(cell_id, 1, CONVERT(INT, SUBSTRING(cell_id, 5, 1)))+
			CONVERT( varbinary(3), replicate(0x00, 4 - CONVERT(INT, SUBSTRING(cell_id, 5, 1))))
			))
			+
			SUBSTRING(cell_id, 5, 1),
		CONVERT(BINARY(4),
			(
			SUBSTRING(cell_id, 1, CONVERT(INT, SUBSTRING(cell_id, 5, 1)))+
			CONVERT( varbinary(3), replicate(0xFF, 4 - CONVERT(INT, SUBSTRING(cell_id, 5, 1))))
			))
			+
			0x04,
			CASE WHEN (cell_attributes = 2) THEN 2 ELSE 0 END, srid
		FROM #temp_window_object_tessellation
		WHERE (CONVERT(INT, SUBSTRING(cell_id, 5, 1))) > 0 and (CONVERT(INT, SUBSTRING(cell_id, 5, 1))) < 4
	)
	INSERT INTO #temp_window_object_tessellation_candidates
	SELECT cell_id_start, cell_id_end, cell_attr, srid
	FROM DescendantCells

	INSERT INTO @results values (N'Base_Table_Rows',
		@base_table_rows,
		1)
	INSERT INTO @results values (N'Bounding_Box_xmin',
		@minx,
		1)
	INSERT INTO @results values (N'Bounding_Box_ymin',
		@miny,
		1)
	INSERT INTO @results values (N'Bounding_Box_xmax',
		@maxx,
		1)
	INSERT INTO @results values (N'Bounding_Box_ymax',
		@maxy,
		1)
	INSERT INTO @results values (N'Grid_Size_Level_1',
		@gridsize1,
		1)
	INSERT INTO @results values (N'Grid_Size_Level_2',
		@gridsize2,
		1)
	INSERT INTO @results values (N'Grid_Size_Level_3',
		@gridsize3,
		1)
	INSERT INTO @results values (N'Grid_Size_Level_4',
		@gridsize4,
		1)
	INSERT INTO @results values (N'Cells_Per_Object',
		@cells_per_object,
		1)

	SELECT @index_total_rows = sum(total) FROM #temp_spatial_index_stats WHERE tag = '_In_Index'
	INSERT INTO @results values (N'Total_Primary_Index_Rows',
		@index_total_rows,
		1)
	INSERT INTO @results values (N'Total_Primary_Index_Pages',
		@index_total_pages,
		1)
	INSERT INTO @results values (N'Average_Number_Of_Index_Rows_Per_Base_Row',
		@index_total_rows/@base_table_rows,
		1);

	-- glogal statistics
	INSERT INTO @results
	SELECT N'Total_Number_Of_ObjectCells_In_Level'+CONVERT(NVARCHAR(10), level)+tag,  sum(total), 0
	FROM #temp_spatial_index_stats
	group by tag, level;

	-- per grid statistics
	INSERT INTO @results
	SELECT N'Total_Number_Of_Interior_ObjectCells_In_Level'+CONVERT(NVARCHAR(10), level)+tag,  sum(total), 0
	FROM #temp_spatial_index_stats
	group by tag, level, cell_attributes
	having cell_attributes = 2;

	INSERT INTO @results
	SELECT N'Total_Number_Of_Intersecting_ObjectCells_In_Level'+CONVERT(NVARCHAR(10), level)+tag,  sum(total), 0
	FROM #temp_spatial_index_stats
	group by tag, level, cell_attributes
	having cell_attributes = 1;

	INSERT INTO @results
	SELECT N'Total_Number_Of_Border_ObjectCells_In_Level'+CONVERT(NVARCHAR(10), level)+tag,  sum(total), 0
	FROM #temp_spatial_index_stats
	group by tag, level, cell_attributes
	having cell_attributes = 0;

	-- Ratio of interior cells to total number of cells.
	-- We normalize the cells to the leaf grid.
	DECLARE @interior_cells FLOAT
	DECLARE @intersect_cells FLOAT
	DECLARE @border_cells FLOAT
	SELECT @interior_cells = (total * gs.leafgrid_normalizer)
		FROM #temp_spatial_index_stats st, @gridsizes gs
		WHERE st.cell_attributes = 2 and gs.level = st.level
	SELECT @intersect_cells = (total * gs.leafgrid_normalizer)
		FROM #temp_spatial_index_stats st, @gridsizes gs
		WHERE st.cell_attributes = 1 and gs.level = st.level
	SELECT @border_cells = (total * gs.leafgrid_normalizer)
		FROM #temp_spatial_index_stats st, @gridsizes gs
		WHERE st.cell_attributes = 0 and gs.level = st.level;

	INSERT INTO @results values (N'Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage',
		100.0*(@interior_cells/(@interior_cells+@intersect_cells+@border_cells)),
		1)
	INSERT INTO @results values (N'Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage',
		100.0*(@intersect_cells/(@interior_cells+@intersect_cells+@border_cells)),
		1)
	INSERT INTO @results values (N'Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage',
		100.0*(@border_cells/(@interior_cells+@intersect_cells+@border_cells)),
		1)
	INSERT INTO @results values (N'Average_Cells_Per_Object_Normalized_To_Leaf_Grid',
		(@interior_cells+@intersect_cells+@border_cells)/@base_table_rows,
		1)
	INSERT INTO @results values (N'Average_Objects_PerLeaf_GridCell',
		@base_table_rows/(@interior_cells+@intersect_cells+@border_cells),
		1)

	-- distinct SRIDs
	INSERT INTO @results values (N'Number_Of_SRIDs_Found',
		(SELECT count( DISTINCT (srid)) AS [SRIDs] FROM #temp_spatial_index_stats),
		1);

	-- size attributes of cell in each grid according to the co-ordinate system
	WITH ComputeCellWidth(depth, proplabel, width)
	AS
	(
		SELECT level+1 AS depth, CONVERT(NVARCHAR(100), (N'Width_Of_Cell_In_Level'+CONVERT(NVARCHAR(10), level))) AS proplabel, ((@maxx-@minx)/size) AS width
		FROM @gridsizes
		WHERE level = 1
		UNION ALL
		SELECT level+1 AS depth, CONVERT(NVARCHAR(100), (N'Width_Of_Cell_In_Level'+CONVERT(NVARCHAR(10), level))) AS proplabel, (width/size) AS width
		FROM @gridsizes g inner join ComputeCellWidth c
		ON g.level = c.depth
	)
	INSERT INTO @results
	SELECT proplabel,  width, 1
	FROM ComputeCellWidth
	OPTION (MAXRECURSION 4);	

	WITH ComputeCellHeight(depth, proplabel, height)
	AS
	(
		SELECT level+1, CONVERT(NVARCHAR(100), (N'Height_Of_Cell_In_Level'+CONVERT(NVARCHAR(10), level))), (@maxy-@miny)/size
		FROM @gridsizes
		WHERE level = 1
		UNION ALL
		SELECT level+1, CONVERT(NVARCHAR(100), (N'Height_Of_Cell_In_Level'+CONVERT(NVARCHAR(10), level))), height/size
		FROM @gridsizes g inner join ComputeCellHeight c
		ON g.level = c.depth
	)
	INSERT INTO @results
	SELECT proplabel,  height, 1
	FROM ComputeCellHeight
	OPTION (MAXRECURSION 4);	

	WITH ComputeCellArea(depth, proplabel, area)
	AS
	(
		SELECT level+1, CONVERT(NVARCHAR(100), (N'Area_Of_Cell_In_Level'+CONVERT(NVARCHAR(10), level))), ((@maxy-@miny)*(@maxx-@minx))/CONVERT(FLOAT, size)
		FROM @gridsizes
		WHERE level = 1
		UNION ALL
		SELECT level+1, CONVERT(NVARCHAR(100), (N'Area_Of_Cell_In_Level'+CONVERT(NVARCHAR(10), level))), area/CONVERT(FLOAT, size)
		FROM @gridsizes g inner join ComputeCellArea c
		ON g.level = c.depth
	)
	INSERT INTO @results
	SELECT proplabel,  area, 1
	FROM ComputeCellArea
	OPTION (MAXRECURSION 4);

	-- percentage of cell coverage in each grid level
	WITH ComputeCellArea(depth, proplabel, cell_area_percent_of_bb)
	AS
	(
		SELECT level+1,
			CONVERT(NVARCHAR(100), (N'CellArea_To_BoundingBoxArea_Percentage_In_Level'+CONVERT(NVARCHAR(10), level))),
			100.0/CONVERT(FLOAT, size)
		FROM @gridsizes
		WHERE level = 1
		UNION ALL
		SELECT level+1,
			CONVERT(NVARCHAR(100), (N'CellArea_To_BoundingBoxArea_Percentage_In_Level'+CONVERT(NVARCHAR(10), level))),
			cell_area_percent_of_bb/CONVERT(FLOAT, g.size)
		FROM @gridsizes g inner join ComputeCellArea c
		ON g.level = c.depth
	)
	INSERT INTO @results
	SELECT proplabel,  cell_area_percent_of_bb, 1
	FROM ComputeCellArea
	OPTION (MAXRECURSION 4);	
	
	IF (@internaltabname is not null)
	BEGIN
		SET @execstr = N'
		WITH PrimaryFilter(pk0, cell_attr)
		AS
		(
			SELECT pk0, max(i.cell_attributes+c.cell_attributes)
			FROM ' + @internaltabname +N' i join
			#temp_window_object_tessellation_candidates c ON i.cell_id  >= c.cell_id and i.cell_id <= c.cell_id_end
			group by pk0
		)
		SELECT ''Number_Of_Rows_Selected_By_Primary_Filter'',
			count(*),
			0
			FROM PrimaryFilter
		UNION ALL
		SELECT ''Number_Of_Rows_Selected_By_Internal_Filter'',
			count(CASE WHEN (cell_attr>=3) THEN 3 ELSE null END),
			0
			FROM PrimaryFilter
		UNION ALL
		SELECT ''Number_Of_Times_Secondary_Filter_Is_Called'',
			count(CASE WHEN (cell_attr<=2) THEN 2 ELSE null END),
			0
			FROM PrimaryFilter ';
		IF (@results_card is not null)
		BEGIN
			SET @execstr = @execstr +
			N'
			UNION ALL
			SELECT ''Number_Of_Rows_Output'', CONVERT(BIGINT, ' + CONVERT(NVARCHAR(20), @results_card) + '),
			0';
		END

		INSERT INTO @results EXEC (@execstr);

		DECLARE @primary_filter_rows float;
		DECLARE @internal_filter_rows float;

		SELECT @primary_filter_rows = CONVERT(float, propvalue) FROM @results where propname = 'Number_Of_Rows_Selected_By_Primary_Filter';
		SELECT @internal_filter_rows = CONVERT(float, propvalue) FROM @results where propname = 'Number_Of_Rows_Selected_By_Internal_Filter';
		
		INSERT INTO @results
			(propname, propvalue, verbose)
		VALUES
			('Percentage_Of_Rows_NotSelected_By_Primary_Filter', ((@base_table_rows - @primary_filter_rows) / @base_table_rows) * 100.0, 0);
		
		INSERT INTO @results
			(propname, propvalue, verbose)
		VALUES
			('Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter', (@internal_filter_rows/ @primary_filter_rows) * 100.0, 0);
		
		IF (@results_card is not null)
		BEGIN
			INSERT INTO @results
				(propname, propvalue, verbose)
			VALUES
				('Internal_Filter_Efficiency', (@internal_filter_rows / @results_card) * 100.0, 0);

			INSERT INTO @results
				(propname, propvalue, verbose)
			VALUES
				('Primary_Filter_Efficiency', (@results_card / @primary_filter_rows) * 100.0, 0);
		END
	END

	IF @do_output_xml = 0
	BEGIN
			SELECT propname, isnull(propvalue, 0.0) propvalue FROM @results WHERE verbose <= @verboseoutput
	END
	ELSE
	BEGIN
		SELECT @xml_output =
		(
			SELECT
				Base_Table_Rows,
				Bounding_Box_xmin,
				Bounding_Box_ymin,
				Bounding_Box_xmax,
				Bounding_Box_ymax,
				Grid_Size_Level_1,
				Grid_Size_Level_2,
				Grid_Size_Level_3,
				Grid_Size_Level_4,
				Cells_Per_Object,
				Total_Primary_Index_Rows,
				Total_Primary_Index_Pages,
				Average_Number_Of_Index_Rows_Per_Base_Row,
				Total_Number_Of_ObjectCells_In_Level0_For_QuerySample,
				Total_Number_Of_ObjectCells_In_Level0_In_Index,
				Total_Number_Of_ObjectCells_In_Level1_For_QuerySample,
				Total_Number_Of_ObjectCells_In_Level1_In_Index,
				Total_Number_Of_ObjectCells_In_Level2_For_QuerySample,
				Total_Number_Of_ObjectCells_In_Level2_In_Index,
				Total_Number_Of_ObjectCells_In_Level3_For_QuerySample,
				Total_Number_Of_ObjectCells_In_Level3_In_Index,
				Total_Number_Of_ObjectCells_In_Level4_For_QuerySample,
				Total_Number_Of_ObjectCells_In_Level4_In_Index,
				Total_Number_Of_Interior_ObjectCells_In_Level1_For_QuerySample,
				Total_Number_Of_Interior_ObjectCells_In_Level1_In_Index,
				Total_Number_Of_Interior_ObjectCells_In_Level2_For_QuerySample,
				Total_Number_Of_Interior_ObjectCells_In_Level2_In_Index,
				Total_Number_Of_Interior_ObjectCells_In_Level3_For_QuerySample,
				Total_Number_Of_Interior_ObjectCells_In_Level3_In_Index,
				Total_Number_Of_Interior_ObjectCells_In_Level4_For_QuerySample,
				Total_Number_Of_Interior_ObjectCells_In_Level4_In_Index,
				Total_Number_Of_Intersecting_ObjectCells_In_Level1_For_QuerySample,
				Total_Number_Of_Intersecting_ObjectCells_In_Level1_In_Index,
				Total_Number_Of_Intersecting_ObjectCells_In_Level2_For_QuerySample,
				Total_Number_Of_Intersecting_ObjectCells_In_Level2_In_Index,
				Total_Number_Of_Intersecting_ObjectCells_In_Level3_For_QuerySample,
				Total_Number_Of_Intersecting_ObjectCells_In_Level3_In_Index,
				Total_Number_Of_Intersecting_ObjectCells_In_Level4_For_QuerySample,
				Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index,
				Total_Number_Of_Border_ObjectCells_In_Level0_For_QuerySample,
				Total_Number_Of_Border_ObjectCells_In_Level0_In_Index,
				Total_Number_Of_Border_ObjectCells_In_Level1_For_QuerySample,
				Total_Number_Of_Border_ObjectCells_In_Level1_In_Index,
				Total_Number_Of_Border_ObjectCells_In_Level2_For_QuerySample,
				Total_Number_Of_Border_ObjectCells_In_Level2_In_Index,
				Total_Number_Of_Border_ObjectCells_In_Level3_For_QuerySample,
				Total_Number_Of_Border_ObjectCells_In_Level3_In_Index,
				Total_Number_Of_Border_ObjectCells_In_Level4_For_QuerySample,
				Total_Number_Of_Border_ObjectCells_In_Level4_In_Index,
				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_SRIDs_Found,
				Width_Of_Cell_In_Level1,
				Width_Of_Cell_In_Level2,
				Width_Of_Cell_In_Level3,
				Width_Of_Cell_In_Level4,
				Height_Of_Cell_In_Level1,
				Height_Of_Cell_In_Level2,
				Height_Of_Cell_In_Level3,
				Height_Of_Cell_In_Level4,
				Area_Of_Cell_In_Level1,
				Area_Of_Cell_In_Level2,
				Area_Of_Cell_In_Level3,
				Area_Of_Cell_In_Level4,
				CellArea_To_BoundingBoxArea_Percentage_In_Level1,
				CellArea_To_BoundingBoxArea_Percentage_In_Level2,
				CellArea_To_BoundingBoxArea_Percentage_In_Level3,
				CellArea_To_BoundingBoxArea_Percentage_In_Level4,
				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,
				Internal_Filter_Efficiency,
				Primary_Filter_Efficiency
			FROM
				(SELECT propname, isnull(propvalue, 0.0) as propvalue FROM @results WHERE verbose <= @verboseoutput) p
			pivot
			(
				MAX(propvalue)
				FOR propname in
				(
					[Base_Table_Rows],
					[Bounding_Box_xmin],
					[Bounding_Box_ymin],
					[Bounding_Box_xmax],
					[Bounding_Box_ymax],
					[Grid_Size_Level_1],
					[Grid_Size_Level_2],
					[Grid_Size_Level_3],
					[Grid_Size_Level_4],
					[Cells_Per_Object],
					[Total_Primary_Index_Rows],
					[Total_Primary_Index_Pages],
					[Average_Number_Of_Index_Rows_Per_Base_Row],
					[Total_Number_Of_ObjectCells_In_Level0_For_QuerySample],
					[Total_Number_Of_ObjectCells_In_Level0_In_Index],
					[Total_Number_Of_ObjectCells_In_Level1_For_QuerySample],
					[Total_Number_Of_ObjectCells_In_Level1_In_Index],
					[Total_Number_Of_ObjectCells_In_Level2_For_QuerySample],
					[Total_Number_Of_ObjectCells_In_Level2_In_Index],
					[Total_Number_Of_ObjectCells_In_Level3_For_QuerySample],
					[Total_Number_Of_ObjectCells_In_Level3_In_Index],
					[Total_Number_Of_ObjectCells_In_Level4_For_QuerySample],
					[Total_Number_Of_ObjectCells_In_Level4_In_Index],
					[Total_Number_Of_Interior_ObjectCells_In_Level1_For_QuerySample],
					[Total_Number_Of_Interior_ObjectCells_In_Level1_In_Index],
					[Total_Number_Of_Interior_ObjectCells_In_Level2_For_QuerySample],
					[Total_Number_Of_Interior_ObjectCells_In_Level2_In_Index],
					[Total_Number_Of_Interior_ObjectCells_In_Level3_For_QuerySample],
					[Total_Number_Of_Interior_ObjectCells_In_Level3_In_Index],
					[Total_Number_Of_Interior_ObjectCells_In_Level4_For_QuerySample],
					[Total_Number_Of_Interior_ObjectCells_In_Level4_In_Index],
					[Total_Number_Of_Intersecting_ObjectCells_In_Level1_For_QuerySample],
					[Total_Number_Of_Intersecting_ObjectCells_In_Level1_In_Index],
					[Total_Number_Of_Intersecting_ObjectCells_In_Level2_For_QuerySample],
					[Total_Number_Of_Intersecting_ObjectCells_In_Level2_In_Index],
					[Total_Number_Of_Intersecting_ObjectCells_In_Level3_For_QuerySample],
					[Total_Number_Of_Intersecting_ObjectCells_In_Level3_In_Index],
					[Total_Number_Of_Intersecting_ObjectCells_In_Level4_For_QuerySample],
					[Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index],
					[Total_Number_Of_Border_ObjectCells_In_Level0_For_QuerySample],
					[Total_Number_Of_Border_ObjectCells_In_Level0_In_Index],
					[Total_Number_Of_Border_ObjectCells_In_Level1_For_QuerySample],
					[Total_Number_Of_Border_ObjectCells_In_Level1_In_Index],
					[Total_Number_Of_Border_ObjectCells_In_Level2_For_QuerySample],
					[Total_Number_Of_Border_ObjectCells_In_Level2_In_Index],
					[Total_Number_Of_Border_ObjectCells_In_Level3_For_QuerySample],
					[Total_Number_Of_Border_ObjectCells_In_Level3_In_Index],
					[Total_Number_Of_Border_ObjectCells_In_Level4_For_QuerySample],
					[Total_Number_Of_Border_ObjectCells_In_Level4_In_Index],
					[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_SRIDs_Found],
					[Width_Of_Cell_In_Level1],
					[Width_Of_Cell_In_Level2],
					[Width_Of_Cell_In_Level3],
					[Width_Of_Cell_In_Level4],
					[Height_Of_Cell_In_Level1],
					[Height_Of_Cell_In_Level2],
					[Height_Of_Cell_In_Level3],
					[Height_Of_Cell_In_Level4],
					[Area_Of_Cell_In_Level1],
					[Area_Of_Cell_In_Level2],
					[Area_Of_Cell_In_Level3],
					[Area_Of_Cell_In_Level4],
					[CellArea_To_BoundingBoxArea_Percentage_In_Level1],
					[CellArea_To_BoundingBoxArea_Percentage_In_Level2],
					[CellArea_To_BoundingBoxArea_Percentage_In_Level3],
					[CellArea_To_BoundingBoxArea_Percentage_In_Level4],
					[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],
					[Internal_Filter_Efficiency],
					[Primary_Filter_Efficiency]
				)
			) AS pvt
			for XML PATH(''), TYPE
		)
	END
END

 
Last revision 2008RTM
See also

  sp_help_spatial_geography_index_helper (Procedure)
sp_help_spatial_geometry_index_helper (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash