Tips to improve performance of Database Operations

1) Return Multiple Resultsets The database code if has request paths that go to the database more than once then, these round-trips decreases the number of requests per second your application can serve. Solution: Return multiple resultsets in a single database request, so that you can cut the total time spent communicating with the database. You’ll be making your system more scalable, too, as you’ll cut down on the work the database server is doing managing requests. 2) Connection Pooling and Object Pooling Connection pooling is a useful way to reuse connections for multiple requests, rather than paying the overhead … Click here to continue…..

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…..