GUID vs BIGINT
-
If you read the documentation on NEWSEQUENTIALID, you'll see that it says that each GUID generated is greater than the previous GUID (until you get a reboot), but not that each one is absolutely in line with the previous one like counting or something. So, basically, you're fine with those differing values.
Can it still fragment? Yes. If you substitute NEWID will that lead to even more fragmentation? Yes. Nothing has changed since the last time we talked about how GUIDs work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant Fritchey (5/19/2014)
Can it still fragment? Yes. If you substitute NEWID will that lead to even more fragmentation? Yes. Nothing has changed since the last time we talked about how GUIDs work.I did checked this one
create table table_name (i UNIQUEIDENTIFIER deafult NEWSEQUENTIALID() , j int,k int)
by inserting 1 miilion time
Insert into table_name (j,k) values (Newid(),1123,456)
and found that there was no that much fragmentation
- Pages Scanned................................: 500506
- Extents Scanned..............................: 62574
- Extent Switches..............................: 62573
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [62564:62574]
- Extent Scan Fragmentation ...................: 6.70%
- Avg. Bytes Free per Page.....................: 4046.0
- Avg. Page Density (full).....................: 50.01%
create table table_name (i UNIQUEIDENTIFIER , j int,k int)
by inserting not even 1/2 miilion rows
Insert into table_name (j,k) values (Newid(),1123,456)
- Pages Scanned................................: 124253
- Extents Scanned..............................: 15605
- Extent Switches..............................: 124248
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 12.50% [15532:124249]
- Logical Scan Fragmentation ..................: 99.20%
- Extent Scan Fragmentation ...................: 0.01%
- Avg. Bytes Free per Page.....................: 4050.1
- Avg. Page Density (full).....................: 49.96%
why there is difference
UNIQUEIDENTIFIER deafult NEWSEQUENTIALID() and UNIQUEIDENTIFIER without deafult NEWSEQUENTIALID()
and UNIQUEIDENTIFIER deafult NEWSEQUENTIALID() insert is very faster in this case
-
If you used this:
Insert into table_name (j,k) values (Newid(),1123,456)Then the default of NEWSEQUENTIALID wasn't actually used. Microsoft says that NEWSEQUENTIALID will be faster, so that's not a surprise. It's right there in the documentation that I linked above.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant Fritchey (5/19/2014)
Then the default of NEWSEQUENTIALID wasn't actually used. Microsoft says that NEWSEQUENTIALID will be faster, so that's not a surprise. It's right there in the documentation that I linked above.what my question was if i create a table with
create table table_name (i UNIQUEIDENTIFIER deafult NEWSEQUENTIALID(), j int )and insert into table_name (i,j) values (newid(),1)
the table fragmentation is seriously less how?
where as
create table table_name (i UNIQUEIDENTIFIER , j int )insert into table_name (i,j) values (newid(),1)
the table fragmentation is high this is expected one.
but if i gave default NEWSEQUENTAILID() and use newid() for insert there is less fragmentation how?
-
No, the fragmentation will be the same, both are having NEWID() specified as the value so the default is being overridden.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (5/20/2014)
No, the fragmentation will be the same, both are having NEWID() specified as the value so the default is being overridden.No Gail
I checked it ...there is less frag when default newsequentialid()
just giving you my sys information where i checked
SQL server 2008 Express edition on 64bit
For testing you can take my table
Thanks!
-
yuvipoy (5/20/2014)
GilaMonster (5/20/2014)
No, the fragmentation will be the same, both are having NEWID() specified as the value so the default is being overridden.No Gail
I checked it ...there is less frag when default newsequentialid()
just giving you my sys information where i checked
SQL server 2008 Express edition on 64bit
For testing you can take my table
Thanks!
If you pass a value during the INSERT, it overrides the default value. The default value is only applied if the data passed in is NULL. You may have seen some difference in fragmentation, but it's not from the NEWSQUENTIALID based on the T-SQL you're showing because you're overriding the NEWSEQUENTIALID with NEWID.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
If you're inserting NEWID() explicitly, it doesn't matter in the slightest whether there's a default defined on the column or what the default is, the values inserted are the same. The default is only used when you don't specify a value for that column. Hence the fragmentation will be the same or very close to the same when you insert the value of NewID, regardless of whether or not there's a default on the column.
Let's test three cases and see how the fragmentation behaves.
-- takes ~30 minutes to run
USE tempdb;
GO
SET NOCOUNT ON;
-- default new sequential ID, explicitly inserting a value
CREATE TABLE t1 (
Col1 UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED,
Col2 CHAR(500) DEFAULT '' -- filler
)
-- no default, explicitly inserting a value
CREATE TABLE t2 (
Col1 UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
Col2 CHAR(500) DEFAULT '' -- filler
)
-- default new sequential ID, allowing the default to define the value
CREATE TABLE t3 (
Col1 UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED,
Col2 CHAR(500) DEFAULT '' -- filler
)
DECLARE @i INT = 0;
WHILE @i < 1000000
BEGIN
INSERT INTO t1 (Col1, Col2) VALUES (NEWID(),' ') -- default new sequential ID, explicitly inserting a value
INSERT INTO t2 (Col1, Col2) VALUES (NEWID(),' ') -- no default, explicitly inserting a value
INSERT INTO t3 (Col2) VALUES (' ') -- default new sequential ID, allowing the default to define the value
SET @i = @i + 1;
END
SELECT t.name ,
ips.avg_fragmentation_in_percent ,
avg_page_space_used_in_percent ,
page_count
FROM sys.dm_db_index_physical_stats(2, NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.tables t ON t.object_id = ips.object_id
WHERE ips.index_level = 0
AND t.name IN ( 't1', 't2', 't3' );
DROP TABLE t1
DROP TABLE t2
DROP TABLE t3
Three tables, one with a default of newsequentialID but with NewID() explicitly inserted into that column, one with no default and newID() inserted into that column, one with a default of newsequentialID and the default used (value for column not specified)
Now, the fragmentation:
Fragmentation for t1 and t2 are high and just about identical, the small (0.04%) is well within the range expected for differences in the page allocations and the values for the uniqueidentifier (due to the randomness, some pages will split more often than others). The one where the newsequentialID default was allowed to be used has much, much lower fragmentation, close to 0, 2/3 of the page count and a higher page density.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gail
so what if the unique id is generated from outside database
If it is from UidcreateSequential function
how the performance will be?
it is same as NEWSEQUENTIALID?
is the behaviour is same for both?
-
No idea. Look up UidcreateSequential in your development language's documentation and see what it is and how it behaves.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
What will happen for the pregenerated id's?
those ids are generated by NEWID() which is around 10 million record in the table.
now changed NEWSEQUENTIALID() and it starts its sequential orders.
will my table will be again fragmented even after changing to NEWSEQUENTIALID() on the existing table.
-
Nothing will happen to the existing values. If the table is fragmented before you change the default, rebuild your indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
My question was to will the table be fragment again if the old values where there.
I did rebuild (alter index) post changing to NEWSEQUENTIALID().
I see table getting fragmentation after changing to NEWSEQUENTIALID() over NEWID().
thats why i asked will it get frag again.
-
Are you explicitly inserting NEWID() into the table, or are you allowing the default to be used?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
And NEWSEQUENTIALID doesn't prevent all fragmentation, it just reduces the level of fragmentation to less than what you'll see with NEWID.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply