July 31, 2007 at 3:14 am
Hello
I have set up a process that reindexes tables where ScanDensity <75 or LogicalFragmentation > 10. This is working fine. My question is having run DBCC reindex on the tables is it then necessary to execute SP_recompile against the reindexed tables, or as I suspect does the dbcc reindex process which causes the schema version to be updated, also mark the table (and procedures that reference the table) for recompilation.
From what I gather, sp_recompile uses DBCC_Lock_Object_Schema to update the schema version of the object and it is this that causes the recompilation to take place.
July 31, 2007 at 8:05 am
You do not need to recompile if you are cleaning up fragmentation. The plans don't take fragmentation into account that I know of, just statistics. If you have a huge data change on a table, then you might need to recompile the procs running against that table to see if the plan changes. Or if you add/remove indexes, but a reindex shouldn't make a plan change.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply