Sign up to the jQuery Grid Subscription list.

What is an optimizer in MS SQL server ?

Original post by sam.SQL at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2995794&SiteID=1

The question:

What does mean by Optimizer in MS SQL ? What is it ?
How do you use it ? what are the benifits of use it ?

The accepted answer by Derekman:

The query optimizer is the component of SQL server that determines the most efficient way to execute a query based upon the distribution statistics and histograms maintained within sql server. When you pass a query to sql server the query is optimized based on the estimated "cost" of the query for the different possible ways to exeute the statement based on the table(s) and index(es) available. SQL Server query optimizer reviews the different options in which to execute the query and decides on the most efficient way based on overall cost. Most, but not all, queries are optimized automatically and you do not need to do anything to invoke query optimization. There are however "query hints" that allow you to over ride a query plan recommended by the optimizer.

Please review BOL and the below link for a more detailed explanation.

Best of Luck!!

2 comments:

dhyarga alfin said...

The query optimizer is the component of SQL server that determines the most efficient way to execute a query based upon the distribution statistics and histograms maintained within sql server. When you pass a query to sql server the query is optimized based on the estimated "cost" of the query for the different possible ways to exeute the statement based on the table(s) and index(es) available. SQL Server query optimizer reviews the different options in which to execute the query and decides on the most efficient way based on overall cost. Most, but not all, queries are optimized automatically and you do not need to do anything to invoke query optimization. There are however "query hints" that allow you to over ride a query plan recommended by the optimizer.

dhyarga alfin said...

The query optimizer is the component of SQL server that determines the most efficient way to execute a query based upon the distribution statistics and histograms maintained within sql server. When you pass a query to sql server the query is optimized based on the estimated "cost" of the query for the different possible ways to exeute the statement based on the table(s) and index(es) available. SQL Server query optimizer reviews the different options in which to execute the query and decides on the most efficient way based on overall cost. Most, but not all, queries are optimized automatically and you do not need to do anything to invoke query optimization. There are however "query hints" that allow you to over ride a query plan recommended by the optimizer.

http://dhyarga.blogspot.com

Related Ads