Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

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?

share|improve this question
    
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
1  
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 (sqlblog.com/blogs/paul_white/archive/2012/09/12/…). 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
add comment

migrated from serverfault.com Dec 10 '12 at 18:56

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

1 Answer

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
add comment

Your Answer

 
discard

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.