Statistics update

How about a home made script that is checking the statistics modification counter, and base on this value, check it with a company / object threshold and if this value is above that threshold, trigger an update of that particular statistics.

For this we must have a utility table that hold the threshold for which will trigger the update. This is kept on object_id and stat_id ( for a more flexibility) but could be only one per database or one per object_id …

ex:  I have a statistics  : _WA_Sys_0000000E_44CA3770 , if this has a value = 4563 and the threshold it’s like 3000. In this case we should trigger , for this statistics, an update.

 

Here are the steps:

1. – List the statistics (for indexes and for columns) of an object. In this case for Sales.Orders
2. – Show the field that holds the modification counter associated
   with this statistics
3. – Do an update/delete in a transaction with rollback – for field  PickingCompletedWhen
4. – Create utility table
5. – Create dynamic string to run the update statistics
6. – Check the value after update statistics

 

I will start with step 3   , the utility table.This should be save in a “utility” database.

use db_workspace
go

DROP TABLE IF EXISTS dbo.utility_stats;
GO

CREATE TABLE dbo.utility_stats
(
uts_id INT IDENTITY(1,1) NOT NULL
,uts_object_id INT NOT NULL
,uts_stats_id INT NOT NULL
,uts_threshold INT NULL
);
GO

ALTER TABLE dbo.utility_stats
ADD CONSTRAINT pk_utility_stats__uts_object_id_uts_stats_id
PRIMARY KEY CLUSTERED(uts_object_id,uts_stats_id);
GO

 

Step 1: List the statistics (for indexes and for columns) of an object.
In this case for Sales.Orders

SELECT
OBJECT_SCHEMA_NAME(s.object_id)
+ ‘.’ + OBJECT_NAME(s.object_id) as objectName
, s.name as statisticsName
,s.object_id as objectID
FROM sys.stats as s
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)

objectName statisticsName objectID
Sales.Orders PK_Sales_Orders 1154103152
Sales.Orders FK_Sales_Orders_CustomerID 1154103152
Sales.Orders FK_Sales_Orders_SalespersonPersonID 1154103152
Sales.Orders FK_Sales_Orders_PickedByPersonID 1154103152
Sales.Orders FK_Sales_Orders_ContactPersonID 1154103152
Sales.Orders _WA_Sys_0000000E_44CA3770 1154103152
Sales.Orders _WA_Sys_0000000A_44CA3770 1154103152

Step 2: Show the fields that holds the modification counter associated
with this statistics

SELECT
OBJECT_SCHEMA_NAME(s.object_id)
+ ‘.’ + OBJECT_NAME(s.object_id) as objectName
, s.name as statisticsName
,s.object_id as objectID
,s.stats_id as statsID
,sp.modification_counter
,sp.last_updated
,sc.colNames
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
CROSS APPLY(SELECT STRING_AGG(c.name,’,’) as colNames
FROM sys.stats_columns as sc
INNER JOIN sys.columns as c
ON sc.column_id = c.column_id
and sc.object_id = c.object_id
WHERE sc.object_id = s.object_id
and sc.stats_id = s.stats_id
) as sc
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)

objectName statisticsName objectID statsID modification_counter last_updated colNames
Sales.Orders PK_Sales_Orders 1154103152 1 3966 2016-06-02 10:39:54.2533333 OrderID
Sales.Orders FK_Sales_Orders_CustomerID 1154103152 2 0 2018-07-25 10:52:18.0733333 CustomerID
Sales.Orders FK_Sales_Orders_SalespersonPersonID 1154103152 3 0 2018-07-25 10:52:18.4233333 SalespersonPersonID
Sales.Orders FK_Sales_Orders_PickedByPersonID 1154103152 4 4751 2016-06-02 10:43:25.6400000 PickedByPersonID
Sales.Orders FK_Sales_Orders_ContactPersonID 1154103152 5 2012 2016-06-02 10:45:05.4033333 ContactPersonID
Sales.Orders _WA_Sys_0000000E_44CA3770 1154103152 6 0 2018-07-25 10:53:22.7400000 PickingCompletedWhen
Sales.Orders _WA_Sys_0000000A_44CA3770 1154103152 7 3495 2016-06-02 10:41:14.3700000 IsUndersupplyBackordered

Step 3 : Do an update/delete in a transaction with rollback – for field PickingCompletedWhen
DECLARE @i_modification_counter_before int = 0
,@i_modification_counter_after int = 0;

SELECT
@i_modification_counter_before = sp.modification_counter
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
AND s.stats_id = 6
BEGIN TRAN
UPDATE TOP(1000) Sales.Orders
SET PickingCompletedWhen = SYSDATETIME()

SELECT
@i_modification_counter_after = sp.modification_counter
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
AND s.stats_id = 6

ROLLBACK TRAN
SELECT @i_modification_counter_before as beforeCounter
,@i_modification_counter_after as afterCounter
,@i_modification_counter_after-@i_modification_counter_before as differ

beforeCounter afterCounter differ
7480 8480 1000

 

Step 4 : utility table and populate with thresholds:

INSERT INTO db_workspace.dbo.utility_stats(uts_object_id,uts_stats_id,uts_threshold)
SELECT
s.object_id as objectID
,s.stats_id as statsID
,5000
FROM WideWorldImporters.sys.stats as s
WHERE s.object_id = OBJECT_ID(‘WideWorldImporters.Sales.Orders’);
GO

 

SELECT * FROM db_workspace.dbo.utility_stats

/*
uts_id uts_object_id uts_stats_id uts_threshold
1 1154103152 1 5000
2 1154103152 2 5000
3 1154103152 3 5000
4 1154103152 4 5000
5 1154103152 5 5000
6 1154103152 6 5000
7 1154103152 7 5000
*/

Step 5: Create dynamic string to run the update statistics , for the statistics with modification counter above the threshold.

 

DECLARE @nvc_SQLText AS nvarchar(4000) = N”;

SELECT
@nvc_SQLText = STRING_AGG(N’ UPDATE STATISTICS ‘
+ OBJECT_SCHEMA_NAME(s.object_id)
+ N’.’ + OBJECT_NAME(s.object_id)
+N'(‘ + s.name +N’)’
+ N’ WITH FULLSCAN ‘ + char(10) + char(13) ,N’;’)
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
LEFT JOIN db_workspace.dbo.utility_stats as us
ON us.uts_object_id = s.object_id
AND us.uts_stats_id = s.stats_id
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
AND sp.modification_counter >= us.uts_threshold

SELECT @nvc_SQLText

/* output :
UPDATE STATISTICS Sales.Orders(_WA_Sys_0000000E_44CA3770) WITH FULLSCAN
*/

EXEC sp_executeSQL @nvc_SQLText

GO

 

Step 6: Check the value after update statistics, for that particullary statistics:
_WA_Sys_0000000E_44CA3770

use WideWorldImporters
go

SELECT
OBJECT_SCHEMA_NAME(s.object_id)
+ ‘.’ + OBJECT_NAME(s.object_id) as objectName
, s.name as statisticsName
,s.object_id as objectID
,s.stats_id as statsID
,sp.modification_counter
,sp.last_updated
,sc.colNames
,us.uts_threshold
FROM sys.stats as s
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) as sp
CROSS APPLY(SELECT STRING_AGG(c.name,’,’) as colNames
FROM sys.stats_columns as sc
INNER JOIN sys.columns as c
ON sc.column_id = c.column_id
and sc.object_id = c.object_id
WHERE sc.object_id = s.object_id
and sc.stats_id = s.stats_id
) as sc
LEFT JOIN db_workspace.dbo.utility_stats as us
ON us.uts_object_id = s.object_id
AND us.uts_stats_id = s.stats_id
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)

/*
objectName statisticsName objectID statsID modification_counter last_updated colNames uts_threshold
Sales.Orders _WA_Sys_0000000E_44CA3770 1154103152 6 0 2018-07-25 10:53:22.7400000 PickingCompletedWhen 5000
*/

That’s it fox!

 

S