Dynamic data masking feature

Question

Post reply

Dynamic data masking feature

kapil_kk

SSC-Insane

Points: 21316
More actions
January 5, 2016 at 11:41 pm

#314642

Hi all,
What are the performance impact of using dynamic data masking feature?
many thanks in advance.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 10, 2016 at 1:17 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 1

None, or negligible. This merely replaces the string being returned with some pattern.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Steve Jones - SSC Editor (5/14/2016)
None, or negligible. This merely replaces the string being returned with some pattern.

That's what a lot of people surmised about the relatively new FORMAT function, which turns out to be 44 times slower than CONVERT.

Have you done or run across any actual "million row" performance testing? An admittedly too-quick search for such a thing is coming up fruitless.

Shifting gears a bit, I know a whole lot of people have been looking forward to data masking but I'm genuinely concerned because a whole lot of people are calling it a "Security Feature" and... it is [font="Arial Black"]NOT[/font]. It's a display feature that anyone with simple DBO privs can easily burn through. It must NOT be considered to be a replacement for any kind of real data security. If data must be protected, it must be properly encrypted with at least 1 salt, period.

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

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 3

I tried a couple quick items for testing. Pulling from the same table, 1m rows, getting a masked column and the same data unmasked. Certainly I need a slightly better perf test, but a few quick tests showed raw pulls taking about the same IO and CPU, within a few (6-8) percent.

I've got it on my list to do more, but since this is very minimal processing of data, it shouldn't affect performance much. Don't forget, any work done by the instance on the data is done on the raw data. The query processor uses indexing and handles everything the same. It's just the return that has a scalar function at the end to strip out the values.

This was with the default. The partial mask might cause more work. I wouldn't expect the random mask to be problematic, as this must be similar to Newid()

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

Thanks, Steve. I appreciate it.

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