3

I'm working with a SQL Server instance that hosts thousands of databases. Each database corresponds to a different customer, but they all share an identical schema.

Recently, I've observed that many queries have the same query hash. Upon further investigation, I found that although the queries have the same text, they are executed against different databases.

From what I understand, since the context of each query is database-specific, the duplicate query hashes are to be expected. However, I'm curious to know if my understanding is correct.

Additionally, I'm interested in exploring options to optimize this setup. Specifically, is there a way to consolidate execution plans for identical queries across different databases, or is this not possible due to the separate database contexts?

Any insights or recommendations on this matter would be greatly appreciated.

Thank you in advance for your help!

1 Answer 1

8

nope

This is a problem with any multi-tenant solution, whether the separation is by database, or by schema. I'm not suggesting you intermix clients just for the sake of the plan cache, though.

For performance troubleshooting, you're way better off using Query Store, which is per-database. It's not a replacement for the plan cache (though I wish it were), but there's not much you can do to help yourself in this case with thousands of databases all running similar workloads.

Different databases may have different settings, different indexes, and will certainly have different statistical information. You really wouldn't want plans shared across all of them anyway, and in some cases it would be technically impossible. Cardinality estimation would be totally wrong searching for a set of identifiers present in one database that aren't present in another database, or are present in far different distributions.

Just as an example, and I'm not saying this is the case with your databases, different ANSI settings may make certain queries fail in one database that would work fine in another.

0

Your Answer

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

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