J
James Palmer
Guest
We've got a query performance issue that we're trying to work out. None of us are particularly expert at this sort of thing. It's hard to debug this in production because there's a lot of noise going on from other users. Running the queries in a development db gives you some benefits but sadly buffer hits are at 100% which doesn't reflect the experience in live. But I'm hoping you can help me work out what is going on. Customer and address are related to each other 1-1. Each address record is unique even if the address is shared. So to read 500 customers you'd expect to also read 500 unique addresses. All of the queries below use the primary index which is on the key. If you do a for each on customer we get 4793 record reads but 13501 logical reads. It takes x amount of time. If you do a for each on customer, first address of customer, we get 9566 record reads but 33277 logical reads. It takes 2.5x amount of time compared to just customers. In live it can take up to 10x as long - probably due to the reduced buffer hits. My initial question is, why so many more logical reads compared with record reads? Is there anything else I can look at to help debug this? Thanks for your help James
Continue reading...
Continue reading...