September 10, 2014 at 8:53 am
I have rebuild index job setup every week. Questions:
1. Do I need to run update statistics?
2. If, so then both 'Column statistic only' and 'Index statistic only' or just one 'Column statistic only'?
4. Should I run before index rebuild job or after?
since I mention above I run rebuild index job once a week, should I run update statistic every day or every week. I have 600GB database.
September 10, 2014 at 9:04 am
dinsab11 (9/10/2014)
I have rebuild index job setup every week. Questions:1. Do I need to run update statistics?
You don't have to, but it's a damn good idea. Column statistics only, since index statistics get updated by the index rebuild. Before or after makes no difference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2014 at 9:13 am
I don't think u need to update column stats as well. If your rebuilds and updates have affected enough rows in table I.e. the threshold value for triggering auto update statistics. Then it will auto update once you fire that column specific query.
Usually this threshold is 500+20% of total rows.
September 10, 2014 at 9:35 am
Thank you for replies. last question, what's the BEST query to check statistics on all tables of A DATABASE on production?
September 10, 2014 at 10:04 am
Check them for what?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2014 at 10:05 am
er.mayankshukla (9/10/2014)
I don't think u need to update column stats as well. If your rebuilds and updates have affected enough rows in table I.e. the threshold value for triggering auto update statistics.
You do (well, not essential but a very good idea). Index rebuilds aren't a data modification, they don't change the value in a column. Hence they do not count towards the auto update threshold.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2014 at 10:15 am
Yes, rebuilding won't count towards data modification. But if he is planning for indexes rebuilt, I guess some heavy updates might be the reason for that. So in case we have some specific column being used by a query let SQL auto update it , rather than updating it manually.
Thanks Gail for clarifying that.
September 10, 2014 at 10:27 am
Hey dinsab ,
May I know the reason of rebuilding indexes.
Due you see a lot of fragmentation on your indexes.
September 10, 2014 at 10:38 am
er.mayankshukla (9/10/2014)
I guess some heavy updates might be the reason for that
Might be. Or might not. I prefer not to assume such things when I have no idea.
Even if heavy updates are the reason for the index rebuilds, and not just regular index maintenance, the updates may not have made enough changes to the columns in question to trigger stats updates. But without in-depth analysis of the OP's system, we have no way of knowing
let SQL auto update it , rather than updating it manually.
Personally I prefer to do stats maintenance out of peak times, so that queries don't have to wait on auto stats updates during my busy periods. I also prefer to be able to specify stats updates with full scan rather than letting SQL pick the sampling rate and possibly generating sub-par statistics. But of course, I could be doing things completely wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2014 at 11:21 am
@gilamonster: My questin was what's the best way to check when statistics were update last time on A DATABASE (all table).
As you said that rebuild index only updates 'index statiscits' not the column statistics. You're right on this. Thank you.
@er.mayankshukla: I thought rebuild index should be a part of data maintaince to eliminate index fragmentaions.
September 10, 2014 at 11:29 am
Hello experts,
Can you please tell me this Glenn Berry query gives both statistics (index and column) or jut Index stat? I would really appreciate you inputs.
-- When were Statistics last updated on all indexes? (Query 51) (Statistics Update)
SELECT o.name, i.name AS [Index Name],
STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
s.auto_created, s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);
September 10, 2014 at 11:32 am
dinsab11 (9/10/2014)
@GilaMonster: My questin was what's the best way to check when statistics were update last time on A DATABASE (all table).
Query sys.stats and use the STATS_DATE function. It takes parameters of object_id and stats_id
Glenn's query is only going to get index stats (because it queries off sys.indexes)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2014 at 11:40 am
To get last updated date you can also use dbcc show statistics
September 10, 2014 at 11:49 am
Thanks you both millions.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply