February 19, 2018 at 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
February 19, 2018 at 11:55 pm
santosh.mane - Monday, February 19, 2018 11:34 PMHi,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 ?
February 20, 2018 at 2:42 am
Thanks for the information.
February 20, 2018 at 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
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
February 20, 2018 at 3:20 am
santosh.mane - Monday, February 19, 2018 11:34 PMHi,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
February 22, 2018 at 7:50 am
andrew gothard - Tuesday, February 20, 2018 3:14 AMHi.
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.
February 22, 2018 at 9:10 am
Is this consistently happening multiple times, or just this time?
February 26, 2018 at 3:26 am
Steve Jones - SSC Editor - Thursday, February 22, 2018 9:10 AMIs this consistently happening multiple times, or just this time?
It is happening every time we execute the Maintenance job
February 26, 2018 at 4:28 am
santosh.mane - Thursday, February 22, 2018 7:50 AMandrew gothard - Tuesday, February 20, 2018 3:14 AMHi.
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.
March 1, 2018 at 3:34 am
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