Best design for OLTP tables

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

  • Very interesting!

    Thanks!

    😀

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

  • What are actual performance results of running these three scenarios?

  • 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

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

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

  • 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

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

  • 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.

  • 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

  • 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.

  • 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
  • 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

  • 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