August 22, 2008 at 8:51 am
Hi All,
We are using SQL Server 2005 for our application software which is bacially a Predictive Dialler.The application runs 27x7. Ant the level of transaction is high.
I just asked something regarding the performance of the SQL Server.
1. Should I use full or simple recovery model?
2. What other server options should I use for performance optimization?
3. What regular actions should I consider to keep the performance?
I do perform a reindexing of tables and it greatly improves the performance.
Please advice.
Thank anfd Regards,
Dinendra
August 22, 2008 at 9:57 am
1. Your choice of recovery mode will depend on if you need point in time recovery of your databases. If you do a restore, will an overnight backup do or will you need the data from the transactions that happened between the backup and the point of failure? In a production system it's usually recomended to use Full Recovery Mode, and setup transaction log backups to happen at intervals depending on the transaction volume and the company's requirements. You will also need Full Recovery mode if you intend to do some database mirroring or replication.
2. Other server options to consider for performace are your transaction isolation levels. In SQL Server 2000 it was common to use READ UNCOMMITED to avoid blocking on SELECT statements, but there are new options available in 2005 if you want to avoid "dirty reads" by turning on READ_COMMITTED_SNAPSHOT. Just make sure your TempDB can handle it. After that, watch memory usage, and try to avoid having too many OS virtual memory page swaps to disk and keep your procedure and data buffer cache hit ratio percents in the mid to upper 90s.
3. For performance optimizaton, maintaining your indexes as oy say you already do is a great start. The other side of the coin is making sure that you index the right things in the right way, making sure you pick the right column(s) for the clustered indexes and have indexes with multiple columns have the most selective column first in the index if practical. Also, there's some really neat reports available in the Performace Dashboard for SQL Server Management Studio, SP2:
August 24, 2008 at 2:55 am
Charshman, I really appreciate your suggestions. Thanks a ton.:):)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply