Service Broker Information

--: Service Broker Information
--Queue rows and Queue table size
SELECT '--: Service Broker information'
SELECT SCHEMA_NAME(q.schema_id) AS SchemaName, q.name AS QueueName, p.row_count AS QueueRows,i.name as QueueSystemTableName,p.reserved_page_count * 8. / 1024. AS [QueueTableSize(MB)]
FROM sys.service_queues AS q with(nolock)
JOIN sys.objects AS o with(nolock)ON o.object_id = q.object_id
JOIN sys.objects AS i with(nolock)ON i.parent_object_id = q.object_id
LEFT JOIN sys.dm_db_partition_stats AS p with(nolock)ON i.object_id = p.object_id AND p.index_id IN(0, 1)
Order by QueueRows desc, [QueueTableSize(MB)] Desc

--Service Broker size and rows
Select case when o.name = 'sysxmitqueue' then 'transmission_queue' else 'conversation_endpoints' END as ServiceBrokerTable,o.name as SystemTable,
p.reserved_page_count * 8. / 1024. AS [TableSize(MB)],p.row_count as [RowCount]
from sys.objects as o with(nolock)
LEFT JOIN sys.dm_db_partition_stats AS p with(nolock) ON o.object_id = p.object_id AND p.index_id IN(0, 1)
where o.name in ('sysdercv','sysdesend','sysxmitqueue')

--Endpoints rows
Select s.name as [EndPointsSourceServices],e.far_service as [EndPointsTargetServices], state_desc as [EndPointsState],count(*) as [EndPointsCount]
from sys.conversation_endpoints as e with(nolock)
inner join sys.services as s WITH(NOLOCK) on s.service_id = e.service_id
Group by s.name,e.far_service,e.state_desc
Order by [EndPointsCount] desc, far_service,state_desc

--Transmission Queue rows
select from_service_name as [SourceServices],to_service_name as [TargetServices],count(to_service_name) as QueueCount
from sys.transmission_queue with(nolock)
group by from_service_name,to_service_name
Order by QueueCount desc,from_service_name,to_service_name

--Normally the Service Broker numbers should be small, otherwise it indicates a Service Broker issue

Database Health Check – Database Level

--1: Database property information
SELECT '--1: Database property information'
SELECT db.[name] AS [DatabaseName], db.recovery_model_desc AS [RecoveryModel], db.log_reuse_wait_desc AS [LogReuseWaitDescription],
db.[compatibility_level] AS [CompatibilityLevel],db.is_auto_create_stats_on,is_auto_create_stats_incremental_on,
db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced, db.snapshot_isolation_state_desc,
db.is_read_committed_snapshot_on, is_auto_shrink_on, is_auto_close_on,db.is_ansi_null_default_on,db.is_ansi_nulls_on,db.is_ansi_padding_on,
db.is_ansi_warnings_on,db.is_trustworthy_on,db.is_broker_enabled, db.log_reuse_wait_desc
FROM sys.databases AS db WITH (NOLOCK)
WHERE db.database_id = db_id();

If (SELECT Compatibility_level FROM sys.databases WHERE database_id = DB_ID()) >=130
BEGIN
SELECT name as ConfiguratioName, [value] AS ConfiguratioValue
FROM sys.database_scoped_configurations WITH (NOLOCK)
END

--2: Database file size and space available
SELECT '--2: Database file size and space available'
SELECT name AS [FileName], physical_name AS [PhysicalPath], size/128 AS [TotalSize(MB)], Convert(bigint,FILEPROPERTY(name, 'SpaceUsed'))/128 AS [UsedSpace(MB)],
Convert(decimal(9,2),100 * (Convert(bigint,FILEPROPERTY(name, 'SpaceUsed'))/128.0)/(size/128.0)) AS [UsedSpace(%)]
FROM sys.database_files WITH (NOLOCK);

--3: Last statistics update for all indexes
SELECT '--3: Last statistics update for all indexes'
SELECT o.name as TableName, i.name AS IndexName,STATS_DATE(i.[object_id], i.index_id) AS [StatisticsDate], st.row_count as RowCounts
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id] AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

--4: Indexes Fragmentation
-- Note: This could take some time on a very large database
SELECT '--4: Indexes Fragmentation'
SELECT DB_NAME(database_id) AS [DatabaseName],OBJECT_NAME(ps.OBJECT_ID) AS [ObjectName],i.name AS [IndexName], ps.index_id as IndexID, index_type_desc as IndexType,
avg_fragmentation_in_percent as [AvgFragmentation(%)], fragment_count as FragmentationCount, page_count as PageCount
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID() AND page_count > 500 and avg_fragmentation_in_percent > 20
ORDER BY avg_fragmentation_in_percent DESC;

--5: Cached queries and SPs
SELECT '--5: Cached queries and SPs'
IF Substring(Convert(nvarchar(128),SERVERPROPERTY('ProductVersion')),1,CHARINDEX('.',Convert(nvarchar(128),SERVERPROPERTY('ProductVersion')))-1) = 12
and Convert(nvarchar(128),SERVERPROPERTY('ProductLevel')) < 'SP2'
BEGIN
EXEC sp_executesql N'SELECT qs.execution_count, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;'
END
ELSE
BEGIN
EXEC sp_executesql N'SELECT qs.execution_count, total_grant_kb/execution_count as [GrantMemory/Execution(KB)],qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;'
END

SELECT p.name AS [SP Name], qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time, qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads], 
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites]
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC;

Database Health Check – Instance Level

USE [master]
GO

--1: SQL Server and operating system information
SELECT '--1: SQL Server and operating system information'
SELECT @@VERSION AS [SQLServer&OSVersionInfo];

--2: Hardware information
--SQL SERVER 2014 and higher
SELECT '--2: Hardware information'
Declare @InstantFileInitializationEnabled nvarchar(10) = 'Unknown'
IF SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion')),0,CHARINDEX('.',CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion')))) >=13
BEGIN
EXEC sp_executesql N'select @IFI = instant_file_initialization_enabled from sys.dm_server_services where servicename like ''SQL Server (%''',
N'@IFI nvarchar(10) OUTPUT',@IFI=@InstantFileInitializationEnabled OUTPUT;
END

SELECT cpu_count AS [LogicalCPUCount], hyperthread_ratio AS [HyperthreadRatio],cpu_count/hyperthread_ratio AS [PhysicalCPUCount],
physical_memory_kb/1024 AS [PhysicalMemory(MB)],affinity_type_desc as [CPUAffinityType],
virtual_machine_type_desc as [VirtualMachineType],sqlserver_start_time as [LastStartTime],@InstantFileInitializationEnabled as InstantFileInitializationEnabled
FROM sys.dm_os_sys_info WITH (NOLOCK);

--3: Instance configuration values
-- Get configuration values for instance
SELECT '--3: Instance configuration values'
SELECT name as [ConfigName], value as [ConfigValue], value_in_use as [ConfigValueInUse], minimum, maximum, [description] as [ConfigDescription],
is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
-- Focus on
-- Automatic soft-NUMA disabled (should be 0 in most cases)
-- backup compression default (should be 1 in most cases)
-- Backup checksum default (should be 1)
-- clr enabled (only enable if it is needed)
-- lightweight pooling (should be zero)
-- max degree of parallelism (recommended setting is zero)
-- max server memory (MB) (set to an appropriate value) Should be set to the maximum amount of memory, in MB, that is allocated to the SQL instance. E.g. if 256GB is allocated to SQL, then set this value to 256000. Be sure to leave sufficient memory to the OS. On a server that is dedicated to SQL, at least 4GB is required for the OS to function. 
-- max worker threads (should be zero)
-- min server memory (MB) (set to an appropriate value) Should be set to the maximum amount of memory, in MB, that is allocated to SQL instance. E.g. if 256GB is allocated to SQL, then set this value to 256000. This value should be equal to the “Max Server Memory” value. 
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)

--4: Database filenames, paths and growth
-- File Names and Paths for Tempdb and all user databases in instance
SELECT '--4: Database filenames, paths and growth'
SELECT DB_NAME([database_id])AS [DatabaseName], [file_id] as FileID, name as [DataFileName], physical_name as [PhysicalPath],
type_desc as [FileType],state_desc as [DatabaseState], CONVERT( bigint, size/128.0) AS [TotalSize(MB)],
Case when is_percent_growth = 0 Then CONVERT(varchar(10), CONVERT(bigint, growth/128.0)) + ' MB'
else CONVERT(varchar(10), growth) + '%' END AS [GrowthSize(MB)]
FROM sys.master_files WITH (NOLOCK)
WHERE ([database_id] > 4 AND [database_id] <> 32767) OR [database_id] = 2
ORDER BY DB_NAME([database_id]);
-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is TempDB on dedicated drives?
-- Are there multiple data files?
-- Are there percentage growth?

--5 Disk Space Monitoring
SELECT '--5 Disk Space Monitoring'
SELECT DISTINCT 
volume_mount_point [Disk Mount Point], 
file_system_type [File System Type], 
logical_volume_name as [Logical Drive Name], 
CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB], 
CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] 
FROM sys.master_files 
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)

--6: Login count information
-- Get logins that are connected and how many sessions they have
SELECT '--6: Login count information'
SELECT DB_NAME(database_id) as DBName,login_name as LoginName, COUNT(session_id) AS [SessionCount]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY DB_NAME(database_id),login_name
ORDER BY DB_NAME(database_id),COUNT(session_id) DESC;
-- This can help characterize the workload

SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name, 
COUNT(ec.session_id) AS [connection count] 
FROM sys.dm_exec_sessions AS es WITH (NOLOCK) 
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) 
ON es.session_id = ec.session_id 
GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name 
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);

--7: Memory information
SELECT '--7: Memory information'
SELECT total_physical_memory_kb/1024 as [TotalPhysicalMemory(MB)], available_physical_memory_kb/1024 as [AvailablePhysicalMemory(MB)],
system_memory_state_desc as [SystemMemoryState]
FROM sys.dm_os_sys_memory WITH (NOLOCK);
-- You want to see "Available physical memory is high"
-- This indicates that you are not under external memory pressure

SELECT physical_memory_in_use_kb/1024 as [SQL Server Memory Usage (MB)], memory_utilization_percentage as [MemoryUtilization%],
process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK);
-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure

--8:CPU Usage By Database
SELECT '--8:CPU Usage By Database'
;WITH DB_CPU_Stats
AS
(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS pa
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)], 
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);

--9:I/O Usage By Database
SELECT '--9:I/O Usage By Database'
;WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM Aggregate_IO_Statistics
ORDER BY [I/O Rank] OPTION (RECOMPILE);

--10:Total Buffer Usage By Database
SELECT '--10:Total Buffer Usage By Database'
;WITH AggregateBufferPoolUsage
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [CachedSize]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id))
SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], CachedSize AS [Cached Size (MB)],
CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
FROM AggregateBufferPoolUsage
ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE);

Querying Temporal Tables

One of the major benefits of temporal/versioning tables is that we can query and see how data in table looked at any given point in time. In this article, let’s see how to query this data.

There are five clauses we can use to query versioned data.

• AS OF <date_time>
• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time> , <end_date_time>)
• ALL

Demo

To get started I am going to set up a temporal/versioning table dbo.TBM_RATE_FEE_HISTORY for the base table [dbo].[TBM_RATE_FEE] as shown below.

-- TBM_RATE_FEE
ALTER TABLE [dbo].[TBM_RATE_FEE] ADD
StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT GETUTCDATE(),
EndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
GO
-- TBM_RATE_FEE_HISTORY
ALTER TABLE [dbo].[TBM_RATE_FEE]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TBM_RATE_FEE_HISTORY));
GO

Let’s perform few update operations to generate some history in the table which we can use in our demos to understand how querying works.

UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 410.0000000000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2786
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 420.0000000000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2786
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 430.0000000000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2786
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 440.0000000000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2786
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 10.0000000000
,[MK_PCNT] = 1.0000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2784
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 20.0000000000
,[MK_PCNT] = 2.0000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2784
GO

Using AS OF

Use the AS OF sub-clause when you need to see the state of data as it was at any specific date and time in the past. The AS OF sub-clause clause can be used with constant literals or with variables, which allows you to dynamically specify time condition. The values provided are interpreted as UTC time.

So, we want to see how the data looked at a given point in time 2019-08-21 22:00:00.000, before we did any changes.

/*State of data AS OF specific date in the past*/
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED]
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME AS OF '2019-08-21 22:00:00.000'
 WHERE ROW_UNO IN (2784,2786)

Let’s look at execution plan for this query and see what exactly it is doing under the hood.

If we right click and get the predicate for first table TBM_RATE_FEE – it looks like this.

[Expert].[dbo].[TBM_RATE_FEE].[StartTime]<=’2019-08-21 22:00:00.0000000′ AND [Expert].[dbo].[TBM_RATE_FEE].[EndTime]>’2019-08-21 22:00:00.0000000′

The TBM_RATE_FEE_HISTORY history table’s predicate looks like below.

[Expert].[dbo].[TBM_RATE_FEE_HISTORY].[StartTime]<=’2019-08-21 22:00:00.0000000′ AND ([Expert].[dbo].[TBM_RATE_FEE_HISTORY].[ROW_UNO]=(2784) OR [Expert].[dbo].[TBM_RATE_FEE_HISTORY].[ROW_UNO]=(2786))

SQL Server is combining data with start time less than or equal to passed date from main table with history table for start date less than date time passed for this key.

Using FROM <> TO

Below is an example of a query that uses a date range instead of ‘as of’ a specific date.

/* Query using FROM...To sub-clause*/
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED]
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME FROM '2019-08-21 22:00:00.000' TO '2019-08-21 22:06:50.1872483'
 WHERE ROW_UNO IN (2784,2786)

The following criteria must be fulfilled:

StartTime < <end_date_time> AND EndTime > <start_date_time>

Using BETWEEN …AND 

/* Query using BETWEEN...AND sub-clause*/
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED],StartTime,EndTime
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME BETWEEN '2019-08-21 22:00:00.000' AND '2019-08-21 22:06:50.1872483'
 WHERE ROW_UNO IN (2784,2786)

The following criteria must be fulfilled:

StartTime <= <end_date_time> AND EndTime > <start_date_time>

The between clause, therefore, returns data that was changed after or equal to start date and is valid beyond the end date.

Using CONTAINED IN  

/*  Query using CONTAINED IN sub-clause */ 

 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED],StartTime,EndTime
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME CONTAINED IN ('2019-08-21 22:00:00.000' , '2019-08-21 22:06:50.1872483')
 WHERE ROW_UNO IN (2784,2786)

CONTAINED IN returns only those that existed within specified period boundaries.

The following criteria must be fulfilled:

StartTime >= <start_date_time> AND EndTime <= <end_date_time>

The temporal sub-clauses FROM…TO, BETWEEN…AND and CONTAINED IN are useful when you want to perform a data audit, i.e. when you need to get all historical changes for a specific row in the current table.

Using ALL

/*  Query using ALL sub-clause */ 
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED],StartTime,EndTime
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME ALL
 WHERE ROW_UNO IN (2784,2786)

ALL returns both the current and historical data without any restrictions. Both tables are being scanned to pull all this data. This can be an expensive query.

If you search for non-current row versions (historical data changes) only, then we recommend you query the history table directly as this will yield the best query performance as shown below.

  /*  Query historical data changes only */ 
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED],StartTime,EndTime
 FROM [dbo].[TBM_RATE_FEE_HISTORY] 
 WHERE ROW_UNO IN (2784,2786)

References

https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-2017