I read this article by Kimberly L. Tripp.
Very interesting stuff about wasting Cache, Ad Hoc Queries optimization,
bad plans:

http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

Dear,
Kevin G. Boles,

We are located in Toronto Canada.
What is the best way to contact you?

Jeff, as usually your response is the most thorough, detailed, to the point.
Thanks a lot.
Just a few questions.

"....it may have been a lot of one-off...

Guys, I did a test on my local SQL Server. Tempdb Initial size was 8MB.
I grew it manually to 50MB.
I did reboot.
Initial Size = 50MB.
Why everybody...

SQLGuru,

It was already set to the same size - 2GB and same Autogrowth 200MB
once. Why do it again?

Perry.
I removed my controversial comments about Brent.
I have to be honest.
I learned so much from Brent.
Even if sometimes I wish he could be a bit more...

"simulate production load".
Easy to say.
Impossible to do in our environment.
Busy Web Application + hundreds of automated SQL jobs running every 1,10,20 minutes.
It's a whole project to...

I read this Brent Ozar article.
It's a tutorial how to collect performance counters data and try to analyze it.
It can take months of experimenting to understand this material...

We set Memory Max to
112640 MB
SQl Server is installed on Virtual machine
128 GB of RAM

Min Memory = 0
Max Degree of Parallelism = 0
Cost...

John Mitchell,
Thanks for your replies.
I thought that the calling machine (where Agent is running) 
will execute SSIS and use its Memory.
SSIS packages are stored as files on...

I read on one of Brent Ozar blogs that it's a good idea to move SQL Server Agent to a separate box
because it's like another busy application that is...

Guys,

What exactly means"....tweak some of the server settings (optimize for ad hoc, parallelism etc) ...." ?

We have:
One main OLTP database 3TB. There is a huge partitioned table that takes 2TB.
Other 7-8 databases much smaller sizes.
SQL Server is on VM.
RAM=128 GB
CPU...

Maybe my English is not good enough...

Is this the setup you're proposing?

Move Batch table to ETLtools database.

Load file into ETLtools database.

Create BatchID (Primary Key) in Batch table.

Staging1 table -...