Well it depends on the workload on the server.

The buffer pool will always remains in the range of Min and Max memory setting value and will not be paged out...

Oops..I wanted to post the reply for the below thread

http://www.sqlservercentral.com/Forums/Topic1074846-1550-1.aspx

Is this the same to what I had replied here ?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=157892#617864

No I am not speaking about indexes.

I am saying that if you try to change the data type of a primary key (as you mentioned earlier) then it will fail...

Also have you considered the fact that changing of data type on a primary key wont be allowed unless the corresponding foreign key data type also is not of the...

Do Not use Mirroring as a substitution for replication.As in your case your boss suggested to use mirroring for reporting purpose.

Mirroring should only be used for High availability only.Keep it...

Is it a 32 bit or a 64 bit ?

Have you set Min and Max Memory settings for each instances ?Is your box a 64 bit or a 32 bit ?

First thing I would monitor would be PLE,Buffer Cache...

A simple answer would be "Restore the Backup".

Thanks for the feedback.

Just one confusion here. Are only those query plans cached whose data pages are present in the buffer pool ?Because if query plans whose data pages...

My best guess would be lot of sort and spool operations happening which maybe creating lot of worktables in the tempdb.

Try monitoring "Workfiles Created/sec" and "Worktables Created/sec" in performance monitor.Ideally...

I think it is "Most Common Questions" or "Multiple Choice Questions" 🙂

Did you delete the files manually before? If yes then this might help

http://support.microsoft.com/?scid=kb%3Ben-us%3B173180&x=8&y=15

You are welcome.

Yes you need to trace Memory\ Page Reads /sec.If those value are also high along with Memory\Page Faults/sec then it will indicate a hard page fault.

All counters look fine for me except Memory\Page Faults/sec which are way over the limit.

But before that you need to figure out how many of them are hard page...