SQL Server 2012, update Stats and Index Organize taking 16 hours for execution‌‌

  • Hi,

    We have SQL Server 2012, On production database SQL job update Stats and Index Organize taking 16 hours for execution. Size of the database is around 300 GB.
    Please advise urgently how to resolve this issue.

    Thanks & Regards,
    Santosh

  • santosh.mane - Monday, February 19, 2018 11:34 PM

    Hi,

    We have SQL Server 2012, On production database SQL job update Stats and Index Organize taking 16 hours for execution. Size of the database is around 300 GB.
    Please advise urgently how to resolve this issue.

    Thanks & Regards,
    Santosh

    Please include only the main prod tables for re indexing and update stats. Also please check whether any parallel activities had happened between your index job runs ?

  • Thanks for the information.

  • Hi.
    One thing I would look for is whether any of the legacy LoB datatypes are used in any of the tables, text, ntext and image.  I have seen a number of cases where their use, especially on large tables, causes this type of problem, particularly with stats updates.
    Try running this;

    SELECT

    Schemas.name + '.' + Tables.name TableName, Columns.Name ColumnName, Types.Name DataType

    FROM

    sys.schemas Schemas

    INNER JOIN

    sys.tables Tables

    ON Schemas.schema_id = Tables.schema_id

    INNER JOIN

    sys.stats Stats

    ON Tables.object_id = Stats.object_id

    INNER JOIN

    sys.stats_columns StatsColumns

    ON Stats.object_id = StatsColumns.object_id AND

    Stats.stats_id = StatsColumns.stats_id

    INNER JOIN

    sys.columns Columns

    ON StatsColumns.object_id = Columns.object_id AND

    StatsColumns.column_id = Columns.column_id

    INNER JOIN

    sys.types AS Types

    ON Columns.system_type_id = Types.system_type_id

    WHERE

    Types.name IN ('image', 'ntext', 'text')

    If so, if you can get back with your results, that'd be helpful

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • santosh.mane - Monday, February 19, 2018 11:34 PM

    Hi,

    We have SQL Server 2012, On production database SQL job update Stats and Index Organize taking 16 hours for execution. Size of the database is around 300 GB.
    Please advise urgently how to resolve this issue.

    Thanks & Regards,
    Santosh

    How is the job doing the update?  If it's using a Maintenance Plan, stop using that immediately, and get Ola Hallengren's solution or something similar in order to do targeted maintenance.

    John

  • andrew gothard - Tuesday, February 20, 2018 3:14 AM

    Hi.
    One thing I would look for is whether any of the legacy LoB datatypes are used in any of the tables, text, ntext and image.  I have seen a number of cases where their use, especially on large tables, causes this type of problem, particularly with stats updates.
    Try running this;

    SELECT

    Schemas.name + '.' + Tables.name TableName, Columns.Name ColumnName, Types.Name DataType

    FROM

    sys.schemas Schemas

    INNER JOIN

    sys.tables Tables

    ON Schemas.schema_id = Tables.schema_id

    INNER JOIN

    sys.stats Stats

    ON Tables.object_id = Stats.object_id

    INNER JOIN

    sys.stats_columns StatsColumns

    ON Stats.object_id = StatsColumns.object_id AND

    Stats.stats_id = StatsColumns.stats_id

    INNER JOIN

    sys.columns Columns

    ON StatsColumns.object_id = Columns.object_id AND

    StatsColumns.column_id = Columns.column_id

    INNER JOIN

    sys.types AS Types

    ON Columns.system_type_id = Types.system_type_id

    WHERE

    Types.name IN ('image', 'ntext', 'text')

    If so, if you can get back with your results, that'd be helpful

    No there are no objects with image or ntext or text.

  • Is this consistently happening multiple times, or just this time?

  • Steve Jones - SSC Editor - Thursday, February 22, 2018 9:10 AM

    Is this consistently happening multiple times, or just this time?

    It is happening every time we execute the Maintenance job

  • santosh.mane - Thursday, February 22, 2018 7:50 AM

    andrew gothard - Tuesday, February 20, 2018 3:14 AM

    Hi.
    One thing I would look for is whether any of the legacy LoB datatypes are used in any of the tables, text, ntext and image.  I have seen a number of cases where their use, especially on large tables, causes this type of problem, particularly with stats updates.
    Try running this;

    SELECT

    Schemas.name + '.' + Tables.name TableName, Columns.Name ColumnName, Types.Name DataType

    FROM

    sys.schemas Schemas

    INNER JOIN

    sys.tables Tables

    ON Schemas.schema_id = Tables.schema_id

    INNER JOIN

    sys.stats Stats

    ON Tables.object_id = Stats.object_id

    INNER JOIN

    sys.stats_columns StatsColumns

    ON Stats.object_id = StatsColumns.object_id AND

    Stats.stats_id = StatsColumns.stats_id

    INNER JOIN

    sys.columns Columns

    ON StatsColumns.object_id = Columns.object_id AND

    StatsColumns.column_id = Columns.column_id

    INNER JOIN

    sys.types AS Types

    ON Columns.system_type_id = Types.system_type_id

    WHERE

    Types.name IN ('image', 'ntext', 'text')

    If so, if you can get back with your results, that'd be helpful

    No there are no objects with image or ntext or text.

    Thanks for getting back.
    Well, that wasn't what I was expecting. 
    What maintenance tools are you using?  First off, can you see if it's the re-indexing or the stats update that's taking longest.  If your maintenance tool logs activity (and if it doesn't, I would personally swap it for something that does otherwise troubleshooting consists mostly of guessing) on an object by object basis, can you see which object or objects are taking a disproportionate length of time?

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • It would be helpful to know exactly what code/process is executing this, as noted by  Andrew. You might be performing quite a bit of unnecessary work and overloading the system. Have you checked latency and metrics on your storage? If you're reindexing and updating all statistics, you might be rewriting the entire database. I wouldn't expect this to take 16 hours on 300GB, but depending on your hardware resources, perhaps things are just overloaded. Is this a physical instance or a VM?

Viewing 10 posts - 1 through 9 (of 9 total)

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