How can I automate statistics management?

2016-04-14 · by Hannah Vernon · in maintenance, tools

Occasionally, it can be beneficial to turn off AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS, and manage these items yourself.

I’ve created the following stored procedure, which lives in master to allow it to be called from the context of some other database. This stored procedure can be added to a SQL Server Agent Job allowing it to run periodically.

The first time the stored proc runs, it creates a dbo.StatsHistory table in MSDB which tracks updates made by the proc. If you create a “Utilities” filegroup in MSDB prior to running the stored proc the first time, the dbo.StatsHistory is automatically created on that filegroup.

USE master;
IF EXISTS (
    SELECT 1
    FROM sys.schemas s
        INNER JOIN sys.objects o ON s.schema_id = o.schema_id
    WHERE s.name = 'dbo'
        AND o.name = 'sp_UpdateAllStats'
    )
BEGIN
    DROP PROCEDURE dbo.sp_UpdateAllStats;
END
GO
CREATE PROCEDURE dbo.sp_UpdateAllStats
(
    @MinHoursSinceLastUpdate INT = 0
    , @ShowCmd BIT = 1
    , @DebugOnly BIT = 0
)
AS
BEGIN
    /*
        Updates statistics for all objects in the current database.
        Date:   2015-11-25
        By:     Max Vernon

NOTE: Stored Proc must be created in the [master] database, 
        and must be marked as a "system procedure" to enable proper
        functionality.

EXEC sys.sp_MS_marksystemobject dbo.UpdateStats;

Version History:
        1.0     Initial Version
        1.1     Reduced noise messages
        1.2     creates StatsHistory table on correct filegroup
    */
    SET NOCOUNT ON;
    SET ANSI_WARNINGS ON;
    DECLARE @cmd NVARCHAR(MAX);
    DECLARE @dsName NVARCHAR(MAX);
    DECLARE @ObjectID INT;
    DECLARE @StatsID INT;
    DECLARE @SchemaName SYSNAME;
    DECLARE @ObjectName SYSNAME;
    DECLARE @StatsName SYSNAME;
    DECLARE @StatsDate DATETIME;
    DECLARE @StartTime DATETIME;
    DECLARE @EndTime DATETIME;
    DECLARE @Rows INT;
    DECLARE @RowsSampled INT;
    DECLARE @ModificationCounter INT;
    DECLARE @DatabaseName SYSNAME;
    DECLARE @Action VARCHAR(50);
    SET @DatabaseName = DB_NAME();

IF NOT EXISTS(
        SELECT 1
        FROM msdb.sys.schemas s
            INNER JOIN msdb.sys.objects o ON s.schema_id = o.schema_id
        WHERE s.name = 'dbo'
            AND o.name = 'StatsHistory'
            )
    BEGIN
        /* get the data_spaces.name independent of the collation and case */
        SELECT @dsName = ds.name 
        FROM msdb.sys.data_spaces ds
        WHERE ds.name = 'Utilities' COLLATE SQL_Latin1_General_CP1_CI_AS;
        IF @dsName IS NULL SET @dsName = 'PRIMARY';
        SET @cmd = '
        CREATE TABLE dbo.StatsHistory
        (
            StatsHistoryID INT NOT NULL
                CONSTRAINT PK_StatsHistory
                PRIMARY KEY CLUSTERED
                IDENTITY(1,1)
            , StatsHistoryDate DATETIME NOT NULL
                CONSTRAINT DF_StatsHistory_StatsHistoryDate
                DEFAULT (GETDATE())
            , DatabaseName SYSNAME NOT NULL
            , SchemaName SYSNAME NOT NULL
            , ObjectName SYSNAME NOT NULL
            , StatsName SYSNAME NOT NULL
            , UpdateDate DATETIME NULL
            , UpdateDurationMs INT NULL
            , Rows INT NOT NULL
            , RowsSampled INT NULL
            , ModificationCounter INT NULL
            , Action VARCHAR(50) NOT NULL
        ) ON [' + @dsName + '];';
        EXEC msdb.sys.sp_executesql @cmd;
    END

SET @cmd = '';

DECLARE cur CURSOR LOCAL FORWARD_ONLY
    FOR 
    WITH StatsDetails AS
    (
        SELECT ObjectID = o.object_id
            , SchemaName = sch.name
            , ObjectName = o.name
            , StatsName = st.name
            , StatsID = st.stats_id
            , StatsDate = dsp.last_updated
            , Rows = dsp.rows
            , RowsSampled = dsp.rows_sampled
            , ModificationCounter = dsp.modification_counter /* number of modifications to the stat's 
                                                                leading column that have been modified
                                                                since the last stats update
                                                             */
            , UpdateStatement = 'UPDATE STATISTICS ' + quotename(sch.name) 
                + '.' + quotename(o.name) 
                + '(' + quotename(st.name) + ') WITH FULLSCAN' 
                + CASE WHEN st.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'
        FROM sys.schemas sch 
            INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
            INNER JOIN sys.stats st ON o.object_id = st.object_id
        OUTER APPLY sys.dm_db_stats_properties(o.object_id, st.stats_id) dsp
        WHERE o.is_ms_shipped = 0
    ) 
    SELECT sd.ObjectID
        , sd.StatsID
        , sd.SchemaName
        , sd.ObjectName
        , sd.StatsName
        , sd.UpdateStatement
        , sd.StatsDate
        , sd.Rows
        , sd.RowsSampled
        , sd.ModificationCounter
    FROM StatsDetails sd
    ORDER BY sd.SchemaName
        , sd.ObjectName
        , sd.StatsID;

OPEN cur;

FETCH NEXT FROM cur
    INTO @ObjectID
        , @StatsID
        , @SchemaName
        , @ObjectName
        , @StatsName
        , @cmd
        , @StatsDate
        , @Rows
        , @RowsSampled
        , @ModificationCounter;

WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @ShowCmd = 1
        BEGIN
            PRINT ('Processing Stats: ' + @SchemaName + '.' + @ObjectName + '.' + @StatsName);
        END
        SET @Action = '';
        IF COALESCE(@Rows, 0) = 0
        BEGIN
            /* 
                sys.dm_db_stats_properties may return NULL values if the stats
                have never been updated.
                Get the current row count for the object.
            */
            SET ANSI_WARNINGS OFF;
            SELECT @Rows = SUM(ips.record_count)
            FROM (
                SELECT record_count = ips.record_count
                FROM sys.dm_db_index_physical_stats(DB_ID(), @ObjectID, NULL, NULL, 'SAMPLED') ips
                UNION ALL SELECT 0 /* ensure NULL is never returned to @Rows */
            ) ips
            SET ANSI_WARNINGS ON;
        END
        IF COALESCE(@Rows, 0) > 0 
            AND ((@StatsDate IS NULL) OR DATEDIFF(HOUR, @StatsDate, GETDATE()) > @MinHoursSinceLastUpdate)
        BEGIN
            SET @StartTime = GETDATE();

/* 
                only update the statistics if rows have been modified.
               @ModificationCounter will be NULL if the statistics
               object has never been updated via UPDATE STATISTICS
            */

IF @ShowCmd = 1 AND COALESCE(@ModificationCounter, 1) > 0 
            BEGIN
                PRINT (CHAR(9) + 'Last updated ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');
                PRINT (CHAR(9) + 'Modifications since last stats update: ' + CONVERT(VARCHAR(50), @ModificationCounter));
                PRINT (CHAR(9) + 'Will update stats using:');
                PRINT (CHAR(9) + @cmd);
            END

IF COALESCE(@ModificationCounter, 1) = 0 
            BEGIN
                SET @Action = 'ZeroModificationsSinceLastUpdate';
                IF @ShowCmd = 1
                BEGIN
                    PRINT (CHAR(9) + 'Zero modifications since last update on ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');
                END
            END

IF @DebugOnly = 0 AND COALESCE(@ModificationCounter, 1) > 0 
            BEGIN
                EXEC sp_executesql @cmd;
                SET @Action = 'Updated';
            END
            SET @EndTime = GETDATE();

IF @DebugOnly = 0
            BEGIN
                SELECT @StatsDate = COALESCE(dsp.last_updated, GETDATE())
                    , @Rows = COALESCE(dsp.rows, 0)
                    , @RowsSampled = COALESCE(dsp.rows_sampled, 0)
                    /*
                        Leave this out since it will be zero at this point.  
                        The value from prior to executing UPDATE STATISTICS
                        shows the number of rows modified since the last time
                        stats were updated, and is useful to save in the 
                        dbo.StatsHistory table; the below code clobbers that
                        value.
                        , @ModificationCounter = COALESCE(dsp.modification_counter, 0)
                    */
                FROM sys.schemas sch 
                    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
                    INNER JOIN sys.stats st ON o.object_id = st.object_id
                OUTER APPLY sys.dm_db_stats_properties(o.object_id, st.stats_id) dsp
                WHERE sch.name = @SchemaName
                    AND o.name = @ObjectName
                    AND st.name = @StatsName;

INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
                VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
            END
            ELSE
            BEGIN
                SET @Action = 'DebugMode';
                INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
                VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
            END
        END
        ELSE
        BEGIN
            IF COALESCE(@Rows, 0) = 0
            BEGIN
                IF @ShowCmd = 1 
                BEGIN
                    PRINT (CHAR(9) + 'Contains zero rows.  Stats will not be updated.');
                END
                SET @Action = 'ContainsZeroRows';
                INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
                VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
            END
            ELSE
            BEGIN
                IF @ShowCmd = 1 
                BEGIN
                    PRINT (CHAR(9) + 'Last updated ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');
                    PRINT (CHAR(9) + 'SKIPPING');
                END
                SET @Action = 'UpdatedTooRecently';
                INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
                VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
            END
        END

IF @ShowCmd = 1 
        BEGIN
            PRINT (''); /* add a blank line between output rows */
        END

FETCH NEXT FROM cur
        INTO @ObjectID
            , @StatsID
            , @SchemaName
            , @ObjectName
            , @StatsName
            , @cmd
            , @StatsDate
            , @Rows
            , @RowsSampled
            , @ModificationCounter;
    END

CLOSE cur;
    DEALLOCATE cur;
END
GO
EXEC sys.sp_MS_marksystemobject 'dbo.sp_UpdateAllStats'; 
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

USE master;

IF EXISTS (

SELECT 1

FROM sys.schemas s

INNER JOIN sys.objects o ON s.schema_id = o.schema_id

WHERE s.name = 'dbo'

AND o.name = 'sp_UpdateAllStats'

)

BEGIN

DROP PROCEDURE dbo.sp_UpdateAllStats;

END

CREATE PROCEDURE dbo.sp_UpdateAllStats

(

@MinHoursSinceLastUpdate INT = 0

, @ShowCmd BIT = 1

, @DebugOnly BIT = 0

)

BEGIN

Updates statistics for all objects in the current database.

Date: 2015-11-25

By: Max Vernon

NOTE: Stored Proc must be created in the [master] database,

and must be marked as a "system procedure" to enable proper

functionality.

EXEC sys.sp_MS_marksystemobject dbo.UpdateStats;

Version History:

1.0 Initial Version

1.1 Reduced noise messages

1.2 creates StatsHistory table on correct filegroup

SET NOCOUNT ON;

SET ANSI_WARNINGS ON;

DECLARE @cmd NVARCHAR(MAX);

DECLARE @dsName NVARCHAR(MAX);

DECLARE @ObjectID INT;

DECLARE @StatsID INT;

DECLARE @SchemaName SYSNAME;

DECLARE @ObjectName SYSNAME;

DECLARE @StatsName SYSNAME;

DECLARE @StatsDate DATETIME;

DECLARE @StartTime DATETIME;

DECLARE @EndTime DATETIME;

DECLARE @Rows INT;

DECLARE @RowsSampled INT;

DECLARE @ModificationCounter INT;

DECLARE @DatabaseName SYSNAME;

DECLARE @Action VARCHAR(50);

SET @DatabaseName = DB_NAME();

IF NOT EXISTS(

SELECT 1

FROM msdb.sys.schemas s

INNER JOIN msdb.sys.objects o ON s.schema_id = o.schema_id

WHERE s.name = 'dbo'

AND o.name = 'StatsHistory'

)

BEGIN

/* get the data_spaces.name independent of the collation and case */

SELECT @dsName = ds.name

FROM msdb.sys.data_spaces ds

WHERE ds.name = 'Utilities' COLLATE SQL_Latin1_General_CP1_CI_AS;

IF @dsName IS NULL SET @dsName = 'PRIMARY';

SET @cmd = '

CREATE TABLE dbo.StatsHistory

(

StatsHistoryID INT NOT NULL

CONSTRAINT PK_StatsHistory

PRIMARY KEY CLUSTERED

IDENTITY(1,1)

, StatsHistoryDate DATETIME NOT NULL

CONSTRAINT DF_StatsHistory_StatsHistoryDate

DEFAULT (GETDATE())

, DatabaseName SYSNAME NOT NULL

, SchemaName SYSNAME NOT NULL

, ObjectName SYSNAME NOT NULL

, StatsName SYSNAME NOT NULL

, UpdateDate DATETIME NULL

, UpdateDurationMs INT NULL

, Rows INT NOT NULL

, RowsSampled INT NULL

, ModificationCounter INT NULL

, Action VARCHAR(50) NOT NULL

) ON [' + @dsName + '];';

EXEC msdb.sys.sp_executesql @cmd;

END

SET @cmd = '';

DECLARE cur CURSOR LOCAL FORWARD_ONLY

FOR

WITH StatsDetails AS

(

SELECT ObjectID = o.object_id

, SchemaName = sch.name

, ObjectName = o.name

, StatsName = st.name

, StatsID = st.stats_id

, StatsDate = dsp.last_updated

, Rows = dsp.rows

, RowsSampled = dsp.rows_sampled

, ModificationCounter = dsp.modification_counter /* number of modifications to the stat's

leading column that have been modified

since the last stats update

, UpdateStatement = 'UPDATE STATISTICS ' + quotename(sch.name)

+ '.' + quotename(o.name)

+ '(' + quotename(st.name) + ') WITH FULLSCAN'

+ CASE WHEN st.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'

FROM sys.schemas sch

INNER JOIN sys.objects o ON sch.schema_id = o.schema_id

INNER JOIN sys.stats st ON o.object_id = st.object_id

OUTER APPLY sys.dm_db_stats_properties(o.object_id, st.stats_id) dsp

WHERE o.is_ms_shipped = 0

)

SELECT sd.ObjectID

, sd.StatsID

, sd.SchemaName

, sd.ObjectName

, sd.StatsName

, sd.UpdateStatement

, sd.StatsDate

, sd.Rows

, sd.RowsSampled

, sd.ModificationCounter

FROM StatsDetails sd

ORDER BY sd.SchemaName

, sd.ObjectName

, sd.StatsID;

OPEN cur;

FETCH NEXT FROM cur

INTO @ObjectID

, @StatsID

, @SchemaName

, @ObjectName

, @StatsName

, @cmd

, @StatsDate

, @Rows

, @RowsSampled

, @ModificationCounter;

WHILE @@FETCH_STATUS = 0

BEGIN

IF @ShowCmd = 1

BEGIN

PRINT ('Processing Stats: ' + @SchemaName + '.' + @ObjectName + '.' + @StatsName);

END

SET @Action = '';

IF COALESCE(@Rows, 0) = 0

BEGIN

sys.dm_db_stats_properties may return NULL values if the stats

have never been updated.

Get the current row count for the object.

SET ANSI_WARNINGS OFF;

SELECT @Rows = SUM(ips.record_count)

FROM (

SELECT record_count = ips.record_count

FROM sys.dm_db_index_physical_stats(DB_ID(), @ObjectID, NULL, NULL, 'SAMPLED') ips

UNION ALL SELECT 0 /* ensure NULL is never returned to @Rows */

) ips

SET ANSI_WARNINGS ON;

END

IF COALESCE(@Rows, 0) > 0

AND ((@StatsDate IS NULL) OR DATEDIFF(HOUR, @StatsDate, GETDATE()) > @MinHoursSinceLastUpdate)

BEGIN

SET @StartTime = GETDATE();

only update the statistics if rows have been modified.

@ModificationCounter will be NULL if the statistics

object has never been updated via UPDATE STATISTICS

IF @ShowCmd = 1 AND COALESCE(@ModificationCounter, 1) > 0

BEGIN

PRINT (CHAR(9) + 'Last updated ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');

PRINT (CHAR(9) + 'Modifications since last stats update: ' + CONVERT(VARCHAR(50), @ModificationCounter));

PRINT (CHAR(9) + 'Will update stats using:');

PRINT (CHAR(9) + @cmd);

END

IF COALESCE(@ModificationCounter, 1) = 0

BEGIN

SET @Action = 'ZeroModificationsSinceLastUpdate';

IF @ShowCmd = 1

BEGIN

PRINT (CHAR(9) + 'Zero modifications since last update on ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');

END

IF @DebugOnly = 0 AND COALESCE(@ModificationCounter, 1) > 0

BEGIN

EXEC sp_executesql @cmd;

SET @Action = 'Updated';

END

SET @EndTime = GETDATE();

IF @DebugOnly = 0

BEGIN

SELECT @StatsDate = COALESCE(dsp.last_updated, GETDATE())

, @Rows = COALESCE(dsp.rows, 0)

, @RowsSampled = COALESCE(dsp.rows_sampled, 0)

Leave this out since it will be zero at this point.

The value from prior to executing UPDATE STATISTICS

shows the number of rows modified since the last time

stats were updated, and is useful to save in the

dbo.StatsHistory table; the below code clobbers that

value.

, @ModificationCounter = COALESCE(dsp.modification_counter, 0)

FROM sys.schemas sch

INNER JOIN sys.objects o ON sch.schema_id = o.schema_id

INNER JOIN sys.stats st ON o.object_id = st.object_id

OUTER APPLY sys.dm_db_stats_properties(o.object_id, st.stats_id) dsp

WHERE sch.name = @SchemaName

AND o.name = @ObjectName

AND st.name = @StatsName;

INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)

VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);

END

ELSE

BEGIN

SET @Action = 'DebugMode';

INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)

VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);

END

ELSE

BEGIN

IF COALESCE(@Rows, 0) = 0

BEGIN

IF @ShowCmd = 1

BEGIN

PRINT (CHAR(9) + 'Contains zero rows. Stats will not be updated.');

END

SET @Action = 'ContainsZeroRows';

INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)

VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);

END

ELSE

BEGIN

IF @ShowCmd = 1

BEGIN

PRINT (CHAR(9) + 'Last updated ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');

PRINT (CHAR(9) + 'SKIPPING');

END

SET @Action = 'UpdatedTooRecently';

INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)

VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);

END

IF @ShowCmd = 1

BEGIN

PRINT (''); /* add a blank line between output rows */

END

FETCH NEXT FROM cur

INTO @ObjectID

, @StatsID

, @SchemaName

, @ObjectName

, @StatsName

, @cmd

, @StatsDate

, @Rows

, @RowsSampled

, @ModificationCounter;

END

CLOSE cur;

DEALLOCATE cur;

END

EXEC sys.sp_MS_marksystemobject 'dbo.sp_UpdateAllStats';

Hopefully you’ll find the above code useful. Let me know if you have questions!

Tags: maintenance, statistics

How can I automate statistics management?

Related

Leave a ReplyCancel reply

Search

Categories

Pages

Meta