Cool Tips‎ > ‎Indexes‎ > ‎

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


ċ
udf_IsIndexStatistsUpdateRequired.sql
(2k)
Andy Hughes,
Jul 12, 2012, 11:21 AM
Comments