Optimizing RLS performance with the Query Store
In a previous post, we looked at best practices for optimizing the performance of Row-Level Security (RLS) predicates: adding relevant indexes, minimizing excessive joins, etc. This post shows you how to discover and verify empirically which configurations yield the best performance for your workload. We’ll do this by A/B testing different RLS configurations, and using the Query Store (new on Azure SQL Database and SQL Server 2016) to measure the impact. Specifically, in this post we'll test the performance of different indexes on the AdventureWorks2014 sample database while using RLS to limit employees' access to sales data based on their position in the org hierarchy.
Full demo script: https://rlssamples.codeplex.com/SourceControl/latest#RLS-Query-Store.sql
Setup: Enabling RLS in AdventureWorks2014
For this demo, we’ll use the AdventureWorks2014 sample database. You can download it here.
AdventureWorks comes with several indexes pre-created. For the purposes of this demo, we’re going to drop all existing nonclustered indexes on the Employee, SalesOrderHeader, and SalesOrderDetail tables. Normally you wouldn’t do this, but for demo purposes, we’d like to start with a clean slate and then learn which indexes are most important to our workload:
DROP INDEX AK_Employee_LoginID ON HumanResources.Employee
DROP INDEX AK_Employee_NationalIDNumber ON HumanResources.Employee
DROP INDEX AK_Employee_rowguid ON HumanResources.Employee
DROP INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
DROP INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DROP INDEX AK_SalesOrderHeader_rowguid ON Sales.SalesOrderHeader
DROP INDEX AK_SalesOrderHeader_SalesOrderNumber ON Sales.SalesOrderHeader
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
DROP INDEX IX_SalesOrderHeader_SalesPersonID ON Sales.SalesOrderHeader
DROP INDEX AK_SalesOrderDetail_rowguid ON Sales.SalesOrderDetail
DROP INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail
go
AdventureWorks has a number of salespeople, whose LoginIDs are stored in the Employee table:
SELECT e.* FROM HumanResources.Employee e
INNER JOIN Sales.SalesPerson sp ON e.BusinessEntityID = sp.BusinessEntityID
go
We’ll create a few test users for these salespeople, since AdventureWorks does not do this for us:
-- Create a few test users (salespeople) for the demo
CREATE USER stephen0 WITHOUT LOGIN -- North American Sales Manager (hierarchyid = /6/1/)
CREATE USER david8 WITHOUT LOGIN -- regular salesperson (hierarchyid = /6/1/9/)
CREATE USER amy0 WITHOUT LOGIN -- European Sales Manager (hierarchyid = /6/3/)
CREATE USER rachel0 WITHOUT LOGIN -- regular salesperson (hierarchyid = /6/3/1/)
GRANT SELECT ON Sales.SalesOrderHeader TO stephen0, david8, amy0, rachel0
GRANT SELECT ON Sales.SalesOrderDetail TO stephen0, david8, amy0, rachel0
go
Each order in the SalesOrderHeader and SalesOrderDetail tables is assigned to a salesperson. We want to ensure that salespeople can only see orders that are assigned to them, or are assigned to employees who report to them in the org hierarchy (i.e. a manager should be able to see all orders assigned to her team). So, we create the following RLS policy:
CREATE SCHEMA rls
go
CREATE FUNCTION rls.salesPersonPredicate(@SalesPersonID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
-- only see orders assigned to you, or assigned to salespeople who report to you
RETURN SELECT 1 AS accessResult
FROM HumanResources.Employee e1
INNER JOIN HumanResources.Employee e2
ON e2.OrganizationNode.IsDescendantOf(e1.OrganizationNode) = 1
WHERE e1.LoginID = 'adventure-works\' + USER_NAME()
AND e2.BusinessEntityID = @SalesPersonID
go
CREATE FUNCTION rls.salesPersonPredicate_LookupSalesPerson(@SalesOrderID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
-- only see orders assigned to you, or assigned to salespeople who report to you
-- (note: need to look up assigned SalesPersonID in SalesOrderHeader)
RETURN SELECT 1 AS accessResult
FROM HumanResources.Employee e1
INNER JOIN HumanResources.Employee e2
ON e2.OrganizationNode.IsDescendantOf(e1.OrganizationNode) = 1
INNER JOIN Sales.SalesOrderHeader soh
ON e2.BusinessEntityID = soh.SalesPersonID
WHERE e1.LoginID = 'adventure-works\' + USER_NAME()
AND soh.SalesOrderID = @SalesOrderID
go
CREATE SECURITY POLICY rls.salesPersonPolicy
ADD FILTER PREDICATE rls.salesPersonPredicate(SalesPersonID) ON Sales.SalesOrderHeader,
ADD FILTER PREDICATE rls.salesPersonPredicate_LookupSalesPerson(SalesOrderID) ON Sales.SalesOrderDetail
go
As a sanity check, we can verify that user ‘stephen0’ (the North American Sales Manager) can only see orders assigned to him or to his reports, and that user ‘david8’ (a regular salesperson) can only see orders assigned to him:
EXECUTE AS USER = 'stephen0' -- North American Sales Manager (hierarchyid = /6/1/)
SELECT * FROM Sales.SalesOrderHeader -- 2989 rows, assigned to stephen0 or his reports lower in the hierarchy
SELECT * FROM Sales.SalesOrderDetail -- 46680 rows, details for stephen0's or his reports' orders
REVERT
go
EXECUTE AS USER = 'david8' -- regular salesperson (hierarchyid = /6/1/9/)
SELECT * FROM Sales.SalesOrderHeader -- 189 rows, assigned to david8
SELECT * FROM Sales.SalesOrderDetail -- 2247 rows, details for david8's orders
REVERT
go
For a more realistic workload than the above ‘SELECT *’ queries, let’s create a stored procedure encapsulating a "critical query" that gets our best-selling products. Because of the security policy, the accessible data will be limited to the orders assigned to you or to your reports:
-- Create an example workload with a ‘critical query’ that gets the most-sold products
-- Security policy restricts this to orders assigned to you, or to your reports
CREATE PROC sp_get_top_products
AS
SELECT TOP 10 sod.ProductID, SUM(sod.OrderQty) AS TotalOrders
FROM Sales.SalesOrderDetail sod
GROUP BY sod.ProductID
ORDER BY TotalOrders DESC
go
GRANT EXECUTE ON sp_get_top_products TO stephen0, david8, amy0, rachel0
GRANT EXECUTE ON sp_get_top_products TO stephen0, david8, amy0, rachel0
go
Performance tuning with the Query Store
With the demo now set up, let's investigate performance optimizations. First, you should turn on the Query Store and clear the cache in case it was on already. We’ll also choose a fine-grained statistics aggregation interval of one minute to make performance changes easier to see for this demo.
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE=ON
go
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 1, QUERY_CAPTURE_MODE = AUTO)
go
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE CLEAR
go
Next, let’s simulate a workload by looping through our critical query ("sp_get_top_products") a few times as an example user:
EXECUTE AS USER = 'david8'
DECLARE @i int = 0
WHILE (@i < 100)
BEGIN
EXEC sp_get_top_products;
SET @i = @i + 1;
END
REVERT
go
Under the AdventureWorks2014 database in the SSMS Object Explorer, navigate to Query Store > Top Resource Consuming Queries. You’ll see that our top products query (query_id = 1, since it’s the first query we ran after clearing the cache) has a recommendation that we create a nonclustered index on Sales.SalesOrderHeader(SalesPersonID). This makes sense because the predicate function on Sales.SalesOrderDetail uses this column to determine whether the current user should have access to a given row.
But how much will adding this index actually improve our query’s performance? To verify this recommendation empirically, let’s create the recommended index and then simulate our workload again:
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_SalesPersonID ON Sales.SalesOrderHeader (SalesPersonID)
go
EXECUTE AS USER = 'david8'
DECLARE @i int = 0
WHILE (@i < 100)
BEGIN
EXEC sp_get_top_products;
SET @i = @i + 1;
END
REVERT
go
Now if we return to the Top Resource Consuming Queries and hit refresh, we can see the performance benefit: On average, the new query plan yields about 50% better performance in terms of Duration, CPU Time, Logical Reads, and Memory Consumption.
Note, if you prefer T-SQL over the SSMS UI, you can examine the performance results by querying the Query Store DMVs like this:
SELECT p.plan_id, q.query_id, qt.query_text_id, qt.query_sql_text, p.query_plan,
RANK() OVER (ORDER BY MIN(rs.first_execution_time) ASC) AS execution_order, -- identify plans by the order in which we ran them
SUM(rs.count_executions) AS total_execution_count,
AVG(rs.avg_duration) AS avg_avg_duration, -- 'average average' because Query Store already aggregates at a smaller interval
MIN(rs.min_duration) AS min_duration,
MAX(rs.max_duration) AS max_duration,
AVG(rs.avg_cpu_time) AS avg_avg_cpu_time,
AVG(rs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
AVG(rs.avg_physical_io_reads) AS avg_avg_physical_io_reads
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE 'SELECT TOP 10 p.Name%' -- only show our query
GROUP BY p.plan_id, p.query_plan, q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY execution_order ASC;
We now have empirical evidence that this new index improves our critical query's performance by around 50 percent. What about other indexes we might add? For instance, looking at both our predicate functions and the two index scans at the far right in our current query plan, we might hypothesize that adding indexes on HumanResources.Employee(LoginID) and HumanResources.Employee(OrganizationNode) could also boost performance.
We can A/B test the performance impact of adding each of these indexes using the same methods as before:
- Set up the configuration you’d like to test
- Loop through the critical query many times, to generate meaningful averages
- Pause for a minute, to ensure that we’re in a new Query Store aggregation interval
- Repeat for as many different configurations as you’d like to test
- Compare results using either the Query Store UI or DMVs
In code:
CREATE UNIQUE NONCLUSTERED INDEX AK_Employee_LoginID ON HumanResources.Employee(LoginID)
go
EXECUTE AS USER = 'david8'
DECLARE @i int = 0
WHILE (@i < 100)
BEGIN
EXEC sp_get_top_products;
SET @i = @i + 1;
END
REVERT
go
WAITFOR DELAY '00:01:00' -- to make sure we're in a new Query Store aggregation interval
go
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationNode ON HumanResources.Employee(OrganizationNode)
go
EXECUTE AS USER = 'david8'
DECLARE @i int = 0
WHILE (@i < 100)
BEGIN
EXEC sp_get_top_products;
SET @i = @i + 1;
END
REVERT
go
Note that exact numbers will vary across different machines and environments (particularly if there are other noisy processes that can cause temporary discrepancies in performance).
These results suggest that the first index we created (on SalesRepID) had a significantly positive impact on our critical query's performance, while the subsequent two indexes had relatively minor positive impacts on the performance. By considering the empirical evidence revealed by experiments like this, we can make informed, data-driven decisions about how to optimize our RLS-enabled workload.
Summary
This post has demonstrated one way to A/B test the performance of RLS predicates using the Query Store. In this demo, we focused on adding indexes to columns used for lookups and joins in the predicate function. However, the same methods can be used to test other kinds of configuration changes. For instance, you could evaluate the impact of eliminating a join in a predicate function, or of eliminating a disjunction, using a similar approach.
Questions or comments about Query Store and RLS? Please let us know in the comments below.
You can find more information about using the Query Store in this blog post and in the MSDN documentation.