Decoding hexa error code to windows error message in SQL Server

Problem Statement:
Sometimes, the error messages generated are not friendly to understand the underlined issues. This is going to be an extra effort to google out for most of us with the error code (mostly a hexa value) and identify the corresponding windows error message.

A simple solution would be to use NET HELPMSG with the errocode. But if the error code is a hexa value, then we need to identify the corresponding Decimal value to use as a parameter to NET HELPMSG.

Here is a script to identify actual error message from hex error code. This is important to be aware that this approach will only work for Win32 error codes from Microsoft Windows. If the net helpmsg command does not return a result, the cause is likely the error has not originated from Windows, or no longer a valid Win32 process.

DECLARE @hex VARCHAR(64) = '0x80070643'
Set @hex = Right(@hex,4)

SELECT @rez = ISNULL(@rez,0) * 16 + 
CHARINDEX(substring(@hex,n.number+1,1),'0123456789ABCDEF') - 1
FROM MASTER..spt_values n WHERE n.TYPE='P' AND n.number<len(@hex)

	--To use NET HELPMSG, need to enable xp_cmdshell as below:

	EXEC sp_configure 'show advanced options',1
	EXEC sp_configure 'xp_cmdshell',1
	EXEC sp_configure 'show advanced options',0

Declare @Table Table(WindowsErrorMessage nVarchar(MAX))
Declare @s nvarchar(500) = 'NET HELPMSG ' + Cast(@rez as varchar(MAX))
Insert into @Table 
exec xp_cmdshell @s

Select @hex HexaVal,@rez DecimalVal,* From @Table 
where WindowsErrorMessage is not null

How to Replay a SQL Server Profiler trace in SQL Server

It is one of the most interesting topic in SQL Server “How to Replay profiler”. But, I have not seen this feature is used efficiently by many. Today, we are going to see how can we use Replay profiler in SQL Server.


1. Can be used to reproduce issues in one environment to another environment(production environment to lower environments)
2. Can be used to run the trace multiple times as part of investigation

Now, Let us quickly see how can we use the Replay option in SQL Server.

1. Collect the Profiler trace with Replay option.

This is a very important step to collect what needs to replay. SQL Profiler provides a default template to choose the Replay option. We need to select the template and run the profiler on the source server/database while we execute the functionality that needs to be applied to target database/server.

2. Save the collected trace to a Table

3. Once saved the Replay trace, you can connect to target server and open the trace collected

File -> Open -> Trace Table -> Connect the server that trace saved

4. Once the trace is opened in the Profiler window, we can run the Replay Trace

Replay -> Start -> Select “Target Server”
You can select Replay Configuration in the window as below:(Not covering all the option here, please explore and configure as required)

I (my personal choice) usually include “Display Execution Time”, but its optional.

Once the configurations are set, we can “Start” the Replay.

If you see the above sample snapshot, we can see that the trace has been run successfully. However, we see errors in the screenshot as when I tried to run the trace in the same server and database.

How do we run this trace in different server and different database?

Its always a case that we cannot expect the database name will be same in all environment. For an example, developer would have kept multiple version of same database like database_dev, database_unittest etc to test various purposes. If we try to run the trace as it is, we will end up with errors. To run on different environment, you need to make few changes as below:

1. Go to Management Studio (SSMS) and select the database where the trace table collected
2. Update the database Name and database id columns of the table to your target database name and database id

That means, the trace data needs to be changed for its database id and database name as per the target environment.Once the trace table data has been modified, we can follow the same procedure mentioned above(3 and 4).

How to identify parallel plans in SQL Server

Few years back, on one of my engagements, identified lots of performance issues with an application. As part of a detailed analysis, it is observed there are lots of queries under performing with wait type “CXPACKET”. In short, CXPACKET wait types are involved in parallel query execution. These wait types indicate that the SPID is waiting on a parallel process to complete or start. There are many reasons your query may undergo a parallel execution like missing index, non optimized sql script etc. But, CXPACKET is not ALWAYS a bad thing.We will see about CXPACKET in detail with another post.

Now, We will see how to identify the queries with parallel execution. Once you identified the queries, you can further examine to identify the actual cause of the issues and optimize.

select p.*,q.*,cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
max(//p:RelOp/@Parallel)', 'float') > 0

Please note that the above query gets information from cached plan in SQL Server. Hence, I will recommend to run a load test with a real time workload and use the script to identify the parallel executing queries. It is also important that the cached plans may get flushed due various factors like memory pressure, recompile of procedures, auto stats updates etc. But, this helped me a lot at times!!!

How do we move or restore a database with CDC enabled in SQL Server

This post explains how efficiently we can move a CDC enabled database to different environment.

To move a database from one environment to another environment, Firstly, we need to take back up of the database from the source environment.


Once we move the backup file to the new environment, we have two options:

1. Restore database as always and configure/set up CDC freshly as in this post.

2. Restore database with KEEP_CDC option.

	MOVE 'CDCOrigin' To 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\CDCCopy1.mdf',
	MOVE 'CDCOrigin_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\CDCCopy1_log.ldf'
	--The below option would keep the CDC of original database

Once restore is completed successfully, we can quickly verify the configurations with below queries.

Select is_CDC_Enabled ,name from sys.databases where is_CDC_Enabled=1 
Select name from sys.tables where is_tracked_by_cdc = 1

Wait! still there are few things to be done, configuring jobs for capture and cleanup process. KEEP_CDC option with RESTORE command will keep the CDC enabled and the tables tracked by default. However, we need to create capture and cleanup jobs manually as below.

EXEC sys.sp_cdc_add_job @job_type = N'capture'; 
EXEC sys.sp_cdc_add_job @job_type = N'cleanup'; 


Do not enable this feature unless there is a good reason. The feature adds more space requirement to store the change data and increases disk activities. Be careful on configuring the capture and clean up jobs. If there are many databases with CDC ( due to non-multi tenant support), CDC on multiple databases will have a performance impact.

How do we set up Change Data Capture in SQL Server

This may be an old topic for many who worked with SQL Server, but, As I was doing a proof of concept on CDC recently for one of my projects, thought of sharing about CDC for those who is yet to explore this feature in SQL Server.

What is Change Data Capture?

Change Data Capture or CDC in SQL Server is introduced to track the DML changes and records insert/update/delete operations in respective change tables. The source of truth is Transaction Logs in SQL Server. Capture process jobs will be picking up the required table information from the Transaction Log and records in the Change tables. As these jobs are asynchronous, you may see a latency ideally between the commit and population of change tables. However, this has a performance advantages compared to Trigger based solutions. Similarly there is another clean up job to prune the data (older) from the change tables.

Supporting Editions: Enterprise/Developer/Standard.

How do we implement CDC on a database?

Step 1: : Let us create a database and table to see the proof of Concept

--Create a Test Database
Create Database CDCORigin;

--Create a Table 
create Table TestCDC(FirstName Varchar(100),SecondName Varchar(100))

--Insert values into tables
Insert into TestCDC Values('Ron','David'),('Chirs','Martin')

Step 1: : Enable CDC (Need two steps as below)

EXEC sys.sp_cdc_enable_db
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'TestCDC',  
@role_name     = NULL

EXEC sys.sp_cdc_add_job @job_type = N'capture'; 
EXEC sys.sp_cdc_add_job @job_type = N'cleanup'; 

You need to make sure that the SQLServerAgent is up and running while configuring the CDC, failing on this, it may end up with not creating the respective jobs in the environment.

Step 3: : Verification of CDC configuration

Once, CDC has set up, we can see the CDC specific tables and Changes tables in Object explorer as below.

In addition, we can also verify the data changes using below scripts.

--Check the CDC tables
Select * From [cdc].[dbo_TestCDC_CT]  --No data

Insert into TestCDC Values('Reddick','Pitt')

--Check the CDC tables
Select * From [cdc].[dbo_TestCDC_CT]

Update TestCDC Set SecondName = 'Pi' Where FirstName='Chirs'

--Check the CDC tables
Select * From [cdc].[dbo_TestCDC_CT]

Delete From TestCDC Where FirstName='Ron'

--Check the CDC tables
Select * From [cdc].[dbo_TestCDC_CT]

Precisely, the __$operation will indicate the action on the table as below.

Hope you enjoyed this post, and I am excited to write more about CDC with my real client experience in next posts. Until then, happy and continuous learning!

String_Split function in SQL Server 2016


SQL Server 2016 has introduced a built-in table valued function string_split to split a string into rows based on the separator.

Earlier, developers need to write custom/user defined function to do the split work. However, with the new function, its a built-in and can be used directly. The problem with user defined function is the way the function is defined and used.There are multiple ways usually developers write custom functions like scalar/Table valued functions. And its observed in many cases, scalar functions are not scaling as desired and causing serious performance issues.


1. Split the variable string

Select * From string_split('first,second,third',',')

2. Split the table rows with the help of CROSS APPLY

Create Table splitTable(Col int, string varchar(100))
Insert into splitTable values(1,'First,Second'),(2,'Primary,Secondary')

Select * From splitTable a
Cross apply string_split(a.string,',') B

Drop Table splitTable

If we analyse the execution plan of the above query, we can see “Table Valued Function” operator is used to implement the function.


1. ONLY a single-character delimiters can be used at a time. If you want to apply multiple delimiter, you can use multiple CROSS APPLY.

2. There is no way to understand the ordinal position/eliminate duplicates/empty strings in the string.

3. This function will work ONLY if the compatibility of your database is 130 or later irrespective of SQL Server version.

I would recommend to use this built-in function for future development and provide your experience as part of learning and sharing.