April 13, 2004 at 4:00 pm
Hi All,
I have a firend that asked me a question about this and I could give a good technical answer. He created a clustered index on a large table (more than 1.5 millioin rows). The columns that he is using are a GUID and a datetime. (Let's leave aside that it is a bad idea to do that.)
When he is inserting a new row and performing a select at the same time he gets some pretty hefty blocking issues. When he removes the datetime column from the clustered index (clustered index just on the GUID) he has no issues with blocking.
My read, even though I haven't been able to look at the system and see the locks, is that he is having a problem because the inserts were forcing SQL Server to move pages to resort the clustered index. In other words, the inserts aren't taking place at the "end of the table" but "in the middle of the table", causing SQL Server to move entire pages.
My question is, why would the additino or subtration the the datetime column change this situation. I suspect that it depends on your sort order and that with the SQL Server default sort order datetime and GUID would not necessarily sort to the end of the last page.
Any ideas?
steve
April 14, 2004 at 1:20 am
Okay, just one note on GUID as keys (from BOL)
At 16 bytes, the uniqueidentifier data type is relatively large compared to other data types such as 4-byte integers. This means indexes built using uniqueidentifier keys may be relatively slower than implementing the indexes using an int key.
Next, SQL Server does not sort the data pages physically in the order of the clustered index. The cost for doing this would be far too high. Instead, SQL Server establishes and maintains the so-called page chain when there is a clustered index on a table. So, SQL Server will not move pages to resort the clustered index.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 14, 2004 at 5:46 pm
Thanks. Yeah I know that it is a bad practice in general to use guid as your clustered index.
I understand what you are saying about SQL Server not moving a page to maintain the sorting of the clustered index, but I still think there are time when SQL Server will have to copy data out of a page to another page to maintain the clustered index. Am I completely wrong on that? I thought that was the reason why you don't want to have a clustered index on a frequently updated column.
Another piece of information that may be helpful. The row size s pretty small on this table. Meaning that I believe SQL Server is going to sotre a unch of rows on the same page.
Anyone want to take a crack at the reason for the blocking described in the original post?
April 15, 2004 at 1:14 am
I understand what you are saying about SQL Server not moving a page to maintain the sorting of the clustered index, but I still think there are time when SQL Server will have to copy data out of a page to another page to maintain the clustered index. Am I completely wrong on that? I thought that was the reason why you don't want to have a clustered index on a frequently updated column.
I think Steve Jones mentioned something like this here
But generally spoken means sorted order in a clustered index maintaining the page chain. This is a doubly linked list. New pages can be added by simply adjusting the links in the page chain.
You can examine this
use pubs
go
dbcc traceon(3604)
go
dbcc page(5,1,88,1)
PAGE: (1:88)
------------
BUFFER:
-------
BUF @0x00E18EC0
---------------
bpage = 0x1A036000 bhash = 0x00000000 bpageno = (1:88)
bdbid = 5 breferences = 1 bstat = 0x209
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x1A036000
----------------
m_pageId = (1:88) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1977058079 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 24 m_slotCnt = 23
m_freeCnt = 6010 m_freeData = 2136 m_reservedCnt = 0
m_lsn = (3:242:2) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = -2147483591
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply