Extremely slow query, no blocking, not bad execution plan: Why?

Question

I have a table valued function that insists of 8 INSERT INTO statements. For each INSERT INTO statement, they follow the same structure as:

DECLARE @TABLE1 TABLE (Column1, 2, .....);

INSERT INTO @TABLE1 (column1, 2, ...)
    SELECT * FROM BASETABLE1 

The function returns a table that joins those 8 tables (using LEFT HASH JOIN). In the past, this function would run about 7 minutes to complete (the base table(s) are big that of over 1 million data), but most recently, this function is very slow and took forever.

Here are what I've done to get an idea of the slowness:

1. I've checked the blockings, no blocks of the server;
2. I've checked the execution plan, no major changes recently, and the base tables are properly indexed with updated statistics;
3. I looked at the sp_who2, I have to admit this server is pretty busy, a lot of agent jobs and tableau connections are going on at the same time, and quite some processes involve with this function. However, I would say the level of business is the same as before and now, and why the slowness is just happened recently?
4. I also checked the active expensive queries, and obviously the most expensive ones are those INSERT INTO queries;
5. We patched the windows (that hosting this SQL Server) about 10 days ago, so I wondered if that patch has any impacts on this slowness? We also rebooted the SQL Server 4 days ago trying to fix the issue. The situation was better after the reboot (but not as good as the issue happened before), and today, it became worse again.

Anything else that I might missed?

Answer 1

Your

insert into ( columns1, 2, etc... )
   select * from BaseTable

Is that your actual query? The *, I would expect you to explicitly indicate the columns you wanted to pull. I have seen weird things if a table structure was altered, are you getting what you really think are the correct columns and same sequence as insert is expecting? Also, you have no WHERE clause, so you are pulling the entire database from one into a memory table and for what benefit / purpose?

You mention a table valued function. Is that what this overly broad insert statement is, or is there some other context to it and you are just masking it for us?