Hi there,

I have a table in SQL Azure with 21.5 million rows, it gains about 50k rows a day on average. There are a few indexes on the table, the one I am interested in is on the created date field. The index is just on that field. When I run a query [select * from table where createddate >= "a few hours ago"] the query takes a very long time (over ten minutes - or longer) to return. The estimated execution plan says the index on the created date field is not being used.

When I use a hint, the query returns almost immediately [select count(*) from table WITH (INDEX(IX_table_CreatedDate))]

Why is the query optimizer not using this index unless I tell it to? What can I do in SQL Azure to flush or otherwise fix the index or the statistics? Is there any safe thing I can do in a high volume production environment like this?

Thanks for any pointers!

• Moved by Kalman Toth Wednesday, April 29, 2015 12:30 PM Azure SQL related