SQL Server Performance Forum – Threads Archive

How to see the data stored in a particular page?

sp_lock returns the following values (just example): Type Resource
PAG 1:1225
RID 1:1225:2
KEY (03000100cb04) The value 1:1225 tells me the page number but I’m interested in knowing what data is stored in this page 1:1225. The value 1:1225:2 tells me the page number and row but I’m interested in knowing what data is stored in that row. For this, I can at least find out what statement is causing the locks and based on the condition of the statement that it is possible to deduce the partial content of the row. I don’t know what the value (03000100cb04) means. If there is way to see the data (or just the unique keys) in the page being locked, then I will know exactly what data cannot be accessed. Without knowing what data are in those locked pages, how can I tell what data cannot be accessed and what data can be accessed during that time?

Refer tohttp://www.sql-server-performance.com/reducing_locks.asp link to reduce the locks.
http://www.databasejournal.com/news/article.php/3288411 &http://www.winnetmag.com/SQLServer/Article/ArticleID/6198/6198.html for further information on locking. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

DBCC PAGE will give you this information (and a lot more [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]) –Frank http://www.insidesql.de</a>

Hi Frank, After I post this thread, I have found that function. I have tried it and I can read data from any page. However, this function cannot be run against any blocked page so this will not work in real time when the locking occurs.
Peter

If you’re able to figure out what page was read during the lock, there should be no need to see the data there in real time as SQL Server will not flip the data pages as it likes. I mean the same data should be there after the lock is resolved.
–Frank
http://www.insidesql.de

Hi Frank, I believe you’re right. But it will require me to save all the lock info. Does SQL Server shift rows when a row is deleted or this shifting only happens during defrag? BTW, I still have not figure out what is this: KEY (03000100cb04) Peter

quote:Originally posted by pcsql Hi Frank, I believe you’re right. But it will require me to save all the lock info. Does SQL Server shift rows when a row is deleted or this shifting only happens during defrag?

What about saving the output of sp_lock, sp_who(2) to a table and analyse thereafter? For your second question:
From Inside SQL Server 2000 Deleting Rows from a Heap
SQL Server 2000 doesn’t automatically compress space on a page when a row is deleted. As a performance optimization, the compaction doesn’t occur until a page needs additional contiguous space for inserting a new row. Deleting Rows from a B-Tree
In the leaf level of an index, when rows are deleted, they’re marked as ghost records. This means that the row stays on the page but a bit is changed in the row header to indicate that the row is really a ghost. The page header also reflects the number of ghost records on a page. Ghost records are primarily a concurrency optimization for key-range locking. I’ll discuss the details of key-range locking, as well as all other locking modes, in Chapter 14, but a short explanation is in order here, just for completeness. Ghost records are present only in the index leaf nodes. If ghost records weren’t used, the entire range surrounding a deleted key would have to be locked. So suppose you have a unique index on an integer and the index contains the values 1, 30, and 100. If you delete 30, SQL Server will need to lock (and prevent inserts into) the entire range between 1 and 100. With ghosted records, the 30 is still visible to be used as an endpoint of a key-range lock so that during the delete transaction, SQL Server can allow inserts for any value other than 30 to proceed. SQL Server provides a special housekeeping thread that periodically checks B-trees for ghosted records and asynchronously removes them from the leaf level of the index. This same thread carries out the automatic shrinking of databases if you have that option set. When this thread is active, the Current Activity window in SQL Server Enterprise Manager or the sp_who command will show you an active process with a spid value of 6.
…
Also note that I put the DELETE statement inside a user transaction. The housekeeping thread that cleans up ghost records is fast and efficient, and there is a chance that the ghost record will be cleaned up between the time that I issue the DELETE operation and the time I execute DBCC PAGE to look at the rows. To guarantee that the ghost will not be cleaned up, I put the DELETE into a transaction, and I don’t commit or roll back the transaction before examining the page or running DBCC CHECKTABLE. The housekeeping thread will not clean up ghost records that are part of an active transaction. HTH
–Frank
http://www.insidesql.de

Hi Frank, I have that book too but I only read a small portion of it. It is a very good technical book. The book also mentions about KEY (03000100cb04) which is a hash value. I guess which won’t tell me which index key (I mean the actual key value) is being locked unless I know how SQL Server generate that hash value. I’m currently trying to resolve locking problem due to running batch processing and normal transactions at the same time. Any suggestion?
Thank you, Peter

http://support.microsoft.com/default.aspx?scid=kb;en-us;289276
http://www.winnetmag.com/Articles/Print.cfm?ArticleID=40925
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc011502/wcblurb011502.asp Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Gaurav, Thank you for the links. I have been watching SQL Server webcasts recently. In fact, I just watch the one related SQL Server and XP SP2 and another one related to BPA today. It is more interesting than reading books.[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Peter

Inside SQL Server is really a book worth reading except for the chapters where Kalen Delaney talks about querying and modifying data. That is an area where she is not so strong at. But the rest is outstanding.
–Frank
http://www.insidesql.de

I run dbcc page and the result has this: FileId PageId Row Level nbalance ?
—— ———– —— —— —————— ——————
3 333851 0 0 .00 0xFF74000003000500
3 333851 1 0 .00 0xFF76000003000400
3 333851 2 0 .00 0xFF7B000003000100
3 333851 3 0 .00 0xFF7D000003000000
3 333851 4 0 .00 0xFF7D000003000300
3 333851 5 0 .00 0xFF7E000003000200
3 333851 6 0 .00 0xFF7E000003000500
3 333851 7 0 .00 0xFF80000003000400 How can I tell which record is each row? If the table has the clustered index, the last column is the clustered key so I can find the record. But in this case, there is no clustered index, how to find the record. I don’t know what the column ? is.

http://www.winnetmag.com/SQLServer/Article/ArticleID/8097/SQLServer_8097.html may help you. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, Thank you for the article. I guess this info may be in the Inside SQL Server 2000. I will check it out.
Peter

Yes ofcourse as the author of the article is same <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />. <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

Yes but the remaining unanswered question is "what is the hex value under the resource column when a key lock is indicated". Several articles indicate that people think it is a byte switched, possibly one way hashed value for the key. (Note: emailed one author Adres Taylor and he is unsure of what the value is but thinks it is the key being used.) BOL indicates that this column holds data that 3rd party vendors can use. So, bottom line, is there a way or has anyone come up with a way to unhash the hex value in the resource column of sp_lock to quickly and easily determine the exact key value that is being locked. ::fn_get_sql shows the unresolved code being passed to the engine. DBCC PAGE offers little value because even though there are RID, PAGE, and TAB locks it would be impossible to use DBCC PAGE without knowing what the key is and on what page the key resides. This is a hot problem for my company. Any help would be really appreciated. Thanks in advance.
BB BB

]]>