Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ahmeds08
Aged Yak Warrior
737 Posts |
 Posted - 2012-11-01 : 02:00:55
|
| Dear Experts,I need your advise in setting up sql server db for production environment.What are the best practices to be followed for good database performance.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-11-01 : 15:57:42
|
| It's a rather vague question. Here are some things to do:1. Fastest IO, CPU and memory you can afford, based upon your load testing2. Proper indexing3. Proper database design4. Proper jobs in place such as update stats5. Monitoring in place for bottlenecks such as CPU, blocking, and unexpected long-running queries6. Tempdb optimized (multiple data files, 1/4 as many as # of CPUs up to 8, but YMMV)7. RCSI if the app can support it8. Changing MAXDOP from 0, depends on your workload and testing; we typically start at 2 and adjust as necessary9. Max memory set10. LPIM granted to the SQL Server service11. File initialization?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-11-01 : 16:58:06
|
| It's a good idea to have some database backups.I recommend the following as a starting point:1. Setup a daily full backup of system databases master, model, and msdb, and retain them for 1 week.2. Setup daily full backups of all user databases, and retain for at least 3 days.3. Setup transaction log backups for all users databases scheduled to run every 15 minutes, 24x7, and retain for at least 3 days.4. Setup weekly, monthly, etc. backups as needed for longer term backups.If you are using a version of SQL Server that supports compressed backups, use backup compression on all backups.Tape backups of the SQL Server database backups for disaster recovery are also a very good practice. Rotate them offsite as needed to support DR objectives.CODO ERGO SUM |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-11-01 : 17:25:39
|
| Expounding on some of Tara's#2 - Choose Clustered indexes appropriately. Random GUIDs are a bad choice (Usually). small (int/bigint), unique, ever increasing...These are things that make for the best CI choice.#4 - Index Maintanence as well.#6 - Make sure the files are equally sized, and large enough so that TempDB doesn't need to regrow every time SQL is restarted.I would add proper File sizing and growth/maintanence. Datafiles with Instant File Init are not a big deal, but Log (auto)growths of any size are problematic. Grow your files manually during down time rather than relying on autogrowth.-Chad |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-11-02 : 15:34:36
|
| Beyond configuration setting for Production environments also think about:1) Monitoring - certain critical\fatal situations2)Daily Reporting - example, backup failure, sql server error logs, windows logs etc - focus on ErrorsJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2012-11-05 : 01:38:57
|
| Thanks everyone for your advice. |
|
|
|
|
|
|
|
|
Production environment best practices