From my limited knowledge of how query plans are compiled, stored and retrieved by queries I understand that a multi statement query or stored procedure will generate it's query plan which will be stored in the query plan cache to be used by the query in future executions.
I think this plan is retrieved from the query plan cache with the query hash, which means if the query is edited and executed the hash is different and a new plan is generated as no matching hash can be found in the query plan cache.
My question is: If a user executes a statement that is one of the statements in the multi-statement query can it use that relevant part of the query plan already in the cache for the multi-statement query? I expect the answer is no because the hash values will obviously not match, but would it be better to hash each statement in a multi-statement query so they could be used by users running individual statements from the query?
I expect there are complications that I'm not taking into account (And it's these that I really want to know about) but it seems like we could be storing the same 'statement plan' in many query plans taking up more space and taking more CPU and time to generate.
Could just be showing my ignorance though.