0%

Today I want to talk about another very interesting topic in SQL Server: disabling Row and Page Level Locks in SQL Server. Every time that you rebuild an Index in SQL Server, you can use the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options to specify that SQL Server should acquire Row and Page Level Locks when accessing your data for reading and writing. Let’s look at what happens internally when we disable these locks.

阅读全文 »

Introduction

One of the most commonly seen questions when dealing with firewalls and other Internet connectivity issues is the difference between active and passive FTP and how best to support either or both of them. Hopefully the following text will help to clear up some of the confusion over how to support FTP in a firewalled environment.

This may not be the definitive explanation, as the title claims, however, I’ve heard enough good feedback and seen this document linked in enough places to know that quite a few people have found it to be useful. I am always looking for ways to improve things though, and if you find something that is not quite clear or needs more explanation, please let me know! Recent additions to this document include the examples of both active and passive command line FTP sessions. These session examples should help make things a bit clearer. They also provide a nice picture into what goes on behind the scenes during an FTP session. Now, on to the information…

阅读全文 »

SQL-92标准中定义了四个隔离级别,这四个隔离级别在以前版本的SQL Server中即受到支持:

READ UNCOMMITTED

READ UNCOMMITTED是限制性最弱的隔离级别,因为该级别忽略其他事务放置的锁。使用READ UNCOMMITTED级别执行的事务,可以读取尚未由其他事务提交的修改后的数据值,这些行为称为“脏”读。这是因为在Read Uncommitted级别下,读取数据不需要加S锁,这样就不会跟被修改的数据上的X锁冲突。比如,事务1修改一行,事务2在事务1提交之前读取了这一行。如果事务1回滚,事务2就读取了一行没有提交的数据,这样的数据我们认为是不存在的。

阅读全文 »

Much like any powerful tool with a command line interface, the WinDBG expression syntax can sometimes seem quite cryptic. In the interest of trying to unlock the power of the command line interface, in this article we’re going to cover a fundamental WinDBG concept that is key to understanding those long, strange commands: the expression evaluators.

As it turns out, there are two built in expression evaluators in WinDBG, the MASM evaluator and the C++ evaluator. In order to better understand the differences, we’ll first discuss the MASM evaluator in detail and then move on to the C++ evaluator.

Please note that this article is not going to attempt to be definitive and list every possible operator or feature of the expression evaluators. We’re simply going to focus on what we find to be the most useful in the hopes of getting you started. However, once you’ve gotten your feet wet full details are available in the WinDBG documentation.

阅读全文 »

In todays blog posting I want to talk about Lock Escalations in SQL Server. Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server. Let’s start in the first step with the description of the so-called Lock Hierarchy in SQL Server, because that’s the reason why the concept of the Lock Escalations exists in a relational database like SQL Server.

Lock Hierarchy

The following picture shows you the lock hierarchy used by SQL Server:

Lock Hierarchy in SQL Server

阅读全文 »

I blogged 2 years ago about why we need UPDATE locks in SQL Server. Today I want to continue this discussion by talking about Intent Locks in SQL Server, and why they are needed.

The Lock Hierarchy in SQL Server

When I talked about Lock Escalations in SQL Server, I started by briefly mentioning that SQL Server uses a Lock Hierarchy when you read or change your data.

The Locking Hiearchy in SQL Server

When you read a row, SQL Server always acquires by default a Shared Lock (S), and when you change a row SQL Server acquires an Exclusive Lock (X). Those Locks are incompatible with each other, and that will introduce blocking situations when you want to read/write a row concurrently.

In addition to the row level locks, SQL Server also acquires so-called Intent Locks at higher levels within the Lock Hierarchy: at the page and at the table level. SQL Server acquires the following Intent-Locks based on the requested row level lock:

  • Intent Shared Lock (IS), when you have a Shared Lock at the row level
  • Intent Update Lock (IU), when you have an Update Lock at the row level
  • Intent Exclusive Lock (IX), when you have an Exclusive Lock at the row level

Therefore you always get the Lock Hierarchy as shown above when you read and write your records. But why is SQL Server using these Intent Locks?

阅读全文 »

Today I want to talk about a specific question that I almost get every time when I teach about Locking & Blocking in SQL Server: Why does SQL Server need to have Update Locks? Before we go down to the details of why they are needed, I first want to give you a basic overview of when an Update (U) Lock is acquired, and how the lock itself behaves regarding its compatibility.

In general an Update Lock is used in SQL Server when performing an UPDATE statement. When you look at the underlying query plan, you can see that such a plan always consists of 3 parts:

  • Reading data
  • Calculating new values
  • Writing data

Update Query Plan

When SQL Server initially reads the data to be changed in the first part of the query plan, Update Locks are acquired on the individual records. And finally these Update Locks are converted to Exclusive (X) Locks when the data is changed in the third part of the query plan. The question that arrises with this approach is always the same: why does SQL Server acquire Update Locks instead of Shared (S) Locks in the first phase? When you normally read data through a SELECT statement, a Shared Lock is also good enough. Why is there now a different approach with UPDATE query plans? Let’s have a more detailed look at it.

阅读全文 »

Introduction

Snapshot Isolation level was introduced in SQL Server 2005 and has been available ever since. Snapshot isolation levels improve performance but there are some things to take into consideration when using this feature. Some people use it frequently as it minimizes blocking and improves performance/concurrency without knowing its impact on maintaining versions in tempdb, whereas some people stay away from it because of this extra overhead. Some people get confused about the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one where the other is needed or vice versa.

In this article series, I am going to discuss what snapshot isolation levels are, their variants, why and when we should use them and how we should start using this feature with examples.

阅读全文 »