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:
- – List the statistics (for indexes and for columns) of an object. In this case for Sales.Orders
- – Show the field that holds the modification counter associated
with this statistics
- – Do an update/delete in a transaction with rollback – for field PickingCompletedWhen
- – Create utility table
- – Create dynamic string to run the update statistics
- – 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
Sql Server 