SQL server uses 4 levels to retrieve data:
- Level 1: Read Uncommitted
- Level 2: Read Committed
- Level 3: Repeatable
- Level 4: Serializable
- Level 5: Snapshot (SQL 2005 Only)
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.
Start SSMS and select in the menu: Tools, Options
Select Query Execution, SQL Server, Advanced, SET TRANSACTION ISOLATION LEVEL)
Set it to READ UNCOMMITTED.
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
AND Transaction_Isolation_Level <> 1
Enjoy, improving the overall performance of your database by avoiding unneccessary lockings.