2

We are using SQL Server 2008 R2 on a Windows Server 2008 server.

We have some main functions which uses some Stored Procedures which has a lot of sub-queries inside of it.

I'll take one SP as an example; when running this SP sequentially it takes about 500ms, and we are ok with it (kinda), it has mainly select queries, some on the same table, it also has some temp tables created and filled with another SP.

We are now in the process of testing these main functions under multiple concurrent requests, and we found out right away that the SP's completion time jumps really quickly to seconds and rising...

after using some suggestions, I noticed that more than one spid is associated with multiple S locks, and that some processes are blocking themselves.

other than that, I'm uncertain as to what is making performance so poor.

I'm not aware if this description is too vague, I would love to give more information if needed, in the end I would like to know where you think should I look at in order to solve this problem.

EDIT:

Here is a portion of the execution plan, it doesn't look good...

Should I just add the indexes as specified?

what does all the parallelism sections in some of the queries' execution plan mean?

does this explain the performance problem? execution plan

Improve this question
8
  • Have you looked at waits? The spid blocking itself is usually very short or is involved with latch waits. I suspect there is an I/O bottleneck.
    – Aaron Bertrand
    May 16, 2012 at 11:28
  • @AaronBertrand can you please elaborate on what is an I/O bottleneck and how it is created?
    – Mithir
    May 16, 2012 at 11:30
  • I/O bottleneck means disk access is slow. Usually caused by a slow disk subsystem and/or insufficient memory. Ideally all of your data gets pulled from memory, not from disk, since it's faster (even SSDs, but obviously less so). If you don't have enough memory to fit all of your data, you'll see lots of disk access, and this can spiral with high concurrency since now all your users are fighting for the same constrained resource.
    – Aaron Bertrand
    May 16, 2012 at 12:10
  • But again, that is just conjecture. I don't know that you have latch waits or that there is an I/o problem.
    – Aaron Bertrand
    May 16, 2012 at 12:23
  • Also, you'll be getting excessive logical and physical reads if you don't have the right indexes on the underlying data. This is just a guessing game without seeing your execution plan. Depending on your isolation level, shared locks won't necessarily cause blocking for other shared lock requests.
    – Thomas Stringer
    May 16, 2012 at 12:40

1 Answer 1

Reset to default
1

Looking at the query plans, the query optimizer is pretty much begging you to add useful indexes to the tables. Until the query plans are robust, forget about looking at locks and I/O.

To make solid suggestions for indexes, you pretty much need to give us a picture of the schema, all the queries, and all existing indexes on the tables. I would not take the missing index suggestions exactly as they are presented. They undoubtedly will help, but they may not be the best thing to do (the suggestions aren't usually... intelligent... for lack of a better word).

The parallelism operators mean that the query used multiple CPUs to process the query in parallel. This is only desirable when you absolutely need SQL Server to do a bunch of work -- in this case, the problem is that you're asking SQL Server to do too much work and it's doing the best it can to satisfy the query in the shortest possible time.

With a small number of rows being returned from each query with what appears to be highly selective predicates, I would expect these queries to fly with useful indexes applied (and no parallelism would be needed).

Just in the visible area, it looks like queries 9 and 10 are virtually the same for ~35% total cost apiece; relatively speaking, the table scan of Transactions should go to pretty much zero. Depending on what you're doing, you may be able to combine those two queries into one.

If that's not enough speed, there are other techniques involving precomputed aggregates, but if you're kinda-sorta-almost okay with 500ms, then you won't need to go this far with just a robust set of base query plans. If all the queries are highly selective, indexes alone should easily get you into the < 5 ms range.

Finally, consider adding useful indexes to your temp tables. A nested loops join with a table scan on the bottom input can be quite expensive depending on the data involved.

Improve this answer
1
  • Thanks for the useful info. I've used the indexes as specified by the plan, just to see if it improves, but it didn't improve that much, then I noticed a problematic query(after indexing) - 'where (w.CardNumber = @CardNumber or w.FullCardNumber = @CardNumber)' - when I delete one of the or parts (2 different locations), it improves to about 5ms, why does it matter so much?
    – Mithir
    May 17, 2012 at 5:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.