As you know Index recommendation is very handy when you execute a query and check the execution plan and SQL Servers suggest to create an index and this much gain will be there in query performance, Its not always correct however very helpful. All the suggestions shouldn’t be implemented neither it should be ignored completely.
These recommendations are stored in DMV sys.dm_db_missing_index_details. In this post, we are going to see example of a query where there is no recommendation from SQL Server side however if you create an index voluntarily based on your understanding of query, it could help optimizer immensely.
Let’s see below screenshot from Stackoverflow database:
Let’s check the messages table for logical reads:
Now, lets create an index on Location column as the grouping is on this column of Users Table as below:
Now, lets the execute the same query again where we were grouping by Location:
It used our index, let’s check the messages table now to see logical reads:
The logical read has come down from 140k to 20k, almost 7 times less. So, as we have seen SQL Server doesn’t recommend creation of index in all the cases.
Let’s see few more examples of similar types:
In the above query, we are selecting top 100 Location from Users table order by Location(Descending Order), as you can see there is no index recommendation from SQL server:
In absence of any supportive non-clustered index, optimizer has to go for full table scan and logical reads and time stats are as below:
Now, lets introduce an index on Location column(similar to previous example) and see how the execution plan changes:
Optimizer has used our index very much and didn’t go for full table scan, lets see logical reads and time stats:
As you may see, logical read has come down from 140k to just 8, isn’t it awesome however this was done without SQL Server recommending it.
Want to check few more? Let’s see one more example:
It is a simple aggregate count function wherein, we are checking number of entry in the Users table, in the absence of any NC index, optimizer has to go for full table scan. Logical read and time stats are as below:
After introducing Nonclustered index, aggregate function will look for the smallest copy of table and it would make use of that as below:
Let’s check the logical reads and Time stats:
Here again, as you can see logical reads have come down from 140k to 20k and this also didn’t have recommendation from SQL Server to create an index.
Want to check one more? Ok, here it is:
It is a random query wherein I want to select top 100 location ordered in descending order who have earned badges. As you may see, there is an index recommendation from SQL Server on the join part however not on the ordering part. In the absence of any index, both the tables are scanned completely. Logical reads and time stats are as below:
Now, lets create two indexes – one as suggested(recommended) by SQL Server and one by ourselves as below:
create index Location on Users(Location)
go
create index UsersId on Badges(UserId)
go
Post creation of these two indexes, we execute the same query again and check the execution plan and notice that optimizer has used both the indexes whereas only one was recommended:
Let’s check logical read and time stats as below:
As you may see, although SQL Server doesn’t recommend index in all the cases however if you create them, it is very much used and helps in performance in big way!
Please leave your comment if you liked this post or have any feedback.