[Nop_PartsFamilyAttribute].[_dta_in… index fragmentation?
How to rewrite query to take small time when executed ?
I work on SQL server 2012 it take too much time to enhance it
so how to enhance it to take small time
how to write query this best enhance to take less time ?
it take too much time near to half hour to return 50000 rows
execution plan
https://www.brentozar.com/pastetheplan/?id=H1XwcNHXv
3 answers
Sort by: Most helpful
-
-
Erland Sommarskog 111.8K Reputation points MVP
2020-08-27T21:50:45.077+00:00 First of all, remove all those NOLOCK in the query. I assume that you want your queries to return the correct result? If you put in NOLOCK on every table you are basically say "I don't care if the result is correct or not".
Next the execution plan you provided only has estimated values. This makes it more difficult to assess where time is being spent. However, I notice that the plan estimates that the query will return two rows, and you say that it returns 50000. This indicates that there is a problem with the estimates somewhere. Maybe your statistics are out of date?
-
MelissaMa-MSFT 24,201 Reputation points
2020-08-28T06:25:37.29+00:00 Hi @ahmed salah ,
According to the exection plan you provided, no enhancement is needed since it is already index seek and seek predicate under it.
It is better for you to provide the execution plan of your complete query which returns 50000 rows so that we could proceed to check any enhancement needed.
In addition, there are some suggetions with your query provided.
- Manually update statistics.
- Use trace flag 2371(DBCC TRACEON(2371,-1))
- Find any possible to change from too many joins to exists.
- Consider to use some tune tools like Database Engine Tuning Advisor (DTA).
If the response is helpful, please click "Accept Answer" and upvote it.
Best regards
Melissa