Primary keys for an OLTP database

Question

Post reply

Primary keys for an OLTP database

Viewing 15 posts - 31 through 45 (of 54 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

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)
Intro to Tally Tables and Functions

RonKyle SSC-Dedicated Points: 31482 More actions · Answer 2

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: 1000539 More actions · Answer 3

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)
Intro to Tally Tables and Functions

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 4

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: 1000539 More actions · Answer 5

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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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)
Intro to Tally Tables and Functions

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 7

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: 1000539 More actions · Answer 8

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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

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)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 11

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: 1000539 More actions · Answer 12

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)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 13

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 · Answer 14

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: 182631 More actions · Answer 15

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!