0

I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators showing large discrepancies between actual and estimated row counts. For example an actual row count of 61 and an estimated row count of 269,445.

When I looked at the stats for the index associated with the highest-cost operator in the plan, its sampled rows count was about 10th of the actual rows so I ran a Full Scan. However that's made no difference. Now sampled rows and actual rows are the same but I'm still seeing large discrepancies between actual and estimated row counts for the same index. I was wondering if the high estimate was causing the plan to request a much higher memory grant than was needed however I can't understand where this high estimate is coming from so I was hoping someone might point me in the right direction.

Gordon.

  • I would be interested in seeing what the query that is being called is. There is a handful of possibilities as to what this might be, but the one that sticks out the most to me is that you have parameter sniffing happening here (frequently a good thing unless it is making you use a plan that is not optimal). Here is a good read on that brentozar.com/archive/2013/06/… but more information would help to say for sure what the issue is. – mskinner Nov 27 '15 at 17:44
  • 2
    Can you post the actual exec plan (you can upload plan xml on pastebin and link it here)? – Kin Shah Nov 27 '15 at 17:56
  • Data might be skewed heavily.can you post stats of that index ,along with info others requested – TheGameiswar Nov 27 '15 at 20:21
  • Don't think it can be parameter sniffing as the buffer cache on the instance in question has been flushed following a service restart and the the problem remains. I'm more inclined to think it's cardinality/skewed data. Execution plan to follow shortly once my Pastebin account becomes active. And many thanks for the feedback thus far. – YaHozna Nov 28 '15 at 17:37
  • Unfortunately the Execution Plan is too big for Pastbin although I could upload a partial Plan. – YaHozna Nov 30 '15 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.