Major Points when Creating Indexes for a database

Best practices to follow when creating Indexes for a database. Examine the WHERE clause and JOIN criteria columns : SQL server Query optimizers looks for indexes on the columns specified in the WHERE clause and the JOIN criteria and if the indexes are available on these columns then optimizer considers Indexes when creating execution plan. Use Narrow Indexes : index on a column with narrow data type will improve performance by reducing storage space, reducing required IO and more effective caching. Prefer Column with high selectivity : Index on a column with a very low range of unique value will not yield any benefit in … Click here to continue…..

How to increase SQL Server performance

These are major problem that badly affects SQL Server performance. If you already know this problem area then you can design database and queries with better understanding. Poor Indexing: Without proper indexing SQL Server needs to retrieve and process much more data when executing. That means more use of disk,memory and CPU, and also increased execution time. That can lead to bloacking and deadlocks. Inaccurate Statistics: Accurate data distribution statistics are important for SQL Server so that it can design optimized execution plan accordingly. Poor Query Design: SQL queries should be written to make best use of indexes.Poorly designed queries can … Click here to continue…..

What is fillfactor? What is the use of it ? What happens when we ignore it? When you should use low fill factor?

When you create a clustered index, the data in the table is stored in the data pages of the database according to the order of the values in the indexed columns. When new rows of data are inserted into the table or the values in the indexed columns are changed, Microsoft® SQL Server™ 2000 may have to reorganize the storage of the data in the table to make room for the new row and maintain the ordered storage of the data. This also applies to nonclustered indexes. When data is added or changed, SQL Server may have to reorganize the … Click here to continue…..

What are statistics, under what circumstances they go out of date, how do you update them?

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query. Some situations under which you should update statistics: If there is significant change in the key values in the index If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated … Click here to continue…..

Difference between Index defrag and Index rebuild?

When you create an index in the database, the index information used by queries is stored in index pages. The sequential index pages are chained together by pointers from one page to the next. When changes are made to the data that affect the index, the information in the index can become scattered in the database. Rebuilding an index reorganizes the storage of the index data (and table data in the case of a clustered index) to remove fragmentation. This can improve disk performance by reducing the number of page reads required to obtain the requested data DBCC INDEXDEFRAG – … Click here to continue…..