Query taking to long to run –

Jayce72 2018-02-12 15:55:08

this query is taking up to an 12 mins to run if the data parameters in the WHERE are for a few day but if the date paramters in the WHERE are for a few years it takes a minute or two. Also, It hogs all the processing power and stops other users from doing anything until the query has stopped or ended

Hugo Kornelis 2018-02-12 19:54:15
Plan Explorer's anonymize feature takes itself serious. So serious that the result is in most cases not suitable for any reasonable tuning.

I guess I can study the plan, draw your attention to a few areas and give you some pointer that may or may not help, but it is very likely that the process will be ineffective, the results inefficient, and I will probably miss the real issue(s).

If for any reason you really NEED to use this anonymization, please say so and I will take a stab – but manage your expectation.

If there is nothing top secret in the query (such as table and column names, but also data values used in the query e.g. in a WHERE clause), then you can get far better help if you do not anonymize the plan. Also, since you mention different behaviour depending on the parameters used, I would prefer if you can post a .pesession file that includes TWO actual plans, one with good performance and the other one with bad performance.

Finally, I know that Plan Explorer has different ways of capturing stuff and not all have the same amount of data. One option in particular includes statistics information as well (you can access this in the Index Analysis tab). Though this can be incredibly useful in some specific scenarios to troubleshoot, note that this information does contain up to 200 actual values from your database in each statistics object. So do check the data that is there before sharing, to make sure you are not sharing any sensitive data.