Credits:
Author: ChillyDBA
Date: 28 Aug 2008
Description
Many times I have been involved in writing scheduled batch tasks that manipulate large data volumes. Common types of tasks are archiving and migration, but ETL tasks can also deal with large data volumes.
Often the source/destination for the data is an existing table that is subject to ongoing changes. This can cause the performance of the batch to vary wildly, especially in data archiving tasks where deletions can invalidate index usage due to the volume of changes.
During a recent data migration project, rather than blindly issue an update statistics command for each iteration, I wrote this small function to allow the update statistics command to be invoked only when necessary.
The threshold for change is set at 10% as I found this to work for the task at the time, but it can easily be modified.
Code
Function:
|
DROP FUNCTION udf_IsIndexStatistsUpdateRequired
GO
CREATE FUNCTION udf_IsIndexStatistsUpdateRequired (@TableName VARCHAR(256)) RETURNS BIT
AS
/****************************************************
Purpose: To determine whether an update stats is required on
a table depending on the percentage of changed rows
Threshold currently set at 10%
Author: ChillyDBA
History: 28 Aug 08 - Initial Issue
****************************************************/
BEGIN
DECLARE @RebuildNeeded BIT,
@PercentRowsChanged DECIMAL(18,2),
@Threshold DECIMAL(18,2)
SELECT @Threshold = 10.0
SELECT @PercentRowsChanged = (CAST(MAX(RowModCtr) AS DECIMAL(18,2))/ CAST(MAX(RowCnt) AS DECIMAL(18,2))) * 100
FROM dbo.SysIndexes
WHERE ID = OBJECT_ID(@TableName)
AND IndID > 0
AND Name NOT LIKE '_WA_Sys%'
IF @PercentRowsChanged > @Threshold
SELECT @RebuildNeeded =1
ELSE
SELECT @RebuildNeeded = 0
RETURN @RebuildNeeded
END
|
Test Code:
|
--create and index to measure against
CREATE NONCLUSTERED INDEX [IX_MaritalStatus] ON [dbo].[ProspectiveBuyer]
(
[MaritalStatus] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- check before - there should be no need to update the statistics
SELECT dbo.udf_IsIndexStatistsUpdateRequired ('dbo.ProspectiveBuyer')
-- perform some updates (~50% of the rows changed)
UPDATE ProspectiveBuyer
SET MaritalStatus = 'P'
WHERE MaritalStatus = 'M'
UPDATE ProspectiveBuyer
SET MaritalStatus = 'M'
WHERE MaritalStatus = 'P'
-- check after - statistics should require updating
SELECT dbo.udf_IsIndexStatistsUpdateRequired ('dbo.ProspectiveBuyer')
-- remove the test index
DROP INDEX ProspectiveBuyer.IX_MaritalStatus
GO
|
