| Author |
 Topic  |
|
|
ahmeds08
Constraint Violating Yak Guru
India
423 Posts |
 Posted - 11/01/2012 : 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
USA
35007 Posts |
Posted - 11/01/2012 : 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 testing
2. Proper indexing
3. Proper database design
4. Proper jobs in place such as update stats
5. Monitoring in place for bottlenecks such as CPU, blocking, and unexpected long-running queries
6. Tempdb optimized (multiple data files, 1/4 as many as # of CPUs up to 8, but YMMV)
7. RCSI if the app can support it
8. Changing MAXDOP from 0, depends on your workload and testing; we typically start at 2 and adjust as necessary
9. Max memory set
10. LPIM granted to the SQL Server service
11. File initialization?
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/01/2012 : 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
USA
15557 Posts |
|
|
chadmat
The Chadinator
USA
1949 Posts |
Posted - 11/01/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1765 Posts |
Posted - 11/02/2012 : 15:34:36
|
Beyond configuration setting for Production environments also think about:
1) Monitoring - certain critical\fatal situations
2)Daily Reporting - example, backup failure, sql server error logs, windows logs etc - focus on Errors
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
ahmeds08
Constraint Violating Yak Guru
India
423 Posts |
Posted - 11/05/2012 : 01:38:57
|
Thanks everyone for your advice.
|
|
|
| |
Topic |
|
Production environment best practices
