SQL Server Performance

Database design for combination OLTP / Reporting database

Discussion in 'T-SQL Performance Tuning for Developers' started by ZLA, Jun 14, 2008.

  1. ZLA New Member

    I have a B2B web site that supports both online transactional processing as well as reporting requests. This is not what one would consider a high volume site but we do have about 100 active users per day. About 10% of the data is active and undergoing daily updates while 90% of the transactions are completed and no longer being modified. So the site supports transactions on the 10% data and allows users to review their total activity (static past and active present).
    What types of database design / schemas would best support this dual purpose database?
    At this time, it's a single relational database but it is growing larger and could perhaps warrant a separation into an active oltp database with replication or other duplication to a separate reporting database. Are there other options?
    Thanks in Advance.
  2. techbabu303 New Member

    At database physical storage level separation is required so workload is distributed , as you posted it is better to keep a duplicate for reporting purposes instead of running it on OLTP instance.
    If the report do not consume lot of CPU ,no locks and probably just 10 users , it would not be required to do the above.
  3. satya Moderator

    What kinds of database optimization jobs you have on this one?
  4. ZLA New Member

    Currently, very little. The auto create statistics, auto update statistics and torn page detection options are checked. Because of table modifications, some of the important indexes have probably been recreated over the last 6 months but probably not most of them.
    I've been reading up on optimizations and profiling and will be doing one or more profiles this week to address performance from that angle. However, I'm also considering future design issues such as archiving of old static data while supporting the smaller percentage of active transactional data. But I welcome any other suggestions you have.
    Please let me know if you need further details. Thanks.

Share This Page