SQL Server: Some Quirks in the Cache, Part II

Some Surprising Traits of SQL Server Query Caching

In the previous section<link SQL Server: Some Quirks In the Cache Part 1>, we saw that it is easy to observe query execution plans being entered into the procedure cache by SQL Server. Now that the tools are in place, we will observe some unexpected behavior of the query engine that must be appreciated if SQL Server’s use of memory is to be optimized.

A common question in Learning Tree’s introductory-level SQL Server courses is “Is SQL Server case-sensitive?” The answer, of course, it “It depends.” The default installation results in an SQL Server that is by default case-insensitive when new objects are created. But this default can be changed. Furthermore, it is always possible to create case-sensitive databases, tables, and columns even when the server default is case-insensitive. What does this mean for us? It means that the query engine must always consider the potential of case-sensitivity when developing a query plan, even on case-insensitive servers.

To illustrate this, we will once again flush the procedure cache and start with a clean slate.

DBCC FREEPROCCACHE

Now we’ll run two logically identical queries searching for employee 11. The only difference between the two queries is the capitalization of the column name, EmployeeID.

SELECT * FROM Employees WHERE EmployeeID = 11

SELECT * FROM Employees WHERE employeeid = 11

Looking at the cache using sys.syscacheobjects, we see that there are two autoparameterized queries, one for each version of the original.

This is confirmed by DBCC PROCCACHE.

Important Point #1: Query caching is case sensitive even on case-insensitive servers!

Note that this case sensitivity does not apply to keywords. It never matters whether you capitalize SELECT or FROM; this is just a convention. It matters very much how you capitalize the names of database object like tables or columns. My suggestion is to establish as a convention among your teammates to always use precisely the same capitalization that was used in the definition of the object when it was created.

The Query Engine Tracks User’s Default Schema

If we look at all the columns returned by sys.syscacheobjects, we see a column named “uid”, which is a reminder that the reason for the existence of the so-called “compatibility views” like syscacheobjects is to maintain compatibility with pre-2005 scripts and batches. Prior to 2005, tables and stored procedures where “owned” by some database user. In 2005 this idiosyncratic ownership was replaced by schema. In a query like “SELECT * FROM dbo.Customers” dbo was a user account prior to 2005; now it is a schema with the same name. The sys.syscacheobjects view still labels the column “uid”, but now it represents the default schema of the user. This is important, since different tables can have the same name as long as they are contained in different schemas.

Imagine that Wilma is a respected member of the Finance department and “Finance” is her default schema. When Wilma executes a query like “SELECT * FROM Customers” SQL Server cannot assume she means dbo.Customers. There might be a table Finance.Customers. Let’s see how this affects cachine. First, we have to create a new schema, a login and user ID for Wilma, and assign the schema (not dbo!) as the default for Wilma.

USE Bigwind

GO

CREATE SCHEMA Finance AUTHORIZATION dbo

GO

CREATE LOGIN Wilma WITH PASSWORD=N’wilmapw’, DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE USER Wilma FOR LOGIN Wilma

ALTER USER Wilma WITH DEFAULT_SCHEMA=Finance

ALTER ROLE db_owner ADD MEMBER Wilma

GO

We’ll need to open a third window, this one connected to the Bigwind database as Wilma rather than as dbo. We can then free the procedure cache and run exactly the same query twice.

SELECT * FROM Employees WHERE EmployeeID = 7

The query will run once with Wilma’s credentials and once with dbo’s. This time, however, when we look at sys.syscacheobjects we will add another column, uid, to our output.

The same query has now been autoparameterized twice and stored twice, because SQL Server cannot assume that the same query from people with different default schemas are, in fact, the same, even though clearly in this case they are.

We can eliminate this useless duplication by simply never relying on default schema for query execution. This time both dbo and Wilma will execute the following query:

SELECT * FROM dbo.Employees WHERE EmployeeID = 7

Now sys.syscacheobjects reveals a completely different picture.

Now there is only one autoparameterized plan even thought both dbo and Wilma have run the query. The uid column shows -2, indicating that this query is not in any way dependent upon the default schema of the user who ran it.

Important Point #2: Tracking users’ default schemas results in needless duplication of query plans!

It’s clear that in a large organization with many schemas for a database could potential suffer not from duplication, but many many multiple copies of a single query plan.

Conclusion

It seems like a small thing, but SELECT statements should always be written using consistent capitalization, preferably that used in the object definition itself. Similarly, it’s always, always better to include the “dbo.” in your queries. Will you be able to get your users to hold down the shift key? Not bloody likely. Will your staff consistently use the schema prefix in queries? Good luck with that. It is, however, worth a try.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.