The Business Intelligence Blog

December 14, 2010

SQL Server best practices

Filed under: SQL Server Database — Tags: , — Shahfaisal @ 10:14 pm

1. Isolate OS, SQL binaries and Data/Log files.
If possible, do not install SQL Server or any other application on C drive and be sure to isolate OS and SQL Server data/log files by having them on different physical drives. Never place data and transaction log files on the same physical disk. Here is an example configuration that will help avoid contention issues:

  • C: Operating System
  • D: SQL Server (or any other application)
  • E: SQL Server Log Files
  • F: SQL Server Data Files

2. Use the principle of least privilege and reduce the surface area of attack
Two general (but important) rules of security are:

  • Reduce the surface area of attack – This means installing only the REQUIRED components/features and disabling the ones that are not needed immediately. This will improve security by reducing the surface area of attack.
  • Use the principle of least privilege – This means granting just enough permissions to users and service accounts to do the job. Granting unnecessary elevated rights will increase the risk of both intentional and accidental damage to the system resources.

Also, ensure the physical security of each SQL Server instance, by preventing any unauthorized users to physically access the servers.

3. Use a dedicated domain account for SQL Server services
For service accounts, use a dedicated user account or domain account having just enough privileges needed to do the job (principle of least privilege). By dedicated I mean the account should not be used by any other service on the same server. Also, use a separate account for each service so that if one service account gets compromised then only one instance associated with the service will be damaged.

4. Leverage Instant File Initialization
When SQL Server service is granted the right “Perform Volume Maintenance Tasks”, SQL Server will perform file allocation “instantly” by skipping zero initialization. Zero Initialization is the process of filling the contents of a file with zeros. Therefore, whenever a file allocation request is made, SQL Server will claim disk space by overwriting the disk content instead of filling it with zeros. This will save a lot of time during database creation, restore, auto-growth events etc.
The reason why this privilege is not granted by default is because the requested disk space might contain sensitive information which can be accessed using the (undocumented) DBCC PAGE command.

Note1:Only data files can leverage this feature as log files cannot be initialized instantaneously (due to architectural reasons).
Note2: Instant file initialization is available only on Microsoft Windows XP, Windows Server 2003, or later systems.

5. Use SQL Server Configuration Manager (SSCM) to change service accounts
If you ever need to change the service account for SQL Server service or SQL Server Agent service for that matter, use SQL Server Configuration Manager. Though you can also use Services (Administrative Tools->Services) to accomplish the same thing, the recommended method is to use SQL Server Configuration Manager because SQL Server Configuration Manager will automatically grant all the required permissions and rights to the new account. If you use the Services add-in to change service account, you might have to add some additional permissions and rights to the new account manually.

6 Divide tempdb into as many files as your processors
Split tempdb into multiple data files of equal size and these multiple files need not be on different disks/spindles (unless there are I/O bottlenecks). The number of data files should be equal to the number of processors (physical or virtual) on the machine. Since SQL Server allows only one active worker on each scheduler at any given time, all the active workers trying to access tempdb at the same time will be logically split to a separate file in tempdb decreasing contention.

However, if you have memory-intensive work loads, writing out of the spilled data can be slow due to thread synchronization. In this case, the number of data files could be ½ or ¼ of the number of CPUs.

7. Configure default database properties using model database
Use model database to set common properties (recovery model, initial file size, auto-growth etc) across all the databases on your SQL Server instance. For example, on a test server you might want to set the recovery model to simple so that all the new databases created on this instance inherit this property.

8. Use Widows authentication, if possible
Unless the SQL Server instance is being used for applications that can’t work with Windows, don’t go for Mixed Mode authentication. If you do, make sure you choose a strong SA password and enforce strong password policies for all SQL Server logins. In case of windows authentication, change the random password created for the ‘sa’ account after the installation is complete. Windows authentication is more flexible as you can setup your domain policies for changing user’s login and lockouts to apply automatically for SQL Server.

9. Use simple recovery model whenever appropriate
Recovery model needs to be business owner’s decision. Don’t be reluctant to use simple recovery model for your databases whenever suitable. It is totally fine to have a production database in simple recovery model as long as the users/business understand the implications. The benefit of having a database in simple recovery model (when appropriate) is that you don’t have to manage transaction logs as the inactive portion of the log will be cleared after check point. If the recovery model is set to full, be sure to schedule log backups. The frequency of which should be decided by business owners.

10. Allocate data/log files to a reasonable initial size and avoid using percentage for growth
Be sure that the database files and log files are large enough so that they will not automatically grow during normal daily operations (causing the users/processes to wait as it is a very slow process). Don’t use percentage for auto-growth, instead monitor and grow your files manually and use auto-growth only to deal with expected growth. Also, grow your databases by substantial amount, otherwise, you will end up with too many Virtual Log Files (VLFs) causing fragmentation.

11. T-SQL best practices

    i. Do NOT use sp_xxx as a naming convention as this causes additional searches and added I/O. SQL Server will scan the procedure cache for Master, no matter what database it was executed from. SQL Server will then acquire an exclusive COMPILE lock to perform a second search in the local database. If a user stored procedure has same name as an sp_xxx stored procedure in MASTER, that user procedure will NEVER be used.

    ii. Use SET NOCOUNT when creating Stored Procedures. SQL Server sends messages to the client after each T-SQL statement is executed. This option will turn off the messages that are sent back to the client.iii. Avoid using arithmetic operators/functions on WHERE clause column. This prevents optimizer from choosing index on the column.

    iv. Fully qualify Database objects – This will reduce name resolution overhead, might avoid execution plan recompiles.

    v. Operate on small result sets; don’t use “SELECT *”, and try to include a highly selective filter in the where clause if possible.

12. Use T-SQL for creating maintenance plans
Use T-SQL for maintenance plans as opposed to using the maintenance plan wizard/designer as T-SQL offers more granularity and flexibility. For example, using the wizard, there is no way to determine when to rebuild and when to reorganize an index. By using T-SQL, you can programmatically determine the level of fragmentation in an index and rebuild/reorganize the index based on the fragmentation. It may even be the case that you don’t have any fragmentation and so you don’t need to do anything. Also, don’t forget to include system databases in your maintenance plan. The system databases, master msdb and model should be backed up weekly or at least monthly.

May 25, 2010

Isolation Levels in SQL Server

An Isolation Level controls how and when the changes made by one transaction are visible to other concurrent transactions. The following are the types of isolation levels in SQL Server.

Read Uncommitted – This isolation level ignores both read and write locks on resources when reading, but honors locks while modifying data to prevent other transactions from modifying the same data. This level is the least restrictive of all the isolation levels and allows dirty reads1, phantom reads2 and non-repeatable reads3.

Read Committed – Read Committed is the default isolation level in SQL Server. This isolation level places a shared lock on resources when reading and only reads committed data. Please note that locks are not held for the duration of the entire transaction and so there are chances that data can change before the end of the transaction. Though it prevents dirty reads, it allows phantom reads and non-repeatable reads.

Repeatable Read – This isolation level places a shared lock on resources (when reading) during the entire transaction thereby preventing other transactions from modifying the same data. This lock prevents non-repeatable reads but allows phantom reads. For example, let us say that a new record was inserted by another transaction after the current transaction has completed its first read. This new record could get included in later reads of the current transaction causing phantom reads.  Thought this isolation level prevents dirty reads and non-repeatable reads, it allows phantom reads.

Serializable – Serializable is similar to Repeatable Read, but it places a Key Range Lock preventing other transactions from modifying or inserting new rows until the transaction is committed or rolled back. This is the least concurrent of all the isolation levels and is the default isolation level in SQL Server Integration Services. This isolation doesn’t prevents reads, phantom reads and non-repeatable reads.

Read Committed Snapshot (new since SQL Server 2005) – This is a variation of Read Committed isolation level which allows user to look at the data as it was during the beginning of the SELECT statement. To accomplish this, it implements row versioning by taking a copy of the row to be modified and storing it in tempdb. This means that shared locks are never placed on resource and read operations retrieve the recent committed copy of the data from version store (tempdb). In other words, readers do not block writers and writers do not block readers. This isolation doesn’t allow dirty reads, phantom reads or non-repeatable reads.

Snapshot Isolation (new since SQL Server 2005) – Snapshot Isolation is similar to Read Committed Snapshot, but it is based at the transaction level unlike Read Committed Snapshot which is based at the statement level. Like Read Committed Snapshot, this isolation doesn’t allow dirty reads, phantom reads or non-repeatable reads.

Please note that Isolation Levels only define how shared locks should behave, exclusive locks are not affected by changing isolation levels.

1. Dirty reads occur when a transaction reads data that is being modified by another transaction which is not yet committed. Since the changes have not been committed, there are chances that these changes might be rolled back and hence the reading operation is said to read “dirty” data.
2. Phantom reads occur when a transaction accesses a collection of rows more than once and another transaction inserts rows that happen to be in the range of rows retrieved by the first transaction causing first transaction to read more number of rows in future reads.
3. Non-repeatable reads occur when a transaction accesses a range of rows more than once and another transaction updates or deletes rows that fall in the range of rows retrieved by the first transaction so that the first transaction sees a different result set each time it retrieves data.

April 13, 2010

Achieving Greater Concurrency in SQL Server

TechNet Webcast: SQL Server 2005 Series (Part 3 of 10): Achieving Greater Concurrency (Level 200)

April 12, 2010

Mixed Workloads, Secondary Databases Wait States Locking and Isolation

Filed under: SQL Server webcasts — Tags: , , , , , , — Shahfaisal @ 8:50 am

MSDN Webcast: A Primer to Proper SQL Server Development (Part 06 of 10): Mixed Workloads, Secondary Databases Wait States Locking and Isolation (Level 200)

March 31, 2010

Error handling in SQL Server

Filed under: SQL Server Database — Tags: , , , , , , — Shahfaisal @ 10:41 pm

SQL Server provides the following functions/commands to help in troubleshooting Transact-SQL code:

@@ERROR function
The @@ERROR function is  used to capture the number of an error generated by the previous Transact-SQL statement. A value of 0 indicates success. Though this function is quite handy, it is a little tricky because its value gets updated for every command. So you will have to save the value of this function to a variable to make use of it in the code.

@@ROWCOUNT function
The @@ROWCOUNT function returns the number of rows affected by the last Transact-SQL statement. Like the@@ERROR function, the value of @@ROWCOUNT gets updated for every command but can be captured in variable for use in the code.

RAISERROR function
This function can be used to return custom error messages when troubleshooting errors in Transact-SQL code. Here is the syntax:

RAISERROR (msg_ id or msg_str, severity, state, optional arguments) WITH option

  • msg_id – A user-defined error message number stored in the sys.messages .
  • message – A user-defined error message.
  • severity – The severity level associated with the message. The severity code used you’ll use most of the time will be 16 indicating critical error. Please note that 11 is the minimum severity that will cause a CATCH block to fire.
  • state – error state
  • Argument – Parameters used in the substitution for variables defined in msg_str  or the message corresponding to msg_id
  • WITH option – There are three values that can be used with this option:
  • LOG – Logs error in the error log and the application log
  • NOWAIT – Sends the message immediately to the client
  • SETERROR – Sets the @@ERROR value to msg_id or 50000


USE [TestDB]
CREATE PROCEDURE [dbo].[UpdateEmployees]
@DepartmentID INT,
DECLARE @RecordCount INT
UPDATE dimEmployees SET DepartmentID = @DepartmentID WHERE EmpID = @EmpID
SELECT  @ErrorMsg = @@ERROR, @RecordCount = @@ROWCOUNT
IF (@ErrorMsg <> 0 OR @RecordCount = 0)
RAISERROR (‘An error occured when updating the dimEmployees table’,16,1)

Try Catch technique consists of a Try block which contains the code that might cause an exception and a Catch block where the control will be transferred if an error is encountered in the Try block. If there are statements in the TRY block that follow the  statement that caused the error, they will not be executed.

{ SQL statements block }
{ Error Handling Code }


PRINT ‘Statement after error’
PRINT ‘Divide by zero error’

And here is the output:
Divide by zero error

Older Posts »

Blog at