Drop statistics

• HappyGeek

  SSCoach

  Points: 18729

  More actions

  September 27, 2016 at 12:24 pm

  #329981

  How is it possible to drop statistics on a view when querying sys stats returns nothing? This is with a linked server.

  ...

• Grant Fritchey

  SSC Guru

  Points: 399009

  More actions

  September 28, 2016 at 7:16 am

  #1903630

  A view doesn't have statistics.

  A materialized view does, because a materialized view is an index. You can't drop statistics from an index.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• HappyGeek

  SSCoach

  Points: 18729

  More actions

  September 28, 2016 at 7:55 am

  #1903651

  Grant Fritchey (9/28/2016)

  ---

  A view doesn't have statistics.

  A materialized view does, because a materialized view is an index. You can't drop statistics from an index.

  Grant

  Many thanks issue solved, the view was to a linked server in an AlwaysOn AG, the situation is described here https://connect.microsoft.com/SQLServer/feedback/details/780062/sql2012-could-not-locate-statistics-on-secondary-replica, dropping the index on the primary server solved the issue, for now at least, but given it can re-occur I now need to find a means of spotting it early!

  ...