Wrong statistics

Tulio Henrique 2014-09-29 23:52:20

Wrong statistics in my query plan

Aaron Bertrand 2014-09-29 23:57:08
Well it doesn't seem to be a parameter sniffing problem (the compiled and runtime parameters are the same); next try would be to actually update statistics on the tables involved…
Tulio Henrique 2014-09-30 00:15:04
the statistics are already updated
Aaron Bertrand 2014-09-30 00:23:32
It's SQL Server 2014, another option to try is if you've been a victim of a regression due to the new cardinality estimator. Try running the query with OPTION (QUERYTRACEON 9481). Also if you can generate an actual plan by executing the query in Plan Explorer that will give us some other interesting details not currently exposed by the plan generated in Management Studio.
Tulio Henrique 2014-09-30 00:32:48
http://pastebin.com/FNLPFTY2 query plan with the trace enabled, I use SQL Server 2014 Enterprise CU3 12.0.2402

DBCC TRACESTATUS
1117, 1118, 2371, 2389, 2390, 2549, 2562, 3226, 4199, 8032,
8048

Tulio Henrique 2014-09-30 00:41:51
SQLkiwi 2014-10-01 07:56:07
>Wrong statistics in my query plan

The estimated row counts are pretty reasonable in most places in this plan. There are a few areas where the discrepancies are larger, but this is not at all unexpected in a query with six joins and seven non-join predicates.

The statistics maintained by SQL Server are single-column only, with a maximum of 200 histogram steps. Even if you were to create all potentially-useful multi-column statistics, there are definite limits to what can be achieved in situations like this. The expected accuracy degrades with each operation, because we are now dealing with derived statistics on the output of, say, a join.

I don't see anything in the uploaded plans that would cause me concern. The one plan that does include performance information shows a total execution time of 381 ms. If you're experiencing a particular performance problem, you should update your question to explain exactly what that is, and what you would like to achieve.

In general, where an inaccurate cardinality estimation does lead to a performance-affecting plan selection, and additional statistics do not help, the query can be broken in steps using temporary tables (not table variables) to give the optimizer information about the size and value distribution of intermediate results.

Tulio Henrique 2014-10-01 08:07:53
My problem is that these queries are run thousands of times a day, they have some variation of parameters only

Multiple queries are experiencing this problem then just posted one of queries to find out the reason for this

Table Conteudos has 2385976 rows
Table ConteudosCategorias has 688850 rows
Table ConteudosGeneros has 2068176 rows

Tulio Henrique 2014-10-01 08:10:47
I updated the statistics again this time with the parameter 'WITH FULLSCAN' seems to yield a better result

In case it makes any difference using the 'WITH FULLSCAN' what would be the best way to update a statistic?