This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 99%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EN%3C/text%3E%3C/svg%3E)
one of our applications that selects data from tables using multiple joins only work when update stats is performed on all underlying tables.
we had to update stats like evry 5 mins.
what could be the reason stats needing update for the query to perfrom, why is the query unable to re use stats.
any possible reasons.
Additional SQL Server features and topics not covered by specific categories
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 78%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECZ%3C/text%3E%3C/svg%3E)
Hi @NeophyteSQL ,
Is there any update on this case?
query involves a cte with multiple joins and then case statement with or in the where clause
then the cte is used to select the data required based on multiple joins with reference tables
Hi,
This does not bring new useful information
Execution Plan (the XML and not image) and DDL and the query text could help more
Seems that you are not very keen on getting help, since you are not sharing much information. You could not even make the effort to gives the output of SELECT @@version!
There is no difference between static and dynamic SQL with regards to statistics.
CASE expression in the WHERE clause may not be the best for performance. Then again, that depends on the rest of the query.
If that CTE is referenced in multiple places in the query, it may be better to materialise the result into a temp table.
A shot in total darkness: add OPTION(RECOMPILE).
Hi,
is there a reason for dynamic sql to not use statistics.
Obviously no. Any execution of query (dynamic or static) is done after the server check the statistics and build Execution Plan.
There are different parameters that impact the Execution Plan which the server build and/or choose
You should really provide DDL and the query which you use at least, so we will have understanding of your table(s) structure. This discussion is not useful without it in my opinion
what could be the reason stats needing update for the query to perform
Usually there is no need to update the statistics for each execution, and it is not done by the server for each query that you execute. Updating the statistics can impact the performance since the server will create new execution plan based on the new statistics. But as Erland said you can simply told the server to create the Execution Plan again by using sp_recompile (test if you really need to update the statistics manually or simply recompile)
thanks you very much.
there are multiple stored procedures involved.
sql server 2016
one of the stored procedures uses dynamic sql, is there a reason for dynamic sql to not use statistics.
only when the statistics are updated, the application can select the data, I shall post the query shortly.
thank you
All sorts. With the amount of information you have provided, this is about as intelligent answer I can give.
Next time, rather than running UPDATE STATISTICS, just do sp_recompile on of the tables.
It certainly could help if you posted the query. Also it would help to know the output of SELECT @@version and the compatibility level of the database.