SQL SERVER – Index Hints – Force Index – Query Hints

-> Lets create required objects for this test,

create table Table1(
Col1 int primary key identity(1,1),
Col2 varchar(255),
Col3 datetime,
Col4 char(10))

create table Table2(
Col1 int primary key identity(1,1),
Col2 varchar(255),
Col3 datetime,
Col4 char(10))

create index ix_test on Table1(Col2) INCLUDE(Col4)
create index ix_test on Table2(Col2) INCLUDE(Col4)

set nocount on


insert into Table1 values ('test',getdate(),'test1')
go 100000

insert into Table2 values ('test',getdate(),'test1')
go 100000

-> Once above objects are created. Lets execute below query,

select Col1,Col2,Col3,Col4 from Table1 where Col2='Test' 

-> We are getting a Clustered Index Scan. Lets check Table1 properties and see what indexes we have,

-> We have a clustered index on Col1 that is created as part of the clustered index and then a non-clustered index on column Col2.

-> Below query we executed has a predicate on Col2, so not sure why the optimizer selected a clustered index scan instead of an Index seek using Index ix_test. Let us try forcing this index now,

select Col1,Col2,Col3,Col4 from Table1 with (index(ix_test)) where Col2='Test' 

-> We see an Index seek using Index ix_test and then a key lookup after we forced the Index Ix_test. It seems like optimizer went for a clustered index scan instead of an Index seek + Key lookup as the plan with Clustered Index scan is comparatively cheap than an Index seek + Key lookup.

-> Lets verify this by running the query with and without the hint. As expected, query without hint has overall cost of just 3% and the query with the index hint is close to 97%.

-> It is always good to go with the plans created by optimizer rather than adding hints. Adding hints can help when there is an optimizer timeout. Since this article is about forcing an index, lets look at another method using which we can force an index without worrying much on the performance.

-> Execute below query and get the index id of Ix_Test on Table1.



select object_name(object_id) TableName,name,index_id,type,type_desc from sys.indexes where object_id=OBJECT_ID('Table1')

-> In my case Index id 3 is Ix_test. We can use below query to force the index Ix_Test,

select Col1,Col2,Col3,Col4 from Table1 with (index(3)) where Col2='Test' 

-> Lets see how can we force an index when join between tables are performed. Lets execute below query,

select a.Col1,a.Col2,a.Col3,b.col4 
from Table1 a 
INNER JOIN Table1 b on a.col1=b.col1  
where a.Col2='Test' 

-> We see an cluster Index scan for both tables. Now lets force index ix_test for both tables using below query,


select a.Col1,a.Col2,a.Col3,b.col4 
from Table1 a with (index(ix_test))
INNER JOIN Table1 b with (index(ix_test)) on a.col1=b.col1  
where a.Col2='Test' 

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Forcing a query to perform Table Scan on a heap table

-> Create below table and supporting index,

create table Table1(
Col1 int,
Col2 varchar(255),
Col3 datetime,
Col4 char(10)
)

create index ix_test on Table1(Col2) INCLUDE(Col1,Col3,Col4)

-> Lets check the table properties. There is no clustered index and Table Table1 is a heap.

-> Insert few rows,

insert into Table1 values (1,'test',getdate(),'test1')
go 1000

-> Lets execute below query with execution plan enabled,

select Col1,Col2,Col3,Col4 from Table1 where Col2='Test'

-> Lets execute below query and see if we can get a table scan,

select Col1,Col2,Col3,Col4 from Table1 with (FORCESCAN) where Col2='Test' 

-> From above screenshot, we see that we have an index scan. But we were looking for a table scan.

-> Lets us check the sysindexes system table using below query and see what we have,

select object_name(object_id) TableName,name,index_id,type,type_desc from sys.indexes where object_id=OBJECT_ID('Table1')

-> Lets force Index_id 0 and see if we can get a table scan,

select Col1,Col2,Col3,Col4 from Table1 with (index(0)) where Col2='Test' 

-> There you go!!! we have a table scan.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.

-> Availability group secondary database was “Not Synchronizing”.

-> Below is the view of Always On Dashboard,

-> SQL Server Error log

JBSAG1

2022-06-14 05:08:04.130 spid11s Always On: DebugTraceVarArgs AR ‘[HADR] [Primary] operation on replicas [CF81C5D4-0DBF-4573-AB9D-018423FB92B2]->[0E5B3832-3DDA-41CB-9730-0F119639B535], database [JBSAG12], remote endpoint [TCP://JBSAG2.JBSWIKI.com:5022], source operation [708829B5-E20D-4112-9D88-050797FE9D91]: Transitioning from [PENDING] to [CHECK_IF_SEEDING_NEEDED].’
2022-06-14 05:08:04.130 spid44s A connection for availability group ‘JBAG12’ from availability replica ‘JBSAG1’ with id [CF81C5D4-0DBF-4573-AB9D-018423FB92B2] to ‘JBSAG2’ with id [0E5B3832-3DDA-41CB-9730-0F119639B535] has been successfully established. This is an informational message only. No user action is required.
2022-06-14 05:08:04.130 spid37s Always On Availability Groups connection with secondary database established for primary database ‘JBSAG12’ on the availability replica ‘JBSAG2’ with Replica ID: {0e5b3832-3dda-41cb-9730-0f119639b535}. This is an informational message only. No user action is required.
2022-06-14 05:08:04.130 spid68s Always On: DebugTraceVarArgs AR ‘[HADR] [Primary] operation on replicas [CF81C5D4-0DBF-4573-AB9D-018423FB92B2]->[0E5B3832-3DDA-41CB-9730-0F119639B535], database [JBSAG12], remote endpoint [TCP://JBSAG2.JBSWIKI.com:5022], source operation [708829B5-E20D-4112-9D88-050797FE9D91]: Transitioning from [CHECK_IF_SEEDING_NEEDED] to [CATCHUP].’
2022-06-14 05:08:04.150 spid68s Always On: DebugTraceVarArgs AR ‘[HADR] [Primary] operation on replicas [CF81C5D4-0DBF-4573-AB9D-018423FB92B2]->[0E5B3832-3DDA-41CB-9730-0F119639B535], database [JBSAG12], remote endpoint [TCP://JBSAG2.JBSWIKI.com:5022], source operation [708829B5-E20D-4112-9D88-050797FE9D91]: Transitioning from [CATCHUP] to [COMPLETED].’
2022-06-14 05:08:04.170 spid37s [DbMgrPartnerCommitPolicy::SetReplicaInfoInAg] DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0E5B3832-3DDA-41CB-9730-0F119639B535:6:4
2022-06-14 05:08:04.170 spid37s DbMgrPartnerCommitPolicy::SetSyncState: 0E5B3832-3DDA-41CB-9730-0F119639B535:6:4
2022-06-14 05:08:41.350 spid68s A connection timeout has occurred on a previously established connection to availability replica ‘JBSAG2’ with id [0E5B3832-3DDA-41CB-9730-0F119639B535]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2022-06-14 05:08:41.350 spid68s Always On Availability Groups connection with secondary database terminated for primary database ‘JBSAG12’ on the availability replica ‘JBSAG2’ with Replica ID: {0e5b3832-3dda-41cb-9730-0f119639b535}. This is an informational message only. No user action is required.
2022-06-14 05:08:41.360 spid83s [DbMgrPartnerCommitPolicy::SetReplicaInfoInAg] DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0E5B3832-3DDA-41CB-9730-0F119639B535:6:1
2022-06-14 05:08:41.360 spid83s DbMgrPartnerCommitPolicy::SetSyncState: 0E5B3832-3DDA-41CB-9730-0F119639B535:6:1

JBSAG2

2022-06-14 05:07:07.030 spid76s A connection timeout has occurred on a previously established connection to availability replica ‘JBSAG1’ with id [CF81C5D4-0DBF-4573-AB9D-018423FB92B2]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2022-06-14 05:07:07.030 spid76s Always On Availability Groups connection with primary database terminated for secondary database ‘JBSAG12’ on the availability replica ‘JBSAG1’ with Replica ID: {cf81c5d4-0dbf-4573-ab9d-018423fb92b2}. This is an informational message only. No user action is required.
2022-06-14 05:07:17.040 spid76s A connection timeout has occurred while attempting to establish a connection to availability replica ‘JBSAG1’ with id [CF81C5D4-0DBF-4573-AB9D-018423FB92B2]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
2022-06-14 05:08:04.130 spid31s A connection for availability group ‘JBAG12’ from availability replica ‘JBSAG2’ with id [0E5B3832-3DDA-41CB-9730-0F119639B535] to ‘JBSAG1’ with id [CF81C5D4-0DBF-4573-AB9D-018423FB92B2] has been successfully established. This is an informational message only. No user action is required.
2022-06-14 05:08:04.130 spid12s Always On: DebugTraceVarArgs AR ‘[HADR] [Secondary] operation on replicas [CF81C5D4-0DBF-4573-AB9D-018423FB92B2]->[0E5B3832-3DDA-41CB-9730-0F119639B535], database [JBSAG12], remote endpoint [TCP://JBSAG1.JBSWIKI.com:5022], source operation [708829B5-E20D-4112-9D88-050797FE9D91]: Transitioning from [PENDING] to [CHECK_IF_SEEDING_NEEDED].’
2022-06-14 05:08:04.130 spid85s Error: 41145, Severity: 16, State: 1.
2022-06-14 05:08:04.130 spid85s Cannot join database ‘JBSAG12’ to availability group ‘JBAG12’. The database has already joined the availability group. This is an informational message. No user action is required.
2022-06-14 05:08:04.130 spid85s Always On: DebugTraceVarArgs AR ‘[HADR] [Secondary] operation on replicas [CF81C5D4-0DBF-4573-AB9D-018423FB92B2]->[0E5B3832-3DDA-41CB-9730-0F119639B535], database [JBSAG12], remote endpoint [TCP://JBSAG1.JBSWIKI.com:5022], source operation [708829B5-E20D-4112-9D88-050797FE9D91]: Transitioning from [CHECK_IF_SEEDING_NEEDED] to [CATCHUP].’
2022-06-14 05:08:04.130 spid85s Always On: DebugTraceVarArgs AR ‘Processing BuildReplicaCatchup event with HADR Role: [SECONDARY]’
2022-06-14 05:08:04.130 spid85s Always On: DebugTraceVarArgs AR ‘[HADR] [Secondary] operation on replicas [CF81C5D4-0DBF-4573-AB9D-018423FB92B2]->[0E5B3832-3DDA-41CB-9730-0F119639B535], database [JBSAG12], remote endpoint [TCP://JBSAG1.JBSWIKI.com:5022], source operation [708829B5-E20D-4112-9D88-050797FE9D91]: Transitioning from [CATCHUP] to [COMPLETED].’
2022-06-14 05:08:04.130 spid31s Always On Availability Groups connection with primary database established for secondary database ‘JBSAG12’ on the availability replica ‘JBSAG1’ with Replica ID: {cf81c5d4-0dbf-4573-ab9d-018423fb92b2}. This is an informational message only. No user action is required.
2022-06-14 05:08:04.130 spid31s The recovery LSN (37:1200:1) was identified for the database with ID 6. This is an informational message only. No user action is required.
2022-06-14 05:08:32.050 spid52 The Database Mirroring endpoint has stopped listening for connections.
2022-06-14 05:08:34.070 spid52 The Database Mirroring endpoint is in disabled or stopped state.
2022-06-14 05:08:34.070 spid77s The connection between server instances ‘JBSAG2’ with id [0E5B3832-3DDA-41CB-9730-0F119639B535] and ‘JBSAG1’ with id [CF81C5D4-0DBF-4573-AB9D-018423FB92B2] has been disabled because the database mirroring endpoint was either disabled or stopped. Restart the endpoint by using the ALTER ENDPOINT Transact-SQL statement with STATE = STARTED.
2022-06-14 05:08:34.080 spid77s Always On Availability Groups connection with primary database terminated for secondary database ‘JBSAG12’ on the availability replica ‘JBSAG1’ with Replica ID: {cf81c5d4-0dbf-4573-ab9d-018423fb92b2}. This is an informational message only. No user action is required.
2022-06-14 05:09:09.370 spid84 ALTER DB param option: SUSPEND
2022-06-14 05:09:09.370 spid84 Always On Availability Groups data movement for database ‘JBSAG12’ has been suspended for the following reason: “user” (Source ID 0; Source string: ‘SUSPEND_FROM_USER’). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
2022-06-14 05:09:26.920 spid81 ALTER DB param option: RESUME
2022-06-14 05:09:26.920 spid81 Always On Availability Groups data movement for database ‘JBSAG12’ has been resumed. This is an informational message only. No user action is required.

-> From the SQL Server error log on secondary JBSAG2, we can see that mirroring endpoint is stopped.

-> Lets check the DMV’s for more details,

select r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.last_connect_error_description,
rs.last_connect_error_number, rs.last_connect_error_timestamp
from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r
on rs.replica_id=r.replica_id

JBSAG1

Connection attempt failed with error: ‘10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)’.

JBSAG2

The Database Mirroring endpoint is in disabled or stopped state.

-> Lets try starting the mirroring endpoint using below command on Secondary JBSAG2,

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED 

-> Once above command completes, I can see databases on Availability group as synchronized,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.