SQL Server Batch Mode: disabling batch mode

If for whatever reason you are unable to get batch mode to run as quickly as row mode, the weapon of last resort is to disable batch mode altogether.

Previous posts in this series:

SQL Server Batch Mode: range join performance

SQL Server Batch Mode: bitmap filter performance

Now onto ways to disable batch mode:

1. Lowering the database compatibility level: batch mode was introduced with different SQL Server versions depending on if you’re using columnstore indexes or rowstore indexes. Lowering the database compatibility level will disable also disable batch mode, along with many other features and enhancements, including the cardinality estimator. I would generally not recommend this route; there are finer grained knobs that can be tweaked.
2. Database-scoped configuration: BATCH_MODE_ON_ROWSTORE. Only disables batch mode on rowstore indexes
3. Query hint: USE HINT(‘DISALLOW_BATCH_MODE’). Disables batch mode altogether (both row and columnstore indexes)
4. Modifying table/query in such a way that it prevents batch mode from occurring. Why would you want this? In some cases, we don’t have direct control over the final query that is executed (i.e. views, SSAS queries, or any framework that wraps your base query in a subquery/cte). In these scenarios, query-level hints can’t be used.
   
   As of SQL2022, MS documentation mentions a few limitations in batch mode, and this became my inspiration for finding another way to force row mode. Specifically, converting an existing an existing column to a LOB type such as nvarchar(max) will force row mode; beware this can have memory grant or un-sargability implications.
   
   (it’s quite interesting that this is yet another reason why you want to avoid using varchar/nvarchar(max); this prevents batch mode, which you generally want)
   

Source: https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-details?view=sql-server-ver17#remarks

SQL Server Batch Mode: range join performance

As of SQL Server 2022, batch mode execution still has a few edge cases where the performance is drastically worse than row mode.

Part 1 discusses bitmap filters with binary datatypes; this post examines range joins in batch mode vs row mode, and possible optimization patterns for both.

Suppose a large table LargeTable, with DateID column; This will then be joined to a smaller Period table with non-overlapping periods, to look up a date. This is pretty common in accounting/insurance domains.

OpenDateID	CloseDateID	ClosingDate
20250101	20250131	20250131
20250201	20250228	20250228
20250301	20250331	20250331

Select A.*, P.ClosingDate
from LargeTable as A 
inner join Period as P
 on A.DateID between P.OpenDateID and P.CloseDateID;

Range joins like this are notorious for bad performance, sometimes caused by bad cardinality estimates; In this case, cardinality estimates were not to blame. Somewhat surprising was how especially badly batch mode performed on these types range joins.

First the row mode range join operator:

Compare this to batch mode:

For a 39M row join, batch mode took about double the CPU time, although it did finish significantly faster. In practice, this may or may not be a good thing; if you have CPU overhead to spare, this is a non-issue; on a heavily loaded server, things will slow down to a crawl.

Of particular interest as well is the rows:batch ratio; the ~3:1 shown here is very low, and most likely due to how range join scanning is implemented in batch mode.

I tested a range of sizes for LargeTable in both row/batch mode; the results are below, with all time in measured in seconds for just the join operator:

Batch mode consistently took longer CPU time (and less elapsed time), and seemed to scale worse as LargeTable grows.

What’s the fix? In the scenario where the range table isn’t very large – simply iterate out every discrete value of the range, so that you can convert the range join to an equijoin.

DateID	ClosingDate
20250101	20250131
20250102	20250131
20250103	20250131
and so on…	25250131

the revised query becomes

Select A.*, P.ClosingDate
from LargeTable as A 
inner join Period as P
 on A.DateID = P.DateID

The results look much better for both batch and row mode:

The number of batches was significantly reduced, and both CPU/elapsed time decreased by couple orders of magnitude. Batch mode also handily beats row mode with this pattern (I didn’t test the 39M row scale in row mode out of laziness – I’m sure the reader can extrapolate the results).

Closing Thoughts

Batch mode still has a lot of quirky behaviors; range joins have bad performance regardless of row/batch mode; convert this to an equijoin whenever possible. Sometimes it’s just computationally unfeasible to materialize every possible discrete value in range.

An open question is at what scale for the Period table (or whatever target table with the range values) would the equijoin pattern no longer be more efficient than a range join. One may imagine that at sufficient scale, with non-integer discrete values (suppose instead of date, we have datetime, down to some tiny interval), it is not CPU or memory efficient to perform the equijoin (disregarding the cost of materializing every value).

SQL Server Batch Mode: bitmap filter performance

As of SQL Server 2022, batch mode execution still has a few edge cases where the performance is drastically worse than row mode.

One of the interesting ones I’ve encountered involves the bitmap filter. Joins on certain types of columns will force batch mode into choosing a very inefficient plan. This was mentioned in this blog post for string types (fixed in SQL 2019 RTM); In my case it was a join on a binary datatype that was causing the issue.

In row mode, the bitmap probe is pushed down to the Clustered Index scan operator (bitmap filtering takes place at the leaf node level):

The equivalent batch mode plan cannot push this predicate down to the Clustered Index scan; the filtering takes place at a later stage:

Another interesting anomaly is that the batch mode CI scan operator seemingly severely underestimates the actual rows:

This is apparently an expected artifact of showplan for bitmap filter in batch mode:

The query optimizer does not make cost-based choices about the position of a batch mode bitmap filter on the probe side of the hash join. It simply assumes that the selectivity of the bitmap will apply to all child operators on the probe side. In reality, the bitmap is only pushed down the probe side once a single final execution plan has been selected by the optimizer. If the bitmap cannot be pushed all the way down to a leaf operator, cardinality estimates will look a bit strange. This is a trade-off that might be improved in future.

So what’s the actual performance penalty by not pushing down the bitmap filter predicate?

Row Mode – CI Scan CPU time: 173,568 ms

Batch Mode – CI Scan CPU time: 2,460,571 ms

SQL Server AG Log Send Rate

A few years back our SQL Server data warehouse exhibited a rather odd behavior: The Availability Group log send rates would slow to a crawl when there’s a large number of databases with a high log send queue.  I don’t have the exact numbers anymore, but when the replication was suspended for all but 3-4 databases, the total throughput shot through the roof.  We never determined the root culprit, (could have been SQL engine/network protocol/storage subsystem), but I heavily suspect some SQL issue with the threadpool.

System Configuration was roughly~
2x SQL 2016 Enterprise in Always On asynch mode

120+ databases of all sizes, 300TB+ total data

Each server physical machines in WSFC

96 physical cpu cores/6TB ram

2x 10Gbps nics

Dedicated Dell EMC XIO all flash storage

This patterned happened more than a few times, usually after one of the servers became unresponsive, allowing the log send queue to build up. DBAs eventually started “juggling” the suspend/resume button for a handful of databases at a time.  I wrote the below script to help manage this aspect; thankfully after a hardware upgrade, we’ve had much better server stability, and the send queues never accumulates.

/*
	Purpose:
	--------
	This script is designed to manage and optimize the log send queue for databases participating in SQL Server Always On Availability Groups (AGs). 
	It prioritizes log send for a specific database (or the one with the largest queue), suspends synchronization for others, and resumes them in order 
	to reduce the overall log send queue size efficiently.

	Key Features:
	-------------
	- Snapshots current log send queue sizes for all secondary replicas in synchronizing state.
	- Optionally prioritizes a specific database for log send, or selects the one with the largest queue.
	- Suspends HADR synchronization for all AG databases, then resumes it for the prioritized database.
	- Polls the log send queue size for the active database, waiting until it is reduced to zero.
	- Iteratively resumes synchronization for the next database with the largest outstanding log send queue above a defined threshold.
	- Once all queues are below the threshold, resumes synchronization for all databases.
	- Includes a debug mode to print, but not execute, the generated T-SQL statements.

	Parameters:
	-----------
	@IsDebug                : BIT      - If set to 1, prints generated SQL statements instead of executing them.
	@PrioritizeDB           : VARCHAR  - Name of the database to prioritize for log send. If empty, selects the one with the largest queue.
	@ThresholdMBToStop      : BIGINT   - Threshold (in MB) below which the script will stop prioritizing log send for databases.

	Temporary Objects:
	-----------------
	#AGInfo                 : Table    - Stores snapshot of AG database log send queue sizes and related metadata.

	Usage Notes:
	------------
	- Ensure all AG databases are in the SYNCHRONIZING state before running this script.
	- Only one instance of this script should be running at a time to avoid conflicts.
	- The script is intended for use by experienced DBAs familiar with Always On AG internals.
	- Review and test in a non-production environment before use in production.

	Author:
	-------
	Xian Wang

*/
set nocount on;

declare @IsDebug bit = 1;
--Initial DB to prioritize log send
declare @PrioritizeDB varchar(200) = '';
--Stop = this script when no DB has more than this amount of log send queue
declare @ThresholdMBToStop bigint = 10000; 

declare @sqlToExecute nvarchar(max)='';
declare @ObjectName nvarchar(100);

--snapshot LogSendQueue sizes
drop table if exists #AGInfo;

SELECT AGName=ag.NAME 
	,AGReplica=ar.replica_server_name
	,DBName=DB_NAME(drs.database_id)
	,ars.is_local
	,role_desc=CASE 
		WHEN ars.role_desc IS NULL
			THEN N'DISCONNECTED'
		ELSE ars.role_desc
		END
	,AGMode=ar.availability_mode_desc
	,SyncState=drs.synchronization_state_desc
	,LogSendQueueSizeMB=log_send_queue_size/1024
into #AGInfo	
FROM sys.availability_groups AS ag
INNER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id
INNER JOIN sys.dm_hadr_database_replica_states drs ON ag.group_id = drs.group_id
	AND drs.replica_id = ars.replica_id
INNER JOIN sys.availability_group_listeners AS agl ON agl.group_id = ars.group_id
WHERE role_desc = 'SECONDARY' and drs.synchronization_state_desc = 'SYNCHRONIZING';
and (redo_queue_size/1024) < 5000;
/*

--Attempt to check if any other instances of this script is running, by looking at the current sync state

declare @SuspendedSyncStateCount int;
select @SuspendedSyncStateCount = count(1) from #AGInfo where SyncState = 'NOT SYNCHRONIZED';

if(@SuspendedSyncStateCount > 0)
	THROW 51000, 'Please ensure all AG databases are in the SYNCHRONIZING state, and that no other instances of this script is running, prior to running this script', 1; 
else
*/

declare ObjectCursor cursor static for
select 	DBName from #AGInfo;

--First, suspend HADR synch for all Dbs
open ObjectCursor
fetch next from ObjectCursor into @ObjectName
while @@fetch_status = 0

begin 
	set @sqlToExecute += 'ALTER DATABASE ' + quotename(@ObjectName) + ' SET HADR SUSPEND; '  ;
	
	fetch next from ObjectCursor into @ObjectName
end
close ObjectCursor;
deallocate ObjectCursor;

print @sqlToExecute;				
if @IsDebug = 0 exec sp_executesql @sqlToExecute;

/*
Now, start HADR synch for either the priority db, 
or, if not provided, the DB with largest outstanding log send queue
*/
if nullif(@PrioritizeDB,'') is null
			/*
			SELECT top(1) @ObjectName=DB_NAME(drs.database_id)			
			FROM sys.dm_hadr_availability_replica_states AS ars
			INNER JOIN sys.dm_hadr_database_replica_states drs ON drs.replica_id = ars.replica_id
			left join sys.dm_os_performance_counters dop on dop.instance_name = DB_NAME(drs.database_id) and counter_name IN ('Log Send Queue')
			WHERE ars.role_desc = 'SECONDARY'
			order by (dop.cntr_value/1024) desc;
			*/
			SELECT top(1) @ObjectName=DBName from #AGInfo order by LogSendQueueSizeMB desc;
else 
	set @ObjectName = @PrioritizeDB;

set @sqlToExecute = 'ALTER DATABASE ' + QUOTENAME(@ObjectName) +  ' SET HADR RESUME;'
print @sqlToExecute;				
if @IsDebug = 0 exec sp_executesql @sqlToExecute;
	
/*
Poll every x seconds to check whether send queue has decreased to 0
*/
declare @CurrentDBSendQueueMB bigint=0;
declare @NextDBToPrioritize varchar(200);
declare @NextDBToPrioritizeLogSendQueueMB bigint;
			
while (1=1)
begin
	/*
	SELECT top(1) @CurrentDBSendQueueMB=ISNULL(dop.cntr_value/1024,0)
	FROM sys.dm_os_performance_counters dop 
	where counter_name IN ('Log Send Queue') and dop.instance_name = @ObjectName;
	*/
	SELECT @CurrentDBSendQueueMB=(isnull(log_send_queue_size,0)/1024)	
	FROM sys.dm_hadr_database_replica_states as drs
	where DB_NAME(drs.database_id) =  @ObjectName
	and synchronization_state_desc = 'SYNCHRONIZING';

	if @CurrentDBSendQueueMB <> 0 
		waitfor delay '00:02:00';
		
	else --send queue for this DB dropped to 0. we can activate the DB with the next largest queue.
		begin
			/*
			SELECT top(1) 
				@NextDBToPrioritize=DB_NAME(drs.database_id)
				,@NextDBToPrioritizeLogSendQueueMB=ISNULL(dop.cntr_value/1024,0)
			FROM sys.dm_hadr_availability_replica_states AS ars
			INNER JOIN sys.dm_hadr_database_replica_states drs ON drs.replica_id = ars.replica_id
			left join sys.dm_os_performance_counters dop on dop.instance_name = DB_NAME(drs.database_id) and counter_name IN ('Log Send Queue')
			WHERE ars.role_desc = 'SECONDARY' and drs.synchronization_state_desc = 'NOT SYNCHRONIZING' and (dop.cntr_value/1024) > @ThresholdMBToStop
			order by (dop.cntr_value/1024) desc;*/
			
			delete from #AGInfo where DBName = @ObjectName;
			
			SELECT top(1) @NextDBToPrioritize=DBName 
			from #AGInfo 
			where LogSendQueueSizeMB > @ThresholdMBToStop
			order by LogSendQueueSizeMB desc 
			
			
			if(nullif(@NextDBToPrioritize, '') is null) --no more work to do, break loop
				break;
			else 
				begin
					--set @sqlToExecute = 'ALTER DATABASE ' + QUOTENAME(@ObjectName) +  ' SET HADR SUSPEND;'
					set @sqlToExecute = ' ALTER DATABASE ' + QUOTENAME(@NextDBToPrioritize) +  ' SET HADR RESUME;'
					
					print @sqlToExecute;				
					if @IsDebug = 0 exec sp_executesql @sqlToExecute;	
				end
		end		
end

--cleanup. Resume all synchronization
declare ObjectCursor cursor static for
select 	DBName=DB_NAME(drs.database_id)
/*
	,AGName=ag.NAME 
	,AGReplica=ar.replica_server_name

	,ars.is_local
	,role_desc=CASE 
		WHEN ars.role_desc IS NULL
			THEN N'DISCONNECTED'
		ELSE ars.role_desc
		END
	,AGMode=ar.availability_mode_desc
	,SyncState=drs.synchronization_state_desc
	*/	
FROM sys.availability_groups AS ag
INNER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id
INNER JOIN sys.dm_hadr_database_replica_states drs ON ag.group_id = drs.group_id
	AND drs.replica_id = ars.replica_id
INNER JOIN sys.availability_group_listeners AS agl ON agl.group_id = ars.group_id
WHERE role_desc = 'SECONDARY' and drs.synchronization_state_desc = 'NOT SYNCHRONIZING';

--First, suspend HADR synch for all Dbs
open ObjectCursor
fetch next from ObjectCursor into @ObjectName
while @@fetch_status = 0

begin 
	set @sqlToExecute += 'ALTER DATABASE ' + quotename(@ObjectName) + ' SET HADR RESUME; '  ;
	
	fetch next from ObjectCursor into @ObjectName
end
close ObjectCursor;
deallocate ObjectCursor;

print @sqlToExecute;				
if @IsDebug = 0 exec sp_executesql @sqlToExecute;

Retrieving SQL Server Product key from Registry (all versions)

In a previous post, I modified an existing script by Jacob Bindslet that retrieves the SQL Server product key stored in the registry.  The modified script worked for SQL Server 2012 only.  Since then, SQL 2014 and SQL 2016 has been released, and inevitably people will misplace product keys. I’ve decided to cleanup/revise the script so that it can run for all versions of SQL Server 2005 and above.

The script scans through the possible registry locations for the different SQL Server versions, and outputs all keys it finds by version.  So far I’ve been able to test it for 2008R2/2012/2014.

## function to retrieve the license key of a SQL 2012 Server.
## by Jakob Bindslet (jakob@bindslet.dk)
## 2012/2014/2016 Modification by Xian Wang

function Get-SQLserverKey {

    param ($targets = ".")
    $hklm = 2147483650 #HK_LOCAL_MACHINE
    $regPath = $null
    $baseRegPath = "SOFTWARE\Microsoft\Microsoft SQL Server\"

    ##SQL2016 130
    ##SQL2014 120
    ##SQL2012 110
    ##SQL2008R2 105
    ##SQL2008 100
    ##SQL2005 90
    $sqlVersionArray = "90","100","105","110","120","130"

    $regValue1 = "DigitalProductId"
    $regValue2 = "PatchLevel"
    $regValue3 = "Edition"

    ##loop through all Hosts
    Foreach ($target in $targets) {

        ##loop through all potential SQL versions
        Foreach($sqlVersion in $sqlVersionArray) {
            $regPath = $baseRegPath + $sqlVersion + "\Tools\Setup"

            $productKey = $null
            $win32os = $null
            $wmi = [WMIClass]"\\$target\root\default:stdRegProv"
            $data = $wmi.GetBinaryValue($hklm,$regPath,$regValue1)

            if($data.uValue -ne $null) {
                [string]$SQLver = $wmi.GetstringValue($hklm,$regPath,$regValue2).svalue
                [string]$SQLedition = $wmi.GetstringValue($hklm,$regPath,$regValue3).svalue

                $binArray = $null

                #Array size is dependant on SQL Version
                if([convert]::ToInt32($sqlVersion,10) -gt 105) {
                    $binArray = ($data.uValue)[0..16]
                }
                else {
                    $binArray = ($data.uValue)[52..66]
                }

                $charsArray = “BCDFGHJKMPQRTVWXY2346789”.toCharArray()

                ## decrypt base24 encoded binary data
                For ($i = 24; $i -ge 0; $i--) {
                    $k = 0
                    For ($j = 14; $j -ge 0; $j--) {
                    $k = $k * 256 -bxor $binArray[$j]
                    $binArray[$j] = [math]::truncate($k / 24)
                    $k = $k % 24
                    }
                    $productKey = $charsArray[$k] + $productKey
                    If (($i % 5 -eq 0) -and ($i -ne 0)) {
                        $productKey = "-" + $productKey
                    }
                }
                $win32os = Get-WmiObject Win32_OperatingSystem -computer $target
                $obj = New-Object Object
                $obj | Add-Member Noteproperty Computer -value $target
                $obj | Add-Member Noteproperty OSCaption -value $win32os.Caption
                $obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture
                $obj | Add-Member Noteproperty SQLver -value $SQLver
                $obj | Add-Member Noteproperty SQLedition -value $SQLedition
                $obj | Add-Member Noteproperty ProductKey -value $productkey
                $obj
            }
        }
    }
}
##Dummyproof local execution for people who don't PowerShell
Get-SQLserverKey

			

SQL Server Query Optimizer – when joins are ignored

The query optimizer in SQL Server is very powerful and smart – provided you give it enough information and clues to do its job.  One recent discovery caught me by surprise – the fact that SQL Server can skip performing joins altogether in certain scenarios.

I came upon this while reviewing the definition of views in Dynamics CRM, most of which look like this:

from [AccountBase]
left join [CustomerAddressBase] XXaddress1 on ([AccountBase].[AccountId] = XXaddress1.ParentId and XXaddress1.AddressNumber = 1)
left join [CustomerAddressBase] XXaddress2 on ([AccountBase].[AccountId] = XXaddress2.ParentId and XXaddress2.AddressNumber = 2)
left join [AccountBase] [account_master_account] on ([AccountBase].[MasterId] = [account_master_account].[AccountId])
left join [LeadBase] [account_originating_lead] on ([AccountBase].[OriginatingLeadId] = [account_originating_lead].[LeadId])
left join [AccountBase] [account_parent_account] on ([AccountBase].[ParentAccountId] = [account_parent_account].[AccountId])
left join [ContactBase] [account_primary_contact] on ([AccountBase].[PrimaryContactId] = [account_primary_contact].[ContactId])
left join [EquipmentBase] [equipment_accounts] on ([AccountBase].[PreferredEquipmentId] = [equipment_accounts].[EquipmentId])
left join ...
left join ...

and so forth. Surprisingly, when viewing the execution plan, entire tables were skipped if no columns from those tables were referenced. Now, how is that possible? The result of a join affects the cardinality of the result set; therefore, the criteria for skipping a join cannot be just not selecting columns from a certain table. The key, it turns out, is that the Query Optimizer must be able to guarantee the a one-to-one relationship between the two join tables.

One way SQL Server can guarantee this is if the right hand table has a unique key on the join column(s). Take for example, a table with this unique index:

Running this query and specifying a left join on both columns of the unique key, you can see the query optimizer realizes there’s no need to actually perform the left join:

But if the join condition on the 2nd column is removed, the query optimizer realizes this is now a many-to-many relationship, and not performing the join would be semantically incorrect:

Not surprisingly, having a foreign key relationship between the join tables also satisfies SQL Server’s criteria of not performing the join. In fact, further optimizations can be made when there’s a trusted foreign key relationship. Playing with the AdventureworksDW database, inner joins can be skipped:

As can where exists conditions:

Of course, this is because SQL Server checks the existence of value against the target table when the record is inserted; effectively, this is improving query performance by transferring the workload to the insert/update/delete phase.

Curiously, if I select the join column from the joined table, SQL still thinks it has to perform the join, even though there is no difference between FIS.OrderDateKey and dd.DateKey in this scenario. I suppose the Query Optimizer isn’t omniscient.

The best use case for this technique is probably improving performance of large views that join to a lot of tables; this is often the case when abstracting complicated schemas from end users. For joins to complicated subqueries or CTEs expressions in the view, I’ve also successfully used this technique by creating a materialized view with a unique index, thereby guaranteeing to the query optimizer that the result set will be a one-to-one relationship.

A better way to write MDX Period over Period calculations

Period over Period calculations, which compare the results of one time period against a previous time period, are extremely useful in the financial world. Everyone’s interested in growth since last month/quarter/year.  Most MDX implementations that I’ve seen use the ParallelPeriod function, which

Returns a member from a prior period in the same relative position as a specified member

The typical approach would be to create separate Year over Year, Quarter over Quarter, and Month over Month calculations. For example, to create a Year over Year calculation for the previous year:

This script first calculates the amount for the current member of the hierarchy:
([Date].[Calendar].CurrentMember, [Measures].[Amount])

Then finds the previous member of this of a specific level in this hierarchy using the ParallelPeriod() function, and subtract that from the current member:
(ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Amount])

Here’s how that looks in excel, along with a demonstration of what happens if you try to use this calculations with other levels in the date dimension:

In essence, using this approach means we’d have to create a calculation for every level of the date hierarchy; this is tedious and a ton of maintenance for what amounts the same logic. Besides, it would more user-friendly to have one calculation that works for ALL levels in a natural date hierarchy. But how can this be done if the first parameter for ParallelPeriod() must accept a specific level argument? The trick here is to use .LEVEL property to dynamically return the level of the current member in the hierarchy:

([Date].[Calendar].CurrentMember, [Measures].[Amount])
-
(ParallelPeriod(
[Date].[Calendar].CurrentMember.Level,
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Amount])

Here’s how that looks in excel:

Lastly, since this calculation exclusively looks at the previous member of the hierarchy (previous year, previous month, etc), we can write the MDX expression even more elegantly using the PrevMember property:

([Date].[Calendar].CURRENTMEMBER,[Measures].[Amount])-
([Date].[Calendar].CURRENTMEMBER.PREVMEMBER,[Measures].[Amount])

SSAS Dynamic Security invalidates Aggregations

Analysis Services role-based security is great –  it gives us the flexibility to define sets of data that users should or shouldn’t see.  And when the number of different possible set combinations get too unwieldy to handle, we can use data driven dynamic security to meet all of our data security requirements in a single role.  To top it all off, in most cases there is very little perceived performance impact: upon connecting to the cube, SSAS creates a subcube of the data that you’re supposed to see.

That is, no performance impact until you check this little box:

Visual Totals means the fact measure aggregates only on members that the user is allowed to see; this is the expected behavior in almost all cases.  However, this also means any aggregations made at a higher granularity level than the attribute used for security is no longer valid.  Here’s a contrived example : we have aggregations designed at the Year level, which pre-calculates the sum for January….December.  Now, if we use the Month attribute to secure our cube, and disallow users from seeing September, that pre-calculated sum is no longer valid; SSAS now has to calculate the Year level from at least the month level.

The moral of the story is, be very careful about defining dynamic security using low granularity attributes.  It WILL invalidate most aggregations.  I don’t see the performance aspect of dynamic security mentioned whatsoever in most how-to articles.  In most cases, the performance hit is an acceptable compromise when it means less maintenance for the developer.  However, if performance considerations are critical, then less elegant solutions (creating separate cubes) could come into play.

As an aside, I would love to see SSAS be smarter about using aggregations.  In the previous example, SASS has to throw away the pre-calculated aggregation at the Year level due to just one member missing at the month level; it now has to calculate the Year total from the 11 Months that the user can see.  It would be a lot more elegant to just subtract September from the pre-calculated Year aggregation.  This becomes especially important in large Parent-Child hierarchies that can only aggregate at the lowest and highest levels.

TL;DR: Dynamic security with visual totals invalidates all aggregations designed at higher granularities.

Ragged Hierarchies and HideMemberIf: an in-depth look

One of the most poorly-documented features I’ve come across in Analysis Services is the handling of ragged/unbalanced hierarchies. I’m not going to write about parent-child hierarchies, which may be more intuitive to model but are terrible for performance. Instead, I’ll provide an analysis of the various options of the HideMemberIf functionality, and for which data modeling scenarios each option will work when clients set MDX COMPATIBILITY=1. The end-goal is not having to drill through repeating/null intermediate levels in client tools which almost always set MDX COMPATIBILITY=1.

Here’s the test query/data we’ll be working with:

SELECT 'Scenario 1' AS Scenario, 'Repeat lowest level' AS [Scenario Description], 'Joe CEO' AS Level1, 'Secretary' AS Level2, 'Secretary' AS Level3,  'Secretary' AS Level4
UNION ALL
SELECT 'Scenario 2' AS Scenario, 'Repeat lowest parent level' AS [Scenario Description], 'Joe CEO' AS Level1, 'Joe CEO' AS Level2, 'Joe CEO' AS Level3, 'Secretary' AS Level4
UNION ALL
SELECT 'Scenario 3' AS Scenario, 'Replace lowest levels with NULL' AS [Scenario Description], 'Joe CEO' AS Level1, 'Secretary' AS Level2, NULL AS Level3, NULL AS Level4
UNION ALL
SELECT 'Scenario 4' AS Scenario, 'Replace intermediate levels NULL' AS [Scenario Description], 'Joe CEO' AS Level1, NULL AS Level2, NULL AS Level3, 'Secretary' AS Level4

Now, we’ll set up the attribute relationships in our test dimension like this:

and create 4 separate hierarchies, each with a different HideMemberIf option:

And here are the results in Excel (I’ve highlighted the scenarios that work):

Couple of things stand out here:

• In this simple dataset, {ParentName & OnlyChildWithParentName} produce the same results, as does {NoName & OnlyChildWithNoName}
• We can sense a general pattern from the combination of options that work: we need to shift all levels “up”, and then either duplicate the lowest levels  or replace them with NULLs.

One disadvantage of modeling your data this way is that the levels in your hierarchies may no longer make sense, e.g.  a Country -> State -> City hierarchy will have “Washington DC” at the State level.  I’ll argue that in hierarchies where each level has a distinct semantic meaning, we should not hide levels; after all, Washington DC is a city, not a state.  In these cases, users may very well expect a NULL for the state level.  However, in hierarchies that DO represent a generic structure such as organizational report, where level names are more abstract e.g. Level 1 > Level 2 > Level 3, hiding levels make sense.  It alleviates the nuisance of users drilling down 10 levels of “Suzy Secretary”.

Note that using HideMemberIf also incurs a performance hit, though nowhere as much as parent-child hierarchies.  I may explore the query performance implications in a future post.

SSRS Tip: Use variables to store formatting expressions

SSRS provides a rich set of formatting options that allows you to control virtually every aspect of a report. Often times you’ll find that these formatting expressions are repeated throughout the report, and having to manually code this numerous times for each affected column.

As a good practice, if I find that the same formatting expression is used more than once, I’ll define a report variable for the format expression, and use that to drive the formatting of multiple columns. Let’s say we want to round a percentage figure to a whole number. Instead of  hard coding it like this:

You can create a report variable:

 

 

and then set the format like this:

 

 

Besides not repeating the same expression in multiple places, we can now also change the formatting for all affected columns by altering one variable.   More innovative uses might entail populating formatting variables with run-time parameters.