SQL Server Performance

What are implications of db growth on stored procedures using indexes?

Discussion in 'General DBA Questions' started by sql_er, Jul 13, 2010.

  1. sql_er New Member

    Hi,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 suggestionsThank you!
  2. satya Moderator

    Before we assuming and coming to a decision you need to clarify few things here:
    • what kind of database maintenance activities are on those databases,
    • what is the transaction log backup schedule,
    • are there any data archive methods followed,
    • do you have any storage free space issue
    As a general practice I have seen that few DBAs always SHRINK the transaction log on regular basis. Allowing SQL Server to grow data and log files automatically is generally a good idea; however, you should keep in mind that allocating extra disk space comes with considerable overhead. If you notice that database files keep growing daily you might wish to increase the growth percentage or amount of megabytes added to the file size each time the database grows.
  3. ashish287 New Member

    as long your indexes are correctly referred and updated, i dont think there should be any problem.
    Also dont consider to upgrade hardware(Ram or processor) immediately, better give sql server a cahnce to manage with exisitng architecture and it will also give you an overview where to look in case of performance problem.
    Most of the time its some query or scheduled activity which consumes most of the resoruce so better have a look of sql server in existing architecture and keep tuning it till you REALLY need upgrade.
    I personally not prefer to upgrade hardware as long I am happy with my investigation and nothing left to tune further.

Share This Page