What’s in Enterprise only? (honor Lazy Schema Validation option in a Linked Server)

Together with the definition of each linked server goes a series of options that control different behavioral aspects of the way the linking server interacts with the linked one. Among those options there us one called “lazy schema validation” (aka deferred schema check or delayed schema check) whose default value is false.

When it is set to false, SQL Server checks for schema changes that have occurred since compilation in remote tables. This check occurs before query execution. If there is a change in the schema, SQL Server recompiles the query with the new schema. On the other hand, if it is set to true, schema checking of remote tables is delayed until execution. This can cause a distributed query to fail with an error if the schema of a remote table changes between the instant when the query gets compiled and its execution.

You may want to set this option to true and consequently improve distributed queries execution performance by deferring the schema validation until just the point when the query is executed. The performance gain may be significant and, as long as you handle the errors that will pop up in the event of a metadata modification in the remote server (errors in the range of 7300, like 7353 “The OLE DB provider "%ls" for linked server "%ls" supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time. ”) you’re fine with the change.

So if your environment is one in which you typically run many distributed queries across linked servers and want to get this performance gain, you must use an Enterprise Edition in your local (linking) server, not necessarily in the remote (linked).

This post is part of the a series I decided to call “Understanding the value of the Enterprise Edition, one feature at a time” which I started in September 2011 and will grow on a weekly basis, having a new post incorporated to the family every Friday.