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

  • santosh.mane

    SSC-Addicted

    Points: 446

    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

  • subramaniam.chandrasekar

    SSCarpal Tunnel

    Points: 4108

    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 ?

  • santosh.mane

    SSC-Addicted

    Points: 446

    Thanks for the information.

  • andrew gothard

    SSChampion

    Points: 12127

    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.

  • John Mitchell-245523

    SSC Guru

    Points: 148192

    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

  • santosh.mane

    SSC-Addicted

    Points: 446

    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.

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713651

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

  • santosh.mane

    SSC-Addicted

    Points: 446

    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

  • andrew gothard

    SSChampion

    Points: 12127

    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.

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713651

    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 10 (of 10 total)

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