Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

CPU high usage when update statistics

Hello,

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

Open in new window


Thanks

regards
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bibi92

ASKER

thanks

regards