THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

sp_updatestats2

-- this is my version of sp_updatestats Some of you know that SQL Server has a problem with the way statistics are sampled. Indexes in which the lead column is not unique can be adversely affected. My system procedure marks indexes which are either not unique or has more than one key column for full scan statistics. If you have an index with more than one key column and the lead column is unique, then the other key columns should have been put in the include list. your bad.

USE [master]
GO

IF EXISTS (
 SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.sp_updatestats2')
) DROP PROCEDURE dbo.sp_updatestats2
GO

CREATE PROCEDURE sp_updatestats2
 @resample char(8)='NO', @modratio bigint = 20
as

 declare @dbsid varbinary(85), @modratio2 int = 25 * @modratio * @modratio
 select @dbsid = owner_sid from sys.databases where name = db_name()

 -- Check the user sysadmin
 if not is_srvrolemember('sysadmin') = 1 and suser_sid() <> @dbsid
 begin
  raiserror(15247,-1,-1)
  return (1)
 end

 -- cannot execute against R/O databases
 if DATABASEPROPERTYEX(db_name(), 'Updateability')=N'READ_ONLY'
 begin
  raiserror(15635,-1,-1,N'sp_updatestats')
  return (1)
 end

 if upper(@resample)<>'RESAMPLE' and upper(@resample)<>'NO'
 begin
  raiserror(14138, -1, -1, @resample)
  return (1)
 end

 -- required so it can update stats on ICC/IVs
 set ansi_warnings on
 set ansi_padding on
 set arithabort on
 set concat_null_yields_null on
 set numeric_roundabort off

IF NOT EXISTS (
SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.zstats')
) -- DROP TABLE zstats
BEGIN

 CREATE TABLE dbo.zstats ( dd smallint, rn int
 , [object] varchar(255), [index] varchar(255)
 , row_count bigint, user_updates bigint, has_filter bit
 , leadcol varchar(255), system_type_id smallint
 , is_identity bit, is_rowguidcol bit, is_unique bit, kct tinyint
 , rw_delta bigint, rows_sampled bigint, unfiltered_rows bigint
 , mod_ctr bigint, steps int
 , updated datetime, otype char(2), no_recompute bit
 )

--ALTER TABLE dbo.zstats ADD  no_recompute bit
--UPDATE dbo.zstats SET no_recompute = 0 WHERE no_recompute IS NULL

 IF NOT EXISTS (
 SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.zstats') AND index_id = 1
 )
 CREATE UNIQUE CLUSTERED INDEX CX ON dbo.zstats(dd, rn)
 WITH (IGNORE_DUP_KEY = ON) -- , DROP_EXISTING = ON)
END

DECLARE @dd int --SELECT @dd = DATEDIFF(dd, '2014-01-01', GETDATE())
SELECT @dd = ISNULL(MAX(dd), 0) + 1 FROM dbo.zstats

;WITH b AS (
 SELECT d.object_id, d.index_id, row_count = SUM(d.row_count)
  FROM sys.dm_db_partition_stats d WITH(NOLOCK) GROUP BY d.object_id, d.index_id
), k AS (
 SELECT object_id, index_id, COUNT(*) kct FROM sys.index_columns WHERE key_ordinal > 0 GROUP BY object_id, index_id
)

INSERT dbo.zstats
SELECT @dd dd, ROW_NUMBER() OVER(ORDER BY s.name, o.name, i.index_id) rn
--, s.name + '.' + o.name [object]
, QUOTENAME(s.name) + '.' + QUOTENAME(o.name) [object]
, i.name [index], b.row_count, y.user_updates, i.has_filter
, c.name [leadcol], c.system_type_id, c.is_identity, c.is_rowguidcol, i.is_unique, k.kct
, rw_delta = b.row_count - t.rows
, t.rows_sampled, t.unfiltered_rows, t.modification_counter mod_ctr, t.steps
, CONVERT(datetime, CONVERT(varchar, t.last_updated,120)) updated
, o.type , d.no_recompute

FROM sys.objects o WITH(NOLOCK)
JOIN sys.schemas s WITH(NOLOCK) ON s.schema_id = o.schema_id
JOIN sys.indexes i WITH(NOLOCK) ON i.object_id = o.object_id
LEFT JOIN sys.stats d WITH(NOLOCK) ON d.object_id = i.object_id AND d.stats_id = i.index_id
JOIN sys.index_columns j WITH(NOLOCK) ON j.object_id = i.object_id AND j.index_id = i.index_id AND j.key_ordinal = 1
JOIN sys.columns c WITH(NOLOCK) ON c.object_id = i.object_id AND c.column_id = j.column_id AND j.key_ordinal = 1
JOIN b WITH(NOLOCK) ON b.object_id = i.object_id AND b.index_id = i.index_id
JOIN k WITH(NOLOCK) ON k.object_id = i.object_id AND k.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats y ON y.object_id = i.object_id AND y.index_id = i.index_id
 AND y.database_id = DB_ID()
OUTER APPLY sys.dm_db_stats_properties(i.object_id, i.index_id) t

WHERE o.type IN ('U','V')
AND i.index_id > 0
AND i.type <= 2
AND i.is_disabled = 0
AND b.row_count > 0
AND s.name <> 'cdc' --AND o.schema_id = 1
AND ( @modratio*t.modification_counter > t.rows
 OR ( t.modification_counter*t.modification_counter > @modratio2*t.rows
/*AND s.name <> 'dbo'*/ )
 OR ( 2* t.rows_sampled < b.row_count AND ( k.kct > 1 OR is_unique = 0) AND is_identity = 0)
 OR t.rows_sampled IS NULL
)
--ORDER BY s.name, o.name, i.index_id

SELECT dd, rn, [object], [index], row_count, user_updates, has_filter filt, leadcol, system_type_id stype, is_identity ident, is_rowguidcol rgc, is_unique uni
, kct, rw_delta, rows_sampled, /*unfiltered_rows uf_rows,*/ mod_ctr, updated, steps, otype, no_recompute nr
FROM dbo.zstats
WHERE dd = @dd

-- SELECT * FROM dbo.zstats WHERE dd = (SELECT MAX(dd) FROM dbo.zstats)

DECLARE @object varchar(255), @index varchar(255), @SQL varchar(1000)
, @ident bit, @uni bit, @kct tinyint, @nr bit, @FS varchar(50), @Re varchar(50)

-- , @row_count bigint, @user_updates bigint, @has_filter bit, @leadcol varchar(255), @system_type_id smallint
--, @is_identity bit, @is_rowguidcol bit, @is_unique bit, kct tinyint, otype char(2)

DECLARE s CURSOR FOR
 SELECT [object], [index], is_identity, is_unique, kct, no_recompute
--, row_count, user_updates, has_filter, leadcol, system_type_id, is_rowguidcol, otype
 FROM dbo.zstats WHERE dd = @dd

OPEN s
FETCH NEXT FROM s INTO @object, @index, @ident, @uni, @kct, @nr
WHILE @@FETCH_STATUS = 0
BEGIN
 IF (@ident = 1 OR (@uni =1 AND @kct  = 1) ) BEGIN
  SET @FS = ''
  IF (@nr = 1) SET @Re = 'WITH NORECOMPUTE' ELSE SET @Re = ''
 END
 ELSE BEGIN
  SET @FS = ' WITH FULLSCAN'
  --IF (@nr = 1)
  SET @Re = ', NORECOMPUTE' --ELSE SET @Re = ''
 END
 --SET @SQL = 'UPDATE STATISTICS ' + @object +'([' + @index+']) ' + @FS + @Re
 SELECT @SQL = CONCAT('UPDATE STATISTICS ', @object, '(', QUOTENAME(@index),') ', @FS, @Re)
 PRINT CONVERT(varchar(50), getdate(),120) + ',' + @SQL  
 EXEC (@SQL)
 FETCH NEXT FROM s INTO @object, @index, @ident, @uni, @kct, @nr
END
CLOSE s
DEALLOCATE s

PRINT ''
PRINT 'start column stats'
--DECLARE @object varchar(255), @index varchar(255) , @SQL varchar(1000)
DECLARE s CURSOR FOR
 SELECT QUOTENAME(s.name) + '.' + QUOTENAME(o.name) [object], i.name [index]
--, t.rows, t.rows_sampled, t.unfiltered_rows, t.modification_counter mod_ctr, t.steps

 FROM sys.objects o WITH(NOLOCK) JOIN sys.schemas s WITH(NOLOCK) ON s.schema_id = o.schema_id
 JOIN sys.stats i WITH(NOLOCK) ON i.object_id = o.object_id
 LEFT JOIN sys.indexes x WITH(NOLOCK) ON x.object_id = o.object_id AND x.index_id = i.stats_id
 OUTER APPLY sys.dm_db_stats_properties(i.object_id , i.stats_id) t

 WHERE o.type IN ('U','V') AND i.stats_id > 0 AND i.auto_created = 1 AND i.no_recompute = 0
 AND x.index_id IS NULL
 AND ( 20*t.modification_counter > t.rows
  OR ( t.modification_counter*t.modification_counter > 1000*t.rows AND s.name <> 'dbo' )
 )
 ORDER BY s.name, o.name, i.stats_id

OPEN s
FETCH NEXT FROM s INTO @object, @index
WHILE @@FETCH_STATUS = 0
BEGIN
-- SET @SQL = 'UPDATE STATISTICS ' + @object +'(' + @index+') '
 SELECT @SQL = CONCAT('UPDATE STATISTICS ' , @object, '(', QUOTENAME(@index),') ', @FS, @Re)
 PRINT CONVERT(varchar(50), getdate(),120) + ',' + @SQL  

 EXEC (@SQL)

 FETCH NEXT FROM s INTO @object, @index
END
CLOSE s
DEALLOCATE s

return 0
GO

EXEC sp_MS_marksystemobject 'sp_updatestats2'
GO

SELECT NAME, IS_MS_SHIPPED FROM SYS.OBJECTS WHERE NAME LIKE 'sp_updatestats%'
GO

/*
USE yourdb
GO

exec dbo.sp_updatestats2 @modratio = 20

SELECT * FROM zstats WHERE dd =
WHERE dd >= (SELECT dd1 = ISNULL(MAX(dd),0) - 1 FROM dbo.zstats )

SELECT t.name, QUOTENAME(i.name), i.*
FROM sys.tables t JOIN  sys.indexes i ON i.object_id = t.object_id
WHERE t.object_id > 1000
AND CHARINDEX('-', i.name) > 0

SELECT QUOTENAME([object]),  QUOTENAME([index])
FROM zstats

*/

Published Thursday, April 5, 2018 9:14 AM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

7gartner said:

Joe, what logic are you looking to implement with the no_recompute option?  You have some code remarked out regarding this and I want to make sure I fully understand what your aim is.

As it is, this code will turn off autostats for objects identified to need a FULLSCAN.  

April 9, 2018 1:37 PM
 

jchang said:

the key is that indexes in which the lead key is not unique need a fullscan statistics,
my intent was that this procedure run daily,
hence turning off auto update - norecompute is ok.
At the time, I did not want an auto statistics recompute to override the fullscan.

When I did this, we did not have PERSIST_SAMPLE_PERCENT
(new in  SQL Server 2016 SP1 CU4 and SQL Server 2017 CU1)

please feel free to modify this to suit your needs,
if you believe you have new logic that every can use, then forward to me and I will put it in.

But it would be better if Microsoft did it right in the first place

ps
this will be updated more often
http://www.qdpma.com/SQL/sp_updatestats2.html

April 9, 2018 1:49 PM
 

jcheng said:

May 20, 2018 9:02 PM
 

شركة تنظيف موكيت بمكة said:

يمكنكم الان الحصول على افضل التنظيف المختلفة الان فى السعودية من شركة تنظيف كنب بمكة الان وباقل الاسعار الرائعة التى لا احد من الشركات الاخرة تقدمة الان حيث اننا نعمل على تثقدم افضل الخدمات التى لا احد من الشركات تقدمة الان وباقل التكاليف الرائعة من شركة تنظيف سجاد بمكة الان ويوجد لدينا افضل فريق متحصصون فى كافة انواع التنظيف الان من ويوجد لدينا الان شركة تنظيف موكيت بمكة وباقل الاسعار المختلفة التى تقدمة الشركة الان

http://www.el3nod.com/3/company-cleaning-moquette-carpet-sofas-mecca

June 2, 2018 3:54 AM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Privacy Statement