Best design for OLTP tables

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    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!)

  • Carlo Romagnano

    SSC-Insane

    Points: 21832

    Very interesting!

    Thanks!

    😀

    But in the real world, if Id is uniqueidentifier, how many splits of page will occur?

  • graham.day 53003

    SSC Enthusiast

    Points: 179

    What are actual performance results of running these three scenarios?

  • ashish.shevale

    Ten Centuries

    Points: 1067

    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

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    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!)

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    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!)

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • DrKiller

    Right there with Babe

    Points: 780

    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? 😛

  • sknox

    SSChampion

    Points: 12241

    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.

  • Hany Helmy

    SSChampion

    Points: 13435

    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.

  • Joel Ewald

    SSCertifiable

    Points: 6010

    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.

  • Luis Cazares

    SSC Guru

    Points: 183581

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • WayneS

    SSC Guru

    Points: 95342

    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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    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 31 total)

You must be logged in to reply to this topic. Login to reply