cancel
Showing results for 
Search instead for 
Did you mean: 

Table not choosing index in DR but working on PROD

habibbankdba
Explorer
0 Kudos

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.

Accepted Solutions (0)

Answers (3)

Answers (3)

habibbankdba
Explorer
0 Kudos

I tried to simulate the same in UAT by dropping the primary key and creating it, also ran reorg rebuild tablename indexname.

Have attached the output for PR and DR(when DR was going tablescanpr-dr-showplan.txt)

After that when I ran it with showplan its taking index:

1> select convert(varchar(30), name) as name, indid,
convert(decimal(5, 3), derived_stat(id, indid, 'sput')) as 'sput',
convert(decimal(5, 3), derived_stat(id, indid, 'dpcr')) as 'dpcr',
convert(decimal(5, 3), derived_stat(id, indid, 'drcr')) as 'drcr',
convert(decimal(5, 3), derived_stat(id, indid, 'ipcr')) as 'ipcr',
convert(decimal(5, 3), derived_stat(id, indid, 'lgio')) as 'lgio'
from syspartitions where id = object_id('visamessagetime')
go2> 3> 4> 5> 6> 7> 8>
name indid sput dpcr drcr ipcr lgio
------------------------------ ------ -------- -------- -------- -------- --------
VISAMessageTime_1529850054 0 0.944 0.998 1.000 0.000 0.998
VISAMessageTime_PK_1529850054 2 0.923 1.000 1.000 0.998 0.998

(2 rows affected)
1> set showplan, noexec on
go
DECLARE @fromdate datetime
select @fromdate = '1 Apr 2018'
select * from visaMessageTime where Date > @fromdate2> 1> 2> 3>
4> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is DECLARE.

Total estimated I/O cost for statement 1 (at line 1): 0.

QUERY PLAN FOR STATEMENT 2 (at line 2).
Optimized using Serial Mode

STEP 1
The type of query is SELECT.

1 operator(s) under root

|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)

Total estimated I/O cost for statement 2 (at line 2): 0.

QUERY PLAN FOR STATEMENT 3 (at line 3).
Optimized using Serial Mode
STEP 1
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
| |SCAN Operator (VA = 0)
| | FROM TABLE
| | visaMessageTime
| | Using Clustered Index.
| | Index : VISAMessageTime_PK
| | Forward Scan.
| | Positioning by key.
| | Keys are:
| | Date ASC
| | Using I/O Size 4 Kbytes for index leaf pages.
| | With LRU Buffer Replacement Strategy for index leaf pages.
| | Using I/O Size 4 Kbytes for data pages.
| | With LRU Buffer Replacement Strategy for data pages.
Total estimated I/O cost for statement 3 (at line 3): 2322575.

After this I did the same in DR and now its taking the index when ran with the query and with stored procedure.

so basically reorg did not help but dropping and recreating the primary key helped it seems.

Still confused what difference did it make?

habibbankdba
Explorer
0 Kudos

If I am not wrong what I was assuming was we usually check ipcr for non-clx indexes and dpcr for clx index, because in that way dpcr for the only clx index is 0.933.

I will try the other possible way of dropping and recreating indexes and revert.

Also, procedure deferred compilation is enabled on DR

0 Kudos

The reason why

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

works differently from

select * from visaMessageTime where Date > '1 Apr 2018'

will be because the first form will use magic numbers in the optimiser rather than the actual statistics which will be used by the second version as can be seen from your 302 output:

Estimated selectivity for Date,
selectivity = 0.33,
scan selectivity 0.33, filter selectivity 0.33

0.33 is just the optimisers best guess when it doesn't know an actual value.

However the behaviour will be different when you run the query within a stored procedure due to the deferred compilation functionality. If you create a simple stored procedure containing the declare/select/select version and compile that with 302 active you should see the optimiser using actual statistical values the first time you run it and the showplan should be correct. If you don't then check the config paramter "procedure deferred compilation".

Use this little procedure to compare prod and DR again. If you still see prod using the index and DR doing a table scan then I suspect it is something to do with your index page cluster ratio which you want as close to 1 as possible. This is very good on prod (Index Page Cluster Ratio 0.9913466) but not so good on DR (Index Page Cluster Ratio 0.7563466). Reorg rebuild should have fixed this and I am at a loss to explain why it did not. Try dropping and recreating the index to fix this.

If you are still experiencing an issue after you get your index page cluster ratio close to 0.99 then I would open an incident so we can look into this further for you.