August 5, 2015 at 8:12 pm
Comments posted to this topic are about the item Best design for OLTP tables
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
August 6, 2015 at 1:37 am
Very interesting!
Thanks!
😀
But in the real world, if Id is uniqueidentifier, how many splits of page will occur?
August 6, 2015 at 2:01 am
What are actual performance results of running these three scenarios?
August 6, 2015 at 2:26 am
Carlo Romagnano (8/6/2015)
But in the real world, if Id is uniqueidentifier, how many splits of page will occur?
Even I am intrested to know answer for above
------------------------------------------------------------------------------------
Ashish
August 6, 2015 at 3:10 am
Carlo Romagnano (8/6/2015)
Very interesting!Thanks!
😀
But in the real world, if Id is uniqueidentifier, how many splits of page will occur?
The fragmentation of the Indexes (INT or GUID) is nearby the same because of the concurrency of the Transactions!
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
August 6, 2015 at 3:12 am
graham.day 53003 (8/6/2015)
What are actual performance results of running these three scenarios?
Hallo Graham,
as always - it depends 🙂
In my test Environment with 4 cores, 32 GB and SSD the elapsed times were:
dbo.GUID: 1.45 seconds
dbo.Identity: 5.32 seconds
dbo.heap: 3,98 seconds
NO OTHER USER PROCESSES WHERE ACTIVE ON SQL SERVER
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
August 6, 2015 at 3:25 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2015 at 4:10 am
This was removed by the editor as SPAM
August 6, 2015 at 4:45 am
I got the answer right, but with the wrong (or different?) theory...
I was looking at the size of the CHAR value... I figured that because it is lower, it'll use less pages and could be faster because of the amount of pages in total.
How true is this? 😛
August 6, 2015 at 6:26 am
DrKiller (8/6/2015)
I got the answer right, but with the wrong (or different?) theory...I was looking at the size of the CHAR value... I figured that because it is lower, it'll use less pages and could be faster because of the amount of pages in total.
How true is this? 😛
While the char value is smaller, the GUID is bigger.
An INT is 4 bytes + char(200) = 204 bytes.
A GUID is 16 bytes + char(188) = 204 bytes.
So, not true.
August 6, 2015 at 6:34 am
Uwe Ricken (8/6/2015)
In my test Environment with 4 cores, 32 GB and SSD the elapsed times were:
dbo.GUID: 1.45 seconds
dbo.Identity: 5.32 seconds
dbo.heap: 3,98 seconds
NO OTHER USER PROCESSES WHERE ACTIVE ON SQL SERVER
hmmm, interesting, and thanx 4 the gr8 question.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
August 6, 2015 at 6:35 am
I am curious of what the default fill factor of this database was since the question was about the speed of inserts and I would think that would have a potentially significant impact on the random GUID.
I will have to test separating the load to random pages vs page splits for multiple concurrent sessions with that TPS.
August 6, 2015 at 6:59 am
sknox (8/6/2015)
DrKiller (8/6/2015)
I got the answer right, but with the wrong (or different?) theory...I was looking at the size of the CHAR value... I figured that because it is lower, it'll use less pages and could be faster because of the amount of pages in total.
How true is this? 😛
While the char value is smaller, the GUID is bigger.
An INT is 4 bytes + char(200) = 204 bytes.
A GUID is 16 bytes + char(188) = 204 bytes.
So, not true.
Although, in the real world, you won't reduce the length of your strings just to use a guid.
August 6, 2015 at 8:29 am
Uwe Ricken (8/6/2015)
Carlo Romagnano (8/6/2015)
Very interesting!Thanks!
😀
But in the real world, if Id is uniqueidentifier, how many splits of page will occur?
The fragmentation of the Indexes (INT or GUID) is nearby the same because of the concurrency of the Transactions!
I would disagree with this. But, let's use the code from this article to see what is really right.
CREATE TABLE dbo.[identity]
(
Id int NOT NULL IDENTITY (1, 1),
C1 char(200) NOT NULL DEFAULT ('only a filler'),
CONSTRAINT pk_identity_Id PRIMARY KEY CLUSTERED (Id)
);
GO
CREATE TABLE dbo.guid
(
Id uniqueidentifier NOT NULL DEFAULT (newid()),
C1 char(188) NOT NULL DEFAULT ('only a filler'),
CONSTRAINT pk_guid_id PRIMARY KEY CLUSTERED (Id)
);
GO
CREATE TABLE dbo.heap
(
Id int NOT NULL IDENTITY (1, 1),
C1 char(200) NOT NULL DEFAULT ('only a filler')
);
GO
DECLARE @i INTEGER = 0;
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.[identity] DEFAULT VALUES;
INSERT INTO dbo.guid DEFAULT VALUES;
INSERT INTO dbo.heap DEFAULT VALUES;
SET @i += 1;
END;
GO
-- look at the fragmentation of the tables in this database
SELECT ObjectName = OBJECT_NAME(object_id),
index_id,
index_type_desc,
page_count,
avg_fragmentation_in_percent,
fragment_count
FROM sys.dm_db_index_physical_stats(DB_ID(), 0, NULL, NULL, NULL);
GO
My results
ObjectName index_id index_type_desc page_count avg_fragmentation_in_percent fragment_count
---------- ----------- --------------- ---------- ---------------------------- --------------------
identity 1 CLUSTERED INDEX 27 25.9259259259259 10
guid 1 CLUSTERED INDEX 37 97.2972972972973 37
heap 0 HEAP 28 50 11
But, this is just for 1000 rows. Not being run by 200 concurrent clients as specified in the question. So, let's empty the tables and do this 200 times to simulate this.
TRUNCATE TABLE dbo.guid;
TRUNCATE TABLE dbo.[identity];
TRUNCATE TABLE dbo.heap;
GO
DECLARE @i INTEGER = 0;
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.[identity] DEFAULT VALUES;
INSERT INTO dbo.guid DEFAULT VALUES;
INSERT INTO dbo.heap DEFAULT VALUES;
SET @i += 1;
END;
GO 200 -- run this batch 200 times
SELECT ObjectName = OBJECT_NAME(object_id),
index_id,
index_type_desc,
page_count,
avg_fragmentation_in_percent,
fragment_count
FROM sys.dm_db_index_physical_stats(DB_ID(), 0, NULL, NULL, NULL);
The results now:
ObjectName index_id index_type_desc page_count avg_fragmentation_in_percent fragment_count
---------- ----------- ---------------- -------------------- ---------------------------- --------------------
identity 1 CLUSTERED INDEX 5269 0.474473334598596 681
guid 1 CLUSTERED INDEX 7681 99.2709282645489 7681
heap 0 HEAP 5411 99.5581737849779 684
Results analysis: the GUID table is what I would call "perfectly fragmented" (> 99% fragmented), while the identity table is essentially completely de-fragmented. The fragmentation of these two tables is NOT nearly the same... they are nowhere close to the same.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 6, 2015 at 8:41 am
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/
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply