Reason why the SLOTS XECustomizableAttribute of package0.synchronous_bucketizer & package0.asynchronous_bucketizer seem to be interpreted incorrectly?

  • Article

Some friends of mine and I were trying to build some monitoring leveraging the XE bucketizer targets. During the exercise, we encountered that the value we were specifying in the SLOTS attribute was not exactly what was being used in the end.

For example, we ran this one event session definition:

CREATE event session LockCounts ON SERVER

ADD EVENT sqlserver.lock_acquired (

ACTION (sqlserver.sql_text)

WHERE database_id = 10)

ADD TARGET package0.synchronous_bucketizer (

SET slots = 10, source_type=1, source='sqlserver.sql_text')

And noticed that it was actually creating 16 (not 10) slots. While when we ran this:

CREATE event session LockCounts ON SERVER

ADD EVENT sqlserver.lock_acquired (

ACTION (sqlserver.sql_text)

WHERE database_id = 10)

ADD TARGET package0.synchronous_bucketizer (

SET slots = 20, source_type=1, source='sqlserver.sql_text')

We noticed that it was creating 32 buckets. So we thought it could have been due to a bug in the way the value was being interpreted. It was actually reasonable to think that it was treating our decimal input as hexadecimal input.

Well, that is not the case. What SQL Server is actually doing is rounding the specified slot number up to the next power of 2 so that the bucketing process is quicker (more efficient).

I’ve asked the product team to document it so that one knows what to expect out of it.