More details about concurrency, locking and blocking in SQL Server.

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:

1. Read Uncommitted
2. Read Ccommitted
3. Repeatable Read
4. Serializable
5. Read Commotted Snapshot
6. 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.

1. Basics of Transaction Isolation Levels
2. Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?
3. Why do I get blocked when no one has locked the row(s) being queried?
4. Minimizing blocking between updaters.
5. 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.

Combine SQL Profiler with Performance monitor logs

To analyze the performance of your applications which is running on SQL Server you need to make use of 2 standard tools:

1. SQL Profiler
2. Windows Performance Monitor. (Perfmon)

Both tools are useful to understand what happens at which moment. In SQL Profiler you load a profile trace however you can also load the performance monitor log file in the same loaded SQL Profiler trace file. See next example:

What do you need to do:

1. Make a SQL Profile trace of your application.
2. Make a performance log file with Performance monitor (Perfmon) of your application via a data collector set. Save the result to a file.
3. Execute some load in your application.
4. Stop the SQL profiler trace file and store it as a trace file.
5. Load the trace file in SQL Profiler.
6. In the menu of SQL Profiler Select File, Import Performance Data and select your performance log file.
7. Select the counters you want to see in the SQL profiler.

Now you have both trace files combined in one application. When you scroll through the profile entries you will see the red vertical bar moving. A perfect way to analyze the performance of your application.

Enjoy using the performance monitor with SQL Profiler.

Whitepaper: High Performance SQL Server Workloads on Hyper -V

As of today, virtualization is used in a lot of server rooms. It started with servers like web servers, domain controllers, ftp servers etc. People are getting now more and more experiences with virtualization. Virtualization is getting a common used technology. To deploy SQL Server to a virtualization environment you need to take more things into account. Microsoft has made a whitepaper how you can deploy Microsoft SQL Server 2008 to a virtualization environment using Microsoft Windows Server 2008 R2 Hyper-V. It demonstrates that Hyper-V provides the performance and scalability needed to run complex SQL Server workloads in certain scenarios. It also shows how Hyper-V can improve performance when used in conjunction with advanced processor technologies. This paper assumes that the reader has a working knowledge of virtualization, Windows Server Hyper-V, SQL Server, Microsoft System Center concepts and features.

Enjoy reading: Whitepaper: High Performance SQL Server Workloads on Hyper -V

Exact Globe and Exact Synergy supports SQL Server 2008 as of product update 393.

As of 21 april 2009 product update 393 for Exact Globe and Exact Synergy is generally available for all customers worldwide with an active maintenance agreement, except for customers with custom solutions .

As of product update 393 both Exact Globe and Exact Synergy supports the use of SQL Server 2008. This means that Exact starts selling this new version of SQL Server. Please contact your local Exact office or reseller. If you want to have a look at the unique selling points of SQL Server 2008, please have a look at the overview on the Microsoft website. For an overview of technical documentation for SQL Server 2008, please refer to the Books Online.

SQL Server 2008 licenses grant customers the right to downgrade, which means that customers wanting to use SQL Server 2005 can still do so when purchasing SQL Server 2008 licenses. The upgrade to SQL Server 2008 can then take place at their own convenience. This is also important to know for customers purchasing or using Exact Synergy Enterprise: they are adviced to purchase SQL Server 2008 licenses and use SQL Server 2005 until product update 241 of Exact Synergy Enterprise, which suports SQL Server 2008,  is available.