SQL Server Performance

The data size for the DB on SQL Server 2000

Discussion in 'Performance Tuning for DBAs' started by licyho, Mar 26, 2003.

  1. licyho New Member

    Dear all,

    I'm handling a DB on SQL Server 2000 with more than 60 tables.
    There are over 1 million records in 9 tables (each of them).
    I found that the performance of the related applications drops
    down a lot recently. I hope I could ask for your help on
    the limitation of the data size on SQL Server 2000. If there is
    a limitation of the data size, what should I do for the
    performance tunning? Will re-index help?

    Thanks a lot

  2. trifunk New Member

    BOL says the maximum is 1,048,516 TB3 (Terabytes) so I doubt this is a problem.

    There's alot you can do to tune your database from updating the statistics to rebuilding your indexes, there's alot of articles on this site about it. What kind of maintenance do you currently do on the database?


    World Domination Through Superior Software
  3. Chappy New Member

    If performance is suffering as the table grows, it could suggest badly designed indices, or even badly designed queries. If this isnt the case a defrag could help, yes
  4. bradmcgehee New Member

    All this advice is good. A million records is nothing to SQL Server. I have many tables with 50 million or more rows, and I can query all of them very quickly.

    Brad M. McGehee
  5. licyho New Member

    Dear all,

    Thank you all for your quick responses.
    The system I'm handling now is a one for the traceability for the production line. I don't have any application source codes. The only thing I could do is working on the DB to figure out what causes the system performance drops. I found that the system needs to retrieve records from a table contains over 1 million rows on every step on the production line. At the same time the table size grows very fast. Now I'm going to work on the statistics and re-indexing.

    Thanks again

  6. barozai New Member


    you r on the right way....
    Re-index your data. It'll most probably solve your problem.

  7. bradmcgehee New Member

    You may also want to get a Profiler trace and run it through the Index Wizard to see if it recommends any new indexes.

    Brad M. McGehee

Share This Page