Why Index Hints Should Not(judiciously) Be Used(My Experience)
I am sharing here my recent experience with one of our vendor who is considered as very much reputed and experienced, they have team of DBAs(development DBA cum Architect, Production DBA, Infrastructure DBA etc..) and mostly their database codes as well as their indexing are not disputable.
I am the lonely DBA working in my organization who is looking after MSSQL server database as well as Sybase ASE . Here I am going to talk about the issue with Sybase ASE code. There was a report which basically sums up all the balances for a given module(number) and for a given branch – to understand in technical terms, it has to take sum of all the modules for all branches one by one(looping through) and the where clause is nothing but date condition which varies from 1 year to 2 year – So, it is safer to say this report basically runs for full table joining with few other tables.
The above report has to be run twice whenever there is a major activity or change happens on the application or database in order to ensure that the balances are intact before and after the change. And this report was taking almost 30-40 minutes for single time execution. So, for any major activity, this 1 hour had to be factored in for getting approval of down-time. Lets say, we need to upgrade our disk to SSD disk or we need to upgrade Sybase ASE or upgrade application etc. which will take 1 hour of down-time however due to above report duration, down-time we needed to have is 2 hour. It wasn’t very logical to me, so we communicated this concern to this vendor – they connected remotely, checked, gathered required data and came back after few days saying that this is the best possible time in the given scenario. If we need to improve the duration then, we should buy more number of cores of CPU or memory. After this, nobody said anything as it involved cost and budgeting.
Recently I was checking the query which gets fired in the back-end when this report starts running, I noticed that they are using index hint in one of the largest table which is referred hugely and stores running balances for each module and branch. I didn’t like it however can’t report it without having concrete evidence considering above situation they have so many clients in this region and nobody seems to have reported anything.
I started looking at the query, ran it manually and to my surprise – it was using some other index which was far-off from the index hint for some of the branches whereas in case of one of the branch i.e. BRANCH_CODE 1(one having most of the data) – it is doing table scan. Compared the logical counts with hint and without hint – saw that logical reads are actually lesser for table scan than the one with their index hint.
Considering above, I was pretty sure that index hint definitely needs to be removed however I needed to support the query with a new index which could support all the branches. Since the report query runs in loop for all the branches, only one query plan which was getting generated for the most heavy branch was getting used for all the branches. Here I had to find an index which could fit to all and shouldn’t be ignored by optimizer.
Query can’t come back to its decision phase even when the branch is changing as it has already entered into execution phase.
I started checking the selectivity and counts were as below:
select count(1) from Table_Name where STATUS = ‘A’ — 68,491,955
select count(1) from Table_Name where Module_Code BETWEEN 100000 AND 591299 — 47,586,751
select count(1) from Table_Name where VALUE_DATE BETWEEN ‘2020/01/01’ AND ‘2021/01/31’ — 37,003,819
select count(1) from Table_Name where BRANCH_CODE = 1 — 50,467,802
select count(1) from Table_Name where BRANCH_CODE = 2 — 1,221,615
select count(1) from Table_Name where BRANCH_CODE = 3 — 2,471,631
select count(1) from Table_Name where BRANCH_CODE = 4 — 1,568,522
select count(1) from Table_Name where BRANCH_CODE = 5 — 1,822,014
select count(1) from Table_Name where BRANCH_CODE = 6 — 2,454,244
select count(1) from Table_Name where BRANCH_CODE = 999 –142,878
select count(1) from Table_Name where CCY BETWEEN 1 AND 15 –47,565,415
select count(1) from Table_Name where Module_Code BETWEEN 100000 AND 591299 –47,504,276
Here BRANCH_CODE is very much selective for all of them except for BRANCH_CODE 1 and VALUE_DATE could also be considered and hence I chose to create a new index with below defintion:
create index IDX_Table_Name_BRANCH_CODE_VALUE_DATE on Table_Name(BRANCH_CODE, VALUE_DATE)
After this index got created, query started using this index and performance of the report increased significantly. Earlier it was taking close to half an hour, is completing now in 15 -17 minutes. After doing these changes, I noticed that its putting lesser load on the CPU also.
Below are key take away from this experience:
- Index hint is always(almost) a bad practice and should be tried to be avoided.
- If Query is using the same index which is hinted even if it is not part of hint then why shouldn’t you let query decide which index to pick from, instead of forcing it.
- If the query is not using index which you think is appropriate, means it either needs a different index or table scan is cheaper than any other index.
Above observations are under the consideration that maintenance job for updating statistics and reorg or rebuild(as appropriate) is in place and executed periodically.
I have explained upside and down-side of using index hint in my video link.
Please leave your comment if you liked this post or have any feedback.