I've attached the actual execution plan containing the top 6 most time time-consuming queries and an Excel file containing more information about these queries taken from a couple of DMV's...

Here's the code for the clustered indexes:

create clustered index cls_ix_stores_for_budgets on #StoresBudgets (storeID, ProgramID);

create clustered index cls_ix_tmp_report_final on #temp_report_data (store_ID, newSourceID);

The other columns in the SELECT and GROUP BY...

I have tested adding the clustered index to the temporary tables and the overall time of running the stored procedure increased (unfortunately).

To be fair, the amount of time added is...

Totally noobie mistake, I attached the wrong file. I replaced it with the correct file both in the original post and have attached it here as well.

I'm sorry, I'm posting this issue on multiple websites, lost track a bit and forgot to mention it was a simplified version of the original query (that's why there are...

Thanks for the suggestions, actually the query posted above is a bit obfuscated, changed column names / some aliases etc. but the obfuscation from SQL Sentry worked much better (didn't...

ChrisM, I'm sorry but after a few discussions with my TL, I can't post the .sqlplan, due to NDA restrictions. Anyway, in the screenshots above table names have been changed...

Ok, I will do so and post it here.

But if I look at the cached plans and this is the one whose last execution time was during the execution time...

The execution plan is taken from:

, cast(qp.query_plan as xml)[Execution plan]

from sys.dm_exec_query_stats as qs

cross apply sys.dm_exec_sql_text (qs.sql_handle) as st

cross apply sys.dm_exec_text_query_plan (qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp

And as per documentation, it doesn't...

1. It's the actual execution plan, not the estimated. The execution plan from above is taken from querying the DMVs mentioned in my post.

The query that gives this execution plan...

I'm curious as to why in the last example he used a LEFT OUTER JOIN, although in other queries in the article he used LEFT JOIN. From my knowledge, there...

Very good article, but I have one comment though.

Possibly most people here know, but for those who don't, it's not dba.stackoverflow.com it's dba.stackexchange.com.

An alternative...

This is such a valuable thread! I have read all of your questions/answers and also searched on the internet for more possible best practices and I have wrote an article...

Hi,

There is an article which I've just recently found, article which details everything you need to do in order to avoid getting stuck in RESTORING state next time you do...