tcronin 95651 (1/9/2015)
I have 2008r2 server 128 gig of RAM, quad proc 48 core, supporting 3rd party Medical Records app (no I can not change the app, I can and have manipulated indexes, they also have a stupdi bug which causes one statement to be run in the background over a million times an hour) About 600 concurrent usersMy CPU almost never gets above 20%, buffer cache usually at 98, however my PLE during the day can get down to 800.
We also have an issue with disk subsystem (getting new one shortly)
My packets_sh seems high, I am leaning towards the disk subsystem may be contributing towards part of this. Does this sound reasonable.
I would go to sqlskills.com to grab the wait stats query from there. Search the site for "tell me where it hurts" and you will find the blog. Run the query and post your results here.
I would not necessarily be concerned with the PLE at 800, depending on what the average is over the course of a day. Do you have the Free List Stalls/Sec, Lazy Writes/Sec and Buffer Cache hit ratio as well? Do you also have the Full Scans/Sec and Index Searches/Sec values from the Perfmon Counter SQLServer: Access Methods? If so, what are those values like, particularly in relation to when you see PLE drop? What about the values for Memory Grants Outstanding and Pendng from the Memory Manager perfmon object?