SQL Server Performance

Observation about modify_date in sys.objects

Discussion in 'SQL Server 2005 General DBA Questions' started by DBADave, Nov 12, 2008.

  1. DBADave New Member

    In previous versions of SQL Server there was only a crdate or createdate in sysobjects and a few other system tables. This was a bit frustrating because I always wanted to know not only the date an object was created, but also when it was last modified. In 2005 sys.objects replaced sysobjects and introduced the modify_date. I was happy to see this until just a few minutes ago when I realized running sp_recompile causes the modify_date to change. A developer asked me to check the last time a stored procedure was modified. I told him Saturday at midnight. Neither of us could understand how it was modified at that time. I expanded my query to check for all objects modified around that time and found every stored procedure in the database. After digging around for a few minutes we uncovered an sp_recompile job that ran exactly at midnight on Saturday. Thus the reason our modify_date changed. Considering stored procedures are marked for recompile in other ways besides using sp_recompile and none of those ways modify the modify_date in sys.objects, we can only assume that this is a bug/feature of 2005. What a tease. They give me my modify_date after all these years and then introduce a process that makes the column essentially useless. Can anyone shine some light on this topic?
    Dave
  2. FrankKalis Moderator

    Well, this is annoying, but documented...
    Check this out: http://msdn.microsoft.com/en-us/library/cc297251.aspx
    In the chapter "Correctness-Based Recompiles" it says
    "In addition, running the procedure sp_recompile on a table or view will change the modification date for the object. Which you can observe in the modify_date column in sys.objects.This will make SQL Server believe that a schema change has occurred sothat recompilation will take place at the next execution of any storedprocedure, function, or trigger that accesses the table or view.Running sp_recompile on a procedure, trigger, or function willclear all the plans for the executable object out of cache, toguarantee that the next time it is executed, it will be recompiled."
    So, it's a feature, not a bug. [:)]
  3. DBADave New Member

    Thanks for the link. I tried to find some documentation about it yesterday, but was unsuccessful. I would love to hear an explanation from Microsoft as to why they chose to use the modify_date. They could have created a bit field in the system table if necessary and turned the bit on/off based upon sp_recompile. Now the modify_date has been rendered useless in our environment. What frustrates me more is there are specific conditions that can also force a recompile of a stored procedure and those conditions do not cause the modify_date to be changed. That appears to be a lack of consistency on their part, but now I'm just venting.Thanks again,
    Dave
  4. FrankKalis Moderator

    I can understand you. [:)]
    You might want to look around http://connect.microsoft.com/ to see if someone probably asked for this to be changed or create a request yourself. I know that MS is paying close attention to these request, so it might be worth a try.
  5. DBADave New Member

    Thanks Frank. I posted a request on the Microsoft support site.
    Dave
  6. sunny197801 New Member

    Hi
    There is something I would like to share though it may be known already. Modify_date may not be reliable to identify procedure changes, but its not the only option though to get this info. SQL Server 2005 has another good feature DDL triggers. Here is an excerpt from Books On Line:
    DDL triggers can be used for administrative tasks such as auditing and regulating database operations.Use DDL triggers when you want to do the following:
    • You want to prevent certain changes to your database schema.
      • You want something to occur in the database in response to a change in your database schema.
        • You want to record changes or events in the database schema.
      • DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.
  7. satya Moderator

    <P mce_keep="true">Dave </P><P mce_keep="true">Have you followed your connect question after you posted there?</P><P mce_keep="true">Here is the reply I found:</P><DIV class=comment-item id=FeedbackComment><DIV class=Post id=FeedbackCommentBody>Hi Dave,<BR>Thanks for your suggestion. We will consider it for a future version of SQL Server. From SQL Server 2005 onwards, you can use DDL triggers to track any ALTER_PROCEDURE statement change for example. This way, you can maintain your own table that tracks the modified date for stored procedures. Please refer to the DDL triggers topic in Books Online for more information.<BR><BR>--<BR>Umachandar, SQL Programmability Team</DIV><DIV class=PostedBy id=FeedbackCommentUserInfoBox><IMG id=ctl00_MasterBody_FeedbackCommentsRepeater_ctl01_Img1 src="http://connect.microsoft.com/Images/arrow1.gif">&nbsp;Posted by <STRONG>Microsoft</STRONG> on 21/11/2008 at 19:06</DIV></DIV>
  8. DBADave New Member

    No I did not. Thanks Satya for pointing that out to me. I was experimenting with the DDL triggers yesterday in 2005. Very nice, although I quickly learned that some only work in 2008 (ADD_ROLE_MEMBER). Auditing object changes however works in 2005.
    Dave

Share This Page