Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

Even after


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?

share|improve this question

migrated from Dec 10 '12 at 18:56

This question came from our site for system and network administrators.

Have you reviewed the query plan to see if partition elimination is occurring as expected? Have you identified if an auto statistics update is occurring after your inserts? What wait type are you observing when your queries are slow? – Mike Fal Dec 10 '12 at 19:11
I'm not sure what your question is. Is it why you aren't seeing partition elimination? If so, see @SQLKiwi's blog post on the topic (…). As for auto create stats asynchronously, the reason you don't see it there is because I believe that is just using DATABASEPROPERTYEX(), and there is no option to check for auto update states async. Instead use the following query to verify: select name, is_auto_update_stats_async_on from sys.databases where name = 'YourDb'; – Thomas Stringer Dec 10 '12 at 19:15
up vote -1 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.