Number of Rows in Base Table Incorrect in sp_help_spatial_geography_index_xml by Bob Beauchemin

Closed

as Fixed

Type:	Bug
ID:	475838
Opened:	7/20/2009 2:25:59 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

The Number of Rows in Base Table reported by the sp_help_spatial_geography_index_xml is incorrect (it's too high), making the Average_Number_Of_Index_Rows_Per_Base_Row incorrect also.

This is SQL Server 2008 SP1, correct counts were reported before SP1.

I'd suspect it was the same issue with the other spatial index stored procs too (sp_help_spatial_geography_index, sp_help_spatial_geometry_index, sp_help_spatial_geometry_index_xml) but don't have a repro for those.

Details (expand)

English

SQL Server 2008 SP1

SQL Engine

Win2003 Standard Server (SP2)

US English

Count the number of rows in the table with SELECT COUNT(*). Then run the spatial index proc and compare with Base_Table_Rows.

They should be the same.

They are different, Base_Table_Rows is too high.

File Name	Submitted By	Submitted On	File Size
Geonames_hhhh_256.xml (restricted)	Bob Beauchemin	7/20/2009	-
spatial_counts.sql (restricted)	Bob Beauchemin	7/20/2009	-
SpatialBackupAndAnalysis.zip (restricted)	Bob Beauchemin	11/5/2009	-

Posted by Microsoft on 4/30/2010 at 8:40 AM

Hi,

this bug will be fixed for next CTP.

Posted by Bob Beauchemin on 11/5/2009 at 5:45 PM

Here's a repro script and a database backup. Some additional observations:

1. It doesn't happen with every spatial table and spatial index. It usually happens with large tables/indexes; it would be hard to post a 3 gb spatial database. So I've come up with the easier (and smallest) one to demonstrate.
2. It seems to happen more if the spatial database was built in pre-SP1 version of SQL Server. I built a lot of them pre-SP1; all of these seem to exhibit the problem.

Posted by Microsoft on 11/5/2009 at 7:21 AM

Hi Bob, we cannot repro the issue, can you please give us a database backup file which contains the issue you described.

Thanks,
Milan

Posted by Microsoft on 7/21/2009 at 6:50 PM

Thanks Bob... we will take a look.

Microsoft