SQL Server Performance

new db

Discussion in 'Performance Tuning for DBAs' started by bfarr23, Nov 5, 2003.

  1. bfarr23 New Member

    we have a database(sql server 2000 ent. ed) that is used 95% for reads. This db has been re-designed recently and will go into production soon. This is an extremely busy web site. These read databases are fed with new data each day at 4:00am by snapshot replication. What are some things I can do to ensure this new re-designed (maily read) DB is as fast as it can get?

    Thanks.





  2. Argyle New Member

    Will it be located on a dedicated server for this database or on a shared server? In any case good indexes and up to date statistics are always a good start. On a shared server you might want to pin the tables for this DB in memory if it's more important than the others.

    Then of course hardware setup and general DB design and queries come into play but I assume that's written in stone already. After the DB goes live monitor with profiler and check cache hit ratio, recompiles etc. to see that the values are ok.

    /Argyle
  3. bfarr23 New Member

    the database is about 600MB. Never grows by much at all. Should I pin all tables in RAM? Would it offer much performance benefit?
    not that concerned about transactions being lost. (its a real estate listing site)

    (there is 2GB RAM in the dedicated DB servers. There are 4 DB servers holding the same data.)
  4. satya Moderator

    DBCC PINTABLE is best used to keep small, frequently referenced tables in memory. The pages for the small table are read into memory one time, then all future references to their data do not require a disk read.

    Although it can provide performance improvements, it must be used with care. If a large table is pinned, it can start using a large portion of the buffer cache and not leave enough cache to service the other tables in the system adequately.

    I believe leaving the current settings as it is will be fine.
    Monitor the PERFMON counters if you feel any performance degradation.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. thomas New Member

    If the database is 600MB, and you have 2GB of RAM, there should be no need to use DBCC PINTABLE as the entire database will fit in memory. SQL Server will automatically hold these tables in RAM if needed, so I would agree with Satya, and leave it as is.

    Tom Pullen
    DBA, Oxfam GB
  6. satya Moderator

    Rather maintain regular backups & dbcc checks to get optimum performance, as you say these are read-only databases.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. bfarr23 New Member

    is there any need to do log backups(since a mainly read db)?

    (Its a snapshot subscriber db(set to FULL recovery model)).
  8. satya Moderator

    Still to provide complete protection against media failure. If one or more data files is damaged, media recovery can restore all committed transactions. In-process transactions are rolled back as recovery model is maintained FULL.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. bfarr23 New Member

    sorry. they are set to simple mode.

    full backups maintained.

  10. satya Moderator

    In that case Tlog backups cannot be performed and so maintain regular full database backups.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page