Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007

    Unanswered: Are MSSQL indexes always stored in memory?


    Recently our company began considering an increase in the amount of content we house in our MSSQL 2000 database. One of the obvious questions that came up was what would be the performance implications - mostly on the stored procedures, which are used by our clients, and are expected to continue to return results within a few hundred milliseconds.

    So if all our stored procedures are well tuned to use indexes and if we have a lot of RAM, would that mean that we can grow without a large affect on our stored procedure performance?

    I know this is not a yes or no question, but just wanted to hear some general thoughts/opinions.

    I guess the first question is whether my assumption is correct about the MSSQL 2000 indexes. Are they always stored in memory?

    Please let me know any suggestions

    Thank you!

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    No, frequently used data is stored in RAM in all versions of SQL Server. SQL 2000 is good about this, but not nearly as good as SQL 2008 R2 is.

    The performance of SQL 2000 will probably support databases up into small numbers of terrabytes (maybe ten to fifteen terrabytes) with sub-second response time for queries that can use indicies.

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

Posting Permissions

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