Previous post about statistics was talking about updating them , base on some value, value that exists in a utility table.
The present post is referring also about update statistics, but now base on a percentage value, threshold . Maybe this is more flexible . This value could also be present in a utility table , it’s up to anyone how to have it.
In my case, I use a value of 5.0, so that I will not update all objects, only some of them. Like I said, this value, could be extracted from a utility table , and could be unique for an particularly statistics object (stats_id) , or an object( object_id ) or database…
The starting point is the calculation of the percentage value : sp.modification_counter/sp.rows
where :
- sp.modification_count = actual value of modification
- sp.rows = number of rows in that object
The actual formula is :
CAST((sp.modification_counter/(sp.rows * 1.0)) *100.0 AS decimal(6,4))
This query will list all the statistics of the object
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.rows as noRows
,CAST((sp.modification_counter/(sp.rows *1.0)) *100.0 AS decimal(6,4)) as modification_Percent
,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 noRows modification_Percent last_updated colNames
Sales.Orders PK_Sales_Orders 1154103152 1 3966 69629 5.6959 2016-06-02 10:39:54.2533333 OrderID
Sales.Orders FK_Sales_Orders_CustomerID 1154103152 2 0 73595 0.0000 2018-07-25 10:52:18.0733333 CustomerID
Sales.Orders FK_Sales_Orders_SalespersonPersonID 1154103152 3 0 73595 0.0000 2018-07-25 10:52:18.4233333 SalespersonPersonID
Sales.Orders FK_Sales_Orders_PickedByPersonID 1154103152 4 4751 71018 6.6899 2016-06-02 10:43:25.6400000 PickedByPersonID
Sales.Orders FK_Sales_Orders_ContactPersonID 1154103152 5 2012 71583 2.8107 2016-06-02 10:45:05.4033333 ContactPersonID
Sales.Orders _WA_Sys_0000000E_44CA3770 1154103152 6 0 73595 0.0000 2018-07-25 10:53:22.7400000 PickingCompletedWhen
Sales.Orders _WA_Sys_0000000A_44CA3770 1154103152 7 3495 70100 4.9857 2016-06-02 10:41:14.3700000 IsUndersupplyBackordered
Adding the additional predicate on the percentage …
WHERE s.object_id = OBJECT_ID(‘Sales.Orders’)
AND CAST((sp.modification_counter/(sp.rows *1.0)) *100.0 AS decimal(6,4)) > CAST(5.0 AS decimal(6,4))
will have this records outputted :
objectName statisticsName modification_counter noRows modification_Percent
Sales.Orders PK_Sales_Orders 3966 69629 5.6959
Sales.Orders FK_Sales_Orders_PickedByPersonID 4751 71018 6.6899
Now, just prepare the dynamic sql for update
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) ,N”)
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 CAST((sp.modification_counter/(sp.rows *1.0)) *100.0 AS decimal(6,4)) > CAST(5.0 AS decimal(6,4))
SELECT @nvc_SQLText
output:
UPDATE STATISTICS Sales.Orders(PK_Sales_Orders) WITH FULLSCAN;
UPDATE STATISTICS Sales.Orders(FK_Sales_Orders_PickedByPersonID) WITH FULLSCAN;
and run it :
EXEC sp_executeSQL @nvc_SQLText;
Now, check if you have some objects over the threshold :
SELECT
OBJECT_SCHEMA_NAME(s.object_id)
+ ‘.’ + OBJECT_NAME(s.object_id) as objectName
,s.name as statisticsName
,sp.modification_counter
,sp.rows as noRows ,CAST((sp.modification_counter/(sp.rows *1.0)) *100.0 AS decimal(6,4)) as modification_Percent
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’)
AND CAST((sp.modification_counter/(sp.rows *1.0)) *100.0 AS decimal(6,4))
> CAST(5.0 AS decimal(6,4))
output :
No rows.
#t-sql #sqlserver #statistics #update
S
Sql Server 