Check Whether Update Statistics Required

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

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

CREATE FUNCTION udf_IsIndexStatistsUpdateRequired (@TableName VARCHAR(256)) RETURNS BIT

/****************************************************

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]

-- 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

Page updated

Google Sites

Report abuse