bibi92
asked on
CPU high usage when update statistics
Hello,
The following procedure generate CPU high usage, how can I resolve :
Thanks
regards
The following procedure generate CPU high usage, how can I resolve :
CREATE PROCEDURE runStatistics
AS
DECLARE @cur CURSOR,
@statistic_sql NVARCHAR(256)
BEGIN
SET NOCOUNT ON
SET @cur = CURSOR FOR
SELECT CASE
WHEN X.[Total Rows] > 100000000 --hundred million rows
THEN CASE
WHEN [Percent Modified] > 0.01
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 100M Big Table Rule'
END
WHEN X.[Total Rows] > 10000000 --ten million rows
THEN CASE
WHEN [Percent Modified] > 0.02
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 10M Big Table Rule'
END
WHEN X.[Total Rows] > 1000000 --million rows
THEN CASE
WHEN [Percent Modified] > 0.05
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 1M Big Table Rule'
END
WHEN X.[Total Rows] > 100000 --hundred thousand rows
THEN CASE
WHEN [Percent Modified] > 0.1
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 100K Big Table Rule'
END
WHEN X.[Total Rows] > 10000 --ten thousand rows
THEN CASE
WHEN [Percent Modified] > 0.2
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 10K Big Table Rule'
END
WHEN X.[Total Rows] > 1000 -- thousand rows
THEN CASE
WHEN [Percent Modified] > 0.5
THEN 'UPDATE STATISTICS ' + [Schema Name] + '.' + [Table Name] + ' WITH ALL, FULLSCAN -- 1K Big Table Rule'
END
END AS [Statistics SQL]
FROM (
SELECT DISTINCT
DB_NAME() AS [Database],
S.name AS [Schema Name],
T.name AS [Table Name],
I.rowmodctr AS [Rows Modified],
P.rows AS [Total Rows],
CASE
WHEN I.rowmodctr > P.rows
THEN 100
ELSE CONVERT(decimal(8,2),((I.rowmodctr * 1.0) / P.rows * 1.) * 100.0)
END AS [Percent Modified]
FROM
sys.partitions P WITH(NOLOCK)
INNER JOIN sys.tables T WITH(NOLOCK) ON P.object_Id = T.object_id
INNER JOIN sys.schemas S WITH(NOLOCK) ON T.schema_id = S.schema_id
INNER JOIN sysindexes I WITH(NOLOCK) ON P.object_id = I.id
WHERE P.index_id in (0,1)
AND I.rowmodctr > 0 AND S.name = 'dbo'
) X
WHERE [Rows Modified] > 1000
ORDER BY [Rows Modified] DESC
OPEN @cur
FETCH NEXT FROM @cur INTO @statistic_sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC (@statistic_sql)
FETCH NEXT FROM @cur INTO @statistic_sql
END
CLOSE @cur
DEALLOCATE @cur
END;
GO
Thanks
regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
regards