You can write scripts that check for the specified amount of changed data at the column, table, or partition level. The time at which you decide to run update statistics can be based on a number of variables collected by the datachange function; CPU usage, percent change in a table, percent change in a partition, and so on.
In this example, the authors table is partitioned, and the user wants to run update statistics when the data changes to the city column in the author_ptn2 partition are greater than or equal to 50%:
select @datachange = datachange("authors","author_ptn2", "city")
if @datachange >= 50
begin
update statistics authors partition author_ptn2(city)
end
go
The user can also specify that the script is executed when the system is idle or any other parameters.
In this example, the user triggers update statistics when the data changes to the city column of the authors table are greater than or equal to 100% (the table in this example is not partitioned):
select @datachange = datachange("authors",NULL, "city")
if @datachange > 100
begin
update statistics authors (city)
end
go
