Query Optimization

SQL Server is an intelligent database server. SQL Server has a main function for processing database queries and transactions provided by the client application with good performance. Applications only need to provide a set of SQL commands, regardless of physical implementation of tables. SQL Server will determine the best way to process these commands with a technique called Cost-Based Query Optimization.

For example an application provides a complex query with the SELECT command. Query is to combine three tables with a join operation and then perform some aggregate operation on numerical data with a particular grouping. The query results should be sorted by date. SQL Server can optimize the query so that it produces several different strategies to run the query. Execution plan, determine the index that is used, the order of the join of the table, the most efficient way to perform aggregate calculations, and the fastest method to sort the results. SQL Server chooses the fastest. Selection is based on the cost of each execution plan consisting of CPU usage.

To generate the execution plan that accurately shows the number and distribution of data in the database, SQL Server uses distribution statistics. Statistical distribution, is a type of histogram that describes the distribution of index and data in a column. SQL Server 2000 provides several mechanisms of query plan reuse and also put together in between multiple users