SQL Server Collation

Collation determines the rules for sorting / comparing the string characters based on a specific language / locale. For example, depending on your collation, the "ORDER BY" clause may return different results. Default collation in SQL Server is Latin1_General which would sort "Children" before "College" when you run an ORDER BY ASC clause. If your database collation however was "Traditional_Spanish", the sort order will be College before Children because in Spanish "Ch" will be treated as one word and will come after all words beginning with "C".
A specific code page is associated with non-Unicode characters such as char, varchar, text. For example, Latin1_General collation Char is interpreted by SQL Server using 1252 code page. Multiple collations may use the same code page. Unicode data such nchar, nvarchar, ntext doesn't use code pages to handle the data interpretation.

In addition to using SQL Server Collations, you can also use Windows Collations. When using Windows Collation, SQL Server will use collation of the windows OS it is running on to determine the sorting of the characters. To know more about Windows and SQL Server Collations, checkout the following MSDN article - http://msdn.microsoft.com/en-us/library/ms175194.aspx

Determining which Collation to Use
If your application is going to be used where all users speak the same language, you should use the collation that supports the language of your user base. If however your users may speak different languages, you should pick the best available collation that would support most of the languages. For example, Latin1_General collation will support western European languages. Alternatively,you can use Unicode data-types such as nchar, nvarchar, ntext (we will discuss implications of using Unicode data-types in future post). Even though Unicode data-types don't use code pages, it is good practice to pick the collation for the language(s) used by majority of your users in case a column or a variable is declared as a non-Unicode data-type.

Collation can be specified at the Server level, Database level, Column, Parameters or variable level. When you install SQL Server, you can specify a collation, which will be the default collation for all the lower level objects i.e. Database, Column etc. You can also change the collation at each level.

Database level Collation
You can specify database collation when creating a database either via management studio or via T-SQL. In Management Studio, when you are creating a database, go to options and pick the collation from the drop-down list. To specify collation using T-SQL you can use a script like this one...

USE master;
GO
CREATE DATABASE MyCollationTest
COLLATE French_CI_AI;
GO
Verify collation
SELECT name, collation_name
FROM sys.databases
WHERE name = N'MyCollationTest';
GO

You can also change the Collation of a database after it has been created. Before you change the collation of an existing database, make sure you are connected to the database in a Single User mode. Also, if any Schema-bound objects such as UDFs, Computed Columns etc. depend on current collation, SQL Server will generate an error. You can change use the following script...

USE master;
GO

ALTER DATABASE MyCollationTest
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

Column level Collation
You can specify a different collation for char, nchar, varchar, nvarchar, text or ntext columns. When creating a new table or modifying an existing table via Management Studio, you can specify the collation in column properties section. Alternatively you can use T-SQL like this...

CREATE TABLE MyTable
  (ID   int PRIMARY KEY,
   Name      varchar(50) COLLATE French_CI_AS NOT NULL
  )
GO

OR

ALTER TABLE MyTable ALTER COLUMN Name
            varchar(50)COLLATE Latin1_General_CI_AS NOT NULL
GO

You cannot alter the collation for a computed column, an indexed column, or a column is used as a foreign key, has a check constraint or is part of the statistics statement.

You can also specify which collation to use in ORDER BY clause of your query. For example...

USE AdventureWorks2008R2;
GO
SELECT LastName FROM Person.Person
ORDER BY LastName
COLLATE Traditional_Spanish_ci_ai ASC;
GO

For the most part default collation works and if you always use Unicode data types, then you don't need to specify collation, but different collation types exist, if you need them.

Thank you.

Using CPU Resource Governor for SQL Backups

As we discussed in previous post, while compressing data during backup operations will result in efficient disk I/O operations, it will significantly increase CPU usage which could impede other operations.

You can set a lower priority backup operation limiting CPU usage by using resource governor. In this post we will describe how to configure backup operation as a low priority operation.

There are several steps you have to complete to setup a resource governor.

1. Setup a SQL Server login you would use to perform this operation. Alternatively, you can use an existing user.

For example, you can run the following query to create a windows user, grant permission and assign the role of backup operator to the AdventureWorks database.

-- Create a new login
USE master;
CREATE LOGIN [<Your Domain>\ResourceGovernor] FROM WINDOWS;
GRANT VIEW SERVER STATE TO [<Your Domain>\ResourceGovernor];
GO
-- add this user to AdventureWorks and assign him/her to DB Operator rule
USE AdventureWorks;
CREATE USER [<Your Domain>\ResourceGOvernor] FOR LOGIN [<Your Domain>\ResourceGovernor];
EXEC sp_addrolemember 'db_backupoperator', '<Your Domain>\ResourceGovernor';
GO

2. Create a Resource Governor resource pool which will limit the maximum average CPU bandwidth that will be given to this resource pool. You can create a resource pool governor using the following command.

CREATE RESOURCE POOL CPUGovernor;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

It will create a resource pool called CPUGovernor with default settings. You can also pass additional parameters such as...

MIN_CPU_PERCENT - The minimum % of CPU resource to limit for this pool.
MAX_CPU_PERCENT - The maximum % of CPU resource to limit for this pool.
MIN_MEMORY_PERCENT - The minimum % of memory to limit for this pool.  MAX_MEMORY_PERCENT - The maximum % of memory resource to limit for this pool.

CREATE RESOURCE POOL CPUGovernor
 WITH
    (  MIN_CPU_PERCENT = 2,
       MAX_CPU_PERCENT = 10,
       MIN_MEMORY_PERCENT = 10,
       MAX_MEMORY_PERCENT = 20
    )

3. Create a Resource Governor workload group that will use this pool.

CREATE WORKLOAD GROUP NewWorkLoadGroup
    USING "default" ;
GO

This will create a new workload group with default values. Alternatively, you can pass certain parameters.

CREATE WORKLOAD GROUP NewWorkLoadGroup
WITH
    ( IMPORTANCE = { LOW | MEDIUM | HIGH },
      REQUEST_MAX_MEMORY_GRANT_PERCENT=10, --max amount of memory a single request can take
      REQUEST_MAX_CPU_TIME_SEC=10, --max CPU time a request can take
      REQUEST_MEMORY_GRANT_TIMEOUT_SEC=10, --max time a query can wait for memory 
      MAX_DOP = 1 -- specifies maximum degree of parallelism
      GROUP_MAX_REQUESTS = 2 -- maximum # of simultaneous requests that can execute)
[ USING { pool_name | "CPUGovernor" } ] -- using above defined resource pool

4. Create a user defined classifier function which will relate the user you created in step 1 with the workload you created in step 3.

CREATE FUNCTION func_ResourceGovernor() RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

DECLARE @workloadGroup AS sysname

IF (SUSER_NAME() = '<Your Domain>\ResourceGovernor') -- SUSER_NAME() is a system function

SET @workloadGroup = 'NewWorkLoadGroup'

RETURN @workloadGroup

END

5. Alter Resource Governor to configure it with classifier function.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = func_ResourceGovernor);

6. Issue a second reconfigure command to apply the changes

ALTER RESOURCE GOVERNOR RECONFIGURE;

Let's put all this together in one script.

-- Configure Resource Governor.
BEGIN TRAN
USE master;
-- Create a resource pool that sets the MAX_CPU_PERCENT to 10%. 
CREATE RESOURCE POOL CPUGovernor
   WITH
      (MAX_CPU_PERCENT = 10);
GO
-- Create a workload group to use this pool. 
CREATE WORKLOAD GROUP NewWorkLoadGroup
USING CPUGovernor;
GO
-- Create a classification function.

CREATE FUNCTION dbo.func_ResourceGovernor() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workloadGroup AS sysname
      IF (SUSER_NAME() = '\ResourceGovernor')
          SET @workloadGroup = 'NewWorkLoadGroup'
    RETURN @workloadGroup 
END;
GO

-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.func_ResourceGovernor);
COMMIT TRAN;
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

I realize this is a tedious process, but it could be very useful when your server is experiencing a heavy workload and you cannot schedule your backups to run during low usage.

Thank you.

SQL Server Statistics

When you run a SQL Query, SQL Engine has to choose the best path to execute that query. It can opt for a table scan, i.e. scanning entire table to determine the result set. It can look at the indexes to determine the best way to retrieve the data or it can use statistics information it collects to execute the query. The goal is to minimize the query execution time for faster data retrieval and minimal data locking.

Statistics allow SQL Server to keep information about the number of records in a table, page density, histogram and any available indexes to determine the best path to execute a requested query.

Starting from SQL Server 2000, all versions have ways to collect necessary information and create / update statistics, provided this feature is on (it is on by default) and for the most part you don't have to do anything.

You can also manually create / update statistics.

Implicit Statistics Creation and Update
When automatic statistics creation and update is enabled (default is on), anytime you execute a query with a WHERE or JOIN clause with a condition column, the statistics is automatically updated or created if necessary.

Manually Create and Update Statistics
You can also manually create / drop / update statistics with either the default sampling rate or your own desired sampling rate.

Let's use an example to check when the statistics is automatically created and then manually create statistics.

--Create a new Table in Temp database

USE TempDB
GO
CREATE TABLE Customers
(
   CustomerID INT IDENTITY,
   FirstName varchar(50),
   LastName varchar(50),
   EmailAddress varchar(255),
   PhoneNumber varchar(15)
)
GO

--Let's insert some records
INSERT INTO Customers(FirstName,LastName,EmailAddress,PhoneNumber)
VALUES('Jane','Doe','janed@email.com','404-111-1111')

INSERT INTO Customers(FirstName,LastName,EmailAddress,PhoneNumber)
VALUES('John','Doe','johnd@email.com','404-111-1111')

INSERT INTO Customers(FirstName,LastName,EmailAddress,PhoneNumber)
VALUES('Sara','Lee','saral@email.com','404-222-2222')

INSERT INTO Customers(FirstName,LastName,EmailAddress,PhoneNumber)
VALUES('Chris','Smith','csmith@email.com','404-333-3333')

INSERT INTO Customers(FirstName,LastName,EmailAddress,PhoneNumber)
VALUES('Shania','Rogers','sr@email.com','404-444-4444')

GO

--now lets check if there is any statistics on this table
sp_helpstats N'Customers', 'ALL'
GO

--Following message is displayed
--This object does not have any statistics or indexes.

--Now run a query
SELECT * FROM Customers WHERE LastName='Lee'
GO
--check statistics again
sp_helpstats N'Customers', 'ALL'
GO

--following message is displayed


statistics_name
statistics_keys
_WA_Sys_00000003_0CBAE877
LastName

--If you create an index on this table, it will also automatically create statistics.

CREATE NONCLUSTERED INDEX ix_Email ON Customers(EmailAddress)
GO

--check statistics again
sp_helpstats N'Customers', 'ALL'
GO

--Query Analyzer will now display following message



statistics_name
statistics_keys
_WA_Sys_00000003_0CBAE877
LastName
ix_Email
EmailAddress

--Let's create an statistic manually

CREATE STATISTICS stat_Name ON Customers(FirstName,LastName) -- multicolumn
GO

--check statistics again
sp_helpstats N'Customers', 'ALL'
GO

--Query Analyzer will now display 3 statistics



statistics_name
statistics_keys
_WA_Sys_00000003_0CBAE877
LastName
ix_Email
EmailAddress
stat_Name
FirstName, LastName

--If you want to know what columns are part of statistics and also the range as well density, 
--you can run the following DBCC command.

DBCC SHOW_STATISTICS (N'Customers', ix_Email)
GO

--You can also also define a sample size when creating a script. 
--For example, in the above create statistics you can 
--also pass the same size.

CREATE STATISTICS stat_Name ON Contact(FirstName,LastName) WITH SAMPLE 75 PERCENT
GO

--While a larger sample size is better, because it will result 
--in faster query execution, but large sample also means statistics 
--creation will take longer because engine has to scan the table more.

--To drop a statistic, you have to provide object name.statistics name

DROP STATISTICS  Customers.stat_Name
GO

--As I mentioned previously, by default SQL Server 
--automatically creates and updates statistics. 
--You can turn auto scanning off or on by running the following query

ALTER DATABSE <yourDBName> SET AUTO_CREATE_STATISTICS OFF
GO
ALTER DATABSE <yourDBName> SET AUTO_CREATE_STATISTICS ON
GO

There have been several improvements in this area in SQL Server 2008 and you rarely have to do anything with them, but choices are available should you need them.

Thank you.

Using Cursors in SQL Server

Cursors are generally not recommended and are slower compared to other methods of reading data. But there are situations where you must use cursors. I have found them to be useful when I am trying to run a SELECT on a table in Query Analyzer and performance is not a concern.

For example, consider a Stores table that has one to many relationship with another table called StoreHours. The store closes at different time depending on weekday. Also stores close at different times in different regions. You need to run a query to determine the latest closing time for each store. You can easily achieve this by running a cursor.

As I mentioned earlier, cursors are rather slow, but you can increase their performance using FAST_FORWARD and READ_ONLY hints. Also remember to always deallocate and close cursor when done.

Let's use the cursor to select latest closing time for each store, insert them into a temporary table and then select the records from the temp table.

DECLARE @tmpStoreClosing(StoreID INT, ClosingTime DateTime)
DECLARE @StoreID INT
DECLARE curStore CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT StoreID FROM Stores
OPEN curStore
FETCH NEXT FROM curStore INTO @StoreID
WHILE @@FETCH_STATUS=0
    BEGIN
         INSERT INTO @tmpStoreClosing(StoreID,ClosingTime)
         SELECT TOP 1 @StoreID,ClosingTime FROM StoreHours 
         WHERE StoreID=@StoreID ORDER BY ClosingTime DESC
         FETCH NEXT FROM curStore INTO @StoreID
    END
CLOSE curStore
DEALLOCATE curStore

SELECT StoreID,ClosingTime FROM @tmpStoreClosing

You can also improve the performance using INSENSITIVE or STATIC keyword. When this keyword is used, cursor copies the data into tempDB and runs the select from there. The drawback of this approach is that any changes made to the data are not reflected.

FAST_FORWARD is same as FORWARD_ONLY and READ_ONLY combined. Data can only be read and in forward only manner. When using this flag, you can only use FETCH NEXT.

There are ways to achieve the same thing using a WHILE loop, which doesn't have the drawbacks of a cursor. We will discuss using a WHILE loop to achieve the same objective in future post.

Cursors should be avoided and if used they should be used primarily for reading the data in forward only manner.

Thank you.

SQL Server Locking Hints

                                                                SQL Server Locks - Part I, Part II, Part III, Part IV

We have been discussing locking mechanism in SQL Server since last three posts. In previous posts we covered locks, deadlocks and isolation levels. Today we will discuss ways to control how SQL Server locks the data by passing locking hints.

Generally leaving SQL Server engine to handle locks works as expected, but there are circumstances where you may want to use locking hints to lock or not lock certain transaction.

Locking Hints

• HOLDLOCK- When you pass this hint to your select queries, shared locks are held until the entire transaction completes instead of releasing the lock as soon as the read is complete and the required lock (table, row or page) is no longer needed. HOLDLOCK is same as SERIALIZABLE isolation level.
• NOLOCK - When you pass this hint, SQL doesn't issue shared locks for reading data and doesn't honor exclusive locks. In other words, if there is an exclusive lock on row, page or table, query with NOLOCK hint will still execute instead of waiting for exclusive lock to be released. With this hint, dirty reads are possible. This hint only applies to SELECT statements.
• PAGLOCK - Forces SQL Server to issue page lock instead of a default table lock that may be taken.
• READCOMMITTED -Same as READ COMMITTED isolation level, i.e. only read committed data.
• READPAST - When reading data, if a row is exclusively locked, read past that row, i.e. any locked rows are skipped. Beware, your results may not have all the data if you use this hint. Also, you can only use this hint if isolation level is set at READ COMMITTED. It also only applies to SELECT.
• READUNCOMMITTED - Same as NOLOCK hint.
• REPEATABLEREAD - Same as REPEATABLE READ isolation level, i.e. once a shared lock is acquired, it isn't released until the entire transaction completes. 
• ROWLOCK - Forces SQL to issue row level locks instead of page and table level locks.
• SERIALIZABLE - Same as SERIALIZABLE isolation level and same as HOLDLOCK. All transactions are blocked until the current transaction completes.
• TABLOCK - Forces SQL to issue a table lock instead of row or page level lock. This lock is only held until the executing statement completes. However if you are running under SERIALIZABLE isolation level or also pass HOLDLOCK, the lock will be held until the end of the transaction.
• TABLOCKX - Issues an exclusive lock on the table, preventing others from reading or updating the table. 
• UPDLOCK - This is a special lock used when you are updating data. When you are updating data, data must be first read and then updated. Ordinarily when data is read, a shared lock is issued which will then be converted to exclusive lock to update the data. But what if a transaction acquires a shared lock and another transaction acquires an exclusive lock on the same data at the same time? The data just read could get out of sync. To prevent this, SQL Server issues an update lock instead of shared lock. This allows other transactions to read the data while this transaction with UPDLOCK is reading the data but no other transaction can obtain an exclusive lock on this data. The UPDLOCK is then converted to exclusive lock to update the data. This ensures data integrity since you can be sure that the data hasn't been updated since this transaction read it.
• XLOCK - A exclusive lock which will be held until the end of the transaction. You can specify it with PAGLOCK or TABLOCK, indicating whether to exclusively lock page or a table.

Let's see a lock in action...

SELECT FirstName, LastName FROM CUSTOMERS WITH (NOLOCK)

SELECT FirstName,LastName FROM CUSTOMERS WITH (ROWLOCK)

Hope this helps you understand how you can use various hints available to you. This was a long series but hopefully it will be a good refresher to understand how locking mechanism works in SQL Server.

Thank you.

SQL Server Isolation Levels

                                                                    SQL Server Locks - Part I, Part II, Part III, Part IV

In previous two posts we have been discussing SQL Server locking mechanism and how deadlocks can occur. Today we will talk about Isolation Levels. Different Isolation levels allow for various levels of concurrent operations that can be performed on the same data.

Isolation is the level to which one operation / transaction should be isolated from the other operation.

Serialization is an important concept in database management systems. The serialization allows concurrent transactions to run one after another serially. For example, if one transaction is running a read operation on a set of data, and another concurrent operation is running an update on the same data, both operations will be serialized in order the requests were received by the database management engine. For example, if read operation was requested first, a shared lock will be issued to this transaction and any other update/delete operations will be blocked or queued. Once the read operation is complete, an update lock will be issued to the update operation next in line which will read the data and then receive an exclusive lock to update the data.

But what if you don't really need this level of isolation? For example, you want to read the data even though the data may be exclusively locked by another transaction? You can achieve this by using a lower level transaction or by using locking hints (we will discuss locking hints in future post).

There are following types of isolation levels according to SQL-92 specifications.

Read Uncommitted - This is the lowest level of isolation. In this level, transactions are isolated only enough to ensure that corrupt data is not read. This level allows dirty reads, i.e. the data not yet committed.

Read Committed - This is the default level. Only committed data will be read. When a transaction has an exclusive lock, all other transactions requesting shared lock will be blocked

Repeatable Read - Repeatable read locks all the rows that it touches unlike read committed. Let's explain this with an example.

Let's say you are running a cursor where you are fetching one row at a time and then adding them to a temporary table (see below)

    DECLARE @tmpTable TABLE (FirstName varchar(50), LastName varchar(50),
                                                            Address varchar(255)

    DECLARE @customerID INT

    DECLARE CURSOR curCustomer READ_ONLY FOR

    SELECT CustomerID FROM Customers

    OPEN curCustomer

    FETCH NEXT FROM curCustomer INTO @customerID

    WHILE @@FETCH_STATUS=0

          BEGIN

              INSERT INTO @tmpTable(FirstName,LastName,Address)

              SELECT FirstName,LastName,Address FROM Customers
              WHERE   CustomerID=@customerID

              FETCH NEXT FROM curCustomer INTO @customerID

          END

    CLOSE curCustomer

    DEALLOCATE curCustomer

If you run this transaction at read committed level, only one row will be locked for reading at a time and then the lock will be released. Imagine you read the first row where address was "123, Roswell Road, Atlanta, GA, 30330" and insert this row in tmpTable. Now you lock the second row to read and insert into @tmpTable. Now imagine another concurrent transaction locked the first row and updated the address to "567, Peachtree Road, Atlanta, GA 30101". The record in your temp table is now incorrect.

If you run this transaction under repeatable read, this isolation level will lock the 1st row and then the second row and then the third row and so on, but will not release any lock until the entire transaction is complete. This ensures that the data read cannot be updated while the transaction is still running. While this promotes better data integrity, concurrency suffers because all other transactions must now wait for the resources to be released, resulting in timeouts.

Although repeatable read prevents data integrity, phantom inserts/updates/deletes are still possible. If one transaction reads the data more than once, then the rows are locked as they are read first time and will then be released. But, before the data is read second time, another transaction may insert a new row or update/delete rows that were read previously, resulting in phantom data.  

Serialization Level - Serialization level works by using key range locks. Using the same example as above, when this isolation is read, entire key range (in this case entire table since we are reading all records in customer table) will be locked until the entire transaction completes, which will prevent phantom rows since the other transactions must wait until this transaction ends. Serialization is the highest isolation level and while it promotes data integrity, it reduces concurrency. To know more about key range locks, refer back to my post about SQL Server Locks.

In addition to these isolation levels, SQL Server supports two additional isolation levels.

READ_COMMITTED_SNAPSHOT - This isolation level uses row versioning instead of shared lock when reading the data. Read operations only require SCH-S table level locks (i.e. to prevent DDL modifications) but no page or row level locks are needed. When reading rows modifed by another transaction, only the version of the row that existed when the transaction started is read. This only works when another isolation level "ALLOW_SNAPSHOT_ISOLATION" option is ON. 

You can set isolation level by using the following syntax...

    SET TRANSACTION ISOLATION LEVEL

    {
        READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ
        | SNAPSHOT |  SERIALIZABLE

     }

For Example,

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    GO

    DECLARE @tmpTable TABLE (FirstName varchar(50), LastName varchar(50),
                                                           Address varchar(255)

    DECLARE @customerID INT

    DECLARE CURSOR curCustomer READ_ONLY FOR

    SELECT CustomerID FROM Customers

    OPEN curCustomer

    FETCH NEXT FROM curCustomer INTO @customerID

    WHILE @@FETCH_STATUS=0

          BEGIN

              INSERT INTO @tmpTable(FirstName,LastName,Address)

              SELECT FirstName,LastName,Address FROM Customers
              WHERE CustomerID=@customerID

              FETCH NEXT FROM curCustomer INTO @customerID

          END

    CLOSE curCustomer

    DEALLOCATE curCustomer

    GO

You can set ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT option at the database level by using the following script...

    ALTER DATABASE <DBName>

    SET

    {
      ALLOW SNAPSHOT_ISOLATION {ON | OFF}

       | READ_COMMITTED_SNAPSHOT {ON | OFF}

    }

Hopefully this post will help you determine the best isolation environment if you must change it. Although, SQL Server defaults work in most cases.

Thank you!

SQL Server Deadlocks

                                                                   SQL Server Locks - Part I, Part II, Part III, Part IV

Deadlocks
In previous post we discussed locking mechanism in SQL Server and what are different types of locks and how you can control default locking mechanism by using various hints with your queries.

Today, we will discuss deadlocks. Locking is a necessity for any DBMS to work in a multi-user environment and to maintain data consistency. Deadlocks occur when two transactions are waiting on each other to release the lock. Eventually SQL Server chooses one of the transaction as victim and kills it.

Let's explain this using an example -

Suppose you have a Customers table and you run the following queries in two separate transactions.

Transaction A
--statement 1
SELECT * FROM Customers WHERE Customer ID = 1
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 2

Transaction B
--statement 1
SELECT * FROM Customers WHERE Customer ID = 2
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 1

Let's assume one user calls a routine that executes transaction A and another use calls a routine that executes transaction B concurrently.

Transaction A will issue a shared lock while executing statement 1 on row 1 and transaction 2 will issue a shared lock while executing statement 1 on row 2.

Now  transaction  A will request an exclusive lock on row 2, while transaction B will request an exclusive lock on row 1.

Transaction A cannot complete because it is waiting on transaction B while transaction B cannot complete because it waiting on transaction A.

This condition is also called cyclic dependency. SQL Server engine deadlock monitor periodically checks for cyclic dependency and when it detects a dependency, it chooses one of the transaction as a victim and terminates it. The transaction chosen as a deadlock victim will terminate but the other transaction will complete successfully.

Remember, deadlocking is different than blocking. Blocking occurs when a transaction requests a lock on a resource which is exclusively locked by another transaction, in that case the requesting transaction will wait for the lock to be released.

Minimizing Deadlocks
Deadlocks are a fact of life, but you can minimize them by following some basic steps...

• Objects should be accessed in the same order
• All concurrent transactions should access the objects in the same order. For example, If there are two tables - Orders and Order Details, all concurrent transactions should request locks in the same order - For example first on Orders table and then on Order Details table. If all lock requests flow in the same direction, the subsequent requests will be blocked until a lock is released from the previous request, but deadlock should not occur.
• Avoid prompting for User input when a transaction is running
• A transaction should be completed as fast as possible so that the locks are acquired and released as quickly as possible. Requiring user input delays the transaction, hence resources will be locked for longer period of time.
• Keep Transactions Short and in one batch
• Keeping transactions short and in one batch will minimize resources, resulting in faster transaction completion.
• Use a lower Isolation Level
• We will discuss isolation levels in more detail in subsequent post but lower the isolation level, less locking. For example if you implement read committed isolation level, it allows a transaction to read data previously read by another transaction without waiting for that transaction to complete.
• Set READ_COMMITTED_SNAPSHOT to ON
• When this database option is ON, a transaction that is running under read committed isolation level uses row versioning instead of shared lock during read operations. Row versioning does not acquire shared lock for read operations. You must also set ALLOW_SNAPSHOT_ISOLATION to ON in addition to read committed snapshot for it to work.
• Use bound connections
• If your application opens two or more connections, you can bound them together such that one is a primary connection while other is a secondary connection. Under this scenario, any locks that are acquired by the primary connection are treated as they are also acquired by the secondary connection and vice versa.

This concludes our topic for today. There are some other aspects of locking such as providing locking hints with your queries or setting up different isolation levels. We will discuss them in subsequent posts.

Thank you.

SQL Server Transactions

Transactions in SQL Server allows you to batch multiple inserts/updates/deletes in one single process and an error in one sub-step will invalidate or roll back the entire transaction. This ensures the data integrity and atomicity.

Let's assume for example, you have a set of three tables in a SQL database

• A Employee Profile Table
• An Earnings table recording the salary, taxes paid etc.
• A Tax rate table that records an employee's tax rate.

If the tax rate changes, you would want to update the TaxRate table first, then update the Earnings table to reflect the taxes paid (based on new tax rate). For example, if the earnings were $100,000 and the tax rate was 20%, the taxes paid in the Earnings table would be $20,000. Now, if the tax rate jumps to 25%, TaxRate table would be updated to reflect tax rate of 25% and the Earnings table would be reflected to show the updated tax of $25,000.

Now lets assume you are performing these operations as individual updates without wrapping them in one transaction.

      --1st you update tax rate table

       UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1

       GO

      -- now you update taxes paid 

      UPDATE Earnings SET TaxesPaid = 

      (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

      WHERE EmployeeID=1

      GO

Now, lets imagine first update executed successfully but a deadlock or some other error occurred before second update could be performed (more on deadlocks in future posts), resulting in out of sync data. TaxRate table will show the tax rate of 25% but Earnings table will show only $20,000 taxes paid.

You want to ensure that either both tables are updated together or none at all. You can wrap both updates in one transaction, and if an error occurred, entire transaction will rollback, ensuring the data integrity.

Here is how you would wrap the above updates in a transaction.

DECLARE @errorcode INT

BEGIN TRAN

    --1st you update tax rate table

       UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1

       GO

       -- check if an error occurred

       If @@Error <> 0 GO TO ERROR  -- @@Error would trap any error that may occur

      -- now you update taxes paid 

      UPDATE Earnings SET TaxesPaid =

          (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

          WHERE EmployeeID=1

      GO

     -- check again if an error occurred

       If @@Error <> 0 GO TO ERROR

COMMIT TRAN

ERROR:    --- GO TO ERROR statement will jump here anything an error occurred.

PRINT ('An error occurred')

ROLLBACK TRAN

This allows you to declare transaction at the SQL Script level - in a stored procedure for example. You can achieve the same effect by using ADO.NET Transaction or TransactionScope object in code when executing two or more separate stored procedures or in-line SQL.

Using Transaction

public void updateTaxInformation (string connectstring)

{

   using (SqlConnection conn =  new SqlConnection(connectstring))

{

      conn.Open();

      SqlCommand cmd = connection.CreateCommand();    // create sql command

      SqlTransaction trans;   // create a new sql transcation

      // start the transaction

      trans = conn.BeginTransaction("TransName");

     //assign connection and transaction to the command

     cmd.Connection = conn;

     cmd.Transaction = trans;

     try

     {

          cmd.commandText = "UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          //second update

          cmd.commandText = "UPDATE Earnings SET TaxesPaid =

                                             (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

                                             WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          // commit transaction

         trans.commit();

      }

    catch (Exception ex)

    {

        Console.WriteLine("Exception Occurred: " + ex.Message);

        trans.Rollback();

     }

  }

}

Using TransactionScope

Beginning in .NET 2.0, Microsoft introduced TransactionScope which basically does the same thing as above, except a few subtle differences. TransactionScope simplifies some of the code and takes care of enrolling SQL commands into a transaction autmatically. The above code can be simplified using TransactionScope 

Using (TransactionScope transscope = New TransactionScope())

  {

     using (SqlConnection conn =  new SqlConnection(connectionstring))

       {

          conn.Open();

          SqlCommand cmd = connection.CreateCommand();    // create sql command

          cmd.commandText = "UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          //second update

          cmd.commandText = "UPDATE Earnings SET TaxesPaid =

                                             (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

                                             WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          transscope.Complete();

         }

    }

       

That's all there is to it. While a transaction has its advantages such as maintaining data integrity, you have to be careful when creating a transaction. Large transactions could result in dreaded deadlocks. We will discuss deadlocks in future posts.

Thank you and your comments are welcome.

Clustered vs. Non-Clustered Index

The other day someone asked me a question about the difference between clustered and non-clustered indexes in SQL Server and why can you only have one clustered index in a table? It is one of those things that you know but often find it difficult to explain in a non-technical manner, at least that's how I felt. So, what is the difference and why can you only have one clustered index?

Clustered Index: Clustered index sort the data physically in the data table according to the keys in the clustered index. Basically all the data rows are physically sorted according to the clustered index. Since you can only sort data rows one way, there can only be one clustered index. The lowest level of clustered index are the data rows themselves. If you have a primary key defined in the table, then SQL Server automatically creates a clustered index on the primary key.
You don't have to have clustered index and the table without a clustered index stores the data on a heap. Data inserted in this table will return in the same order it was inserted in, unless you apply ORDER BY clause to your query which is SLOW!!!

Non-Clustered Index: A Non-Clustered index on the other hand is stored independently of the data rows' physical order. Think of this as a logical order of the data rows and hence there can be more than one non-clustered index. Non-clustered index's lowest level contains the keys used in the index and are a pointer to the actual data rows containing that key. This pointer is called a row location and its structure depend on whether the table has a clustered index or not. As mentioned earlier, if a table does not have a clustered index, the data rows are stored in a heap and in this case the row locator is simply a pointer to the row. For tables that do have a clustered index, the row locator is the key of the clustered index.

Both clustered and non-clustered indexes can be unique. Primary key by default is a unique index whether it is defined as a clustered or non-clustered index. Unique indexes ensure that no two rows can contain the same keys used in the unique index.

IGNORE_DUP_KEY: When creating a unique index, you can also use "IGNORE_DUP_KEY" hint. When a duplicate key is inserted, if IGNORE_DUP_KEY was specified, SQL will issue a warning and ignore the duplicate row, but if it was not specified, SQL server will issue an error message and roll back the entire insert. IGNORE_DUP_KEY is only allowed when you create a UNIQUE clustered or a non-clustered index.

FILL FACTOR: A Fill Factor defines how densely the index will be packed when it is created. If you are going to have many inserts and updates, you should use a lower fill factor to leave room for new data, which reduces the number of page splits that may occur when data is inserted. The space doesn't change when data is inserted or updated, only when index is created, so it is recommended to rebuild indexes periodically. If the table is rather static, create an index with a high fill-factor.

Indexes generally speed up the retrieval, update and deletion but often slow down inserts because every new record will have to be added to the index. Care should be taken in creating the number of indexes on a table, especially when data is frequently inserted.

While you can create some indexes during database design, often time it is not possible to know the table usage until it is out in the field. Once the database is out in the field and you want to optimize certain heavily used tables, you can do the following

• In SQL Management Studio (SQL 2005 or SQL 2008) go to Tools > SQL Profiler and run the trace for the tables that are heavily used.
• Collect some metrics and save the file.
• Go to Tools > Database Engine Tuning Advisor and load the workload file, select the database and start analysis.
• Review the recommendations and apply them to the appropriate tables.

I have generally found the tuning advisor to be a good tool, but be careful in applying all its recommendations. As always, do this in staging environment first, before applying to your production database.

As always, your comments are welcome and feel free to correct me if I made any mistake!

Creating Indexes using Management Studio

In my previous two posts, I wrote about the indexes in general and why you should create them. In this blog, I will walk through creating an index using Visual Studio Management.

Note: I have used SQL 2008 Management Studio, but the process is more or less same in SQL 2005.

Using SQL Management Studio

1. Open Management Studio and expand the database > table you want to index.

2. Select the appropriate name. I generally add a prefix to the name "idx_". Also select whether the index is a clustered or non-clustered index. (Remember, only one clustered index is allowed in a table). Check Unique checkbox if you want the index to not allow duplicate keys.

3. Click on "Add" and select the columns you want to index. As we discussed in previous post, CHAR/VARCHAR are not good candidate keys, unless you have to use them. The order is also very important. The key which will have most duplicate data should be ranked first.

4. Go to Options and adjust the settings as needed. Defaults are generally OK here but you may want to adjust fill factor. See my previous post to learn more about fill factor.

That's all there is to it. Click on OK and you are done!

As always, feel free to leave me comments!

Thank you