Cleanup of Load Runner Test data from SQL Server

Here is a script to purge the Load test data from your load runner database. This would be handy at times like running of disk space or performance issues related to load runner etc.


--Provide your Load runner database name
USE <<Loadrunner DBName>>
Create proc SQLZealot_CleanupTestData  (@DeleteDateUpto Datetime)
with encryption
as
Begin

	If not exists(Select 1 From sys.tables where name = 'TempTobeDeletedLoadRecords')
		Select LoadtestrunID into TempTobeDeletedLoadRecords from Loadtestrun (nolock) where StartTime < @DeleteDateUpto

	Declare @LoadtestrunID bigint
	While exists(Select 1 From TempTobeDeletedLoadRecords )
	Begin
		Set @LoadtestrunID = (Select Top 1 Loadtestrunid from TempTobeDeletedLoadRecords ORder by 1 asc)
		Exec Prc_DeleteLoadTestRun @Loadtestrunid
		Delete TempTobeDeletedLoadRecords Where Loadtestrunid = @LoadtestrunID
	End

	Drop Table TempTobeDeletedLoadRecords

End
Advertisements

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

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(
	ParentOperationID, OperationID, PhysicalOperator, LogicalOperator, EstimatedCost, EstimatedIO,
	EstimatedCPU, EstimatedRows, QueryText, QueryPlan, CacheObjectType, ObjectType
)
AS
(
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.

XACT_ABORT in SQL Server

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:

SET XACT_ABORT {ON/OFF}

Another option is at Database Engine level as below:

EXEC sp_configure 'user options', 16384
RECONFIGURE WITH OVERRIDE

You can refer more about this here.

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

DECLARE @XACT_ABORT VARCHAR(3)  = 'OFF'
IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';  
SELECT @XACT_ABORT AS XACT_ABORT; 

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
as
Begin

	Declare @Options Bigint
	SET @Options = @@options

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

	SET XACT_ABORT ON

	/*

	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' 
	SELECT @XACT_ABORT AS XACT_ABORT; 	


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

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

End

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

CREATE TABLE [DBO].[DOCUMENTTABLE](
	[DOCUMENTID] [UNIQUEIDENTIFIER] NOT NULL,
	[DOCUMENT_CONTENT] [IMAGE] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE INDEX IX_DOCUMENTTABLE ON DOCUMETTABLE(DOCUMENTID)

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!

References:
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.”

Reason:

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.

Solutions:

There are two solutions to add these databases into AG:

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


ALTER AVAILABILITY GROUP AGName ADD DATABASE DBName

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.


SELECT T.TABLE_CATALOG DATABASE_NAME,T.TABLE_SCHEMA AS SCHEMA_NAME,
    T.TABLE_NAME, 
    C.COLUMN_NAME,C.DATA_TYPE DATA_TYPE,C.CHARACTER_MAXIMUM_LENGTH MAXIMUM_LENGTH,C.COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
    INNER JOIN INFORMATION_SCHEMA.TABLES T
        ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
        AND C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE' 
AND ((C.DATA_TYPE IN ('VARCHAR', 'NVARCHAR') AND C.CHARACTER_MAXIMUM_LENGTH = -1)
OR DATA_TYPE IN ('TEXT', 'NTEXT', 'IMAGE', 'VARBINARY', 'XML', 'FILESTREAM'))
AND T.TABLE_SCHEMA NOT IN('CDC') -- EXCEPTION LIST
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME

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!