The query is a bit suspect to be honest, but since it's a end of day batch. It's not expected to run in seconds, but 4 hrs plus is too...

Thats what I done in the end recently, I first of all populated the temp table, then updated the columns that use the function seperately, what I dont understand is...

This is just a benchmark load, the intended DB workload is a datawarehouse.

The size is around 200GB for both differential and full.

Looks as though the problem lies within your joins, start bit by bit and don't try to run the whole thing at once. I am seeing things like Hash match...

If you can send the graphical execution plan, I may be able to help you.

In my own experience, when I had same issues, it could be due to the following

Bad...

Any ideas guys.

Have you tried powershell ?

I generally dont like maintenance plans, I like the idea of having script and customising to business needs etc.

What I had was a stored procedure that can do all 3...

Thanks for the reply, is that the best way to do it, how about functions that are part of a check constraint, returns a bit. is that better ?

Depending on certain factors and also how your infrastructure is setup. You have options.

You can use backup/restore to refresh the data.

You can use SQL replication.

You can use SQL log shipping.

You...

Look at parameter sniffing, thats normally the cause of these problems. look at the procedure cache for invalid plans which have been cached.

Adding a bit of more information.

Source,Severity,Message

spid74,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.

spid74,Unknown,MEMORYBROKER_FOR_RESERVE (internal) Pages<nl/>----------------------------------------...

OK, thanks chaps. I am thinking how about environments where the database design is suspicious, the queries/stored procedures dont look tuned or dont follow best practice.

Also, I think it would...

If its one specific query, I would look at the Database Tuning advisor. From experience of upgrading to SQL 2008, this i what you should do.

Update statistics on every table...