QAID # 11569 Published
Question / Problem:
What SQL commands are issued when the Delete Statistics command is executed in the Report Viewer?
Answer / Solution:
It is recommended that the Report Viewer be used to Delete Statistics by most users, but this information can be utilized by a DBA for scheduled maintenance.
The following commands (with example date values) are issued when the Delete Statistics command is used in Report Viewer:
EXEC ProcDeleteStatsBatch @ThruDate = 'Jul 31 2008 11:59PM' EXEC ProcDeleteStatsBatchModule @ThruDate = 'Jul 31 2008 11:59PM' EXEC ProcDeleteStatsFormType @ThruDate = 'Jul 31 2008 11:59PM' EXEC ProcDeleteStatsModuleLaunch @ThruDate = 'Jul 31 2008 11:59PM'
The @ThruDate can be changed to be the desired date, just as it can in Report Viewer, and as long as the @ThruDate value on all four SQL queries above is the same.
Please note that the ProcDeleteStats procedures only affects 1000 records at a time.
The report viewer uses the following command to determine if ProcDeleteStats needs to be run multiple times because the data exceeds 1000 records:
procCountStatsRecords @ThruDate='Jul 31 2008 11:59PM'
To determine if the ProcDeleteStats queries should be run again, run the command with the date value used prior, and if the count is not zero, the commands should be run again.