Link server insert – 'lazy schema validation' | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Link server insert – ‘lazy schema validation’

Hi!
Does anyone know what kind of performance gain (hard question to answer
ofcourse…, depends on many factors etc… but nevertheless.. ) one can
achive when doing linked server update/insert (with or without using
the OPENQUERY command) and being able to set
sp_serveroption ‘linkedservername’,’lazy schema validation’, TRUE
which i read is being used when building a federated databases structure.
This option only seems to be available in the Enterprise edition of SQL
Server so I haven’t been able the to test it myself. MagnusJ
Yes its available on EE and you may want to set this option to true when distributed, partitioned views are being used against a linked SQL Server. A given table participating in the partitioned view may not be actually used in a given execution of a query against the view, so deferring the schema validation can be useful to improve performance. For reference review thishttp://www.microsoft.com/technet/tr…odtechnol/sql/reskit/sql2000/part10/c3861.asp link. HTH
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>