Saturday, April 13, 2013

A look into SQL Server's Plan Caching when object names are not fully qualified

The other day while demonstrating the circumstances which control when SQL Server's Optimiser will create a new query plan or reuse a plan from cache I was asked an interesting question. While I hadn't come across the specific scenario before, I presented my logic based on my knowledge of the optimiser's behaviour and that it would be an interesting investigation. In this blog is my investigation and I am pleased to say my initial answer (based on logical thought) was correct.

We have 2 Tables in a database with the same name but owned by separate schema's, if a user executes a query against one of those tables but does not fully qualify the table name using the owning schema (i.e. relies on their default schema to qualify the name), and another user who's default schema would qualify to the other table within the database runs an identical query, would the Optimiser use the same plan for both.

My Initial Thoughts
While not having investigated this specific behaviour before, my knowledge of the Optimisers behaviour is that it would try and fully qualify the name while generating the Query Plan and therefore create separate plans regardless of if the object name is fully qualified or not.

The Investigation
To reproduce this behaviour I have created a test database [CacheTest] and within that database 2 schemas, TestSchema1 and TestSchema2, along with this 2 logins, TestUsr1 and TestUsr2 which are mapped to the database and schema's respectively. Also within the database are 2 tables, one for each schema [TestSchema1].[ProcCacheTest] and [TestSchema2].[ProcCacheTest], and each table has been populated with some test data.

Lets start by ensuring we have a nothing in our plan cache so it is easier to witness the behaviour, obviously you don't want to do this on a production system.


Now we need to run a query as each user individually.

Open a New Query Window and change the connection to authenticate as [TestUsr1] and then run:

SELECT * FROM [TestSchema1].[ProcCacheTest]
WHERE SomeDate = '19000107'

Then open a New Query Window and change the connection to authenticate as [TestUsr1] and then run.

SELECT * FROM [TestSchema2].[ProcCacheTest]
WHERE SomeDate = '19000107'

Now check the cache, for that I am going to use the script that I wrote in my blog here. Nothing special in these results as we have specified the Schema name so we get 2 individual Query Plans cached.

So lets find out what happens when we don't specify a Schema name (i.e. we don't Fully Qualify the object name). Run the following from the connection context of [TestUsr1]:

SELECT * FROM [ProcCacheTest]
WHERE SomeDate = '19000107'

So what does the cache tell us.

As you can see a new Query Plan is cached. Again nothing unexpected here as we changed the text being executed (removed the schema name) so the query text no longer matches and we know that even the inclusion of an extra White Space, Line Break or even a change in Character Case would cause this behaviour.

How about if we execute this exact same query under the context of the [TestUsr2], and be sure that it is the exact same query with no extra White Space or variance in text.

SELECT * FROM [ProcCacheTest]
WHERE SomeDate = '19000107'

The results of this query should return data from the [TestSchema2].[ProcCacheTest] table, even though we don't specify the schema because the users default schema will map back to the table.

After executing the query lets check the cache again.

Whoa, we now have a 4th Plan in the cache, but wait the Query Text looks identical yet the optimiser has generated a new plan. But how can we be sure we are looking at two completely different statements? The sys.dm_exec_query_stats DMV can help us there as it includes the Query_Hash column, and this is the binary hash value calculated on the query and used by the Optimiser to identify queries with similar logic.

So lets add that into the result set.

As you can see the Query Hash for the 2 queries are different, but wait the Query Hash for the queries in rows in 1 and 4 are identical, and so is the Hash for queries 2 and 3 which match the pairs of statements we executed under each user context. So this verifies to us that the Optimiser is creating the Query Plan based on the Fully Qualified Object path, not just the text we enter (in fact you can verify this even further by looking at the Query_Plan_Hash as it shows the same paired values).

We can also use the query_plan output of the sys.dm_exec_query_plan DMV to view the xml or graphical plan, and from within the plan we can see that the Predicate has the fully qualified object name.

Wow this is great, but lets just be sure because we know some connection providers will use the Prepare / Execute method or maybe we are using the Forced Parameterisation option. So lets test with the sp_executesql to ensure parameterisation of the SQL text.

I executed the following in the context of [TestUsr1]

exec sp_executesql @stmt = N'SELECT * FROM [ProcCacheTest] WHERE SomeDate = @1'
, @params = N'@1 varchar(8)'
, @1 = '19000107';

And then the following in the context of [TestUsr2]

exec sp_executesql @stmt = N'SELECT * FROM [ProcCacheTest] WHERE SomeDate = @1'
, @params = N'@1 varchar(8)'
, @1 = '22000107';

Note that it was my intention to use a different date, due to the data that I have in my [TestSchema2].[ProcCacheTest] table and we have already proved that it will not perform a direct match.

So we check the cache again.

Again we see the same behaviour, the Optimiser creates a new Query Plan in the cache, but it recognises that the statements relate to each other based on the Fully Qualified Object path (see the Query Hash or Query Plan Hash columns).

So after all this we can see that the Optimiser is working as we expect, and mapping the Query Plans to the individual objects based on qualified object paths regardless of if the user names the objects correctly or not. Of cause the recommendation should always still be to qualify the name of the object with the Schema to reduce this additional work that the optimiser is required to do as well as some of the other issues that it can cause.

Thanks for reading and props to the student in the recent workshop that spiked my curiosity on this subject :)

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.


  1. Replica longines orologiLongines non è il più antico tra gli orologiai svizzeri, replica longines dolcevita orologi ma ha dimostrato che merita la sua statura. Fondata nel 1832, la sua eredità continua a essere considerata tra i marchi di orologi più affidabili, promettendo sia qualità nella fabbricazione che raffinatezza. Ciò è evidente nel La Grande Classique, fatto per uomini e donne. L'eleganza contenuta non è estranea al marchio, ma La Grande Classique offre questo con una custodia sottile.

  2. My boyfriend likes to copy watches.Replica Watches UK Fashion Shop When you buy it back, this gorgeous replica watch is amazing. The watch design and function are very fresh and elegant. The quality is relatively perfect. replica rolex milgauss watches This copy watch is very special, the quality is very good, the price is very cheap

  3. Cette montre est très élégante en termes de design et de fonctionnalité.acheter des montres de luxe à bas prix La qualité est relativement parfaite. C'est la première fois que j'achète une réplique de montre.acheter pas cher omega globemaster les montres Cette montre est incroyable et m'a surpris. Éloge cinq étoiles

  4. This watch is very elegant in terms of design and function.replica breitling watches uk The quality is relatively perfect.replica breitling navitimer 8 This is my first time to buy a replica watch. This watch is amazing and surprised me. Five-star praise

  5. replica louboutin scarpeDalla fondazione dello studio di Parigi nel 1991, repliche donne piattaforme la suola rossa di Christian Louboutin è un segno in sé. La lacca su tacchi alti, scarpe da ginnastica e stivali adorna il fondo di ogni paio di scarpe di Christian Louboutin.

  6. The quality of this replica gucci dress is perfect,cheap gucci shoes it is very comfortable to wear, and wear this dress when you gucci cosmetic case I recommended this website to my good friends. They are very happy to buy such cheap gucci clothes.

  7. Este reloj es muy elegante en términos de diseño y función.Réplica de relojes Rolex La calidad es relativamente perfecta.Réplica de relojes sky dweller Esta es mi primera vez para comprar un reloj de réplica. Este reloj es increíble y me sorprendió. Elogio de cinco estrellas

  8. Denne replika af parisiske familiesko er af perfekt kvalitet,billige Balenciaga sko meget behagelig at bære, iført skoene, når du rejser, ikke træt. Jeg anbefalede denne hjemmeside til mine gode venner.billige Balenciaga sandaler damer De er meget glade for at købe sådanne billige parisiske sko.

  9. The AAA cheap cartier watches uk sale at are best quality cheap fake watch from China,The replica ronde solo de cartier watches high end Cartier replica watches include Ballon Bleu De Cartier, Calibre De Cartier,Cartier La Dona,Cartier Love Watch,Cartier Pasha,Cartier Roadster,Cartier Santos,Cartier Tank replica watches,We can shipp to Uk,USA,canada,australia,and almost all country worldwide

  10. Le magasin a résolu le problème pour moi, et le service est très patient,Moncler Jas Verkoop c'est une expérience de magasinage parfaite. Quand j'ai reçu la tenue Moncler, je pensais que c'était une bonne réplique de la doudoune Moncler.Goedkope kinderen trainingspak J'aime cette boutique en ligne, cette robe Moncler est simple et élégante. Très satisfait de cet achat.

  11. Gli orologi Longines sono stati un punto di riferimento del settore da quando il loro marchio è iniziato nel 1832 a Saint-Imier.repliche longines orologi Oggi il marchio offre una vasta gamma di orologi da uomo e da donna. repliche longines 1832 Gli orologi prodotti da Longines sono stati utilizzati nel corso della storia per tutto, dalle esplorazioni all'espressione dell'eleganza. Oltre ad essere famosi per i loro legami con il mondo delle corse e dei cavalli, gli orologi Longines sono riconosciuti a livello globale per la loro raffinatezza senza tempo e chic. abbiamo una varietà di orologi Longines in vendita, tutti nuovi, belli e garantiti per essere genuini. Ancora meglio, quando acquisti i nostri orologi Longines online, sai che stai ottenendo il miglior valore. Sfoglia la nostra selezione per trovare il miglior prezzo dell'orologio Longines su Internet.