Having a site, a dynamic one, is cool. But it's not a secret that the Database may be the bottleneck and create you lot of issues as your traffic, DB records number and DB calls grows. So i am going to have a set of posts on how to optimize your SQL Server so that everyone to be happy.
  1. Create proper indexes
    Make sure that every table in your database has a primary key.
    Create non-clustered indexes on columns which are:
    • Frequently used in the search criteria
    • Used to join other tables
    • Used as foreign key fields
    • Of having high selectivity (Column which returns a low percentage (0-5%) of rows from a total number of rows on a particular value)
    • Used in the ORDER BY clause
    • Of type XML (Primary and secondary indexes need to be created)
    To create an index use the following sample:
    CREATE INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor (VendorID)

    You can use also the SQL Server Management Studio to create indexes on the desired table, BUT if your table already have some high number of rows it may create performance issues while the manual creation will work like a charm.

  2. Create appropriate covering indexes
    You create Covered indexes in table columns to specify what are the additional column values the index page should store along with the clustered index key values (primary keys). Example:
    CREATE INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor (VendorID) INCLUDE(ProductName, ProductPrice)

    Note: Covered index should be created including a few columns that are frequently used in the select queries. But be aware that including too many columns in the covered indexes would not give you too much benefit. Rather, doing this would require too much memory to store all the covered index column values resulting in over consumption of memory and slow performance. Can surely confirm this as was in such situations.

  3. Defragment indexes if fragmentation occurs
    Index fragmentation is a situation where index pages split due to heavy insert/update/delete operations on the tables in the database. If indexes have high fragmentation, scanning and seeking the indexes takes much time or the indexes are not used at all while executing queries and so data retrieval operations perform slow.
    There are 2 types of fragmentation:
    • Internal Fragmentation - occurs due to the data deletion/update operation in the index pages which ends up in creation of lots of empty rows in the pages. Also results in increase of index/data pages that increase query execution time.
    • External Fragmentation - occurs due to the data insert/update operation in the index/data pages which ends up in page splitting and allocation of new index/data pages that are not contiguous in the file system. That reduces performance in determining query result where ranges are specified in the WHERE clauses. Also, the database server cannot take advantage of the read-ahead operations as, the next related data pages are not guaranteed to be contiguous, rather, these next pages could be anywhere in the data file.

    To defragment indexes you can Reorganize or Rebuild them. You should Reorganize indexes when the External Fragmentation value for the corresponding index is in between 10-15 and Internal Fragmentation value is in between 60-75. Otherwise, you should rebuild indexes. But note that while rebuilding indexes for a particular table, the entire table will be locked. But in SQL Server 2005, there is a solution where you can use the ONLINE option as ON while rebuilding indexes for a table. This will rebuild the indexes for the table along with making the table available for transactions.
    And remember that if you work on a transactional database, you should not create more than 5 indexes on the tables on an average. On the other hand, if you work on a Data warehouse application, you should be able to create up to 10 indexes on the tables on an average.

    Below see a list of links to my previous posts where you can find different sql scripts that will help identify potentially useful indexes, indexes not used for some time, etc. Also you may use cfSQLMaster to get all that SQL scripts and many other as a ColdFusion Component.

See you soon with more recommendations.