Very similar to 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.
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON GO
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?