SQL Azure connection termination causes.

Microsoft SQL Azure database is a clud based relational database service that's hosted and maintained by Microsoft. Multiple databases from different customers are hosted on the same server. To garantuee performance for all these different customers, the SQL server should not come in an overload state. SQL Azure has the ability to monitor and rebalance active and online user databases. To achieve this, SQL Azure continuously gathers and analyze database usage statistics and will terminate connections when necessary. Throttling is a mechanism used by SQL Azure to prevent machines from becoming overloaded and unresponsive. Throttling can be divided in:

1. Soft throttling. This applies to the database consuming the most resources on the box. Soft throttling happens when a physical machine seems to be on the way of beig overloaded, unless its workload is reduced.
2. Hard throttling. This is the final stage of throttling. It happens when the machine is critically impacted due to overload. It terminates existing operations and prevents new ones until the metric returns below expected thershold.

This blog will summarize all connection terminations which can happen:

• Worker threads. When soft throttling limit for worker threads on a machine is exceeded, the database with the highest requests per second is throttled. Existing connections to that database are terminated and new connections to the database are denied, until number of workers drops below soft limit
• Database size. When the database space allocatted to user db is full, the user gets a db full error.
• Physical database space. When total database size on a machine exceeds 90% of total space available on machine, all databases become read-only. Load balancer ensures the situation is resolved by balancing databases across machines.
• Log bytes used. SQL Azure supports transactions generating log of up to 2 GB in size.
• Transaction log length. Uncommitted transactions can block the truncation of log files. To prevent this, the distance from the oldest active transaction log sequence number (LSN) to the tail of the log (current LSN) cannot exceed 20% of the size of the log file. When violated, the offending transaction is terminated and rolled back so that the log can be truncated.
• Transaction Lock Count. Sessions consuming greater than one million locks are terminated.
• Blocking System tasks. Transactions request locks on resources like rows, pages, or tables, on which the transaction is dependent and then free the locks when they no longer have a dependency on the locked resources. Due to these locks, some transactions might block resources required by system sessions. If a transaction locks a resource required by an underlying system operation for more than 20 seconds, it is terminated. In addition, any transaction that runs for more than 24 hours is terminated.
• Tempdb usage. When a session uses more than 5 GB of tempdb space, the session is terminated.
• Memory use. When there are sessions waiting on memory for 20 seconds or more, sessions consuming greater than 16 MB for more than 20 seconds are terminated in the descending order of time the resource has been held, so that the oldest session is terminated first. Termination of sessions stops as soon as the required memory becomes available.
• Too many requests. If number of concurrent requests made to a database exceed 400, all transactions that have been running for 1 minute or more are terminated.
• Idle connections. Connections to SQL Azure database that are idle for 30 minutes or longer will be terminated.
• Denial of service. When there are a high number of login failures from a particular source internet protocol (IP) address, SQL Azure will block the connections from that IP address for a period of time. The connection is terminated and no error is returned.
• Fail over issues. SQL Azure is flexible to cope with any variations in usage and load. The service replicates multiple redundant copies of data to multiple physical servers to maintain data availability and business continuity. In case of a hardware failure, SQL Azure provides automatic failover to optimize availability for your application. Currently, some failover actions may result in an abrupt termination of a session.

More details about all these termination causes and returned error messages can be found here.

Bad performance and lockings occur ad random on my database.

Within SQL server you can retrieve data via a query. To retrieve the data you need to specify the transaction isolation level. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions. A lower isolation level increases the ability of many users to access data at the same time but increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter. Conversely, a higher isolation level reduces the types of concurrency anomalies that users may encounter but requires more system resources and increases the chances that one transaction will block another.

SQL server uses 4 levels to retrieve data:

1. Level 1: Read Uncommitted
2. Level 2: Read Committed
3. Level 3: Repeatable
4. Level 4: Serializable
5. Level 5: Snapshot (SQL 2005 Only)

By default level 2 will be used to retrieve data. The higher the level the more data is locked during read operation to guarantee consistency. Only level 1 will not block other users during read operations and you will not be blocked by other users. So even when you are only reading data you can block other users, depending on the used level. Especially when you are running big reports with level 2 or higher you can block a lot of other users who are reading or writing on the same table. So using level 2 or higher will have a negative impact on the overall performance of the system.

In most situation a database is created by a specific applications. The isolation level of this application is leading for all other applications whio wants to connect to the same database. I will use Exact Globe and Exact Synergy as an example in this case.


Which levels of data retrieval is used by Exact Globe and Exact Synergy?
Within Exact Globe and Exact Synergy the runtime is configured to connect to the database with level 1. (Read Uncommitted). This means that read operations never will be blocked by others users who are also reading data with level 1 or by users who are modifying records. If somebody is reading data with level 2, they can block other users who are modifying or inserting records for the same table. In Exact Globe and Exact Synergy all transactions are stored in one table named GBKMUT. So it is very important to read all data with level 1 otherwise you will have a big chance that locks on this table will occur. Crystal Reports which are executed within the Exact Globe shell are always reading data with level 1. This also applies to the Exact Excel add-in.

Which levels of data retrieval are used by external applications to connect to a Exact Globe or Exact Synergy database?
External applications like Crystal Reports or Excel connects to the database with the default level 2. So it can happen that a big Crystal report will lock Globe 2003 users who are processing data. For instance an external Crystal Report on the orderliness tables (ORSRG) can lock a Globe user who is doing the fulfillment. Therefore it is very important to configure the level to retrieve data for external applications to level 1. This needs to be done per application. The most common applications I have seen are:

• SQL Server Management Studio, mostly the DBA administrator itself.
• Crystal Reports.
• Custom made solutions.

How to configure the default isolation level to read uncommitted level in the SQL Server Management Studio (SSMS)?
Start SSMS and select in the menu: Tools, Options
Select Query Execution, SQL Server, Advanced, SET TRANSACTION ISOLATION LEVEL)

Set it to READ UNCOMMITTED.

See also my blog about my favorite SSMS settings

How to configure the default isolation level to read uncommited for Crystal Reports ?
To use external Crystal Reports on a Globe 2003 or e-Synergy database it is important to use level 1 (Transaction isolation level READ UNCOMMITTED)
Visit the support web site of Crystal Reports and do a search on: 'isolation level' to find documents which explain how you can set the default isolation level for different Crystal Reports versions


How to configure the default isolation level to read uncommited for my customer made solution? 
Contact your custom solution provider. More information about adjusting the isolation property can be found here.

How to detect the applications which connecs with the default isolation level read committed?
Use next query (as of SQL 2005 and higher). It will only show the applications which are running queries at that specific moment, you executed this query. Therefor it is usefull to execute this query on different moments of the day.

SELECT ss.Program_Name, Isolation_Level= Case Transaction_Isolation_Level WHEN '0' THEN 'Unspecified' WHEN '1' THEN 'Uncommitted' WHEN '2' THEN 'Committed' WHEN '3' THEN 'Repeatable' WHEN '4' THEN 'Serializable' WHEN '5' THEN 'Snapshot' END, sd.name AS DBName, spid, Host_Name, NT_User_Name, Memory_Usage * 8 Mem_KB, Reads, CPU_Time,ss.Login_Time, Last_Request_End_Time
FROM master.sys.dm_exec_sessions SS
INNER JOIN master..sysprocesses SP ON SP.spid=SS.session_id
INNER JOIN master..sysdatabases SD ON SD.dbid=SP.dbid
WHERE ss.session_id>50
AND Transaction_Isolation_Level <> 1

Enjoy, improving the overall performance of your database by avoiding unneccessary lockings.