Wrong statistics

Wrong statistics in my query plan

avatar image By Tulio Henrique 3 asked Sep 29, 2014 at 11:52 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Sep 29, 2014 at 11:57 PM

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...

avatar image Tulio Henrique Sep 30, 2014 at 12:15 AM

the statistics are already updated

avatar image Aaron Bertrand ♦ Sep 30, 2014 at 12:23 AM

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.

avatar image Tulio Henrique Sep 30, 2014 at 12:32 AM

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

avatar image Tulio Henrique Sep 30, 2014 at 12:41 AM

link text required query plan

10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By SQLkiwi ♦ 6.6k answered Oct 01, 2014 at 07:56 AM
more ▼
(comments are locked)
avatar image Tulio Henrique Oct 01, 2014 at 08:07 AM

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

avatar image Tulio Henrique Oct 01, 2014 at 08:10 AM

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?

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x631
x166
x109
x87
x26

asked: Sep 29, 2014 at 11:52 PM

Seen: 293 times

Last Updated: Oct 01, 2014 at 08:10 AM