Home Forums SQL Server 2005 Administering Reindexing requires recompile of Stored procedures RE: Reindexing requires recompile of Stored procedures

  • Reg_Mayfield (5/23/2008)


    I have created a MP that runs on the weekend, I have two procedures that run, one to Rebuild Indexes and the second is Update Statistics...

    This is a VERY common Worst Practice I see at the majority of my new clients. A REBUILD of the index is forced to read 100% of the pages, obviously, in order to completely REBUILD the index. Thus the statistics that it builds after index creation have a 100%-page-read set of stats. Then when you do the update stats action next - it reads only a PORTION of the pages in the just-REBUILT index . . . and you wind up with LESS EFFECTIVE statistics because of that. More work and 'worse' data. Double hit!

    Everyone who reads this - write it down in your document of what NOT to do. 😀

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service