August 29, 2011 at 5:11 am
Hi all,
I came across a SQL Server 2005 where my predecessor implemented a SQL Agent Job for maintenance purposes (e.g. one script running several DBCCommands and so on)
Since I am no fan of shrinking a database and then rebuilding the IDX I want to edit the job.
But there´s one little section inside the script where I have no idea why this was implemented.
A cursor loops through sysindexes and just drops every statistic where the
indid is between 0 and 255
set @tab_id = object_id(@table_name, 'local')
DECLARE STAT_CURSOR CURSOR FAST_FORWARD LOCAL FOR
select [name] as 'statistics_name' from sysindexes
where id = @tab_id
and indid > 0 and indid < 255 and (status & (64 | 8388608)) > 0
order by indid -- User created & auto-created stats
OPEN STAT_CURSOR
FETCH NEXT FROM STAT_CURSOR INTO @STAT_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql_cmd = 'DROP STATISTICS [' + @table_name + '].[' + @STAT_name + '];'
PRINT @sql_cmd
EXEC(@sql_cmd)
FETCH NEXT FROM STAT_CURSOR INTO @STAT_name
END
CLOSE STAT_CURSOR
DEALLOCATE STAT_CURSOR
I cannot explain to myself if there´s any reason to do that.
Any ideas?
Regards
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
August 29, 2011 at 5:16 am
No good reason. It's just forcing SQL to recreate them when it needs them.
p.s. Maybe also change the job not to use deprecated system views while you're editing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2011 at 6:46 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply