Need help in right direction to tune this Long running query with high cost in the Prod
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…!!
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.
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.