How to add DB2 as a linked server in SQL

 How to add DB2 as a linked server  in SQL

In this post, I will demonstrate how to add a new linked server in SQL Server.

-- Creating the Linked Server: [AS400DB]
EXEC master.dbo.sp_addlinkedserver 
    @server = N'AS400DB',
    @srvproduct = N'AS400',
    @provider = N'IBMDASQL',
    @datasrc = N'10.0.1.1';

-- Setting up the linked server login
EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'AS400DB',
    @useself = N'False',
    @locallogin = NULL,
    @rmtuser = N'MyUser',
    @rmtpassword = '########';
GO

-- Configuring server options
EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'collation compatible',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'data access',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'dist',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'pub',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'rpc',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'rpc out',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'sub',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'connect timeout',
    @optvalue = N'0';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'collation name',
    @optvalue = NULL;
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'lazy schema validation',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'query timeout',
    @optvalue = N'0';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'use remote collation',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'remote proc transaction promotion',
    @optvalue = N'true';
GO

This script will successfully configure the linked server, allowing you to connect to an AS400 database from SQL Server. Ensure that all server names, IP addresses, and credentials are accurate before executing.

Restore database in SQL Server TSQL Statement

In this post, I will demonstrate how to retrieve backup file details and relevant information using SQL queries. The query below fetches the latest 100 backup records ordered by date.

SELECT TOP 100
    DatabaseName = bs.database_name,
    BackupStartDate = bs.backup_start_date,
    CompressedBackupSize = bs.compressed_backup_size,
    ExpirationDate = bs.expiration_date,
    BackupSetName = bs.name,
    RecoveryModel = bs.recovery_model,
    ServerName = bs.server_name,
    CASE bs.[type]
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        WHEN 'F' THEN 'File or filegroup'
        WHEN 'G' THEN 'Differential file'
        WHEN 'P' THEN 'Partial'
        WHEN 'Q' THEN 'Differential partial'
        ELSE bs.[type]
    END AS BackupType,
    LogicalDeviceName = bmf.logical_device_name,
    PhysicalDeviceName = bmf.physical_device_name,
    'RESTORE LABELONLY FROM DISK = ''' + bmf.physical_device_name + ''' WITH CHECKSUM ' AS ChkSQL,
    'RESTORE ' + CASE bs.[type]
        WHEN 'D' THEN 'DATABASE'
        WHEN 'I' THEN 'DATABASE'
        WHEN 'L' THEN 'LOG'
    END + ' ' + bs.database_name + ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY' AS 'RestoreSQL'
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = '<Database Name>'
ORDER BY bs.backup_start_date DESC;

Make sure to replace `` with the actual database name, or simply omit this part from the query.

The following query extracts backup media information based on the backup file name retrieved from the previous query:

RESTORE LABELONLY FROM DISK = 'I:\\MSSQL\\Backup\\dbname\\dbname_backup_2020_12_04_090003_0380615.trn' WITH CHECKSUM

This query will retrieve file details from the backup file name provided:

RESTORE FILELISTONLY FROM DISK = 'I:\\MSSQL\\Backup\\DBNAME\\DBNAME_backup_2020_12_03_200002_1858707.bak'

The following query provides the full backup header information for all backups stored on a specific device in SQL Server:

RESTORE HEADERONLY FROM DISK = N'I:\\MSSQL\\Backup\\AdventureWorks-FullBackup.bak';

Here’s a query that creates a temporary table and stores file information from the backup file. This can be useful if you want to restore the database to a different file path.

CREATE TABLE #RestoreFileList (
    RowID INT IDENTITY(1, 1),
    LogicalName VARCHAR(250),
    PhysicalName VARCHAR(500),
    [Type] VARCHAR(2),
    FileGroupName VARCHAR(100),
    Size VARCHAR(100),
    MaxSize VARCHAR(100),
    FileId INT,
    CreateLSN VARCHAR(250),
    DropLSN VARCHAR(250),
    UniqueId VARCHAR(250),
    ReadOnlyLSN VARCHAR(250),
    ReadWriteLSN VARCHAR(250),
    BackupSizeInBytes VARCHAR(250),
    SourceBlockSize INT,
    FileGroupId INT,
    FileGroupGUID VARCHAR(250),
    DifferentialBaseLSN VARCHAR(250),
    DifferentialBaseGUID VARCHAR(250),
    IsReadOnly VARCHAR(2),
    IsPresent VARCHAR(2),
    TDEThumbPrint VARCHAR(250)
);

INSERT INTO #RestoreFileList (
    LogicalName,
    PhysicalName,
    [Type],
    FileGroupName,
    Size,
    MaxSize,
    FileId,
    CreateLSN,
    DropLSN,
    UniqueId,
    ReadOnlyLSN,
    ReadWriteLSN,
    BackupSizeInBytes,
    SourceBlockSize,
    FileGroupId,
    FileGroupGUID,
    DifferentialBaseLSN,
    DifferentialBaseGUID,
    IsReadOnly,
    IsPresent,
    TDEThumbPrint
)
EXEC ('RESTORE FILELISTONLY FROM DISK = ''I:\\MSSQL\\Backup\\DBNAME\\DNNAME_backup_2020_12_03_200002_1858707.bak''');

The following query generates a restore script with the specified path. You should replace `@newPath` and the backup filename as per your requirements. Depending on your SQL Server version, you might need to adjust the columns accordingly.

DECLARE @dataFile VARCHAR(1000) = '', @newPath VARCHAR(100) = 'c:\\testpat\\';
SET @dataFile = 'RESTORE DATABASE [signphoto] FROM DISK = N''I:\\MSSQL\\signphoto.bak'' WITH FILE = 1, ';
SELECT @dataFile = @dataFile + 'MOVE' + '''' + LogicalName + '''' + ' TO '
 + '''' + @newPath + '\\' + RIGHT(PhysicalName, CHARINDEX('\\', REVERSE(PhysicalName)) - 1) + ''',' + CHAR(13)
FROM #RestoreFileList
ORDER BY FileId;
PRINT @dataFile + ', NOUNLOAD, STATS = 5';

The generated restore script will look like this:

RESTORE DATABASE [signphoto] FROM DISK = N'I:\\MSSQL\\signphoto.bak' WITH FILE = 1, MOVE 'signphoto' TO 'c:\\testpat\\signphoto.mdf',
MOVE 'signphoto_log' TO 'c:\\testpat\\signphoto_log.ldf', NOUNLOAD, STATS = 5;

Additional References:

Restore HeaderOnly Documentation

Restore FileListOnly Documentation

Related blog posts:

Backup History Using T-SQL

Backup History

How to Add Linked server with a different name

In this post, I will walk you through the process of creating a linked server with a custom name.

EXEC master.dbo.sp_addlinkedserver @server = N'STUDENTINFO'
, @srvproduct = N'SQL Server'


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'STUDENTINFO'
, @useself = N'False'
, @locallogin = NULL
, @rmtuser = N'ReadLogin'
, @rmtpassword = 'hJa1b3v&*7NB5q98j76('
GO

For additional reference, check the following articles:

DB2 as a Linked Server

Oracle as a Linked Server

How to Find Compressed Objects and Their Properties in SQL Server

📦 How to Find Compressed Objects and Their Properties in SQL Server

SQL Server offers data compression to help reduce storage requirements and improve I/O performance. Over time, it's important to review which objects in your database are compressed, what type of compression is used, and whether further optimization or decompression might be needed.

The following query helps you identify compressed objects in your database along with key details, such as the compression type and a ready-to-run command to remove compression if needed.

🧾 SQL Query to List Compressed Database Objects

SELECT 
    SCHEMA_NAME(o.schema_id) AS [Schema Name],
    OBJECT_NAME(o.object_id) AS [Object Name],
    'ALTER INDEX ALL ON ' + SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) + 
    ' REBUILD WITH (DATA_COMPRESSION = None);' AS [Decompress CMD],
    (
        SELECT OBJECTPROPERTY(OBJECT_ID(OBJECT_NAME(o.object_id)), 'TableHasVarDecimalStorageFormat')
    ) AS [Table Has VarDecimal Storage Format],
    p.[rows],
    p.[data_compression_desc] AS [Compression Type],
    p.[index_id] AS [Index ID on Table]
FROM 
    sys.partitions p
INNER JOIN 
    sys.objects o ON p.object_id = o.object_id
WHERE 
    p.data_compression > 0
    AND SCHEMA_NAME(o.schema_id) <> 'sys'
ORDER BY 
    [Schema Name],
    [Object Name];

🧠 What This Query Does

• Retrieves a list of all compressed objects (tables or indexes) in the current database.
• Shows the schema and object names for clarity.
• Displays the compression type (ROW, PAGE, or COLUMNSTORE).
• Provides an ALTER INDEX command to decompress each object if needed.
• Checks whether a table uses the VarDecimal Storage Format, which helps save space for decimal values.

✅ Why This Is Useful

• Helps DBAs audit storage savings across the database.
• Assists in evaluating whether certain tables or indexes should be recompressed, left as-is, or decompressed.
• Supports performance tuning efforts when analyzing I/O or CPU trade-offs for compression.

---

💡 Tip: Regularly reviewing compressed objects is a good habit—especially in environments where performance and storage costs are closely monitored.

How to add oracle as a linked server in SQL server

In this post, I will guide you through the steps to add a new Oracle linked server in SQL Server.

/****** Linked Server Creation for [PAYROLL] ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PAYROLL'
   ,@srvproduct = N'MSDAORA'
   ,@provider = N'MSDAORA'
   ,@datasrc = N'pay4win20'  -- pay4win20 is the service name, payroll is the server name, and MSDAORA is the Oracle driver name

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PAYROLL'
   ,@useself = N'False'
   ,@locallogin = NULL
   ,@rmtuser = N'system'
   ,@rmtpassword = '########'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'collation compatible'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'data access'
   ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'dist'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'pub'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'rpc'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'rpc out'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'sub'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'connect timeout'
   ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'collation name'
   ,@optvalue = NULL
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'lazy schema validation'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'query timeout'
   ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'use remote collation'
   ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'remote proc transaction promotion'
   ,@optvalue = N'true'
GO

Additional References:

DB2 as a Linked Server

Linked Server with a Different Name

Get the most frequently executed queries for this database

Uncovering Your Most Frequent and Resource-Intensive SQL Queries

Ever wonder which queries are truly hammering your SQL Server? Or which ones are eating up the most resources? This handy T-SQL script helps you pinpoint the top 50 queries based on their execution count, along with crucial performance metrics like logical reads and worker time.

SQL Script to Analyze Query Performance

Here's the SQL query that will give you insights into your database's performance:

SELECT TOP (50) LEFT(t.[text], 50) AS [Short Query Text]
 ,qs.execution_count AS [Execution Count]
 ,qs.total_logical_reads AS [Total Logical Reads]
 ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
 ,qs.total_worker_time AS [Total Worker Time]
 ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time]
 ,qs.total_elapsed_time AS [Total Elapsed Time]
 ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,qs.creation_time AS [Creation Time]
--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
ORDER BY qs.execution_count DESC
OPTION (RECOMPILE);

Understanding the Metrics

This script leverages two powerful SQL Server Dynamic Management Views (DMVs):

• sys.dm_exec_query_stats: Provides aggregate performance statistics for cached query plans.
• sys.dm_exec_query_plan: Returns the showplan in XML format for the batch specified by the plan handle.

By analyzing the output, you can quickly identify queries that are frequently executed, have high logical reads (indicating significant I/O operations), or consume a lot of worker time (CPU usage). The "Has Missing Index" column is particularly useful for spotting potential performance bottlenecks that could be resolved with a new index.

Xml with null Column

How to Retrieve Null Values from an XML Column

XML Basics

Some key points to remember when working with XML:

• XML tags are case-sensitive; make sure the opening and closing tags match in case.
• Elements in XML must be correctly nested.
• Attribute values should always be enclosed in quotes.
• The attribute xsi:nil is used to represent a null value in XML.

Special Characters in XML:

<message>salary < 1000</message>
&lt; < less than
&gt; > greater than
&amp; & ampersand 
&apos; ' apostrophe
&quot; " quotation mark

Example SQL Query:

select name, object_id, principal_id from sys.tables for XML PATH, ELEMENTS XSINIL;

Sample XML Output:

object_id	principal_id	name
117575457	NULL	spt_fallback_db
133575514	NULL	spt_fallback_dev
149575571	NULL	spt_fallback_usg
1803153469	NULL	spt_monitor
2107154552	NULL	MSreplication_options

SQL to Parse XML with Null Values:

declare @xml xml
select @xml = N'<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_db</name>
  <object_id>117575457</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_dev</name>
  <object_id>133575514</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_usg</name>
  <object_id>149575571</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_monitor</name>
  <object_id>1803153469</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>MSreplication_options</name>
  <object_id>2107154552</object_id>
  <principal_id xsi:nil="true" />
</row>'
; 
with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as xsi)
select  N.C.value('object_id[1]', 'int') as object_id,
        N.C.value('principal_id[1][not(@xsi:nil = "true")]', 'int') as principal_id,
        N.C.value('name[1]', 'varchar(100)') as name
from @xml.nodes('//row') N(C)

For Additional Information:

You can refer to the following article for more details: XML Type Using Query

How to find orphan database files

In this post, I will share a sample SQL query that helps identify database files that are not in use by SQL Server.

DECLARE @data_file_path VARCHAR(255)

SET @data_file_path = 'D:\\data\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\'

-- Creating a temporary table to store file names from the directory.
IF object_id('tempdb..#folder') IS NOT NULL
    DROP TABLE #folder

CREATE TABLE #folder (
    cid INT identity(1, 1) PRIMARY KEY CLUSTERED,
    subdirectory VARCHAR(255),
    depth INT,
    isfile INT
)

-- Populating the temporary table with file names using xp_dirtree.
INSERT INTO #folder (
    [subdirectory],
    [depth],
    [isfile]
)
EXEC master..xp_dirtree @data_file_path, 1, 1

-- Comparing files found in the OS data file location to files linked to live databases.
-- WARNING: This does not account for detached data files. If you have detached data files, consult your DBA before cleaning up orphaned files.
SELECT 'path location' = @data_file_path,
       'orphaned data files' = subdirectory
FROM #folder
WHERE subdirectory LIKE '%df' -- Only compares .mdf, .ndf, and .ldf files
  AND subdirectory NOT IN (
        SELECT right(smf.physical_name, charindex('\\', reverse('\\' + smf.physical_name)) - 1)
        FROM sys.master_files smf
        JOIN sys.databases sd ON smf.database_id = sd.database_id
    )
ORDER BY subdirectory ASC

Escape XML special characters in SQL Server Query

In this post, I'll demonstrate how to handle special characters in SQL Server.

DECLARE @xml XML = '<zzz xmlns="http://himanshupatel.in"><aaa>aaa</aaa> <bbb>param1=xyz&amp;para2=dasdasdfdas&amp;param3</bbb></zzz>'

SELECT @xml [before], DATALENGTH(@xml) dl

;WITH XMLNAMESPACES (DEFAULT 'http://himanshupatel.in')
SELECT @xml.value('(zzz/bbb/text())[1]', 'VARCHAR(100)')

Here’s an image that illustrates the output when using special characters in the XML.

SELECT s.name AS Schema_Name, o.name AS Object_Name, o.type_desc,
    (SELECT m.definition FOR XML PATH(''), type) AS Object_Definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

SELECT s.name AS Schema_Name, o.name AS Object_Name, o.type_desc,
    (SELECT m.definition FOR XML PATH('')) AS Object_Definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

Here’s the output when you include the 'type' keyword in the query:

Now, here’s the result without the 'type' keyword:

select (select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''), type )

select (select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''))

select stuff((select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''), type).value('(./text())[1]', 'varchar(max)'), 1, 2, '') as namelist;

Here’s the output of the three queries above:

For further reference, check out the following resources:

Using XML Type in SQL Queries

XML with Null Columns

Difference between row_number(), rank() and dense_rank() window functions in SQL Server

Difference between row_number(), rank() and dense_rank() window functions 

All three functions operate according to the ORDER BY clause.

The ROW_NUMBER() function generates a unique ranking for each row, even when there are duplicate values.

The RANK() function works like ROW_NUMBER(), but it assigns the same rank to rows with identical values.

The DENSE_RANK() function is similar to RANK(), but it ensures that there are no gaps in the ranking between groups.

SELECT *, ROW_NUMBER() OVER(ORDER BY Col) AS [ROW_NUMBER],
       RANK() OVER(ORDER BY Col) AS [RANK],
       DENSE_RANK() OVER(ORDER BY Col) AS [DENSE_RANK]
FROM (VALUES('a'),('a'),('a'),('b'),('c'),('c'),('d'),('e')) AS Tab(Col)

Other References:

Table Value Constructor - Transact-SQL

SQL Server Functions

SELECT with VALUES

How to read the SQL Server query plan file using TSQL

 How to read the SQL Server query plan file using T-SQL

In this post, I'll explain how to read an SQL Server execution query plan from the SSMS query file.

DECLARE @plancontents xml, @xml XML
SET @plancontents = (SELECT * FROM OPENROWSET(BULK 'P:\\Users\\hpatterson\\Documents\\myplan.sqlplan', SINGLE_CLOB) x)
-- Remove unnecessary namespace
-- SET @plancontents = REPLACE(@plancontents, 'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"', '')
-- Store the cleaned content into the XML variable
SET @xml = CAST(@plancontents AS XML)

-- You can also get the query plan directly like this:
-- SELECT @xml = query_plan FROM sys.dm_exec_query_plan
SELECT @xml

-- Notice the removal of the xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan attribute

-- Now, let's process all the execution plan nodes and extract relevant details
WITH xmlnamespaces (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
    c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
    c.value('.[1]/@AvgRowSize', 'nvarchar(max)') AS AvgRowSize,
    c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@Parallel', 'nvarchar(max)') AS Parallel,
    c.value('.[1]/@EstimateRebinds', 'nvarchar(max)') AS EstimateRebinds,
    c.value('.[1]/@EstimateRewinds', 'nvarchar(max)') AS EstimateRewinds,
    c.value('.[1]/@NodeId', 'nvarchar(max)') AS NodeId,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') AS EstimatedTotalSubtreeCost,
    c.value('.[1]/@EstimateRows', 'nvarchar(max)') AS EstimateRows,
    c.value('.[1]/@EstimateIO', 'nvarchar(max)') AS EstimateIO,
    c.value('.[1]/@EstimateCPU', 'nvarchar(max)') AS EstimateCPU,
    -- This will return the node XML for easier inspection
    c.query('.') AS ExecPlanNode        
FROM 
    -- Only returns nodes with the name RelOp, including those that are nested
    @xml.nodes('//child::RelOp') T(c)
ORDER BY EstimatedTotalSubtreeCost DESC

How to find a backup task by percentage complete

How to find a backup task by percentage complete

In this post, I will demonstrate how to track the completion of a backup task using a percentage indicator.

SELECT query = a.text, start_time, percent_complete,
    eta = dateadd(second, estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%'

For more related information, check out the following resources:

Find Databases Without Backup

Backup History Using T-SQL

Restore Database