We have a table with 190M records. there is an index on a fairly unique column but still, if I run a query on the table selecting top 10 with only the indexed column in the where and select clause it still takes ages to complete.
If I turn on live query plan sql server shows the 'waiting for query plan" waiting spinner. If the query finally finishes and if I run it again it then completes instantly. Even if I run DBCC FREEPROCCACHE before So it feels like the creation of the query plan is taking all the time but then i cannot reproduce by running DBCC FREEPROCCACHE before the query. After a while the same issue will reoccur.
Note: The database has 2 filegroups. Indexes and system databases are stored on SSD disk while normal table data is stored on a non SSD disk.