[kbenson]

I admit it's entirely possible most of my bad experiences with SQL Server is due to a horrible schema. I don't control the schema in this case, nor the majority of different types of queries that run against that schema.

What I do know is that I routinely get single, non-transactional select queried deadlocked and killed. That seems like something that shouldn't happen. If I had to guess, it's likely because of the subselects to approximate group_concat, as outlined above, and they have different lock times than the main query, allowing for this single query to attampt to get locks at different times. Unfortunately, this is an ORM generated query, so sticking NOLOCK all over the place is easier said than done, and since I don't control the DB, I can't change default transactional levels. Perhaps that would solve it too.

It just seems odd that what appears to be (from my admittedly non SQL server expert) eyes to be a fairly standard database schema is so easily susceptible to this problem.