We have a huge SQL 2017 database containing more than 5 billion internetpages. One of the table contains the info about the page: PageID is the primary clustered key and UriKey is the non clustered index so we can lookup based on the Uri of the page. There are a lot more columns in that table as well, related to that page.
If we join with other tables and we join on the UriKey, the query will use the non clustered index only if we only require the UriKey itself. Once we add another column, it does a clustered index scan, wich can take up to days to complete.
This uses the correct index and takes less than a second to complete https://www.brentozar.com/pastetheplan/?id=rkk1vIb07
select kps.Page_ID, p.PageID, r.*, p2.UriKey
from LB_Keywords_Pages_Static kps
inner join spidir2.dbo.se_pages p
on kps.page_id=p.pageid
inner join Temp_KeyboostRedirects r
on p.UriKey like '%/'+ r.source +'/%'
inner join Spidir2.dbo.SE_Pages p2
on p2.UriKey='https://www.keyboost.nl'+ r.target
where Account_ID=-2147482063
This selects one column extra (p2.Domain_ID) and takes more than a day to complete https://www.brentozar.com/pastetheplan/?id=ByOpUIb0m
select kps.Page_ID, p.PageID, r.*, p2.UriKey, p2.Domain_ID
from LB_Keywords_Pages_Static kps
inner join spidir2.dbo.se_pages p
on kps.page_id=p.pageid
inner join Temp_KeyboostRedirects r
on p.UriKey like '%/'+ r.source +'/%'
inner join Spidir2.dbo.SE_Pages p2
on p2.UriKey='https://www.keyboost.nl'+ r.target
where Account_ID=-2147482063
When we specify the index to be used, it works like a charm https://www.brentozar.com/pastetheplan/?id=SkvkKIbAm
select kps.Page_ID, p.PageID, r.*, p2.UriKey, p2.Domain_ID
from LB_Keywords_Pages_Static kps
inner join spidir2.dbo.se_pages p
on kps.page_id=p.pageid
inner join Temp_KeyboostRedirects r
on p.UriKey like '%/'+ r.source +'/%'
inner join Spidir2.dbo.SE_Pages p2 with (index(SE_Pages_UriKey))
on p2.UriKey='https://www.keyboost.nl'+ r.target
where Account_ID=-2147482063
I noticed this strange behavior also with other keys and queries. I didn't notice this behavior before when we were using SQL 2016. This is causing our database to become very slow.
If we really have to specify every time the index to be used, wouldn't it be better to move to a nosql database?