Primary keys for an OLTP database

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  December 28, 2018 at 7:29 am

  #2017500

  sqlvogel - Friday, December 28, 2018 2:27 AM

  Jeff Moden - Thursday, December 27, 2018 5:42 PM

  Ok... so what would you use for a "Natural" key for an Employee or Customer table?

  Well that's exactly the kind of question that this article ought to be addressing but it's meaningless to give a single answer without more context. All information in a database consists of symbols invented by humans or machines so personally I don't think it helps to refer to data as "natural". A unique identifier that can be used in the business process outside the database is what matters. I don't think most businesses would ask customers to log in to their website using a 32 character guid or a sequential integer.

  We get all that you have stated but you haven't answered the specific question.  I'll be more specific...

  I Have a table where I want to store just the First, Middle (could be absent or abbreviated), and last name of employees that work for a company. What would YOU assign as the Primary Key for the table?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• RonKyle

  SSC-Dedicated

  Points: 31558

  More actions

  December 28, 2018 at 7:37 am

  #2017501

  Jeff Moden - Friday, December 28, 2018 7:29 AM

  We get all that you have stated but you haven't answered the specific question.  I'll be more specific...

  I Have a table where I want to store just the First, Middle (could be absent or abbreviated), and last name of employees that work for a company. What would YOU assign as the Primary Key for the table?

  I would assign an integer, which would ideally server as the Employee Number.  We did exactly that at my last company.  My employee number was a four digit number beginning with an 8.  Most people's began with an 8.  I never really needed it, so I didn't know it by heart.  But that's better than a GUID id.  If there was an obvious natural key, however, and there often is, I would use it.

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  December 28, 2018 at 7:41 am

  #2017502

  RonKyle - Thursday, December 27, 2018 12:00 PM

  Slow may be a relative term.  A GUID to GUID join is going to perform slower than an integer to integer join all other things being equal.  Maybe a person won't notice the difference in speed for one join.  But on a busy system, this surely takes it's toll.  If a GUID is necessary, use it.  Otherwise avoid it.

  Absolutely agreed on all points, Ron.  Still, because of your predecessor, you're stuck with GUIDs.  While I agree that joining on 16 bytes is going to be slower than joining on 8 or 4, have the GUIDs caused any other performance issues for you?  And, no... I'm not asking that question to support the use of GUIDs because I'm with you in that I avoid using them as keys and for other things unless there's absolutely no better choice (and there's only a couple of rare places where that's true).

  I'm asking because I have been saddled with the mistake of GUIDs being used for every key in the database and I have a couple of ways to help (other than the obvious 16 byte penalty for joins) with performance if I know what the specific complaint is.  As you know, there are few magical wands you can wave at GUIDs to help them perform better in certain areas but I know of a couple (especially when it comes to severe fragmentation, index maintenance, reducing/eliminating page splits during inserts, and the effect those page splits take on the log file) and I'm offering to help if you're having issues in those areas.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• xsevensinzx

  One Orange Chip

  Points: 25560

  More actions

  December 28, 2018 at 7:47 am

  #2017503

  I've said it in other threads, did not like the write performance of a clustered or non-clustered GUID (non-unique) on billions of records in a single fact table within a SMP system. I had to cleanse the keys for sequentials. But, in the MPP columnstore world, I absolutely love GUID because the randomness is great for clustering the data pretty evenly across N databases without having any single database with more or less records than the others.

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  December 28, 2018 at 7:54 am

  #2017504

  xsevensinzx - Friday, December 28, 2018 7:47 AM

  I've said it in other threads, did not like the performance of a clustered GUID (non-unique) on billions of records in a single fact table within a SMP system. I had to cleanse the keys for sequentials. But, in the MPP columnstore world, I absolutely love GUID because the randomness is great for clustering the data pretty evenly across N databases without having any single database with more or less records than the others.

  Now there's an interesting aspect.  Thanks.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  December 28, 2018 at 8:02 am

  #2017506

  RonKyle - Friday, December 28, 2018 7:37 AM

  I would assign an integer, which would ideally server as the Employee Number.  We did exactly that at my last company.  My employee number was a four digit number beginning with an 8.  Most people's began with an 8.  I never really needed it, so I didn't know it by heart.  But that's better than a GUID id.  If there was an obvious natural key, however, and there often is, I would use it.

  Perfect and totally agreed on all points there, especially since the influx of new rows would be rated as "nearly static". 

  The reason for me pressing for an answer to the question is that there are a couple of people on this site that insist that numeric PKs should never be used because (and you'll "love" this), they're never used for mathematical calculations <major face-palm><deep breath><major sigh><head-desk>.  Instead, they suggest the use of a "natural" key but (apparently) refuse to identify what they would use for a proper natural key that would still meet the 6 requirements for a proper Key in SQL Server.

  Heh... thanks for restoring my faith in humanity. 😀

  How about you, @sqlvogal... since you brought up natural keys, which incited my question, what is your answer?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• xsevensinzx

  One Orange Chip

  Points: 25560

  More actions

  December 28, 2018 at 8:09 am

  #2017508

  Jeff Moden - Friday, December 28, 2018 7:54 AM

  xsevensinzx - Friday, December 28, 2018 7:47 AM

  I've said it in other threads, did not like the performance of a clustered GUID (non-unique) on billions of records in a single fact table within a SMP system. I had to cleanse the keys for sequentials. But, in the MPP columnstore world, I absolutely love GUID because the randomness is great for clustering the data pretty evenly across N databases without having any single database with more or less records than the others.

  Now there's an interesting aspect.  Thanks.

  Just for further clarification.

  You have to hash data using a key with each table. This is like adding a clustered index to define how the data is stored on disk. Being there is 60 databases with N disks per database, you want to ensure the key you select has a even distribution of that data across those databases. Thus, if you have 500 million records with random keys and 1 billion records with 0 as their key, then 1 of those 60 databases will have 1 billion records stuck in it where the other 59 databases would have evenly distributed the remaining random keys the best they could, which is likely about 8 million per database.

  If you write a query to read some of those 0 keys, you would have just 1 computer working for you versus if you wrote one for the other keys, you would have 59 databases and their computers working for you. Thus, using the GUID in place of these keys, including the 0 keys, can help evenly distribute the data evenly across all computers/databases/etc. Hopefully about 25 million per database in this example now that you have used the GUID key to help evenly distribute the data.

  Using the sequential here may be bad because it ticks the ranges forward in a linear fashion causing the distribution to always shift forward with the data as it comes into the system. The same is true for date/time.

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  December 28, 2018 at 8:59 am

  #2017522

  Eirikur Eiriksson - Thursday, December 27, 2018 4:25 AM

  Thanks for the write up Evgeny!
  😎
  One thought on the test setup, the loop iteration and the execution overhead may skew the results.I've done similar tests and found that IDENTITY is the fastest and NEWID always the slowest. Here is my test harness
  
  USE TEEST;
  GO
  SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.TBL_TEST_NEWSEQUENTIALID') IS NOT NULL DROP TABLE dbo.TBL_TEST_NEWSEQUENTIALID;
  CREATE TABLE dbo.TBL_TEST_NEWSEQUENTIALID
  (
    NSID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DFLT_DBO_TBL_TEST_NEWSEQUENTIALID_NSID DEFAULT (NEWSEQUENTIALID())
              CONSTRAINT PK_DBO_TBL_TEST_NEWSEQUENTIALID_NSID PRIMARY KEY CLUSTERED
   ,INT_VAL INT NOT NULL
  );

  IF OBJECT_ID(N'dbo.TBL_TEST_NEWID')     IS NOT NULL DROP TABLE dbo.TBL_TEST_NEWID;
  CREATE TABLE dbo.TBL_TEST_NEWID
  (
    NSID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DFLT_DBO_TBL_TEST_NEWID_NSID DEFAULT (NEWID())
              CONSTRAINT PK_DBO_TBL_TEST_NEWID_NSID PRIMARY KEY CLUSTERED
   ,INT_VAL INT NOT NULL
  );

  IF OBJECT_ID(N'dbo.TBL_TEST_IDENTITY')    IS NOT NULL DROP TABLE dbo.TBL_TEST_IDENTITY;
  CREATE TABLE dbo.TBL_TEST_IDENTITY
  (
    NSID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_IDENTITY_NSID PRIMARY KEY CLUSTERED
   ,INT_VAL INT NOT NULL
  );

  DECLARE @timer TABLE (T_TXT VARCHAR(50) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
  DECLARE @SAMPLE_SIZE  INT = 1000000;

  ---------------------------------------------------------------------
  -- FIRST RUN
  ---------------------------------------------------------------------

  INSERT INTO @timer(T_TXT) VALUES('NEWSEQUENTIALID 1');
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
       FROM T T1,T T2,T T3,T T4,T T5,T T6)
  INSERT INTO dbo.TBL_TEST_NEWSEQUENTIALID WITH (TABLOCKX) (INT_VAL)
  SELECT
    NM.N
  FROM  NUMS  NM
  OPTION (MAXDOP 1);
  INSERT INTO @timer(T_TXT) VALUES('NEWSEQUENTIALID 1');
  IF OBJECT_ID(N'dbo.TBL_TEST_NEWSEQUENTIALID') IS NOT NULL TRUNCATE TABLE dbo.TBL_TEST_NEWSEQUENTIALID;

  INSERT INTO @timer(T_TXT) VALUES('NEWID 1');
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
       FROM T T1,T T2,T T3,T T4,T T5,T T6)
  INSERT INTO dbo.TBL_TEST_NEWID WITH (TABLOCKX) (INT_VAL)
  SELECT
    NM.N
  FROM  NUMS  NM
  OPTION (MAXDOP 1);
  INSERT INTO @timer(T_TXT) VALUES('NEWID 1');
  IF OBJECT_ID(N'dbo.TBL_TEST_NEWID')     IS NOT NULL TRUNCATE TABLE dbo.TBL_TEST_NEWID;

  INSERT INTO @timer(T_TXT) VALUES('IDENTITY 1');
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
       FROM T T1,T T2,T T3,T T4,T T5,T T6)
  INSERT INTO dbo.TBL_TEST_IDENTITY WITH (TABLOCKX) (INT_VAL)
  SELECT
    NM.N
  FROM  NUMS  NM
  OPTION (MAXDOP 1);
  INSERT INTO @timer(T_TXT) VALUES('IDENTITY 1');
  IF OBJECT_ID(N'dbo.TBL_TEST_IDENTITY')    IS NOT NULL TRUNCATE TABLE dbo.TBL_TEST_IDENTITY;

  ---------------------------------------------------------------------
  -- SECOND RUN
  ---------------------------------------------------------------------

  INSERT INTO @timer(T_TXT) VALUES('NEWSEQUENTIALID 2');
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
       FROM T T1,T T2,T T3,T T4,T T5,T T6)
  INSERT INTO dbo.TBL_TEST_NEWSEQUENTIALID WITH (TABLOCKX) (INT_VAL)
  SELECT
    NM.N
  FROM  NUMS  NM
  OPTION (MAXDOP 2);
  INSERT INTO @timer(T_TXT) VALUES('NEWSEQUENTIALID 2');
  IF OBJECT_ID(N'dbo.TBL_TEST_NEWSEQUENTIALID') IS NOT NULL TRUNCATE TABLE dbo.TBL_TEST_NEWSEQUENTIALID;

  INSERT INTO @timer(T_TXT) VALUES('NEWID 2');
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
       FROM T T1,T T2,T T3,T T4,T T5,T T6)
  INSERT INTO dbo.TBL_TEST_NEWID WITH (TABLOCKX) (INT_VAL)
  SELECT
    NM.N
  FROM  NUMS  NM
  OPTION (MAXDOP 2);
  INSERT INTO @timer(T_TXT) VALUES('NEWID 2');
  IF OBJECT_ID(N'dbo.TBL_TEST_NEWID')     IS NOT NULL TRUNCATE TABLE dbo.TBL_TEST_NEWID;

  INSERT INTO @timer(T_TXT) VALUES('IDENTITY 2');
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
       FROM T T1,T T2,T T3,T T4,T T5,T T6)
  INSERT INTO dbo.TBL_TEST_IDENTITY WITH (TABLOCKX) (INT_VAL)
  SELECT
    NM.N
  FROM  NUMS  NM
  OPTION (MAXDOP 2);
  INSERT INTO @timer(T_TXT) VALUES('IDENTITY 2');
  IF OBJECT_ID(N'dbo.TBL_TEST_IDENTITY')    IS NOT NULL TRUNCATE TABLE dbo.TBL_TEST_IDENTITY;

  ---------------------------------------------------------------------
  -- THIRD RUN
  ---------------------------------------------------------------------

  INSERT INTO @timer(T_TXT) VALUES('NEWSEQUENTIALID 3');
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
       FROM T T1,T T2,T T3,T T4,T T5,T T6)
  INSERT INTO dbo.TBL_TEST_NEWSEQUENTIALID WITH (TABLOCKX) (INT_VAL)
  SELECT
    NM.N
  FROM  NUMS  NM
  OPTION (MAXDOP 0);
  INSERT INTO @timer(T_TXT) VALUES('NEWSEQUENTIALID 3');
  IF OBJECT_ID(N'dbo.TBL_TEST_NEWSEQUENTIALID') IS NOT NULL TRUNCATE TABLE dbo.TBL_TEST_NEWSEQUENTIALID;

  INSERT INTO @timer(T_TXT) VALUES('NEWID 3');
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
       FROM T T1,T T2,T T3,T T4,T T5,T T6)
  INSERT INTO dbo.TBL_TEST_NEWID WITH (TABLOCKX) (INT_VAL)
  SELECT
    NM.N
  FROM  NUMS  NM
  OPTION (MAXDOP 0);
  INSERT INTO @timer(T_TXT) VALUES('NEWID 3');
  IF OBJECT_ID(N'dbo.TBL_TEST_NEWID')     IS NOT NULL TRUNCATE TABLE dbo.TBL_TEST_NEWID;

  INSERT INTO @timer(T_TXT) VALUES('IDENTITY 3');
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
       FROM T T1,T T2,T T3,T T4,T T5,T T6)
  INSERT INTO dbo.TBL_TEST_IDENTITY WITH (TABLOCKX) (INT_VAL)
  SELECT
    NM.N
  FROM  NUMS  NM
  OPTION (MAXDOP 0);
  INSERT INTO @timer(T_TXT) VALUES('IDENTITY 3');
  IF OBJECT_ID(N'dbo.TBL_TEST_IDENTITY')    IS NOT NULL TRUNCATE TABLE dbo.TBL_TEST_IDENTITY;
  ---------------------------------------------------------------------
  -- TIMER RESULTS
  ---------------------------------------------------------------------
  SELECT
    T.T_TXT
   ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
  FROM @timer T
  GROUP BY T.T_TXT
  ORDER BY DURATION ASC;

  Results on my i5 laptop
  
T_TXT               DURATION
------------------- -----------
IDENTITY 3              2452339
IDENTITY 2              2491696
IDENTITY 1              2881321
NEWSEQUENTIALID 1       2924338
NEWSEQUENTIALID 2       2945289
NEWSEQUENTIALID 3       3414175
NEWID 1                 4309828
NEWID 2                 4363634
NEWID 3                 6505897


  Looking at the sys.dm_db_index_physical_stats
  
SELECT
  N'NEWSEQUENTIALID' AS METHOD_NAME
 ,IXPS.index_type_desc
 ,IXPS.alloc_unit_type_desc
 ,IXPS.index_depth
 ,IXPS.avg_fragmentation_in_percent
 ,IXPS.fragment_count
 ,IXPS.avg_fragment_size_in_pages
 ,IXPS.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(N'TEEST'),OBJECT_ID(N'dbo.TBL_TEST_NEWSEQUENTIALID'),NULL,NULL,'DETAILED') IXPS
WHERE IXPS.page_count > 0
UNION ALL
SELECT
  N'NEWID' AS METHOD_NAME
 ,IXPS.index_type_desc
 ,IXPS.alloc_unit_type_desc
 ,IXPS.index_depth
 ,IXPS.avg_fragmentation_in_percent
 ,IXPS.fragment_count
 ,IXPS.avg_fragment_size_in_pages
 ,IXPS.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(N'TEEST'),OBJECT_ID(N'dbo.TBL_TEST_NEWID'),NULL,NULL,'DETAILED') IXPS
WHERE IXPS.page_count > 0
UNION ALL
SELECT
  N'IDENTITY' AS METHOD_NAME
 ,IXPS.index_type_desc
 ,IXPS.alloc_unit_type_desc
 ,IXPS.index_depth
 ,IXPS.avg_fragmentation_in_percent
 ,IXPS.fragment_count
 ,IXPS.avg_fragment_size_in_pages
 ,IXPS.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(N'TEEST'),OBJECT_ID(N'dbo.TBL_TEST_IDENTITY'),NULL,NULL,'DETAILED') IXPS
WHERE IXPS.page_count > 0;


  The output
  
METHOD_NAME     index_type_desc  alloc_unit_type_desc  index_depth avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages  page_count
--------------- ---------------- --------------------- ----------- ---------------------------- --------------- -------------------------- -----------
NEWSEQUENTIALID CLUSTERED INDEX  IN_ROW_DATA           3            0.641562064156206              24           149.375                    3585
NEWSEQUENTIALID CLUSTERED INDEX  IN_ROW_DATA           3            0                              23             1                          23
NEWSEQUENTIALID CLUSTERED INDEX  IN_ROW_DATA           3            0                               1             1                           1
NEWID           CLUSTERED INDEX  IN_ROW_DATA           3           99.1434262948207              5020             1                        5020
NEWID           CLUSTERED INDEX  IN_ROW_DATA           3           95.2380952380952                21             1                          21
NEWID           CLUSTERED INDEX  IN_ROW_DATA           3            0                               1             1                           1
IDENTITY        CLUSTERED INDEX  IN_ROW_DATA           3            0.333174678724417               8           262.625                    2101
IDENTITY        CLUSTERED INDEX  IN_ROW_DATA           3            0                               7             1                           7
IDENTITY        CLUSTERED INDEX  IN_ROW_DATA           3            0                               1             1                           1   

  Interesting.  When I run it on my i5 (4 cores total, 4GB Ram allocated to SQL Server) laptop for SQL Server 2008, I come up with the following on the first run of your code...
  

  Seems to be a bit inconclusive so I ran the code again just to make sure it wasn't file growth or some other thing causing the seriously odd output.  Here are the results from the second run.
  

  Just as inconclusive, especially since the differences are no longer in an order of magnitude different.

  So, I wondered what the results would be on the 48 core, 384GB RAM, SSD fire breather at work... here are those results from the first run.  Subsequent runs did pan out roughly the same way...
  

  That appears to be more what I expected for such batch runs.  Guess I'm going to have to try a couple of the runs I did for the charts you saw for the "Black Arts Index Maintenance #1" session (those were rapid fire single row inserts rather than batch runs) that you saw on your visit to the U.S.A on the fire breather and see what happens.

  I was thinking that the real reason for NEWID taking comparatively so long (which is still less than I expected because it's 4 times wider than an INT) is there should be a sort for the NEWID run somewhere but the first blush look at the execution plans has them all the same.  I'll have to dig into some of the properties and other things there.

  Also, just in case anyone wanted to know, all 3 inserts ended up using a MAXDOP of 1 even on the fire breather.  While the SSDs did make things run twice as fast, this is proof positive that you shouldn't believe that hardware will be the ultimate solution for performance woes.  Twice as fast is nearly nothing compared to what fixing code can do.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  December 28, 2018 at 9:24 am

  #2017528

  xsevensinzx - Friday, December 28, 2018 8:08 AM

  Jeff Moden - Friday, December 28, 2018 7:54 AM

  xsevensinzx - Friday, December 28, 2018 7:47 AM

  I've said it in other threads, did not like the performance of a clustered GUID (non-unique) on billions of records in a single fact table within a SMP system. I had to cleanse the keys for sequentials. But, in the MPP columnstore world, I absolutely love GUID because the randomness is great for clustering the data pretty evenly across N databases without having any single database with more or less records than the others.

  Now there's an interesting aspect.  Thanks.

  Just for further clarification.

  You have to hash data using a key with each table. This is like adding a clustered index to define how the data is stored on disk. Being there is 60 databases with N disks per database, you want to ensure the key you select has a even distribution of that data across those databases. Thus, if you have 500 million records with random keys and 1 billion records with 0 as their key, then 1 of those 60 databases will have 1 billion records stuck in it where the other 59 databases would have evenly distributed the remaining random keys the best they could, which is likely about 8 million per database.

  If you write a query to read some of those 0 keys, you would have just 1 computer working for you versus if you wrote one for the other keys, you would have 59 databases and their computers working for you. Thus, using the GUID in place of these keys, including the 0 keys, can help evenly distribute the data evenly across all computers/databases/etc. Hopefully about 25 million per database in this example now that you have used the GUID key to help evenly distribute the data.

  Using the sequential here may be bad because it ticks the ranges forward in a linear fashion causing the distribution to always shift forward with the data as it comes into the system. The same is true for date/time.

  Good stuff right there and it's confirmation of what I told folks when they wanted to invest in an MPP appliance at one of the companies that I work do work for.  The hype (I know you already know this and have confirmed that knowledge with what you wrote above) was that MPP would make things run 30X faster.  Everyone but me was ready to loosen up their purse strings even though I didn't have much knowledge of MPP appliances but knew enough to know better.  And so I challenged the salesman on the spot... "Tell them what the necessary modifications are to the underlying data structure and code is to achieve that 30X improvement.  Then explain why the expenditure of that amount and cost of development and testing, not to mention the cost of the appliance itself, is better than spending the time to tweak the code to make it run 60 to 1000 times faster" and cited several major examples where we had done so.

  Understand, that they don't have the billions of rows in tables that you do.  They only recently (in the last year) had a database go over the 2TB mark and I just got rid of half of that data for them (with their concurrence, of course) because it was never accessed and was duplicated in other places.

  Thanks again for the awesome information.  I'll probably never have the opportunity to work on such a system as what you do.  To be honest, I'm not sure I'd want to but it would be interesting.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  December 28, 2018 at 9:41 am

  #2017533

  steve.homer@gmail.com - Friday, December 28, 2018 6:30 AM

  Jeff Moden - Thursday, December 27, 2018 8:50 AM

  While I appreciate the experiences that have made you come to such conclusions (and, to be clear, I used to believe in the same thing), most of that isn't true, although the recommendation for a proper FILL FACTOR and the use of REBUILDs for NEWID usage is absolutely spot on.

  It turns out that the larger (in terms of row count) a NEWID Clustered Index gets, the longer you can go with absolutely ZERO page splits and ZERO fragmentation.  Done properly and maintained correctly (and the current "Best Practices" for index maintenance are absolutely the worst thing to do for index maintenance, especially in this case).  I do have several demonstrable tests that exercise such indexes by adding 1,000 rows per simulated hour for 10 simulate hour per simulated day for 365 simulated days (3.65 Million rows per test and, yeah, it does take a while to run each test) and it demonstrates that you can actually go MONTHs with no page splits (not even good ones) and no fragmentation when it's done properly.  Oddly enough, it turns out the method is a fair bit faster than when using the "Append only" nature of sequential keys because even supposedly "good" page splits write more to the log file than ZERO pages splits do.

  Of course, there are disadvantages to the use of NEWID that we all know but performance of INSERTs, page splits, and fragmentation isn't a part of that (seriously contrary to popular belief).  Well... unless you use REORGANIZE as a part of your index maintenance, which removes free space when you need it the most and, very much like a really bad drug habit, the more you use it the more you need to use it because it actually perpetuates page splits and that perpetuates fragmentation and that perpetuates the need to defragment... over and over and over for the life of the table.

  I'm giving a 2 hour presentation on this and other types of indexes as well as why the current recommendations of supposed "Best Practice" index maintenance is actually causing more harm than good and it's the result of literally hundreds of hours of testing.  The next presentation will be at SQLSaturday in Cleveland on 2 Feb 2019.  If you're in the area, come see it.  I can almost guarantee you've never seen indexes or the harmful effects of current "Best Practice" index maintenance on indexes in the way that I present them.

  Hi Jeff,
  Will the presentation be available online at any point? I'm in a Cleveland, but unfortunately one on a different continent. What you're suggesting sounds intriguing.

  Thanks,
  Steve

  It's already online because I presented it at the Oct 2018 Pittsburgh SQL Saturday.  If you go through it, understand that I'm in the "continuous improvement" stage of the presentation.  There are some recommendations as to what to do but I've found ways to automate a lot of what must currently be done manually to determine which of the 6 "insert patterns" indexes fall under (which is uber important when it comes to what type of index maintenance to do and which FILL FACTOR should be used) and whether or not the index is also suffering from "ExpAnsive Updates" and whether or not that very serious problem can be remedied (especially when it comes to clustered indexes).

  I don't actually write Power Point "presentations".  I write Power Point "Books" and they're highly animated in an "Alice's Restaurant Fashion".  If you play the slide show, you can almost hear me talking because of the animations.  The presentation is broken into two sessions and, like I said, I'm in the process of modifying it with new/additional information for Cleveland.  There's also "extras" at the end of the presentation.  There are some references to code in the slides and I forgot to take those references out because it actually wasn't necessary to include much of the code.  The real "goodie" code is the new sp_IndexDNA proc I built and the spreadsheet to dump the output onto to see an index in a way like you're probably never seen an index.  Be advised that on large indexes, it can take a while for sp_IndexDNA to do it's thing.  On a 146GB clustered index on my production box, it took a little bit less than an hour to run.  I strongly recommend you rebuild the index stats on such a large index prior to running sp_IndexDNA against it because the underlying DBCC IND appears to occasionally return unlinked pages.  It's not the fault of DBCC IND... it's the fault of "timing".  A lot can happen to such a large index on a busy system in 45 minutes and, if it ends up recording a page where the next linked page has disappeared, the code will get "stuck" forever.  That's another thing I'm working on but it seems unavoidable.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 28, 2018 at 2:52 pm

  #2017557

  Jeff Moden - Friday, December 28, 2018 9:41 AM

  steve.homer@gmail.com - Friday, December 28, 2018 6:30 AM

  Jeff Moden - Thursday, December 27, 2018 8:50 AM

  While I appreciate the experiences that have made you come to such conclusions (and, to be clear, I used to believe in the same thing), most of that isn't true, although the recommendation for a proper FILL FACTOR and the use of REBUILDs for NEWID usage is absolutely spot on.

  It turns out that the larger (in terms of row count) a NEWID Clustered Index gets, the longer you can go with absolutely ZERO page splits and ZERO fragmentation.  Done properly and maintained correctly (and the current "Best Practices" for index maintenance are absolutely the worst thing to do for index maintenance, especially in this case).  I do have several demonstrable tests that exercise such indexes by adding 1,000 rows per simulated hour for 10 simulate hour per simulated day for 365 simulated days (3.65 Million rows per test and, yeah, it does take a while to run each test) and it demonstrates that you can actually go MONTHs with no page splits (not even good ones) and no fragmentation when it's done properly.  Oddly enough, it turns out the method is a fair bit faster than when using the "Append only" nature of sequential keys because even supposedly "good" page splits write more to the log file than ZERO pages splits do.

  Of course, there are disadvantages to the use of NEWID that we all know but performance of INSERTs, page splits, and fragmentation isn't a part of that (seriously contrary to popular belief).  Well... unless you use REORGANIZE as a part of your index maintenance, which removes free space when you need it the most and, very much like a really bad drug habit, the more you use it the more you need to use it because it actually perpetuates page splits and that perpetuates fragmentation and that perpetuates the need to defragment... over and over and over for the life of the table.

  I'm giving a 2 hour presentation on this and other types of indexes as well as why the current recommendations of supposed "Best Practice" index maintenance is actually causing more harm than good and it's the result of literally hundreds of hours of testing.  The next presentation will be at SQLSaturday in Cleveland on 2 Feb 2019.  If you're in the area, come see it.  I can almost guarantee you've never seen indexes or the harmful effects of current "Best Practice" index maintenance on indexes in the way that I present them.

  Hi Jeff,
  Will the presentation be available online at any point? I'm in a Cleveland, but unfortunately one on a different continent. What you're suggesting sounds intriguing.

  Thanks,
  Steve

  It's already online because I presented it at the Oct 2018 Pittsburgh SQL Saturday.  If you go through it, understand that I'm in the "continuous improvement" stage of the presentation.  There are some recommendations as to what to do but I've found ways to automate a lot of what must currently be done manually to determine which of the 6 "insert patterns" indexes fall under (which is uber important when it comes to what type of index maintenance to do and which FILL FACTOR should be used) and whether or not the index is also suffering from "ExpAnsive Updates" and whether or not that very serious problem can be remedied (especially when it comes to clustered indexes).

  I don't actually write Power Point "presentations".  I write Power Point "Books" and they're highly animated in an "Alice's Restaurant Fashion".  If you play the slide show, you can almost hear me talking because of the animations.  The presentation is broken into two sessions and, like I said, I'm in the process of modifying it with new/additional information for Cleveland.  There's also "extras" at the end of the presentation.  There are some references to code in the slides and I forgot to take those references out because it actually wasn't necessary to include much of the code.  The real "goodie" code is the new sp_IndexDNA proc I built and the spreadsheet to dump the output onto to see an index in a way like you're probably never seen an index.  Be advised that on large indexes, it can take a while for sp_IndexDNA to do it's thing.  On a 146GB clustered index on my production box, it took a little bit less than an hour to run.  I strongly recommend you rebuild the index stats on such a large index prior to running sp_IndexDNA against it because the underlying DBCC IND appears to occasionally return unlinked pages.  It's not the fault of DBCC IND... it's the fault of "timing".  A lot can happen to such a large index on a busy system in 45 minutes and, if it ends up recording a page where the next linked page has disappeared, the code will get "stuck" forever.  That's another thing I'm working on but it seems unavoidable.

  Are you going to try and present this at the SQL Saturday 830 in Colorado Springs?

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  December 28, 2018 at 4:05 pm

  #2017568

  Lynn Pettis - Friday, December 28, 2018 2:52 PM

  Jeff Moden - Friday, December 28, 2018 9:41 AM

  steve.homer@gmail.com - Friday, December 28, 2018 6:30 AM

  Jeff Moden - Thursday, December 27, 2018 8:50 AM

  While I appreciate the experiences that have made you come to such conclusions (and, to be clear, I used to believe in the same thing), most of that isn't true, although the recommendation for a proper FILL FACTOR and the use of REBUILDs for NEWID usage is absolutely spot on.

  It turns out that the larger (in terms of row count) a NEWID Clustered Index gets, the longer you can go with absolutely ZERO page splits and ZERO fragmentation.  Done properly and maintained correctly (and the current "Best Practices" for index maintenance are absolutely the worst thing to do for index maintenance, especially in this case).  I do have several demonstrable tests that exercise such indexes by adding 1,000 rows per simulated hour for 10 simulate hour per simulated day for 365 simulated days (3.65 Million rows per test and, yeah, it does take a while to run each test) and it demonstrates that you can actually go MONTHs with no page splits (not even good ones) and no fragmentation when it's done properly.  Oddly enough, it turns out the method is a fair bit faster than when using the "Append only" nature of sequential keys because even supposedly "good" page splits write more to the log file than ZERO pages splits do.

  Of course, there are disadvantages to the use of NEWID that we all know but performance of INSERTs, page splits, and fragmentation isn't a part of that (seriously contrary to popular belief).  Well... unless you use REORGANIZE as a part of your index maintenance, which removes free space when you need it the most and, very much like a really bad drug habit, the more you use it the more you need to use it because it actually perpetuates page splits and that perpetuates fragmentation and that perpetuates the need to defragment... over and over and over for the life of the table.

  I'm giving a 2 hour presentation on this and other types of indexes as well as why the current recommendations of supposed "Best Practice" index maintenance is actually causing more harm than good and it's the result of literally hundreds of hours of testing.  The next presentation will be at SQLSaturday in Cleveland on 2 Feb 2019.  If you're in the area, come see it.  I can almost guarantee you've never seen indexes or the harmful effects of current "Best Practice" index maintenance on indexes in the way that I present them.

  Hi Jeff,
  Will the presentation be available online at any point? I'm in a Cleveland, but unfortunately one on a different continent. What you're suggesting sounds intriguing.

  Thanks,
  Steve

  It's already online because I presented it at the Oct 2018 Pittsburgh SQL Saturday.  If you go through it, understand that I'm in the "continuous improvement" stage of the presentation.  There are some recommendations as to what to do but I've found ways to automate a lot of what must currently be done manually to determine which of the 6 "insert patterns" indexes fall under (which is uber important when it comes to what type of index maintenance to do and which FILL FACTOR should be used) and whether or not the index is also suffering from "ExpAnsive Updates" and whether or not that very serious problem can be remedied (especially when it comes to clustered indexes).

  I don't actually write Power Point "presentations".  I write Power Point "Books" and they're highly animated in an "Alice's Restaurant Fashion".  If you play the slide show, you can almost hear me talking because of the animations.  The presentation is broken into two sessions and, like I said, I'm in the process of modifying it with new/additional information for Cleveland.  There's also "extras" at the end of the presentation.  There are some references to code in the slides and I forgot to take those references out because it actually wasn't necessary to include much of the code.  The real "goodie" code is the new sp_IndexDNA proc I built and the spreadsheet to dump the output onto to see an index in a way like you're probably never seen an index.  Be advised that on large indexes, it can take a while for sp_IndexDNA to do it's thing.  On a 146GB clustered index on my production box, it took a little bit less than an hour to run.  I strongly recommend you rebuild the index stats on such a large index prior to running sp_IndexDNA against it because the underlying DBCC IND appears to occasionally return unlinked pages.  It's not the fault of DBCC IND... it's the fault of "timing".  A lot can happen to such a large index on a busy system in 45 minutes and, if it ends up recording a page where the next linked page has disappeared, the code will get "stuck" forever.  That's another thing I'm working on but it seems unavoidable.

  Are you going to try and present this at the SQL Saturday 830 in Colorado Springs?

  Yes I am.  In fact, I was going to PM you and ask you for your phone number so we can talk before I submit my sessions.  I think you have my address, Lynn, if that would be easier.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 28, 2018 at 5:08 pm

  #2017572

  Jeff Moden - Friday, December 28, 2018 4:05 PM

  Lynn Pettis - Friday, December 28, 2018 2:52 PM

  Jeff Moden - Friday, December 28, 2018 9:41 AM

  steve.homer@gmail.com - Friday, December 28, 2018 6:30 AM

  Jeff Moden - Thursday, December 27, 2018 8:50 AM

  While I appreciate the experiences that have made you come to such conclusions (and, to be clear, I used to believe in the same thing), most of that isn't true, although the recommendation for a proper FILL FACTOR and the use of REBUILDs for NEWID usage is absolutely spot on.

  It turns out that the larger (in terms of row count) a NEWID Clustered Index gets, the longer you can go with absolutely ZERO page splits and ZERO fragmentation.  Done properly and maintained correctly (and the current "Best Practices" for index maintenance are absolutely the worst thing to do for index maintenance, especially in this case).  I do have several demonstrable tests that exercise such indexes by adding 1,000 rows per simulated hour for 10 simulate hour per simulated day for 365 simulated days (3.65 Million rows per test and, yeah, it does take a while to run each test) and it demonstrates that you can actually go MONTHs with no page splits (not even good ones) and no fragmentation when it's done properly.  Oddly enough, it turns out the method is a fair bit faster than when using the "Append only" nature of sequential keys because even supposedly "good" page splits write more to the log file than ZERO pages splits do.

  Of course, there are disadvantages to the use of NEWID that we all know but performance of INSERTs, page splits, and fragmentation isn't a part of that (seriously contrary to popular belief).  Well... unless you use REORGANIZE as a part of your index maintenance, which removes free space when you need it the most and, very much like a really bad drug habit, the more you use it the more you need to use it because it actually perpetuates page splits and that perpetuates fragmentation and that perpetuates the need to defragment... over and over and over for the life of the table.

  I'm giving a 2 hour presentation on this and other types of indexes as well as why the current recommendations of supposed "Best Practice" index maintenance is actually causing more harm than good and it's the result of literally hundreds of hours of testing.  The next presentation will be at SQLSaturday in Cleveland on 2 Feb 2019.  If you're in the area, come see it.  I can almost guarantee you've never seen indexes or the harmful effects of current "Best Practice" index maintenance on indexes in the way that I present them.

  Hi Jeff,
  Will the presentation be available online at any point? I'm in a Cleveland, but unfortunately one on a different continent. What you're suggesting sounds intriguing.

  Thanks,
  Steve

  It's already online because I presented it at the Oct 2018 Pittsburgh SQL Saturday.  If you go through it, understand that I'm in the "continuous improvement" stage of the presentation.  There are some recommendations as to what to do but I've found ways to automate a lot of what must currently be done manually to determine which of the 6 "insert patterns" indexes fall under (which is uber important when it comes to what type of index maintenance to do and which FILL FACTOR should be used) and whether or not the index is also suffering from "ExpAnsive Updates" and whether or not that very serious problem can be remedied (especially when it comes to clustered indexes).

  I don't actually write Power Point "presentations".  I write Power Point "Books" and they're highly animated in an "Alice's Restaurant Fashion".  If you play the slide show, you can almost hear me talking because of the animations.  The presentation is broken into two sessions and, like I said, I'm in the process of modifying it with new/additional information for Cleveland.  There's also "extras" at the end of the presentation.  There are some references to code in the slides and I forgot to take those references out because it actually wasn't necessary to include much of the code.  The real "goodie" code is the new sp_IndexDNA proc I built and the spreadsheet to dump the output onto to see an index in a way like you're probably never seen an index.  Be advised that on large indexes, it can take a while for sp_IndexDNA to do it's thing.  On a 146GB clustered index on my production box, it took a little bit less than an hour to run.  I strongly recommend you rebuild the index stats on such a large index prior to running sp_IndexDNA against it because the underlying DBCC IND appears to occasionally return unlinked pages.  It's not the fault of DBCC IND... it's the fault of "timing".  A lot can happen to such a large index on a busy system in 45 minutes and, if it ends up recording a page where the next linked page has disappeared, the code will get "stuck" forever.  That's another thing I'm working on but it seems unavoidable.

  Are you going to try and present this at the SQL Saturday 830 in Colorado Springs?

  Yes I am.  In fact, I was going to PM you and ask you for your phone number so we can talk before I submit my sessions.  I think you have my address, Lynn, if that would be easier.

  Dropped you a quick email.

• xsevensinzx

  One Orange Chip

  Points: 25560

  More actions

  December 29, 2018 at 7:11 am

  #2017587

  Jeff Moden - Friday, December 28, 2018 9:24 AM

  xsevensinzx - Friday, December 28, 2018 8:08 AM

  Jeff Moden - Friday, December 28, 2018 7:54 AM

  xsevensinzx - Friday, December 28, 2018 7:47 AM

  I've said it in other threads, did not like the performance of a clustered GUID (non-unique) on billions of records in a single fact table within a SMP system. I had to cleanse the keys for sequentials. But, in the MPP columnstore world, I absolutely love GUID because the randomness is great for clustering the data pretty evenly across N databases without having any single database with more or less records than the others.

  Now there's an interesting aspect.  Thanks.

  Just for further clarification.

  You have to hash data using a key with each table. This is like adding a clustered index to define how the data is stored on disk. Being there is 60 databases with N disks per database, you want to ensure the key you select has a even distribution of that data across those databases. Thus, if you have 500 million records with random keys and 1 billion records with 0 as their key, then 1 of those 60 databases will have 1 billion records stuck in it where the other 59 databases would have evenly distributed the remaining random keys the best they could, which is likely about 8 million per database.

  If you write a query to read some of those 0 keys, you would have just 1 computer working for you versus if you wrote one for the other keys, you would have 59 databases and their computers working for you. Thus, using the GUID in place of these keys, including the 0 keys, can help evenly distribute the data evenly across all computers/databases/etc. Hopefully about 25 million per database in this example now that you have used the GUID key to help evenly distribute the data.

  Using the sequential here may be bad because it ticks the ranges forward in a linear fashion causing the distribution to always shift forward with the data as it comes into the system. The same is true for date/time.

  Good stuff right there and it's confirmation of what I told folks when they wanted to invest in an MPP appliance at one of the companies that I work do work for.  The hype (I know you already know this and have confirmed that knowledge with what you wrote above) was that MPP would make things run 30X faster.  Everyone but me was ready to loosen up their purse strings even though I didn't have much knowledge of MPP appliances but knew enough to know better.  And so I challenged the salesman on the spot... "Tell them what the necessary modifications are to the underlying data structure and code is to achieve that 30X improvement.  Then explain why the expenditure of that amount and cost of development and testing, not to mention the cost of the appliance itself, is better than spending the time to tweak the code to make it run 60 to 1000 times faster" and cited several major examples where we had done so.

  Understand, that they don't have the billions of rows in tables that you do.  They only recently (in the last year) had a database go over the 2TB mark and I just got rid of half of that data for them (with their concurrence, of course) because it was never accessed and was duplicated in other places.

  Thanks again for the awesome information.  I'll probably never have the opportunity to work on such a system as what you do.  To be honest, I'm not sure I'd want to but it would be interesting.

  Yes, you will need to alter the model to make it work in the MPP world. The prime example is the fact your hashing key can only accept a single key in most MPP systems. You cannot for example hash on more than one key. This causes you to replicate data on different hashing keys for different computational processes. Dimension tables that are below 60 unique keys or under 2 GB in size, will need to be treated differently than larger dimensional tables in how they are distributed across those 60 databases. For example, instead of hashing on a key, you have to choose replicate, which will not distribute the data across 60 databases, but replicate the entire dataset per database. When you modify that table, you will need to constantly rebuild it with SELECT TOP 1 * FROM DimTable. Dimensional tables are commonly modified when new dimensional values come into the system. Thus you have to fact that in.

  I won't even go into the design decisions needed for concurrency slots as most MPP does not allow every process to run at the same time. Queuing systems with concurrency values are needed in meaning, not only do you have to schedule your ETL, but also put them into priority order and how much currency they can use when running in batches.

  Anyways, I moved to MPP not just for the big data stuff, but also for the fact a good portion of my data is alphanumeric like GUID's. This is surely a good example of where GUID's perform the best and can be used with ease.

• Eirikur Eiriksson

  SSC Guru

  Points: 182916

  More actions

  December 29, 2018 at 7:58 am

  #2017590

  Jeff Moden - Friday, December 28, 2018 8:59 AM

  Interesting.  When I run it on my i5 (4 cores total, 4GB Ram allocated to SQL Server) laptop for SQL Server 2008, I come up with the following on the first run of your code...
  

  Seems to be a bit inconclusive so I ran the code again just to make sure it wasn't file growth or some other thing causing the seriously odd output.  Here are the results from the second run.
  

  Just as inconclusive, especially since the differences are no longer in an order of magnitude different.

  So, I wondered what the results would be on the 48 core, 384GB RAM, SSD fire breather at work... here are those results from the first run.  Subsequent runs did pan out roughly the same way...
  

  That appears to be more what I expected for such batch runs.  Guess I'm going to have to try a couple of the runs I did for the charts you saw for the "Black Arts Index Maintenance #1" session (those were rapid fire single row inserts rather than batch runs) that you saw on your visit to the U.S.A on the fire breather and see what happens.

  I was thinking that the real reason for NEWID taking comparatively so long (which is still less than I expected because it's 4 times wider than an INT) is there should be a sort for the NEWID run somewhere but the first blush look at the execution plans has them all the same.  I'll have to dig into some of the properties and other things there.

  Also, just in case anyone wanted to know, all 3 inserts ended up using a MAXDOP of 1 even on the fire breather.  While the SSDs did make things run twice as fast, this is proof positive that you shouldn't believe that hardware will be the ultimate solution for performance woes.  Twice as fast is nearly nothing compared to what fixing code can do.

  Looking forward to see the results of the rapid fire inserts, which in a way may be more realistic. As always, I'm trying to isolate and limit the effort to the problem in question by dampening any external noise in the test harness 😉
  😎
  The "Black Arts Index Maintenance #1" session is brilliant and I recommend it to anyone that has the opportunity to see it, truly an eye opener!