CODEWITHSUNDEEP
sql-servert-sql
yakya
yakya

Reputation: 5220

How does FQNs affect Query Execution Plan caching?

In link, it is stated that

The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Person;

SELECT * FROM Person.Person;

I don't quite understand what does that mean. Is it related to the fact that schema name and table name being the same? Why the first one isn't matched with an existing plan?

Upvotes: 1

Views: 77

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28930

Fully Qualified names(FQNs) helps in faster name resolution. Without an explicit schema, SQL Server will first look under the schema associated with your login.

Assume you have the below login associated with below schema

create schema test

create table test.schematest
(id int
)

select * from test.schematest


--create a login
CREATE LOGIN foo WITH
    PASSWORD = 'foo',
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF;
GO

--create schema
CREATE SCHEMA foo AUTHORIZATION dbo;
go

CREATE USER foo FOR LOGIN foo WITH DEFAULT_SCHEMA = foo;
GO

EXEC sp_addrolemember 'db_owner', 'foo';
GO


--now first create a table 
--this will be created in dbo schema

create table test
(
id int
)

--now execute as user foo

execute as user='foo'
go


create table test
(
namee varchar(100)
)

insert into test
values
('foo schema')

select * from test--gives foo schema

Now, coming to plan caching aspect, sql server will generate a new plan for the same query even if there is a slight change (I remember reading this has to do with sql hashing query text and comparing)

below queries are same, except for foo.test on new line

dbcc freeproccache

select * from foo.test

select * from 
foo.test

select  txt.text,* From sys.dm_exec_query_stats ec
cross apply
sys.dm_exec_sql_text(ec.plan_handle) txt
where txt.text like '%foo.test%'

You can see there are two plans

enter image description here

So in summary appending schema prefix will always help, but it has less to do with plan caching.

Example you provided also falls into the example I showed

References:
Bad habits to kick: avoiding the schema prefix

Upvotes: 3

Related Questions