none
Serious Performance Degradation when the Simplest Row Level Security condition is added to a role RRS feed

  • Question

  • We have a complex Tabular model through not a very large model. Strict Kimball construction where all measures are associated with a fact table and assembled star schemas. Using an optimized test query scraped from the Power BI Performance Analyzer to test performance, connecting with a role having no Row Level Security conditions defined, the query runs in ~100 ms on a 24GB machine with 4 cores. Total size of the Tabular model is ~ 10 GB. When any and I do mean any RLS condition is defined on that same role, performance of query degrades to ~8 seconds. I even added a two row table composed of a single integer column having no relationships to any other table in the model and adding a RLS condition to filter to just one row of that table caused the query to experience the same performance degradation. I would expect that an RLS condition defined for a table that had no relationships to any other table to have very little impact but I'm seeing the performance degrade to the point where our sponsor has suspended deployment.

    Have tested on both SQL Server 2017 CU16 and SQL Server 2019 CTP and have seen the same behavior. Any ideas on where I should look?

    Martin

    Yes, this is a duplicate of this post. https://social.msdn.microsoft.com/Forums/en-US/75d81abe-41c8-411b-a35e-2a09884762e3/rls-in-ssas-2017-tabular-not-ready-for-prime-time?forum=sqlanalysisservices but if you can open that one than you're better off than I am.


    Martin Mason Wordpress Blog

    Monday, August 19, 2019 10:50 PM

All replies

  • Hi Martin,

    Thanks for your question.

    Here are answers for your questions.

    >>

    Yes, this is a duplicate of this post. https://social.msdn.microsoft.com/Forums/en-US/75d81abe-41c8-411b-a35e-2a09884762e3/rls-in-ssas-2017-tabular-not-ready-for-prime-time?forum=sqlanalysisservices but if you can open that one than you're better off than I am.

    <<

    Sorry for that, this belongs to forum issue, we have reported it to related team of Microsoft, hope that they could solve it as soon as possible.

    >>

    Have tested on both SQL Server 2017 CU16 and SQL Server 2019 CTP and have seen the same behavior. Any ideas on where I should look?

    <<

    So what about other versions of SQL Server? (SQL Server 2016, 2014 etc.) Have you seen the same behavior on these versions? Or you could use tool SQL profiler to replay queries, commands that are submitted by users, and see if it has any abnormal difference.

    If other older versions of SQL Server doesn't have the same behavior, it is necessary to open a support case with Microsoft to ask for performance improvement.

    Reference

    Use SQL Server Profiler to Monitor Analysis Services

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 20, 2019 2:44 AM