The role of key Hash value in SQL Server (bottom)

Although there are still a lot of problems with keyhashvalue, it is generally possible to determine that "Keyhashvalue is used to lock resources."

Rather than what I said before, in order to quickly find the corresponding records according to this Keyhashvalue

I'm sorry to have misled you,!!!!.

Resource                                            Description
RID                             is used to lock a row in a heap (heap)
Key is                              used to lock a row on the index, or an index key
Page                            locks a 8KB page in the database, such as a data page or index page
EXTENT                        A continuous set of 8 pages (district)
HOBT lock                           Entire heap or B-tree lock
The table                          locks entire tables that include all data and indexes
                                   File Database files
Application                Application-specific resources
METADATA                        Meta Data lock
Allocation_unit             allocation unit
                     Database entire databases

Key is to lock the rows in the index by the generated Keyhashvalue

KEY is used to lock a row on the index

Why do you need this keyhashvalue???

Because it is very gou did not write C # code, but I think from C # Multithreading synchronization to understand will be better

For example:

Lock statement Lock ensures that when one thread is in the critical section of the code, another thread does not enter the critical section. If another thread attempts to enter a locked generation

Code, it will be waiting (that is, blocked) until the object is released, you can interpret the synchronization object as Keyhashvalue

C # multithreaded Synchronization

C # multithreaded Synchronization

We still look at the article links to the bar, because I am very gou did not write C # code, the details of things to see the article better

There are a number of related articles on the Web:

For example

What are the limitations of 900bytes when indexing is established?

For performance, it is not possible to make an index on a field with a larger data type and a very large amount of data stored

Because by doing so, calculating the keyhashvalue will be very consuming performance

This article:improvement in minimizing lockhash key collisions into SQL Server 2008r2 and its impact on concurrency

Since the key to a row could is as large as 900 bytes, using the real key values would have inflicted larger memory Ption.

Introduced

The solution to this problem is found when designing SQL Server 7.0 into 1996 and 1997 by using the key of the row and APPL Y

A hash algorithm to it which then results in a 6 byte long Lockhash value

I've sorted these articles into my article:undocumented virtual column%%lockres%%

Under SQLSERVER2005 and SQLSERVER2012, create the same clustered index, and you'll see that under SQL2005, the table's clustered index page has keyhashvalue

But under SQL2012, the keyhashvalue column of the table's clustered index page is all null

Since I do not have SQL2008, so did not test SQL2008, estimated from SQL2008 start,keyhashvalue began to change a little

In SQL2005, you use DBCC page to view data pages, each row in the data page is not displayed Keyhashvalue , and you do not know which trace tag to open to see

The only keyhashvalue in the SQL2005 that can see the data page is the use of%%lockres%%

In SQL2012, you don't have to make any settings, and you can use DBCC page to see keyhashvalue

Of course, you can also use%%lockres%%:

1 SELECT%%lockres%% as ' data page Keyhashvalue ' from table name

Page 126 is a data page

1 DBCC TRACEON (3604,-1) 2 Go 3 DBCC PAGE (testhashkey,1,126,3) 4 go

Slot 0 Offset 0x0 Length 0 Length (physical) 0

Keyhashvalue = (8194443284a0)

DBCC execution completed. If DBCC prints an error message, contact your system administrator.

The biggest improvement in SQL2012 is that the hints are very clear, and that the previous SQL Server version doesn't show up for you,

So sometimes you don't have to do a table connection with so many system tables, and if the previous version of SQL Server wants to see a certain information, do it again.

The table connection of this system table will also make the table connection of that table to come up with the things that you want to see, very tedious