Post reply

sys.dm_db_index_usage_stats not updated after bulk insert?

  • September 26, 2012 at 6:20 am

    #262937

    Hi all,

    When analyzing our ETL process (SSIS packages) for a newly created data warehouse on a SQL 2008 box, I was looking at seeks/scans/lookup index usage stats for some optimization. I realized the user_updates value was 0 on all affected tables for the clustered index. This was after executing the packages, which use OLE DB destinations/BULK INSERT to insert the data. This surprised me as I was expecting millions of updates in some cases.

    I tried inserting 1 row by hand in one of the tables and that made the value for user_updates raise from 0 to 1.

    Have searched a bit around the web but I have not found anything about this. Has anyone of you ran into this behavior? Am I missing something and is this something known, or could someone confirm this behavior?

    It's not a big deal for me, was just surprised about missing statistics 🙂

    /*----------------------------------------------------------------------------------*/
    Always eager to learn.

  • September 26, 2012 at 7:17 am

    #1542380

    Please check following URL:

    http://sqlblog.com/blogs/louis_davidson/archive/2007/07/22/sys-dm-db-index-usage-stats.aspx

  • September 26, 2012 at 7:27 am

    #1542390

    Thanks for your reply, however that doesn't say anything about statistics not being updated after BULK INSERTs.

    About this view sys.dm_db_index_usage_stats, BOL states: "Returns counts of different types of index operations and the time each type of operation was last performed." and a bit further down "The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view."

    Just wondering why the records being inserted through BULK INSERT aren't counted in the user_updates statistic.

    /*----------------------------------------------------------------------------------*/
    Always eager to learn.

  • September 26, 2012 at 7:40 am

    #1542404

    Check AUTO_CREATE_STATISTICS

  • September 26, 2012 at 7:47 am

    #1542411

    I appreciate your help, but I'm not after statistics for query optimization.

    My question is about the value in user_updates column in DMV sys.dm_db_index_usage_stats, it doesn't seem to be updated after BULK INSERT operations and I was wondering why.

    /*----------------------------------------------------------------------------------*/
    Always eager to learn.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply