Database Design Best Practices :-
Although there are several factors that needs to be considered while designing databases, here are some tips for designing your relational data warehouse database:
• Keep the data files and log files on separate drives with separate spindles.
• Make use of the fastest drives possible for data and log files.
• Create data files for as many processors on the machine and distribute the files equally on the different available drives.
• As transactional backups are normally not taken, set the Recovery Model of the database to SIMPLE. If it is required to do transactional backups, then switch to BULK LOGGED recovery model before bulk data load operations and switch back to FULL recovery model after the data load.
• Design views to pull data from base tables of relational data warehouse and specify query hints or filter conditions in them.
• To avoid more locks or lock escalations, specify the TABLOCK query hint while querying or ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF when creating tables or indexes or pull data from a read only database.
• Sometimes to aggregate fact data at the source before pulling the data, one can improve performance by creating indexed (materialized) views for this and instead of doing aggregations every time, pull the data from the indexed view.
• Make sure that the resources are available to SQL Server for serving the data pull requests; one can use RESOURCE GOVERNOR to control the amount of resources available to OLTP and OLAP operations.
Hope it will be helpful.
Thanks,
Although there are several factors that needs to be considered while designing databases, here are some tips for designing your relational data warehouse database:
• Keep the data files and log files on separate drives with separate spindles.
• Make use of the fastest drives possible for data and log files.
• Create data files for as many processors on the machine and distribute the files equally on the different available drives.
• As transactional backups are normally not taken, set the Recovery Model of the database to SIMPLE. If it is required to do transactional backups, then switch to BULK LOGGED recovery model before bulk data load operations and switch back to FULL recovery model after the data load.
• Design views to pull data from base tables of relational data warehouse and specify query hints or filter conditions in them.
• To avoid more locks or lock escalations, specify the TABLOCK query hint while querying or ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF when creating tables or indexes or pull data from a read only database.
• Sometimes to aggregate fact data at the source before pulling the data, one can improve performance by creating indexed (materialized) views for this and instead of doing aggregations every time, pull the data from the indexed view.
• Make sure that the resources are available to SQL Server for serving the data pull requests; one can use RESOURCE GOVERNOR to control the amount of resources available to OLTP and OLAP operations.
Hope it will be helpful.
Thanks,
No comments:
Post a Comment