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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Production environment best practices

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 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
Go to Top of Page

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
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-01 : 17:15:32
For Tara's suggestions on #10 and #11, here are details:

http://msdn.microsoft.com/en-us/library/ms190730.aspx
http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx
Go to Top of Page

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
Go to Top of Page

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 situations
2)Daily Reporting - example, backup failure, sql server error logs, windows logs etc - focus on Errors


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-11-05 : 01:38:57
Thanks everyone for your advice.
Go to Top of Page
   

- Advertisement -