locked
Spatial index statistics missing RRS feed

  • Question

  • If I run a query that utilises a spatial index the query plan says the statistics are missing.

     

    This is born out by the fact the optimiser thought estimated the number of rows as 11,000 and the actual number was 123.

     

    All in all the estimated number of rows and the actual number are wildly out. This is after building the indexes and not making any modifications to the data

    Wednesday, December 19, 2007 11:35 PM

Answers

  • Spatial indices are implemented as internal tables that keep their own statistics.

     

    If you'd like to send me the data and query, we can take a look at this particular case.

     

    Here is some TSQL to look at the statistics of the spatial indices (provided as-is without implied or expressed warranty Wink, note it is using varchar instead of nvarchar for example ):

     

    declare c cursor static for

    select object_id, name, index_id from sys.indexes where type_desc = 'SPATIAL'

    open c

          declare @obj int

          declare @statsname varchar(max)

          declare @indid int

          declare @tname varchar(max)

    fetch next from c into @obj, @statsname, @indid

    while (@@fetch_status = 0)

    begin

          declare @internaltablename varchar(max)

          declare @stmt varchar(max)

          fetch next from c into @obj, @statsname, @indid

          select top 1 @tname = name from sys.objects where object_id = @obj

          set @internaltablename = 'sys.extended_index_' + cast(@obj as varchar(max)) + '_' + cast(@indid as varchar(max))

        select 'Showing statistics for ', @tname

          select @stmt = 'dbcc show_statistics (''' + @internaltablename + ''', ''' + @statsname + ''')'

          exec (@stmt)

    end

    close c

    deallocate c

     

    I hope this helps a bit

    Michael

    Wednesday, January 9, 2008 9:11 PM

All replies

  • Simon,

     

    My guess is that the spatial indexes (which are different to normal indexes of course) don't use statistics in the same way. Do you get the same problem if you explicitly create statistics on those columns?

     

    Rob

    Monday, December 24, 2007 2:58 AM
  • Spatial indices are implemented as internal tables that keep their own statistics.

     

    If you'd like to send me the data and query, we can take a look at this particular case.

     

    Here is some TSQL to look at the statistics of the spatial indices (provided as-is without implied or expressed warranty Wink, note it is using varchar instead of nvarchar for example ):

     

    declare c cursor static for

    select object_id, name, index_id from sys.indexes where type_desc = 'SPATIAL'

    open c

          declare @obj int

          declare @statsname varchar(max)

          declare @indid int

          declare @tname varchar(max)

    fetch next from c into @obj, @statsname, @indid

    while (@@fetch_status = 0)

    begin

          declare @internaltablename varchar(max)

          declare @stmt varchar(max)

          fetch next from c into @obj, @statsname, @indid

          select top 1 @tname = name from sys.objects where object_id = @obj

          set @internaltablename = 'sys.extended_index_' + cast(@obj as varchar(max)) + '_' + cast(@indid as varchar(max))

        select 'Showing statistics for ', @tname

          select @stmt = 'dbcc show_statistics (''' + @internaltablename + ''', ''' + @statsname + ''')'

          exec (@stmt)

    end

    close c

    deallocate c

     

    I hope this helps a bit

    Michael

    Wednesday, January 9, 2008 9:11 PM
  • I am running into the same situation while running SQL Server 2008 SP1, at least a couple of my datasets seem to generate warnings.

    In short, the execution plan says the choosen spatial index is missing statistics on several, but not all, columns.  The spatial index being used has good performance.  The spatial column of interest has five different indexes on it, for testing purposes, and I wonder if the missing statistics are affecting which spatial index is being chosen.  Or, are the warning messages red herrings because the Missing Column Statistics event class doesn't fully understand spatial indexes?

    I ran the TSQL provided by M. Rys, and the results say some statistics do exist for the spatial index being chosen.  As I said, the performance is adequate, and I am willing to live with the warning messages.  I am simply trying to determine if the warnings are affecting performance or are red herrings.

    Tuesday, May 12, 2009 3:43 PM
  • This behaviour still exists in SQL Server Denali CTP3 - I believe you're correct in saying that the warning messages for missing statistics for spatial columns are red herrings, but it would be nice to hear that confirmed....
    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, July 20, 2011 1:54 PM
    Answerer
  • I think the 2nd fetch statement needs moving to the bottom of the while loop, otherwise the first set of results displayed is the 2nd set of results from the cursor.

    rgds

    Thursday, June 18, 2015 9:45 AM