Recently in production server, one of our query is performing poorly. I checked the Query Plan which is suggesting missing index. I was confused as it supposed to have the suggested Index. I checked SSMS, Reports -> Standard Reports -> Schema Changes History and identified that there was a schema modification on this table.
Diagram: Query Execution Plan with Missing Index Suggesting
Discussing with team members, I got an interesting insights. Last time when the table was re-created, one junior team member generated the CREATE scripts without the Indexes! Further investigating, I found that the default settings of Script indexes was false.
Diagram: Script indexes Options
I updated the default settings from Tools -> Options -> SQL Server Object Explorer -> Scripting. Now it is scripting tables with all their indexes!
My learning from this incident is "Before scripting database objects, always check the Script indexes options".
Let me know, if you had similar experience!