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.