Doing some integration work and ran into this.
I ran this query – “SELECT * FROM AlertActive a INNER JOIN AlertObjects b ON a.AlertObjectID = b.AlertObjectID JOIN AlertConfigurations c ON b.AlertID = c.AlertID”
Took 2:33 seconds to execute and return 2700 rows. I then did a estimated execute plan and 97% of the resources were done on the PK for the active alerts table, clustered index or whatever.
I then reviewed the daily index rebuild and made sure active alerts was included in the plan, then I executed it.
Post maintenance plan that query now returns in 3 seconds with same amount of rows.
Totally didn’t expect such a drastic difference. The fragmentation on that index was only .2%
