Skip to Content
0
May 02, 2018 at 07:51 AM

Table not choosing index in DR but working on PROD

142 Views

Standby version: Adaptive Server Enterprise/16.0 SP03/EBF 27168

we have recently switchover to DR and after that we have noticed few tables going for table scan and not using the index whereas the same table in prod are using the index.

all the configurations and cache binding are same both sides

for e.g we have taken a small table VisaMessageTime:

This has a composite index:

VISAMessageTime_PK Date, MessageType, TranSerial, TranSequence, InOROut,clustered, unique

This table is actually used in a procedure but we have simulated this by making a test query as below:

1> DECLARE @fromdate datetime
2> select @fromdate = '1 Apr 2018'
3> select * from visaMessageTime where Date > @fromdate

But when we use BETWEEN or pass the dates directly it uses the index.

We have tried running reorg rebuild, reorg rebuild index,update index statistics, sp_recompile and dbcc proc_cache(freeunused.)

name indid sput dpcr drcr ipcr lgio
------------------------------ ------ -------- -------- -------- -------- --------
VISAMessageTime_1529850054 0 0.944 0.999 1.000 0.000 0.995
VISAMessageTime_PK_1529850054 2 0.923 0.700 0.930 0.998 0.983

I am attaching the sp_help,trace 302 output for both primary and DR also optdiag for DR,query plan for testing quey

we had confirmed about out of range statistics as well for Date column as it has 72 steps in total. I am not sure how to analyze that.

someone recommended using only Date column instead of a composite index of 6 keys.