I noticed that when sp(stored procs) are run second time they get data from buffer so they are run fast on second time.
on the other side i noticed that database never gets bigger than 2.5 gb, and the ram on system is 4 gb.
there is only one situation , when server is restarted it flushs the buffer and cahce.(db does not get insert update when it is online,we transfer data at offpick time and it takes quite less time)
above was the only situation when sp will become slow.
q1) So please tel me , what i am thinking is correct or not , that is my sp will be slow for first time and then
they will be alywas fast and all data will be in memory. untill unless server is restarted.
q2) Is there any technique to bring all tabels data in buffer manually so that sp can become fast even on first time , similar to the execution of second time?
I have pk and fk in the database , i want to find out where i can make some good indexes.(sqlserver 2005)