SQL Server 2012 – Incorrect Client Statistics Issue

Perhaps we can pull out the execution plan when it seems that particular query is running slow and compare it to the execution plan for when you run it. If you recently updated indexes it might still have the old exec plan cached, however, it does not answer the question as to why it runs fast when you execute the stored procedure from SQL Server, unless you're using different parameters.

Did you check if AUTO_UPDATE stats is on?

Is your tempdb optimized? Sometimes PAGELATCH_SH issues will be intermittent.

Can you view the wait stats, clear them, run the query, and then see the wait stats for when it was slow?

Viewing the plan cache: http://www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory

There is a simple solution to this:

Drop all of the _dta_... statistics and stop blindly applying DTA recommendations.

More information

The particular problem was that there were multiple sets of statistics for the column in question. The extra dta statistics were created by sampling the data (the default behaviour for statistics not associated with an index).

As is often the case with sampled statistics, the resulting histograms did not cover the full range of the unerlying data. The query in the question happened to choose a value that was outside the histogram, resulting in a 1-row estimate.

The exact behaviour of the query optimizer when multiple sets of statistics exist for the same column is not fully documented. It does tend to prefer 'full scan' statistics over sampled, but it also prefers more recently-updated statistics to older ones.