Macronimous Web Design

Indexing:                 
           One of the most advantageous methods for increasing the performance of queries is the formation  of efficient indexes.  A well-built index helps the query to avoid the necessity of scanning the entire table for results. If you have created an index, SQL server will automatically measures and stores the equivalent statistical information regarding the distribution of values in the indexed column. The optimizer to determine the optimal strategy for evaluating a query uses this statistical information.

There are two types of indexes: clustered and non-clustered, each with some unique advantages depending on the data set.

Clustered Index:
           A clustered index dictates the storage order of the data in a table. When searched for range of values the clustered indexes are more efficient on columns of data as the data is sorted. These index types also shines at finding a specific row when the indexed value is unique.

Non-clustered index:
            In a non-cluster index, the data is stored in one place and the data value in another place. When a query search for a data value, first the query searches the non-cluster index for the corresponding data value in the table to find the location and then retrieves the data from the location. Most often non-cluster  indexes are used for the queries resulting in exact matches.

Statistics:
               In a broad-spectrum, indexes should be kept as slight as possible, to reduce the amount of processing related with each query. For analyzing and optimizing the performance of a query the measurement and collection of statistical data is inevitable. The statistical performance data about the index keys are maintained in SQL server. If its configured, non-indexed keys can also be measured statistically.

 The basic statistical concepts, which a database administrator needs to understand regarding performance optimization, are:

• Cardinality: measures how many unique values exist in the data set.
• Density: measures the uniqueness of values within a data set. Density is determined by dividing the number of rows that correspond to a given key by the number of rows in the table. The optimizer will likely ignore indexes with high densities.
• Selectivity: measures the number of rows that will be returned by a particular query. Selectivity is determined by dividing the number of keys requested by the number of rows they access. To calculate the relative cost of a query plan, the optimizer needs a valid selectivity measure.

               SQL Server automatically updates the statistical information periodically as the data in the tables change. Sampling the data, rather than analyzing all of it minimizes the cost of this automatic statistical update.

Optimal performance:
               To design and choose indexes in a complex database table is normally a tedious task. Providentially, SQL Server has a built-in Index Tuning Wizard to establish an optimal set of statistics and indexes. Running the Index Tuning Wizard will provide a list of suggestions based on a scripted workload for improving the query performance of your database. We need to implement only the recommendations of the wizard, which applies to the particular situation. In case of any dynamic system, periodically we need to update our analysis on optimal database performance.

Liked the post? Feel free to Subscribe to this Feed

Related Posts

• No Related Post

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.