Dynamic data masking feature

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

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • This was removed by the editor as SPAM

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

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