October 5, 2018 at 2:32 am
Hi,
I've got two queries which Access the same tables. For both of them, SS17 recommends to add non-clustered Indexes. As for my experience, these recommendations work quite well. The suggested Indexes target the same table. They're quite similar and they use and INCLUDE slightly different columns. So my idea was to 'manually merge' these Indexes into one, that's of course a bit larger.
When I run the query again, the System (execution plan) still recommends the original index to be added to that table. Now I'm somewhat confused. Would the engine use an index only, if it matches exactly? So in this case, would I have to accept the drawbacks of disk storage, maintaining etc. in order to accelerate the queries?
Thanks,
Roger
October 5, 2018 at 3:15 am
The SQL Engine would only use an index if it feels that it's costs are lower than otherwise getting the data. One very well example of INCLUDE Columns which might cause SQL Server to not consider (by default) an index is included (n)varchar(max) Columns, especially if they're not needed. You can use query hints to force SQL Server using that specific Index or even Execution Plan tho.
Just because SQL Server doesn't statistically see a certain Index as fastest option, doesn't have to be that it's not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply