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.

SQL Denali CTP available for download

The first CTP of the successor of SQL Server 2008 R2, codename SQL 'Denali' is available for download. It contains a lot new stuff. Keep in mind SQL server isn't just a database, it is an entire information platform by making use of SharePoint server. The need for a SharePoint installation can be a challenge for the companies with do not have or a very small ICT department.

Here is a short list of new high level topics:

• Project codename “Crescent”, a web-based, data visualization and presentation solution, and follow-on to the PowerPivot technology that is part of SQL Server 2008 R2
• Project codename “Apollo”, new column-store database technology aiming to provide greater query performance
• SQL Server AlwaysOn, a new high-availability “solution that will deliver “increased application availability, lower TCO (total cost of ownership) and ease of use
• Project codename “Juneau”, a single development environment for developing database, business intelligence (BI) and web solutions
• SQL Server Data Quality Services (based on technology from Microsoft’s 2008 Zoomix acquisition)
• Other data integration and management tools

My personal favorites are:

• Project codename 'Crescent'. This will help the people in the board room to easily analyze and play with the data they are looking at.
• Project codename 'Apollo'. Performance is one of my specialties in SQL Server. For every new SQL version I look forward to the improvements on performance. This new column-store database technology will indeed improve the performance of your datawarehouse significantly.

Another important thing to mention: No big compatibility issues are expected as we have had in the past from SQL 2000 to SQL 2005. This will make it easy to upgrade to a the new version with you current SQL Server databases.
If you are interested to look around in the CTP version, you can download it here

Enjoy testing this new version.

Announcement of SQL Azure Reporting CTP

Microsoft has announced SQL Azure Reporting service. Microsoft SQL Azure Reporting lets you use the familiar on-premises tools you’re comfortable with to develop and deploy operational reports to the cloud. There’s no need to manage or maintain a separate reporting infrastructure, which leads to the added benefit of lower costs (and less complexity). Your customers can easily access the reports from the Windows SQL Azure portal, through a web browser, or directly from your applications. SQL Azure Reporting enables developers to enhance their applications by embedding cloud based reports on information stored in a SQL Azure database. By using the SQL Data Sync CTP, you can upload data from your on premise database to a SQL Azure database. This SQL Azure database can be used by SQL Azure Reporting CTP.
You create your reports in the same way as your are doing it for an on premise SQL Reporting Server. The only difference is that you deploy your reports to the SQL Azure Reporting services.

Take a look at next video about SQL Azure Reporting services.
If you are interested sign up here for the SQL Azure Reporting CTP.

More detailed information about SQL Azure Reporting see:
http://www.microsoft.com/en-us/sqlazure/reporting.aspx
Enjoy creating your dashboards in the cloud.

Whitepaper: Inside in SQL Azure

Kalen Delaney has written a good whitepaper about the inside in SQL Azure. This is a must read for people who are Corporate decision makers, SQL Server database developers and DBA's. A lot is already writting about SQL Azure however this document is a good overview

SQL Azure Database is Microsoft’s cloud-based relational database service. Cloud computing refers to the applications delivered as services over the Internet and includes the systems, both hardware and software, providing those services from centralized data centers. This introductory section will present basic information about what SQL Azure is and what it is not, define general terminology for use in describing SQL Azure databases and applications, and provide an overview of the rest of the paper

Next topics are discussed:

1. What is SQL Azure?
2. What is in this whitepaper
3. What is NOT in this whitepaper
4. Terminology
5. Target Audience
6. Prerequisites
7. Setting up SQL Azure
1. Subscriptions
2. Databases
8. Security
9. Compatibility with SQL Server
10. SQL Azure Architecture Overview
1. Logical Databases on a SQL Azure Server
2. Network topology
3. Services Layer
4. Platform Layer
11. High Availability with SQL Azure
12. Scalability with SQL Azure
1. Throttling
2. Load Balancer
13. SQL Azure Development Considerations
14. SQL Azure Management
15. Future Plans for SQL Azure
16. Conclusion

Enjoy reading this whitepaper: http://social.technet.microsoft.com/wiki/contents/articles/inside-sql-azure.aspx