Some SQL best practices

September 24, 2009 Leave a comment Go to comments

I had been seeing lot of SQL best practices posts on the web. I found some points missing in many of these post. I have consolidated few of my learning and posted it below. Hope it will be useful to you.

Storing objects in Database files
You should store the database catalog in the primary file store and all data and objects in secondary files  . Disk access contention can be reduced by this configuration.

Designing Filegroups
Create one user-defined filegroup to hold secondary data files and database objects. Configure  this filegroup as the default, this will automatically let  SQL Server will store all objects you create in this filegroup.

For good performance of data and log files
Do not place data files and the operating system files on the same drive .doing so will result in disk contention.

Place the tempdb database on a separate drive if possible. In environments in which there is intensive use of tempdb databases, you can get better performance by putting tempdb on a separate drive. It lets SQL Server perform tempdb operations in parallel with database operations.

Place data files and transaction log files on a separate drives . This will give you the best performance by reducing disk contention between data and transaction log files.

Password
use the options to check the Windows expiration policy for SQL Server logins and apply the local Windows password policy .

Cleaning up temp objects
If you create a temporary table, you should drop it after its use. This ensures that structures are not left hanging around and allows resources to be reused/ reclaimed .

Do not wait for the connections to be closed to clean up any temporary tables, because many applications use connection pools in which the connections may never get closed. Explicitly dropping a temporary objects ensures that you never receive errors on attempting to create the temp objects again.

Security – assigning permissions

  • Security best practices mentions that you never grant permissions directly to a user. You should add a Windows login to a Windows group and the Windows group as a login to  SQL Server. You then add this group as a user in a database.
  • Create roles in a database corresponding to various job functions, and assign database users to the appropriate role.
  • Assign security permissions on objects in the database to the database role.

Queries
If possible, avoid SELECT * queries, which return all columns in a table/ s. Always specify a column list, which will ensure that you don’t select columns that are not needed.

Use different column names
Make sure that every output column of a query has a distinct column name. Applications should always be able to rely on column names for  retrieving data and should not be allowed to use column ordinal position.

Avoid cursors
This might be the millionth time you hearing it,  but thought of adding it. Avoid cursors . Ideally, cursors should be used only when a set-based solution is impossible to implement.

Test your code by Using transactions
Begin a transaction before running your code, and then roll back the transaction when you’re done testing. Your data will be in the same state it was in when you started.

use schema binding
This will avoid dropping objects accidentaly. Create some views even if it is not used on a table and then make it bonded to schema.

Using Stored Procedures to modify data
Stored procedures are always a better option because you can more easily validate changes via stored procedures. Stored procedures are also more flexible.

Try to avoid recompilation of Stored Procs
Stored procedures are compiled into the query cache when executed. Compilation creates a query plan as well as an execution plan. SQL Server can reuse the query plan for subsequent executions, which conserves resources. But the RECOMPILE option forces SQL Server to discard the query plan each time the procedure is executed and create a new query plan. There are only a few extremely rare cases when recompiling at each execution is beneficial, such as if you add a new index from which the stored procedure might benefit. You typically should not add the RECOMPILE option to a procedure when you create it.

Code efficiency
Use built in procedures and function instead of custom ones ,as these are optimized for performance.

Referential integrity and triggers
You can use triggers to enforce referential integrity. However, you should not use triggers in place of declarative referential integrity (DRI) via a FOREIGN KEY constraint. DRI is enforced when the modification is made, before the change is part of the table, and is much more efficient than executing trigger code. However, you cannot define FOREIGN KEY constraints across databases. To  enforce referential integrity across databases, you must use triggers.

Using filegroup backups
You should select a filegroup backup method when the size of a database makes it impractical to either back up or restore an entire database while still meeting your recovery requirements.

moving databases
Creating the files from scratch can consume a significant  amount of time, you should not drop a database before a restore if you are going to overwrite it. If you are using backup and restore to move a database to a different server with a different directory structure or the directory structure has changed, you can use the WITH MOVE option to cause the restore operation to create the underlying files in a path different from the original backup.

Recovering to a point in time after a disaster
During most disaster scenario, you always have transactions in the log that have not yet been backed up. For this reason, your first step in any recovery operation is to issue one final BACKUP LOG command. This process captures all remaining committed transactions that have not been backed up. Because you can issue a BACKUP LOG command against a database even if every data file, including the primary data file, is no longer available. The backup of the tail of the log then becomes the final transaction log that you apply in a restore process, enabling the database to be recovered without any loss of data.

Shrinking databases
As with the automatic shrink setting, the manual shrink process takes place in the background and does not affect users in the database, but the process of shrinking a database can consume system resources and degrade server performance. Also, as with auto shrinks, continually shrinking and regrowing a database can lead to fragmentation at the file level, which can be difficult to fix in busy database environments. DBAs should perform database shrink operations or transaction log shrink operations (covered in a moment) only when they are certain that the unused space being reclaimed will not be needed in the future.

Authentication
When all instances reside within a single domain or across trusted domains, you should use Windows authentication. When instances span nontrusted domains, you should use certificate-based authentication.

use sys.dm_db_index_usage_stats
The sys.dm_db_index_usage_stats DMV can be used to find any indexes that the query optimizer is not using. If the system has been running for awhile, and an index does not have any seeks, scans, or lookups registered for it, it is a strong possibility that the index is not being used to satisfy any queries. Or an index might show activity but is no longer being used.

Encryption
Use RC4 for minimal encryption strength and best performance and AES if you require strong encryption, but this algorithm will affect performance.

Availability
When using log shipping to increase availability reasons only, use No Recovery mode.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: