Prev: querying a view
Next: VS_ISBROKEN
From: RG on 4 Jan 2010 13:21
If I understand this correctly, not having schema qualifier like dbo.,
triggers recompilation every time the query is being executed. If so, what
kind of performance impact does this have on the db performance?

Thanks in advance,
From: Erland Sommarskog on 4 Jan 2010 17:45
RG (RG(a)discussions.microsoft.com) writes:
> If I understand this correctly, not having schema qualifier like dbo.,
> triggers recompilation every time the query is being executed. If so,
> what kind of performance impact does this have on the db performance?

No, this is not really that bad.

Assume that in a database there is a single table named tbl, and it
resides in the dbo schema.

If you have a query

SELECT col FROM tbl

And this is executed by a user fred who has the default schema fred,
SQL Server will first look for fred.tbl and then for dbo.tbl. When fred
runs this query the first time, the query will be put into cache.

However, if then the user barbara with the default schema barbara runs
the query, she will not be table to use the cache entry that fred created,
but she will get her own cache entry.

Next comes user rodney. His default schema is dbo. This means that the
query is unambiguous, tbl can only refer to dbl.tbl. He cannot use
fred's or barbara's entries, but yet an entry is created.

And so comes the user wilma whose default schema is also dbo - and
she can reuse the cache entry created by rodney.

The reason for all this is that SQL Server must at any point assume
that the tables fred.tbl or barbara.tbl must be created, in which case
the cache entries for fred or barbara must be wiped out.

If the query had been written as

SELECT col FROM dbo.tbl

all four could have shared the plan entry.

Now, in SQL 2005 when you use CREATE USER to create a user, the default
schema will be dbo unless you explicitly define something else, or later
change it with ALTER USER. So there is some likelihood that all users
have dbo as the default schema. But if the databases comes from SQL
2000, or the DBA out of habit have created users with sp_adduser or
sp_grantaccess, there would be one schema per user, that also would
be there default schema, as this was the way it worked in SQL 2000 and
earlier.

So bottom line is: you should always specify the schema in whatever
you send from the client, queries or calls to stored procedures.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: querying a view
Next: VS_ISBROKEN