Large inserts breaking indexed queries Very similar to https://serverfault.com/questions/163442/large-insert-update-in-table-causes-timeout-while-sql-server-re-arrange-indexes only I am not using SSIS with only occassional large (predominently annual) inserts from another staging table in a stored procedure and the target table is partitioned with data being written to an existing partition. All indexes are aligned.

I've tried setting AUTO_UPDATE_STATISTICS_ASYNC ON but that doesn't seem to help. Queries are still blocked for about 10 minutes while the indexing catches up even when they should use partition elimination and not hit the inserted data.

I'd rather have a fast but stale result than a time-out.

Even after 

ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO


sp_helpdb MyDB.status 

still reads 


  Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
  Recovery=SIMPLE, Version=655, Collation=Latin1_General_CI_AS,
  SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics,
  IsFullTextEnabled


Shouldn't it have something to indicate that async is on?

Is there anything else I can do to make sure queries aren't slowed down?
I've found the problem. It's because one of the columns in the partitioned table has a varchar(max). Limiting it to varchar(120) resolves to problem. It seems ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON requires the online index build.

I should have said I'm on 2008 R2

Thanks though.