November 4, 2015 at 12:13 pm
Hi,
I saw this job below which runs from 20 to 20 minutos on my production server.
Is it normal to have a statistics update every 20 minutos?
DECLARE @AlterSQL varchar(255)
Print '-------------------------------- Doing Stats --------------------------------'
DECLARE UpdStatsCursor CURSOR FOR
SELECT distinct 'Update Statistics '+d.name+'.['+c.name+'] ;'
FROM sys.indexes AS b
JOIN sys.objects AS c on b.object_id = c.object_id
JOIN sys.schemas AS d on c.schema_id = d.schema_id
WHERE b.name is not NULL
and c.name like 'MS%'
OPEN UpdStatsCursor
FETCH NEXT FROM UpdStatsCursor INTO @AlterSQL
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ( @AlterSQL )
EXEC ( @AlterSQL )
FETCH NEXT FROM UpdStatsCursor INTO @AlterSQL
END
CLOSE UpdStatsCursor
DEALLOCATE UpdStatsCursor
Print '-------------------------------- End --------------------------------'
November 4, 2015 at 12:31 pm
That's not automatic stats update, that's a manual update job. If you're seeing that every 20 minutes, then you have a SQL Agent job that's scheduled to run every 20 minutes.
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
November 4, 2015 at 5:22 pm
river1 (11/4/2015)
Hi,
I saw this job below which runs from 20 to 20 minutos on my production server.
Is it normal to have a statistics update every 20 minutos?
...
and c.name like 'MS%'
...
No, that's not normal. By the name restriction, it seems like the code is intended to update stats for MS's internal replication tables. Not sure why someone felt that should be done every 20 minutes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 5, 2015 at 2:29 am
How can I see if the statiscts of other tables are ok or if they need to be updated?
November 5, 2015 at 6:21 am
river1 (11/5/2015)
How can I see if the statiscts of other tables are ok or if they need to be updated?
You can run this query:
SELECT
[sch].[name] + '.' + [so].[name] AS [TableName],
[ss].[name] AS [Statistic],
[ss].[auto_Created] AS [WasAutoCreated],
[ss].[user_created] AS [WasUserCreated],
[ss].[has_filter] AS [IsFiltered],
[ss].[filter_definition] AS [FilterDefinition],
[ss].[is_temporary] AS [IsTemporary],
[sp].[last_updated] AS [StatsLastUpdated],
[sp].[rows] AS [RowsInTable],
[sp].[rows_sampled] AS [RowsSampled],
[sp].[unfiltered_rows] AS [UnfilteredRows],
[sp].[modification_counter] AS [RowModifications],
[sp].[steps] AS [HistogramSteps],
CAST(100 * [sp].[modification_counter] / [sp].[rows] AS DECIMAL(18,2)) AS [PercentChange]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties] ([so].[object_id], [ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND CAST(100 * [sp].[modification_counter] / [sp].[rows] AS DECIMAL(18,2)) >= 10.00
ORDER BY CAST(100 * [sp].[modification_counter] / [sp].[rows] AS DECIMAL(18,2)) DESC;
Igor Micev,My blog: www.igormicev.com
November 5, 2015 at 6:23 am
ScottPletcher (11/4/2015)
river1 (11/4/2015)
Hi,
I saw this job below which runs from 20 to 20 minutos on my production server.
Is it normal to have a statistics update every 20 minutos?
...
and c.name like 'MS%'
...
No, that's not normal. By the name restriction, it seems like the code is intended to update stats for MS's internal replication tables. Not sure why someone felt that should be done every 20 minutes.
It's normal for some systems. Imagine a system that updates its data non-stop. e.g. sport betting application offering odds that are changing all the time.
I'm maintaining such a system and I update the stats on every 30 minutes.
Igor Micev,My blog: www.igormicev.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply