The performance of your application on a SQL Server database is for most customers xtremely important. In most situations applications from different providors will connect to the same database. For instance: Excel, SSRS, Crystal Reports, MRP Application X etc.. Connections to a database can be made in different ways:
- Read Uncommitted
- Read Ccommitted
- Repeatable Read
- Serializable
- Read Commotted Snapshot
- Snapshot Isolation
There is not one best isolation level to use. It depends on your application. If different application uses different isolation levels to connect to the same database, it can result in unexpected lockings.
Sunil Agarwal, Principal Program Manager in SQL Server Storage Engine Group, has made some great blog posts about these isolation levels. These blog posts will explain in detail the behaviour of every isolation level. Beside this he made some demo scripts to show lockings which can occur in situations you do not expect.
- Basics of Transaction Isolation Levels
- Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?
- Why do I get blocked when no one has locked the row(s) being queried?
- Minimizing blocking between updaters.
- My application was running fine yesterday but why is it blocking today?
The big question during the analyze of unexpected blocking issues in your database is which application is responsible for this. The first step in this analyze process is to understand which isolation levels are used by the different applications. In this blog post:
"Bad performance and lockings occur random on my database."
you can find a query to retrieve the different isolation levels of the different applications which are connecting to your database.
Enjoy it and good luck in finding the root cause of the unexpected locking and blockings.
No comments:
Post a Comment