What is Algebrizer

What is Algebrizer(in SQL 2000 Normalizer in 2005/8 Algebrizer) Algebrizer is a process in query execution, which starts its work after Parser. Once the parser finds query syntax correct, it passes it to the algebrizer. Here start work of algebrizer: • Algebrizer is responsible for objects and columns names (which you have provided in query or which are being referenced by your query) verification. For example if wrong name for a table is typed in a query, then its algebrizer’s duty to verify and generate an error. • Algebrizer also identifies all data types which are being processed for a given query. • Algebrizer verify that GROUP BY and aggregate columns are placed in right place or not. For example if you write following query and just Ctrl+F5 (just to parse). No error will be generated. But when you will press F5 (execute it). After Parsing, algebrizer will work and return an error. sys.dm_exec_query_optimizer_info Is DMV which provide the query optimizer information A. Viewing statistics on optimizer execution What are the current optimizer execution statistics for this instance of SQL Server? SELECT * FROM sys.dm_exec_query_optimizer_info; B. Viewing the total number of optimizations How many optimizations are performed? SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations'; C. Average elapsed time per optimization What is the average elapsed time per optimization? SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time'; D. Fraction of optimizations that involve subqueries What fraction of optimized queries contained a subquery? SELECT (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery') / (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations') AS ContainsSubqueryFraction;