SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Production environment best practices
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ahmeds08
Aged Yak Warrior

India
656 Posts

Posted - 11/01/2012 :  02:00:55  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
37142 Posts

Posted - 11/01/2012 :  15:57:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 11/01/2012 :  16:58:06  Show Profile  Reply with Quote
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

USA
15676 Posts

Posted - 11/01/2012 :  17:15:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
1974 Posts

Posted - 11/01/2012 :  17:25:39  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 11/02/2012 :  15:34:36  Show Profile  Visit jackv's Homepage  Reply with Quote
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

India
656 Posts

Posted - 11/05/2012 :  01:38:57  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
Thanks everyone for your advice.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000