I will keep this as simple as I can despite the complexities. I'm looking for a strategy rather than specifics - what have I missed?
I have a web app which runs against a SQL 2k8 r2 DB using Java DB driver. The app a has a search function which allows the users to, erm, search! This performs well enoughabout 50% of the time - returning results to the users browser in a second or two. However the rest of the time, it can take over 1 minute to do the same search. The issue occurs for all users simultaneously - sometimes fast, sometimes slow. There is no real pattern over the times of day that it occurrs and all the servers involved perform only their key tasks (app server only runs the app, db server only runs the db, etc). Only this function of the website is slow - everything else works fine.
I have checked
Blocking (there's none)
CPU (no more than 50% max)
Memory pressure (grants, page life exp., total \ target - all fine)
Recompilations (only about 1% of batch requests)
Wait times (shows PAGELATCH as largest wait time, but it's not excessive in my opinion - it's got to be waiting for something, right?)
IO to physical and virtual files (no queueing anywhere)
Network contention (there is none)
App server resource pressures (all basic stats are fine (cpu, memory, disk...))
TempDB usage (table creation rate - nothing to see here)
User connnections (no great fluctuation all day)
At the same time as the searches are in 'go slow' and all the users are waiting, I can run the exact same procedure from SSMS using the exact same parameters as the user is using, and it returns in sub-second times.
I'm at a loss and don't know where to go from here - if anyone can suggest anything that I've missed, I will be very happy to hear from you.
Thanks in advance