Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Aged Yak Warrior

737 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.


Almighty SQL Goddess

38200 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

Subscribe to my blog
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

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.

Go to Top of Page

Most Valuable Yak

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

The Chadinator

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.

Go to Top of Page

Flowing Fount of Yak Knowledge

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

Aged Yak Warrior

737 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  
 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.04 seconds. Powered By: Snitz Forums 2000