-- SQL Scripts , sp_spaceused , sp_helpindex2 , sp_partitions , sp_updatestats2

/* --
-- updates 2018-04-10
An alternative to the SQL Server sp_updatestats.
The internal statistics update is based on all rows from a random sample of pages.
There can be adverse effects for indexes in which the lead key is not unique
and may be especially severe if compounded.
See Statistics that need special attention.

sp_updatestats2 does fullscan on indexes excluding identity or single key column unique.
Note: consider PERSIST_SAMPLE_PERCENT = ON
per Persisting statistics sampling rate by Pedro Lopes, Aug 17, 2017
for SQL Server 2016 SP1 CU4 and SQL Server 2017 CU1.

TBD: implement incremental statistics: sp_updatestats2 (test version)
*/

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
 , @incr1 bit
 select @dbsid = owner_sid -- , @incr1 = is_auto_create_stats_incremental_on
 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, is_incremental bit -- , partition_number int
 )

--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 = 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 WITH(NOLOCK)
 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
, 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 , d.is_incremental -- , 0 partition_number

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 ON b.object_id = i.object_id AND b.index_id = i.index_id
JOIN k 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 ( @modratio*t.modification_counter > t.rows
 OR ( t.modification_counter*t.modification_counter > @modratio2*t.rows )
 OR ( 2* t.rows_sampled < b.row_count AND ( k.kct > 1 OR is_unique = 0) AND is_identity = 0 )
 OR ( is_unique = 1 AND k.kct = 1 AND t.modification_counter > 0 )
 OR t.rows_sampled IS NULL
)

 

SELECT dd, rn, [object], [index], row_count, user_updates, has_filter filt, leadcol
, system_type_id, 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, is_incremental incr
FROM dbo.zstats
WHERE dd= @dd

 

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

DECLARE s CURSOR FOR
 SELECT [object] , [index] , is_identity , is_unique , kct , no_recompute , is_incremental
 FROM dbo.zstats WHERE dd = @dd

OPEN s
FETCH NEXT FROM s INTO @object , @index , @ident , @uni , @kct , @nr , @icr
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 = ''
 -- note: consider PERSIST_SAMPLE_PERCENT = ON
 -- for SQL Server 2016 SP1 CU4 and SQL Server 2017 CU1
  IF ( @icr = 0 ) SET @Inc = ' '
  ELSE SET @Inc = ', INCREMENTAL=ON'
 END
 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 , @icr
END
CLOSE s
DEALLOCATE s

 

PRINT ''
PRINT 'start column stats'
DECLARE s CURSOR FOR
 SELECT QUOTENAME(s.name) + '.' + QUOTENAME(o.name) [object], i.name [index]

 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 i.is_incremental = 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
 SELECT @SQL = CONCAT('UPDATE STATISTICS ' , @object, '(', QUOTENAME(@index),') ' )
 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 >= (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

 

*/