Need help in right direction to tune this Long running query with high cost in the Prod

DeeGee 2017-03-23 01:01:37

Can anybody please help me to understand the performance tuning better and guide in right direction.

This query used to take 1 hr 33 mins to run before. Surprisingly, they have non-clustered indexes on all the tables and the tables dont have any primary key, but it is a logical primaray key (combo of 5 – 7 different source system keys). I created and dropped the clustered indexes on the 6/7 different base tables in the query to get the rows defragmented and created a few covering non-clustered indexes with included columns on the existing ones which helped to bring down the execution time to 33 mins but the need is to reduce this time to just about 1-2 seconds.

i have developer access only & i have to get help from a DBA to get my tasks/queries running. i did all possible ways that I could think of. can someone pls help what else can i do to achieve the processing time to 1-2 sec

Every suggestion / help is greatly appreciated.

— Student forever…!!

Vlady Oselsky 2017-03-23 18:49:57
It is hard to tell a lot by seeing Anonymized plan, but few things that jump out right away are Conversions listed in Warnings, see screenshot below. Jonathan Kehayias has an excellent blog post How expensive are column-side Implicit Conversions? on issues with NVARCHAR conversion and how CPU intensive they can be.

My first recommendation would be to get rid of NVARCHAR conversions if you don't need them. If you don't have any UNICODE characters just by simply doing an Explicit conversion to VARCHAR, you can avoid a costly operation of going the other way.

The second recommendation would be to look at statistics as you have several bad estimates of 1 row instead of 32,244 rows. Having such large discrepancy will produce wrong execution plan which is trying to do the wrong type of physical join operations. A lot of times incorrect estimates can also be related to CONVERSION.

Finally you are using both DISTINCT and GROUP BY clause, which I'm not sure for what purpose, something is possibly missing by anonymizing the plan. The DISTINCT operation does not reduce the number of rows, therefore, is likely obsolete in this case.

alt text

DeeGee 2017-03-31 15:27:54
Thanks Vlady.

I achieved the performance tuning on this query. now it takes about 6-13 sec to finish the run based on the load on the server and jobs running in the background.

It was the bookmark lookup situation happening and i guess that is why it was doing bad estimates. So, when I rebuild the indexes with the INCLUDE clause for the non-included columns, the performance shooted up …!

There is a SELECT INTO happening here, so the source columns are NVARCHAR and so i have limited control over it, but In the join condition, I am converting it to VARCHAR to make a explicit match/lookup.

Vlady Oselsky 2017-04-10 15:17:38
If you have SELECT INTO in your plan you can still control datatype of target table by doing explicit conversion on columns, this will force SQL Server to create target table with specified data types instead column types coming from SELECT statement.