How to identify Scans (Table/index) from cached plan in SQL Server

Today, we will quickly see how to identify the scans happening on SQL Server. I had to analyse a test SQL Server environment to identify performance bottlenecks. So, the team was looking for a way to get the scans happening on their server to further optimize and confirm the performance.

Here is a small script I created to understand the Scans on the server. Please note that I divided the script into two parts,
1. To generate a snapshot of the cached plans into a table called – Temp_CacheDump_Analyser
2. To query the Temp_CacheDump_Analyser table for scan operators

This way, even if you want to query it multiple times for different reasons, you can query the cache dump table instead of cached plan tables.

–Generate the snapshot with the required fields

SELECT usecounts,cacheobjtype,objtype,query.text
 ,executionplan.query_plan into Temp_CacheDump_Analyser
 FROM sys.dm_exec_cached_plans
 OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
 OUTER APPLY sys.dm_exec_query_plan(plan_handle) as executionplan
 WHERE [text] NOT LIKE '%sys%'
 AND cacheobjtype ='compiled plan' 

–Query the row data to identify scans on your SQL Server

	ParentOperationID, OperationID, PhysicalOperator, LogicalOperator, EstimatedCost, EstimatedIO,
	EstimatedCPU, EstimatedRows, QueryText, QueryPlan, CacheObjectType, ObjectType
SELECT	RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
		RelOp.op.value(N'@NodeId', N'int') AS OperationID,
		RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
		RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
		RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
		RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
		RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
		RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
		qp.TEXT AS QueryText, qp.query_plan AS QueryPlan,
		qp.cacheobjtype AS CacheObjectType, qp.objtype AS ObjectType
	FROM Temp_CacheDump_Analyser qp
	CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
SELECT	QueryPlan, ParentOperationID, OperationID, PhysicalOperator, LogicalOperator, QueryText,
		CacheObjectType, ObjectType, EstimatedCost, EstimatedIO, EstimatedCPU, EstimatedRows
FROM CachedPlans
	WHERE CacheObjectType = N'Compiled Plan'
		AND	(PhysicalOperator = 'Clustered Index Scan' OR PhysicalOperator = 'Table Scan'
		OR PhysicalOperator = 'Index Scan' OR PhysicalOperator = 'Lookup')

The caveat is the above results are based on the data available at the point in time in the cache. There may be sceanrios these data gets flushed, so the data should be collected and analysed in a regular way that means, its not a one time activity.

Hope, you enjoyed this post, please share your thoughts and feedback.



Today’s post will explain XACT_ABORT in SQL Server. An efficient way of error handling before SQL Server 2005.Post SQL Server 2005, there is a new feature included in SQL Server, TRY… CATCH. But, I would still think there is a good amount of use cases and a great value addition where XACT_ABORT is important in SQL Server. Let us cover few things in the below sections.

Whats the significance of XACT_ABORT in SQL Server?

On a run time error scenario, XACT_ABORT On settings will terminate and rollback the entire transaction. XACT_ABORT does not have any significance on compile or parse time exceptions.

How do we set up XACT_ABORT in SQL Server?

A simple and common method is to set using T-SQL as below:


Another option is at Database Engine level as below:

EXEC sp_configure 'user options', 16384

You can refer more about this here.

We can always understand the current setting of XACT_ABORT as below:

IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';  

Whats the scope if XACT_ABORT?

— SET XACT_ABORT ON/OFF is applied ONLY for the session
— By default XACT_ABORT is OFF

— XACT_ABORT ON, Try…Catch & Transaction

As mentioned earlier, by default XACT_ABORT is OFF by default and if there are any issue in one of transaction of a batch, the transaction alone gets failed as below:

There is no difference even with XACT_ABORT ON in similar case as below:

However, There is a difference in XACT_ABORT when its applied with a transaction. If there are any issue in a batch, the entire batch gets terminated as below:(please note, there is no explicit rollback applied in the example, still no transactions are committed.)

— XACT_ABORT, Transaction and Object Resolution

This is interesting to know about the fact that if there are any failures, the transaction becomes open without XACT_ABORT. If we specify the XACT_ABORT ON, then the transaction is terminated automatically.

Finally, How to preserve XACT_ABORT state in SQL Server?

Sometimes, preserving the XACT_ABORT is a requirement for many of us. As we discussed, we can use @@options and a bit operation with the corresponding user options value to preserve the value. Here is a small example, how can this be achieved as below:

Create procedure Preserve_XACTABORT_Settings

	Declare @Options Bigint
	SET @Options = @@options

	--To know the setting of XACT_ABORT for testing purpose
	IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON' Else Set @XACT_ABORT = 'OFF'



	You can put your actual procedure implementation


	--To know the setting of XACT_ABORT for testing purpose
	IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON' Else Set @XACT_ABORT = 'OFF' 

	--Preserve the XACT_ABORT settings
	IF ( (@OPTIONS & 16384 ) = 0 ) 

	--To know the setting of XACT_ABORT for testing purpose
	IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON' Else Set @XACT_ABORT = 'OFF'


Hope, you enjoyed this post, share your thoughts and feedback as always!

CONTAINSTABLE is not returning records from full text enabled table in SQL Server

Recently, we have an issue, FULL TEXT search was not working with CONTIANSTABLE. My friend was searching for a word in a document where the table is enabled with FULL TEXT feature, however the search was not bringing any records.

My first thought was there could be a missing join condition that could not get the results. The actual query in the issue was really a big one with lots of tables and filters involved.So the first thing that we did remove all joins or conditions and see if CONTAINSTABLE works good, but that attempt was also failing. It was a clear indication that there is something wrong that it could not return results at FULL TEXT functionality.

For everyone to understand better, Let us go through a sample script to replicate the issue:

1. Create a table for our example



2. Load some data into Table – We uploaded the same file in question which is a .xlsx type

3. Query table with CONTAINSTABLE

Select * From CONTAINSTABLE (DocumentTable, DOCUMENT_CONTENT,'reporting')

Msg 7601, Level 16, State 2, Line 2
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ‘DocumentTable’ because it is not full-text indexed.

4. Enable FULL TEXT Indexing on the table

5. While executing the query, it is observed again no data returned

Further, we decided to check the document and its content for the search word manually and found the word was present and the document was also the same “.xlsx”. It is to be noted that we saved the data in an IMAGE datatype as shown in the example. With that information, we wanted to know the supporting file types in our full text enabled table with the below query.

select document_type, path from sys.fulltext_document_types

The above query was not returning the document type – “.xlsx” and it clearly indicated that the reason why the document could not search the word as the document type was a non-supporting one. [DOCUMENTTABLE] is a table that enabled FULL TEXT and the datatype of the field “[DOCUMENT_CONTENT]” was IMAGE. Then, my analysis took me a very important information that if we need to index a document type for which a pre-installed iFilter does not exists at OS level, we need to install the appropriate iFilter in the OS and then load those into SQL Server for a successful search functionality.

How to install the iFilter and Load into SQL Server

1. Download the iFilter , the below supports a wide range of document types

Microsoft Office 2010 Filter Packs

(Please note that MSmight retire the above as latest version is released, you may need to check for the latest.)

The following file types are supported by the Office 2010 Filter Pack:

2. Install the downloaded set

3. Load the installed OS resources to SQL Server as below.

exec sp_fulltext_service 'load_os_resources', 1;
exec sp_fulltext_service 'verify_signature', 0;
exec sp_fulltext_service 'Update_languages'
exec sp_fulltext_service 'Restart_all_fdhosts'

Once the above steps are completed, we were able to query the word and the results were returned as expected.

Hope this helps you in a similar situation and it was a good lesson learned to me!

How to register iFilters

“The database is encrypted by database master key, you need to provide valid password when adding to the availability group.”

Recently, one of my colleague has encountered an issue an error while adding a database to availability group as below:

Error Message:
“The database is encrypted by database master key, you need to provide valid password when adding to the availability group.”


The error message is self explanatory one as this database does not meet a requirement to add AG as this database is protected with a master key. We may need to provide the master key while configuring the database into AG.Please note that this is not due to TDE, but due to master key.

One observation is that this is recently added as a validation in SQL Server 2016 version. Earlier version, you would be able to add seamlessly.


There are two solutions to add these databases into AG:

1. Use T-SQL to add the database into AG


2. Provide the password and use GUI with few extra steps

In GUI, this is not so clear for the user that the password can be provided directly and configure AG.

Double click in the Password column
Type the password in the column
Click Refresh (To enable check box for the database)
Check the checkbox for the database.
Click Refresh again (To enable the Next button)
Click Next to progress

Toulouse Chapter

This post has nothing to do with SQL Server, but sharing my experiences at Toulouse in France.

I stayed near by Blagnac airport, Fasthotel, a decent hotel with all amenities and a clam place to live. And another reason, the hotel is just behind my office!!!

It was an official visit to Toulouse for 2 weeks from 12th May 2019 to 25th May 2019. As this was my first visit, I was really excited. At this point, I need to mention about one of my colleague – Pascal Benguigui . He was right there at Toulouse airport waiting for my arrival, have to say a big thanks to him!

Few things I really felt energized at Toulouse:

1. The French start their day with a warm greet – “bonjour” with a hand shake!

2. People are “Working to Live” unlike “Living to Work” 🙂 – Really appreciate the commitment towards the work during office time and family during the rest!

Another person – Alexis Molteni, I have to mention about him as a most kind and down earth, a perfect gentle man! We three had really really good time. We have been there out almost every day visiting many places like Spanish Tapas “Las Tapas Locas“, Lebanian restaurants, “Capitole de Toulouse” , “Castle Carcassonne” and long walks at nights in the street of Toulouse and many… They really made me feel like I am part of them.

I am really missing these two guys while writing this blog, keeping a good faith that we will meet on another day soon. until then Thank you Pascal and Alexis…

We have a lunch team of 7 people including me and all were trying to find a better food for me by trying different dishes and even different restaurants everyday. Thanks to everyone accommodating me as part of your lunch family!!!

Here are few food items I tried…

How do we identify LOB datatype columns in SQL Server

Large OBject (LOB) data types are datatypes that can store large data. These datatypes can hold values larger than 8K row size.Prior to SQL Server 2005, we have only TEXT, NTEXT and IMAGE to hold large data. With SQL 2005, Microsoft has introduced additional three datatypes to hold large values like VARCHAR(MAX),NVARCHAR(MAX) and VARBINARY(MAX).

How to identify LOB columns in your database?

The below query is useful to identify LOB columns. Please note that the query contains a filter not to fetch CDC schema objects. Feel free to add more filters as required.


How do we identify the length or size of LOB datatype in SQL Server

At times, you may need to understand the length or size of your LOB columns. One example is to set a right value for “max text repl size (B)” to avoid certain issues like this. Usually, we use a function LEN to get the length of the data, however, LEN is not compatible certain datatypes like TEXT,NTEXT and IMAGE.

To resolve, SQL Server has another function – DATALENGTH(). The function returns the number of length or size of the data in bytes. The below query will help to identify the length or size of LOB datatypes like TEXT, NTEXT, IMAGE etc.

--Table creation script
Create Table tablename (columnname image)

--Sample data insert (The data has been trimmed for readability)
Insert into tablename values('0xFAEFCD8FFE000104A46494600010100000100010000FFDB00840009060')

SELECT  DATALENGTH(columnname) as inBytes,
        DATALENGTH(columnname) / 1024.0 as inKb,      
        DATALENGTH(columnname) / 1024.0 / 1024.0 as inMb FROM   tablename

DROP Table tablename

Thats it with this, hoping to see you with another blog post soon!

Length of LOB data to be replicated exceeds configured maximum 65536 in SQL Server

Problem Statement:

Sometimes When we do a DML operation in SQL Server, we end up with the below error –

“Length of LOB data to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type. The statement has been terminated.”

From the error message, its clear that the operation has been terminated by SQL Server.


I would say, this is a clear error message that means it contains the error information, steps to resolve the exception and its implication. Let us expand the error message a bit more than what its provided for our clear understanding with this blog post.

Length of LOB data to be replicated exceeds configured maximum 65536.

This means that the length of a LOB data is exceeding the configured value of 65536 which defaults to 65536. There is a configuration “max text repl size option” in SQL Server which can be viewed by sp_configure system procedure or querying sys.configurations system table. By default, the value of the configuration has been set to 65536 in SQL Server.This has an impact on systems configured with replication, Change Data Capture. The configuration “max text repl size (B)” is not an advanced configuration, so you do not need to reconfigure with “show advanced options” to see this setting.

From the above picture, the description of max text repl size (B) configuration is clear that it denotes the maximum size of a text field in replication. However, we need to understand the value is also applicable for LOB datatypes as well. The issue can be raised whenever a LOB data that contains more length than the specified value for this configuration. The operation gets terminated for the same reason.

What do we do when we get this error?

The simple solution is to set the configuration “max text repl size (B)” to “-1”. This will ensure there is no limit other than imposed by the datatype of the column. However, this may have some detrimental impact on replication as the size increases we need to make sure the replication has no other side effects due to the size of the data to be replicated over the network etc.

1. Identify the LOB datatype column in the table
2. Assess the size or length of the data in the table and confirm that the size of the data is exceeding the configured value of “max text repl size (B)”
3. Identify if there are any replication/CDC configured with the table/database
4. If Change data capture does not require the LOB column to be tracked, avoid the column from capture by specifying ONLY required columns in captured_column_list for the table while configuring CDC for the table
5. Assess your data and conclude the maximum size of data it can reached up to as per the business requirement. As an example, a photo field can be restricted with a specified size through application that can be uploaded etc.
6. Set the required or expected size for max text repl size (B) configuration if possible instead of maximum -1

Note: We recently observed similar issue with a database configured AlwaysOn-Availability Group.