Bitwise operations

There are still occasions when bit level manipulation is required in SQL Server.

One such place is the execution plan set options. If we return the plan attributes for a plan handle via: –


SELECT * FROM sys.dm_exec_plan_attributes(0x050006004108CF0D60ABDC3A0C00000001000000000000000000000000000000000000000000000000000000);

We get something like: –

Plan attributes

Each set option is assigned a unique value of 2 raised to the power of incrementing integers e.g. 2,4,8,16 etc. The full list is defined on the sys.dm_exec_plan_attributes MSDN page. Therefore any combination of set options can be defined in one 4-byte integer. This makes interpreting the results at a glance difficult and querying them more complex. The query is performed via the Bitwise AND operator. So for the example above where set_options = 4347, we would perform the following T-SQL select: –


WITH cteSetOptions (SetOption,Value) AS (
SELECT 'ANSI_PADDING',1 UNION ALL
SELECT 'Parallel Plan',2 UNION ALL
SELECT 'FORCEPLAN',4 UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL',8 UNION ALL
SELECT 'ANSI_WARNINGS',16 UNION ALL
SELECT 'ANSI_NULLS',32 UNION ALL
SELECT 'QUOTED_IDENTIFIER',64 UNION ALL
SELECT 'ANSI_NULL_DFLT_ON',128 UNION ALL
SELECT 'ANSI_NULL_DFLT_OFF',256 UNION ALL
SELECT 'NoBrowseTable',512 UNION ALL
SELECT 'TriggerOneRow',1024 UNION ALL
SELECT 'ResyncQuery',2048 UNION ALL
SELECT 'ARITH_ABORT',4096 UNION ALL
SELECT 'NUMERIC_ROUNDABORT',8192 UNION ALL
SELECT 'DATEFIRST',16384 UNION ALL
SELECT 'DATEFORMAT',32768 UNION ALL
SELECT 'LanguageID',65536 UNION ALL
SELECT 'UPON',131072 UNION ALL
SELECT 'ROWCOUNT',262144)

SELECT [Set Option] = SetOption + ' option is set'
FROM cteSetOptions
WHERE Value & 4347 = Value
ORDER BY [Set Option];

Giving the results: –

set option results

I have created a table valued function to pivot and return all plan attributes (both those defined in the set options single attribute and other explicit options).

First create the dbo.SetOptions table in the DBA database: –


USE DBA;

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM sys.tables WHERE [object_id] = OBJECT_ID(N'dbo.SetOptions'))
BEGIN;
DROP TABLE dbo.SetOptions;
END;

CREATE TABLE dbo.SetOptions (
SetOption VARCHAR(128) NOT NULL,
Value INT NOT NULL
);

INSERT INTO dbo.SetOptions
SELECT 'ANSI_PADDING',1 UNION ALL
SELECT 'Parallel Plan',2 UNION ALL
SELECT 'FORCEPLAN',4 UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL',8 UNION ALL
SELECT 'ANSI_WARNINGS',16 UNION ALL
SELECT 'ANSI_NULLS',32 UNION ALL
SELECT 'QUOTED_IDENTIFIER',64 UNION ALL
SELECT 'ANSI_NULL_DFLT_ON',128 UNION ALL
SELECT 'ANSI_NULL_DFLT_OFF',256 UNION ALL
SELECT 'NoBrowseTable',512 UNION ALL
SELECT 'TriggerOneRow',1024 UNION ALL
SELECT 'ResyncQuery',2048 UNION ALL
SELECT 'ARITH_ABORT',4096 UNION ALL
SELECT 'NUMERIC_ROUNDABORT',8192 UNION ALL
SELECT 'DATEFIRST',16384 UNION ALL
SELECT 'DATEFORMAT',32768 UNION ALL
SELECT 'LanguageID',65536 UNION ALL
SELECT 'UPON',131072 UNION ALL
SELECT 'ROWCOUNT',262144;

ALTER TABLE dbo.SetOptions ADD CONSTRAINT pkSetOptions PRIMARY KEY CLUSTERED (SetOption) WITH FILLFACTOR = 100;
ALTER TABLE dbo.SetOptions ADD CONSTRAINT ukSetOptions UNIQUE NONCLUSTERED (Value) WITH FILLFACTOR = 100;
GO

Then create the dbo.fn_Plan_Attributes function: –


USE DBA;
GO

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;

IF EXISTS (SELECT 1 FROM sys.sql_modules WHERE [object_id] = OBJECT_ID(N'dbo.fn_Plan_Attributes'))
BEGIN;
DROP FUNCTION dbo.fn_Plan_Attributes;
END;
GO

/*
Name: dbo.fn_Plan_Attributes

(C) Andy Jones

mailto:andrew@aejsoftware.co.uk

Description: -

Returns all plan attributes from sys.dm_exec_plan_attributes pivoted into a single row result-set.

Example usage: -

SELECT TOP 10 cp.plan_handle,cp.usecounts ,cp.size_in_bytes ,cp.cacheobjtype,cp.objtype, pa.*
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY dbo.fn_Plan_Attributes(cp.plan_handle) AS pa

Change History: -
1.0 06/11/2015 Created.

License: free to download and use providing this header is kept in place. Author consent is required for
any sale or re-distibution.
*/
CREATE FUNCTION dbo.fn_Plan_Attributes (@plan_handle VARBINARY(64)) RETURNS TABLE AS

RETURN

WITH cteSetOptions (PlanAttribute, Value) AS (
SELECT DISTINCT CASE pa.attribute WHEN N'set_options' THEN o.SetOption ELSE pa.attribute END,
CASE pa.attribute WHEN N'set_options' THEN CASE WHEN o.Value & CAST(pa.Value AS INT) = o.Value THEN 'Set' ELSE 'Not set' END ELSE pa.value END
FROM dbo.SetOptions AS o
CROSS JOIN sys.dm_exec_plan_attributes(@plan_handle) AS pa)

SELECT [acceptable_cursor_options],[ANSI_NULL_DFLT_OFF],[ANSI_NULL_DFLT_ON],[ANSI_NULLS],[ANSI_PADDING],[ANSI_WARNINGS],[ARITH_ABORT],[compat_level],[CONCAT_NULL_YIELDS_NULL],[date_first],[date_format],[DATEFIRST],[DATEFORMAT],[dbid],[dbid_execute],[FORCEPLAN],[free_cursors],[free_exec_context],[hits_cursors],[hits_exec_context],[inuse_cursors],[inuse_exec_context],[is_replication_specific],[language_id],[LanguageID],[merge_action_type],[misses_cursors],[misses_exec_context],[NoBrowseTable],[NUMERIC_ROUNDABORT],[objectid],[optional_clr_trigger_dbid],[optional_clr_trigger_objid],[optional_spid],[Parallel Plan],[parent_plan_handle],[QUOTED_IDENTIFIER],[removed_cursors],[removed_exec_context],[required_cursor_options],[ResyncQuery],[ROWCOUNT],[sql_handle],[status],[TriggerOneRow],[UPON],[user_id]
FROM
(SELECT PlanAttribute, Value
FROM cteSetOptions) AS cteSource
PIVOT
(
MAX(Value)
FOR PlanAttribute IN ([acceptable_cursor_options],[ANSI_NULL_DFLT_OFF],[ANSI_NULL_DFLT_ON],[ANSI_NULLS],[ANSI_PADDING],[ANSI_WARNINGS],[ARITH_ABORT],[compat_level],[CONCAT_NULL_YIELDS_NULL],[date_first],[date_format],[DATEFIRST],[DATEFORMAT],[dbid],[dbid_execute],[FORCEPLAN],[free_cursors],[free_exec_context],[hits_cursors],[hits_exec_context],[inuse_cursors],[inuse_exec_context],[is_replication_specific],[language_id],[LanguageID],[merge_action_type],[misses_cursors],[misses_exec_context],[NoBrowseTable],[NUMERIC_ROUNDABORT],[objectid],[optional_clr_trigger_dbid],[optional_clr_trigger_objid],[optional_spid],[Parallel Plan],[parent_plan_handle],[QUOTED_IDENTIFIER],[removed_cursors],[removed_exec_context],[required_cursor_options],[ResyncQuery],[ROWCOUNT],[sql_handle],[status],[TriggerOneRow],[UPON],[user_id])
) AS t;

GO

An example select statement from the plan cache using this function is: –


USE DBA;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT ObjectName = OBJECT_NAME(st.objectid, st.[dbid]) ,
cp.plan_handle,
cp.usecounts ,
cp.size_in_bytes ,
cp.cacheobjtype ,
cp.objtype ,
qp.query_plan ,
st.[text],
pa.*
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY dbo.fn_Plan_Attributes(cp.plan_handle) AS pa
WHERE st.[dbid] = DB_ID(N'AdventureWorks2012')
AND st.objectid = OBJECT_ID(N'AdventureWorks2012.dbo.uspGetBillOfMaterials');

Advertisements

One thought on “Bitwise operations

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s