TheSQLGuru (3/27/2013)
Given that profiler shows no queries with large numbers of reads took away my normal response.Complete guesses here:
1) someone accidentally put windows/sqlserver 32 bit on the system (yes, I have seen that happen!)
2) the IO subsystem is completely insufficient and/or grossly misconfigured. Have you done IO stall analysis yet?
3) did you do a wait stats analysis yet?
Note: I have been a professional SQL Server tuner for 15 years and I pretty much never care about PLE unless it is taking a precipitous dive to 0 indicating a likely bug somewhere on the system (drivers/firmware/3rd party apps, etc).
Speaking of professional, there really are a LOT of things that could be at play here and you could hunt and peck on a forum for days without resolution. Consider getting a good tuner to remote into the system. It could be just a matter of minutes to a few hours before the cause is isolated and corrected. Client wins, and you win by getting some mentoring to enable you to better troubleshoot the next time you come across something like this.
* SQL Server is 64 bit
* The IO System is overloaded because we are issuing much more reads per second than would have been necessary if the cache had worked as expected. We are using an IBM SAN, we have had IBM experts involved to verify that there are no problems on the SAN side.
* Wait stats analysis indicates that the only signigificant waits are for IO
* I also usually do not care about PLE, but the combination of lots of free memory (over 16 GB) and very low PLE (going down to 0 almost every minute) indicates that something is really wrong here.
* I am an SQL tuning professional myself, but I have never seen this behavior before.
If you have any concrete ideas on how to isolate the cause of this behavior I really want to hear about it.
/SG