There is a stored procedure used for one of my daily reports that includes 62 sub-queries UNION ALL Recently, The display lasted less than 1 minute for 8 minutes and was using the SQL Profiler, it was showing very CPU and Reids. To prevent sniffing the parameter, the process set in the variable value is currently set to local variables.
As a selection statement, the content of the process was run and the performance was returned under one minute in the management studio through the process called Acic and the display was terrible and was more than 8 minutes RECOMPILE The calling process, including the EXEC, did not improve with command and performance. I participated from DBCC Freeprocachach and DBCC Draplenbufers and there was no improvement yet. Finally, I left the process and applied it again and now the performance is back.
Can someone help me explain why the initial steps did not correct the performance of the process, but due to the process being abandoned and re-implemented?
The blocking parameter seems to be sniffing a bad plan is produced when you use local variables So the query optimizer uses the density to come up with cardinalysis estimates for each column, essentially optimizing for the average value. If you have data delivery important, then this estimate will close importantly for some values. This principle tells you why your initial steps were not there. Experimenting with reflm or running dbcc freepraxes will not help if the sniffing of the parameters is blocked. It will produce the same plan every time since you say that the subject of the process is going on in the form of a selection statement, it is faster than I think you actually need to sniff the requirement parameter, however if you Collection time is acceptable, then you have to try experimenting with Ricom, otherwise there is a risk of getting associated with a bad plan based on the values of atypical sniffing.
No comments:
Post a Comment