Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2013
    Posts
    35

    Unanswered: List of System Stored procedures (SQLServer2008)

    Hi,
    I had 3 questions regarding system SPs in SQLServer2008:
    1. Does "stored procedures are documented in SQL Server Books Online" in System Stored Procedures (Transact-SQL) have a special meaning or it means that these system SPs have documentation while the others don't?

    2. Why the system SPs listed in MSDN are less than the actual ones in SQLServer2008?

    3. Is there a list that shows which system SPs are most used?

    Thanks in advance,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    1. I have no idea how to answer this question.
    2. Documented stored procedures are "published to the public" and undcoumented stored procedures are "published only to Microsoft". The undocumented stored procedures can be changed with a simple email to the product group. The documented stored procedures require notice, service packs, etc. Most of the system stored procedures are undocumented.
    3. The "most used" stored procedures varies wildly from one machine to another, and often changes over time within a given machine. There are a variety of lists with a variety of degrees of applicability for any given server. The best way to determine this for your system is to use Extended Events or the SQL Profiler to track usage on your machine.
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    35
    Quote Originally Posted by Pat Phelan View Post
    Documented stored procedures are "published to the public" and undcoumented stored procedures are "published only to Microsoft". The undocumented stored procedures can be changed with a simple email to the product group. The documented stored procedures require notice, service packs, etc. Most of the system stored procedures are undocumented.
    Sorry Pat could you please give a simpler definition if possible, I didn't get quite much!!
    Quote Originally Posted by Pat Phelan View Post
    The "most used" stored procedures varies wildly from one machine to another, and often changes over time within a given machine. There are a variety of lists with a variety of degrees of applicability for any given server. The best way to determine this for your system is to use Extended Events or the SQL Profiler to track usage on your machine.
    By the common ones I meant the system SPs that are widely used in web apps if I'm right?

    Regards

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When Microsoft documents a stored procedure in BOL (Books On-Line) or MSDN, that procedure becomes part of the product that is "published to the public" and is relatively resistant to change. The documented parts of the product are expected to be used directly by applications and user-written stored procedures, so Microsoft has established a process that makes it relatively difficult to change these documented features.

    The vast majority of the code in any Microsoft product is only documented internally within Microsoft. Micorosoft's engineers/developers/staff have access to that documentation, but the public has no access to that documentation. This undocumented code is comparatively easy to change, and much of it will change over the life of a product.

    There are a number of undocumented features that through sheer usage have migrated into the documented features. This migration doesn't happen often, but it has happened.

    The usage of any given feature in a product like MS-SQL varies based on many conditions. The most used stored procedures on your machine will actually vary a great deal over time, so that daytime and nighttime may be quite different, as will data entry versus data reporting.

    No two servers are likely to be the same, and certainly a server used for an application like BizTalk will be different from a server used for SharePoint or a server used for a user written website or app. I've seen a great deal more variation than similarity between servers. When I need to understand the usage of a specific server, I actually go measure that usage using either Extended Events or SQL Profiler. I've become pretty cynical about "average" usage because I can't find an "average" server!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2013
    Posts
    35
    Many thanks to you Pat.
    You are really a life saver, hope that I could use your knowledge and experience more in future.

    Kindest Regards,

  6. #6
    Join Date
    Jan 2013
    Posts
    35
    Quote Originally Posted by Pat Phelan View Post
    This undocumented code is comparatively easy to change, and much of it will change over the life of a product.

    -PatP
    Sorry Pat. I came to read your answer once more and this question came up: Do you mean that Microsoft it self changes them through the life cycle?

    thanks

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, Microsoft can and does change the undocumented stored procedures with hotfixes, patches, service packs, etc. That is exactly WHY those procedures are undocumented!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jan 2013
    Posts
    35
    Quote Originally Posted by Pat Phelan View Post
    Yes, Microsoft can and does change the undocumented stored procedures with hotfixes, patches, service packs, etc. That is exactly WHY those procedures are undocumented!

    -PatP
    First of all thanks, after that:
    So can we say that it's not a good idea to use and rely on these SPs until they're documented?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Correct... If any feature (including stored procedures) isn't documented, then there is no guarantee that it will not change or disappear at any time. Even the documented features can change, but the undocumented ones can and do change with little or no notice and fairly frequently too.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jan 2013
    Posts
    35
    Thank you very much Pat.
    There is one thing that bothers me a lot and I would be really grateful if you take a look at it.
    Please check this link: System Stored Procedures (Transact-SQL)
    here at first a list of different SPs are given after that a list of documented and undocumented ones. The questions I have are:
    1. What about the other SPs that BOL has included them in the first list but has not mentioned the in either doc or undoc? (for example Active Directory Stored Procedures and lots more)
    2. I know I asked this question before but can you even not give a guess why the SPs of MSSQL 2008 are 1350 while BOL has given much less than that?
    I have one more question about the link: General Extended Stored Procedures (Transact-SQL)
    3. Why are these SPs not divided into doc and undoc? Are they all documented?

    Regards

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The vast majority of all of Microsoft's code is undocumented, stored procedures are not a special case at all. Only the portions of the code that Microsoft is willing to support being used by end users are documented.

    Adding any entry point to the documented and supported list is an expensive process. It takes a lot of time to ensure that the code is stable, secure, and that it contributes to the long term plans for the product. Any code that is documented effectively "ties the hands" of developers to change the functionality of that code if needed.

    I've been around SQL Server a long time (years before Microsoft owned the product), and have seen several periods where there was an effort made to "document everything" and that caused the product development and innovation to nearly freeze. Relative newcomers clamor to document everything, experienced users have a somewhat different perspective!

    In reference to your extended stored procedure question, all of the code that is listed on that web page can be considered to be documented.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jan 2013
    Posts
    35
    Thank you very very much pat.

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •