July 2, 2008 at 8:16 am
Hi Friends,
I'm in the process of creating a list of best practices which can be compared whenever we go for SQL server setup for new project, I'm preparing a checklist with points categorized as
1) DBA Best Practice for Standalone Instance.
2) DBA Best Practice Specific to Clustered Instance.
4) DBA Best Practice Specific to 64 bit Instance.
5) Developer Best Practices.
I started almost and thought that if anyone is ready to add some points then i can group all and post it as a big and complete checklist in sqlservercentral so that upcoming DBA's can use this! Please reply to this post with the best practices other than common things listed below... Please focus on SQL 2005 rather than 2000 🙂
Category 1: DBA Best Practice for Standalone Instance.
1) Don’t use unrestricted growth while creating database. Keep a maximum size
2) Profile during the peak time and the Index during non Peak time.
3) Keep Log, Data, TempDB in different Physical drives to reduce queue.
4) Keep a good amount of virtual memory
5) Use Named Pipes connections incase LAN Speed is good like 1 Gbps
6) Increase the network packet size from 4096 to higher or lower value if needed.
Category 2: DBA Best Practice Specific to Clustered Instance.
1) Ensure the private network is private
2) Ensure that your network card settings are identical for every server in your cluster and that they are not configured to automatically detect network settings.
3) For your public network, use at least two WINS or DNS servers on your cluster network segment or VLAN
Category 4: Developer Best Practices.
Proper Naming Convention for Databases, Tables, Views, Stored Procs…
Use NOCOUNT ON in Stored Procedure.
Parameterize all your queries.
Double think before using Triggers
Avoid Cursors. (Use do..)
Try to use primary key columns while JOIN'ing
Don’t use function in the where clause
Select only required columns (processor time, memory and network traffic)
Use Stored procedure for all queries even for single select to utilize cache
Make sure that the Primary Keys always exist.
Avoid using dynamic SQL
The foreign key relation should exist for data accuracy
The objects should be accessed in the same order in different stored procedures or triggers
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
July 2, 2008 at 11:43 am
On the unrestricted growth thing, I'd say it's more important to predict the size the database should be in 6 month (or some other reasonably long interval), and set it to that size, then set up autogrowth for a large chunk. Don't let it use percentage growth.
This is to avoid file fragmentation, which will degrade performance of the whole database.
It applies at least as much to tempdb as it does to user databases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 11:44 am
Another one that's oft forgotten is splitting tempdb into multiple files if you have multiple CPUs and can split it across multiple drives.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 11:49 am
Here's a list of a few more I'd add:
Choose your RAID carefully. RAID-5 is okay for OLAP, but RAID-1/10/01 is better for OLTP.
Do backups to a different drive than the data and log files. Test restoring your backups.
Manage your indexes. Monitor them for use, monitor them for updates, monitor them for redundancy. Make sure that Include is used where appropriate.
Have a Numbers table (Tally/Integers/Whatever). Have a Calendar table. Use them.
Don't use table variables unless you absolutely have to. Even then, check your options with someone else.
ASK FOR ADVICE!!! (That's the most important one.)
Those are the ones I can think of off the cuff.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 8:42 pm
Category 2: DBA Best Practice Specific to Clustered Instance.
--> make SQL/MSDTC/Cluster Service seperate groups.
July 2, 2008 at 9:04 pm
Two more:
1. "SQL Server clustering best practices" By Hilary Cotter with Geoff Hiten
http://searchsqlserver.techtarget.com/
2. SQL Server Consolidation on the 64-Bit Platform, Lessons Learned
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx
July 2, 2008 at 10:08 pm
GSquared (7/2/2008)
Don't use table variables unless you absolutely have to. Even then, check your options with someone else.
Why do you say that? I'm not necessarily an advocate of table variables, just an advocate of using the right technology (in this case table variable vs local temp table vs global temp table) in the right circumstance. I am familiar with the differences - transactions / recompiles / statistics / usability in functions / etc, but I've never read anything or come across any situation that indicates table variables should never be used ... :ermm:
July 3, 2008 at 12:02 am
Don't shrink your databases.
Make sure that you have a good backup strategy
Make sure that you test your backups. A backup that you cannot restore is worse than no backup at all.
Make sure you do regular integrity checks on your database. If you get corruption, you need to be able to narrow down when it occured.
Make sure that you set up regular monitoring and that you establish a performance baseline. If you don't know what nomal performance is, you'll have trouble picking up abnormal performance and you will have great difficulty finding the cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2008 at 12:19 am
Hi All,
Happy to see that you all are interested in sharing your experience... Also give some explanation about setting performance baseline and how to compare that when we face problems... Also what is the alternative for SHRINK DATABASE? You suggest to SHRINK FILE or something else? Also i will check those best practices link and update the post... Thanks again... 🙂
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
July 3, 2008 at 12:40 am
Sakthivel Chidambaram (7/3/2008)
Also what is the alternative for SHRINK DATABASE? You suggest to SHRINK FILE or something else?
I suggest that you don't shrink your databases at all. Databases tend to grow and need some free space inside to work properly. Shrinking (especially if combined with auto grow) tends to lead to index fragmentation and file level fragmentation.
I wrote a brief article[/url] on it a while back. Be sure to check the links at the end also. They're probably better than the article itself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2008 at 7:59 am
Simon Facer (7/2/2008)
GSquared (7/2/2008)
Don't use table variables unless you absolutely have to. Even then, check your options with someone else.Why do you say that? I'm not necessarily an advocate of table variables, just an advocate of using the right technology (in this case table variable vs local temp table vs global temp table) in the right circumstance. I am familiar with the differences - transactions / recompiles / statistics / usability in functions / etc, but I've never read anything or come across any situation that indicates table variables should never be used ... :ermm:
Table variables have such a tendency to mess up execution plans that I have to recommend against them as much as possible. Of course, that's not as important as "don't turn on autoshrink", or "avoid cursors", but I've seen them overused so many times in so many places, and seen the dramatic reduction in performance they can result in, that I feel it does need to be said.
The one place I've found that they can improve performance, is moving data from one server to another, across a slow connection with significant latency. Because of the lack of transaction, they don't invoke DTC in those cases, and that can help with speed (at the cost of reliability). SSIS can accomplish the same speed increase over T-SQL in this case, and without the loss of transactions, etc. DTS doesn't do it as well. So, again, I have to recommend against them, unless you really need them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 3, 2008 at 8:24 am
I can see using table variables in test environments to test insert/update/delete processes in a explicit transaction. In SQL Server 2005 this is easier using the OUTPUT clause to load the data into a table variable then rollback the insert/update/delete. In SQL Server 2000, you have to code this seperately inside the explicit transaction.
Since table variables are not affected by transactions, you now have a means to easily compare before and after values. If you need to retain the values from the test, you can insert the data from the table variable to a temp table after the rollback.
Also, table variables are good for small result sets that would not benefit from indexing anyway (maybe a few hundred rows or so depending on how wide the rows were).
😎
July 3, 2008 at 10:21 am
A new category should be security:
Only grant the minimum privileges necessary including:
Only DBAs should have any server level roles.
In development, grant minimum rights (ddl_admin is the highest privilege) and do not grant db_owner.
Outside of development, only persons having responsibility to make changes should have a privilege to make a change. e.g. Applications should not have any server level roles, any built in database level roles or be granted "create "
Assign privileges to groups and then logins/users to groups - do not grant privileges directly to a user.
Logins are best managed via Active Directory groups.
Secure all passwords.
Lock out the SA account.
A new category - Networking:
For each application, use DNS to alias the server name. Then when a database needs to be moved for performance reasons or the server hardware is being replaced, just the DNS needs to be changed not all of the application connection configurations.
Firewall the server so that only the necessary ports are open.
Other:
Server should be dedicated to SQL Server and
1) Application files should be stored on a different server.
2) Application programs should run on a different server.
3) Consider setting up a central job server. If the jobs are scattered across many different SQL Servers, inter-job dependencies are difficult to manage. This also restricts the ability to replace the job scheduler with different software.
Under Category 4: Developer Best Practices:
Be careful about using Schemas other than dbo
Qualify all objects references to include schema (performance).
Use synonyms to reference objects outside of the current database and do not hard code multi-part names such as linked server names or database names.
Be very careful about using linked servers due to the performance impact. Consider instead moving databases that are accessed from a single SQL statement to the same SQL Server.
For "Parameterize all your queries." use sp_executesql and do not use exec ('sql statement') to prevent SQL injection.
SQL = Scarcely Qualifies as a Language
July 3, 2008 at 10:30 am
Carl Federl (7/3/2008)
A new category - Networking:For each application, use DNS to alias the server name. Then when a database needs to be moved for performance reasons or the server hardware is being replaced, just the DNS needs to be changed not all of the application connection configurations.
How does this work for connecting to named instances?
😎
July 3, 2008 at 10:46 am
"How does this work for connecting to named instances?" DNS aliases work for the server name and not for named instances.
That is why I recommend only one instance per server.
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply