Best design for OLTP tables
-
Best question for ages. Got it wrong but am enjoying investigating. I might be here for some time ...
-
Uwe Ricken (8/6/2015)
Hallo Wayne,your measures are not correct because you didn't run the process:
- for each table separately
- with 200 concurrent Connections
Than you will have a HIGH fragmentation on the IDENTITY table the same way.
I'm currently with a customer but will Show the results tomorrow morning.
I've blogged about it (GERMAN) but the code and the pics may help 🙂
http://www.db-berater.de/2015/04/guid-vs-intidentity-als-clustered-key-2/
If identity is ever increasing, how can it create the same fragmentation of a random value?
Even with concurrent sessions, the key for the index will only be increasing with the identity while the guid will just generate page splits and index fragmentation.
-
steve.childs (8/6/2015)
Best question for ages. Got it wrong but am enjoying investigating. I might be here for some time ...+1
Thanks for the very interesting question. learning a lot.
-
Essentially, you're trading a hot-spot, with latch problems, for a fragmented index. If your table is write-mostly, this might be OK.
BTW, this is the sort of problem that In-Memory OLTP is designed to address.
Gerald Britton, Pluralsight courses
-
Good question that made me do a little research and created a good discussion.
-
Luis Cazares (8/6/2015)If identity is ever increasing, how can it create the same fragmentation of a random value?[/Quote]
Hi,
fragmentation on an identity value can surely occur if you have a high concurrent System. I've linked the Pictures from my blog post about it.
The above picture shows what happens in a clustered index with an contigious increasing clustered key. You generate a hotspot at the very end of the leaf levels.
This picture shows the Transaction log for the insert process in the numeric_table. Please have a look to the SLOT ID! You can see that the Slot Id permanently changes. Why could this happen in an ever increasing key?
The [RowLog Contents 0] column shows the values which are inserted into the table. I don't want to go to deep into record structures BUT...
the ID-Value is from Byte 5 to 9!
SELECT CAST(0x14 AS INT) AS [Slot_0],CAST(0x21 AS INT) AS [Slot 1],
CAST(0x22 AS INT) AS [Slot 2],
CAST(0x23 AS INT) AS [Slot 3],
CAST(0x24 AS INT) AS [Slot 4],
CAST(0x20 AS INT) AS [Slot w],
CAST(0x1F AS INT) AS [Slot x],
CAST(0x15 AS INT) AS [Slot y],
CAST(0x1D AS INT) AS [Slot z];
GO
The above SQL Statement will decrypt the ID's of the inserted values in the order of their INSERTS!
This will be the result:
The picture demonstrates that IDs have NOT been inserted contigious but "random". The reason is quite simple - the processes have been blocked by other processes which have inserted the records!
The above picture Shows what happens inside:
Every single transaction gets it's ID from the IDENTITY-Pool first. This process is not revertable!
When the process has recievd it's ID (System process) it goes on to insert the record.
But there are 199 other processes which want to do the same!
Where do they all want to do that? - At the very end of the index - LATCH CONTENTION!
So that is the explanation WHY a clustered index with contigious index keys can become highly fragmented 🙂
[h1]IDENTITY[/h1]
[h1]GUID[/h1]
All the best to all of you. I'm off now for at least 3 weeks. I'm on my family holiday in Singapore and the Philippines (diving).
I really hope to see many of you in Seattle to the PASS Summit or the SQL in the City-Event!
Bye, Uwe
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!) -
First, very nice question! I rather like these sorts of questions as opposed to the "Here's a script, what's the result of the last SELECT?" questions. While the latter sort can still be quite instructive, I prefer these that require a bit more thinking and footwork by us. Of course, they're harder to write, but I digress.
From reading the question I knew it would be either the heap with IDENTITY or the GUID, and while I knew the GUID would reduce latch contention the most, I wasn't sure about overall duration.
I ran tests using 200 simultaneous users using SQL Load Generator, and the heap and clustered index on the GUID performed similarly in terms of duration for 1 set of 200 concurrent executions to complete (consistently 20 seconds for the heap and 22 seconds for the clustered GUID on my machine; the clustered IDENTITY came in around 30 seconds).
The explanation provided was borne out by significantly lower latch wait times for the clustered GUID scenario. On my test machine, though, that didn't translate to faster run times. It seems the CPU pressure induced by the 200 concurrent sessions, combined with all the writes to the log from all the separate transactions mostly overwhelmed the other differences between the heap and clustered GUID, as evidenced by the fact that WRITELOG was consistently the highest wait, and in the clustered GUID scenario, WRITELOG wait time went up significantly compared to the heap, but the difference was entirely accounted for by signal wait time.
Finally, in the clustered GUID scenario, each INSERT was doing 2 or more reads (averaged about 3, since the index hit that depth after about 8500 rows were inserted), while each INSERT into the heap was only doing 1. Combined with the CPU pressure brought on by that many concurrent sessions, that was enough to even give the heap a consistent, small edge in duration on my machine (the 20 seconds to 22 seconds persisted over 20 individual runs of each set of 200 concurrent INSERT loops).
Wrapping the loops in an explicit transaction to eliminate the excessive writes to the log brought down the duration significantly for both, of course, but the heap maintained a consistent slight edge on my machine.
So, if the main goal is to reduce latch contention, then yes, the GUID takes the day by quite a margin. If the goal is to reduce overall duration, well, then it seems that it depends on whether the latch contention is outweighed by the additional reads. 🙂
Cheers!
-
Thank you for your time, have a great holiday.
-
By "theory" answer should be "Guid" but I dont see any reason for not clicking the right answer against "Identity". Both are using here "Clustered Index Insert". IF we choose the answer for "guid" thinking that CI does not play a role in inserting data to the table, then its totally wrong. In both cases, it uses CIs.
Thanks.
-
It's rather a nice interesting question, and I hope it will lead to some debate about performance measurement and sizing for OLTP systems and its implications for schema design.
I suspect that whether additional reads outweigh the latch contention depends heavily on how big a buffer pool is available; if the buffer pool is big enough the latch contention is more important, but if it's small the reads will outweigh latch contention. And if the buffer pool is small the GUID version will cause additional writes as well as additional reads. So the sensible answer is "it depends" rather than "go for guid". Maybe with modern hardware prices we can assume a big enough buffer pool, maybe we can't.
Also, in the real world here will probably be some regular maintenance, which will include defragmentation of badly fragmented indexes; in the GUId version the unfragmented index will become totally fragmented again by new inserts, while in the identity version the part of the index that is not fragmented will not be fragmented by new inserts - so over a period of time, the defragment portion of the maintenance task becomes much more expensive in the GUID version than in the identity version. It may, then, be sensible to omit the defragmentation from maintenance for teh GUID version - but that means accepting that in anything but the very sort term the GUID version will require more permanent storage (whether disc, ssd, or whatever) than the identity version - eventually I think it would be a factor of 3 in disc occupancy, or maybe even worse than that.
And it is perhaps interesting to ask whether a write-only database is useful. Can a test with a write-once write-only workload be useful to measure OLTP performance or determine what teh best design for OLTP tabes is? I'm pretty sure the answer is NO, and over the decades since TPC has been specifying benchmarks for DBMS systems the benchmarks aimed at OLTP have included UPDATE as well as INSERT (and all but the trivial and now obsolete TPC-A and TPC-B include read without update as well as UPDATE and INSERT as part of the normal workload, not just as an exception to the norm). So the title of the question seems to me to be rather misleading.
Tom
-
Awesome questions, thanks!
Be still, and know that I am God - Psalm 46:10
-
Here's a thought... If this is a table which needs to support a large number of concurrent writes as quickly as possible, why was one option not a memory-optimized OLTP design...?
-
sknox (8/7/2015)
Here's a thought... If this is a table which needs to support a large number of concurrent writes as quickly as possible, why was one option not a memory-optimized OLTP design...?Hi - this is a good Suggestion BUT...
- i try to cover scenarios from "real life support questions" from my customers
- this will not work with Standard Edition
I was looking for a "general" solution...
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!) -
Great comment!!!!
Thanks,
webrunner
Uwe Ricken (8/6/2015)
Luis Cazares (8/6/2015)If identity is ever increasing, how can it create the same fragmentation of a random value?[/Quote]
Hi,
fragmentation on an identity value can surely occur if you have a high concurrent System. I've linked the Pictures from my blog post about it.
The above picture shows what happens in a clustered index with an contigious increasing clustered key. You generate a hotspot at the very end of the leaf levels.
This picture shows the Transaction log for the insert process in the numeric_table. Please have a look to the SLOT ID! You can see that the Slot Id permanently changes. Why could this happen in an ever increasing key?
The [RowLog Contents 0] column shows the values which are inserted into the table. I don't want to go to deep into record structures BUT...
the ID-Value is from Byte 5 to 9!
SELECT CAST(0x14 AS INT) AS [Slot_0],CAST(0x21 AS INT) AS [Slot 1],
CAST(0x22 AS INT) AS [Slot 2],
CAST(0x23 AS INT) AS [Slot 3],
CAST(0x24 AS INT) AS [Slot 4],
CAST(0x20 AS INT) AS [Slot w],
CAST(0x1F AS INT) AS [Slot x],
CAST(0x15 AS INT) AS [Slot y],
CAST(0x1D AS INT) AS [Slot z];
GO
The above SQL Statement will decrypt the ID's of the inserted values in the order of their INSERTS!
This will be the result:
The picture demonstrates that IDs have NOT been inserted contigious but "random". The reason is quite simple - the processes have been blocked by other processes which have inserted the records!
The above picture Shows what happens inside:
Every single transaction gets it's ID from the IDENTITY-Pool first. This process is not revertable!
When the process has recievd it's ID (System process) it goes on to insert the record.
But there are 199 other processes which want to do the same!
Where do they all want to do that? - At the very end of the index - LATCH CONTENTION!
So that is the explanation WHY a clustered index with contigious index keys can become highly fragmented 🙂
[h1]IDENTITY[/h1]
[h1]GUID[/h1]
All the best to all of you. I'm off now for at least 3 weeks. I'm on my family holiday in Singapore and the Philippines (diving).
I really hope to see many of you in Seattle to the PASS Summit or the SQL in the City-Event!
Bye, Uwe
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Interesting question, thanks. I'm not sure of the best overall solutions for these problems, but it has reminded me I need to think about them more.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply