I have a batch (7 queries) which executes in a couple of seconds locally by takes more than an hour in production.

I've have a .sqlplan XML file from that long running query on production and trying to find the bottleneck based on the execution plan. I'm looking for Actual number of rows = a big number, but don't see anything above a 1000 rows, I'm expecting something in the millions there considering it the query took so long.

I think this is not a deadlock because running sp_who shows the first query from the batch doing tens of millions of IO operations, but no clue about it in the actual execution plan.

What other metrics in the execution plan should I pay attention too to try find the bottleneck?

migrated from stackoverflow.com Jun 3 '15 at 17:05

This question came from our site for professional and enthusiast programmers.

  • Try to turn on statistics io, it usually gives you a good idea where the time is spent. If you're using user defined functions, that's a likely reason. Functions, except inline ones, aren't shown in statistics io. – James Z Jun 3 '15 at 16:13
  • 3
    Help us help you here. Post your execution plan. – Code Different Jun 3 '15 at 16:16
  • 2
    Small row counts resulting from table scans of millions of rows will take a relatively large amount of time, regardless of the small row count. Without seeing the query and plan, we can only guess. Are you sure the local and production databases are completely identical? – hatchet Jun 3 '15 at 16:20
  • Execution Plan + SQL Query = much faster answer – Kris Gruttemeyer Jun 3 '15 at 17:25
  • 1
    Having a large number of actual rows isn't necessarily a reason to be slow. Having a massive difference between estimated and actual probably is. – Mark Sinkinson Jun 3 '15 at 18:11

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.