August 22, 2010 at 4:55 pm
Yes, it will affect performance as now there is less of your database cached in in the buffer.
Unless your next few requests also take data from that big long running query then it has to pull the data back from disk which will be much slower than pulling from the buffer.
August 23, 2010 at 11:35 am
1) buffer cache hit ratio 99+ but page life expectancy very low indicates lots of IO but the IO subsystem is keeping up (otherwise BCHR would be low due to read aheads not getting data there quick enough).
2) disk queue length is pretty useless. what is avg disk sec/read and /write?
3) My guess is that you have missing indexes that are causing table scans and hashes and/or functions on columns that are causeing index scans instead of index seeks. You just hit a breakover point in volume of data where plans changed and now you are getting hammered.
4) take a looksee at tempdb io stalls especially, but all io stalls in general.
5) what about wait stats analysis?
6) I highly recommend getting a performance tuning professional on your system for a perf review and some mentoring.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply