locked
What is role of Algebrizer process 'Object Binding'? RRS feed

  • Question

  • I am trying to understand role of Algebrizer during 'Object Binding' process. As per definition:

    "The algebrizer
    resolves all the names of the different objects, meaning the tables, the columns, and so on, that are being
    referenced in the T-SQL in a process called binding." 

    Meaning it will check for existence of objects (tables/ columns) referred in the t-sql query. If if finds any non-existent object, then it will throw error. But, if this is the case then following query should not get passed by Algebrizer because table "dbo.no_test1" does not exist:

    IF (SELECT OBJECT_ID('dbo.Test1')
    ) IS NOT NULL
    DROP TABLE dbo.Test1 ;
    GO
    
    CREATE TABLE dbo.Test1 (c1 INT) ;
    INSERT INTO dbo.Test1
    VALUES (1) ;
    
    SELECT 'Before Error', c1
    FROM dbo.Test1 AS t ;
    
    SELECT 'error', c1
    FROM dbo.no_Test1 ;
    --Error: Table doesn't exist
    SELECT 'after error', c1
    FROM dbo.Test1 AS t ;

    Tuesday, June 2, 2015 1:49 PM

Answers

  • The algebrizer resolves all the names of the various objects, tables and columns, referred to within the query string. The algebrizer identifies, at the individual column level, all the data types (varchar(50) versus datetime and so
    on) for the objects being accessed. It also determines the location of aggregates (such as GROUP BY, and MAX) within the query, a process called aggregate binding. This algebrizer process is important because the query may have aliases or synonyms, names that don't exist in the database, that need to be resolved, or the query may refer to objects not in the database. When objects don't exist in the database, SQL Server returns an error from
    this step, defining the invalid object name. As an example, the algebrizer would quickly find the table Person.Person in the AdventureWorks2008R2 database. However, the Product.Person table, which doesn't exist, would cause an error and the whole optimization process would stop.
    The algebrizer outputs a binary called the query processor tree, which is then passed
    on to the query optimizer. The algebrizer's output includes a hash, a coded value representing
    the query. The optimizer uses the hash to determine whether there is already a
    plan generated and stored in the plan cache. If there is a plan there, the process stops here
    and that plan is used. This reduces all the overhead required by the query optimizer to
    generate a new plan. Hope this helps.

    This is a quote from "SQL Server Execution Plans Second Edition" by Grant Fritchey

    http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Eric__Zhang Wednesday, June 10, 2015 2:17 AM
    • Marked as answer by Eric__Zhang Thursday, June 11, 2015 2:11 AM
    Monday, June 8, 2015 7:18 PM

All replies

  • SELECT 'error', c1 FROM dbo.no_Test1 ;

    wrong table name...check and confirm..



    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Tuesday, June 2, 2015 2:37 PM
  • The first step is Parsing, and the whole batch is parsed and will fail if it contains a syntax error.  After parsing the batch execution starts, and queries in the batch only go through the algebrizer as they are executed.  This behavior is known as "Deferred Name Resolution", as object identifiers are not validated until the query plan is actually needed.

    For instance this batch will not fail at parse or runtime.

    if 1=2
    begin
      SELECT 'error', c1
      FROM dbo.no_Test1 ;
    end

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, June 2, 2015 2:50 PM
  • Hi davidbaxterbrowne

    Thanks for you quick reply!

    Any supporting link which describes this case in details?

    Tuesday, June 2, 2015 5:58 PM
  • The algebrizer resolves all the names of the various objects, tables and columns, referred to within the query string. The algebrizer identifies, at the individual column level, all the data types (varchar(50) versus datetime and so
    on) for the objects being accessed. It also determines the location of aggregates (such as GROUP BY, and MAX) within the query, a process called aggregate binding. This algebrizer process is important because the query may have aliases or synonyms, names that don't exist in the database, that need to be resolved, or the query may refer to objects not in the database. When objects don't exist in the database, SQL Server returns an error from
    this step, defining the invalid object name. As an example, the algebrizer would quickly find the table Person.Person in the AdventureWorks2008R2 database. However, the Product.Person table, which doesn't exist, would cause an error and the whole optimization process would stop.
    The algebrizer outputs a binary called the query processor tree, which is then passed
    on to the query optimizer. The algebrizer's output includes a hash, a coded value representing
    the query. The optimizer uses the hash to determine whether there is already a
    plan generated and stored in the plan cache. If there is a plan there, the process stops here
    and that plan is used. This reduces all the overhead required by the query optimizer to
    generate a new plan. Hope this helps.
    Monday, June 8, 2015 7:05 PM
  • The algebrizer resolves all the names of the various objects, tables and columns, referred to within the query string. The algebrizer identifies, at the individual column level, all the data types (varchar(50) versus datetime and so
    on) for the objects being accessed. It also determines the location of aggregates (such as GROUP BY, and MAX) within the query, a process called aggregate binding. This algebrizer process is important because the query may have aliases or synonyms, names that don't exist in the database, that need to be resolved, or the query may refer to objects not in the database. When objects don't exist in the database, SQL Server returns an error from
    this step, defining the invalid object name. As an example, the algebrizer would quickly find the table Person.Person in the AdventureWorks2008R2 database. However, the Product.Person table, which doesn't exist, would cause an error and the whole optimization process would stop.
    The algebrizer outputs a binary called the query processor tree, which is then passed
    on to the query optimizer. The algebrizer's output includes a hash, a coded value representing
    the query. The optimizer uses the hash to determine whether there is already a
    plan generated and stored in the plan cache. If there is a plan there, the process stops here
    and that plan is used. This reduces all the overhead required by the query optimizer to
    generate a new plan. Hope this helps.

    This is a quote from "SQL Server Execution Plans Second Edition" by Grant Fritchey

    http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Eric__Zhang Wednesday, June 10, 2015 2:17 AM
    • Marked as answer by Eric__Zhang Thursday, June 11, 2015 2:11 AM
    Monday, June 8, 2015 7:18 PM