Could parallelism be the problem here?
We are witnessing the slowness on the attached plan.
I have tried to remove the key lookups and other scans. But still see Many index seeks on red zone showing high operator cost.
Also, i can see lot of paraller threads.
Please advise what can be done to handle this SP executing run in almost 7 mins, which earlier used to run in a min or so. link text
No. There are only two queries in the extensive list that use parallel execution, and both run for less than four seconds.
Please advise what can be done to handle this SP executing run in almost 7 mins, which earlier used to run in a min or so.
The fundamental problems are pretty much the same as in your previous questions. Many relate to the expensive query inside SPGetManagerPendingAssetAndEmployeeCount, which is executed repeatedly within a cursor, with different parameter values each time.
But still see Many index seeks on red zone showing high operator cost.
These are just estimates. And the estimates are mostly wrong because you're using a multi-statement function to split strings into a table variable. I have suggested before that you stop doing this, and split the strings into temporary tables instead.
The different executions of SPGetManagerPendingAssetAndEmployeeCount have different specific problems, but most could be resolved by providing better indexes and switching to temporary tables for the split strings.
If you lack the time and/or skills to do a proper analysis and determine corrective actions, you may get some benefit from recompiling the problem query each time with an OPTION (RECOMPILE) hint. This will at least allow the optimizer to see the variable values each time, rather than reusing the existing plan. This still won't solve the split string problem, but it might help.
There are other queries with problems, but there's no way I'm going to even attempt to comment on each of the statements in that enormous file.
So. Try adding OPTION (RECOMPILE) to the select PendingITAdminAssetCount, case when @totalAssetCount =0 then 0 else… query in SPGetManagerPendingAssetAndEmployeeCount and see how that goes.
After that, concentrate on the queries that have the longest durations. You are welcome to post further questions about them, but please make the question about a single query plan. I try to be helpful, but it is difficult when a hundred different queries are supplied.