Code Review and Code Optimization - ASP.NET

Resource Management

• Use Close or Dispose on objects that support it using the Using Statement

• Implement Finalize only if you hold unmanaged resources across client calls, UnManaged code should be removed, since it won't be managed by the CLR

Session Usage

• Disable session state if you do not use it.

• Use the ReadOnly attribute wherever required

• Remove the stored values from the Session

• Don't store infrequently used values in Session

• Check if the entire object needs to be stored. If not, store the individual attributes with unique key and fetch them later

• Don't store the same object multiple times

Page Size

• Reduction of page size - remove unwanted/hidden columns in grid/list views

• Reduce the size of the view state, post-back information which is needed

• Post back information about the labels, controls, JavaScript which have been used for display purpose no need to post back this data

• In general disable view state on control-by-control basis

Ajax

• Avoid Unnecessary AJAX Calls

DataReader

• Code should use drOutput.IsDBNull(index)? Instead of

         drOutput["COL_NAME"]!=Convert.DBNull)

• Use DataReader methods like (GetString, GetInt64, etc) to read value from Data reader. Instead of using

GetObject

• Usage of GetOrdinal methods in IDataReader block

ExecuteScalar

• If only one value is expected from database use ExecuteScalar. Else, use ExecuteReader. Avoid using ExecuteDataset.

Collection Usage

• Implement strongly typed (Generics) collections to prevent casting overhead

Reflection & Late Binding

• Prefer early binding and explicit types rather than reflection

Avoid XMLDocument

• Avoid XMLDocument wherever necessary, Instead use XPathDocument, which is read only.

String Handling

• If ToLower,ToUpper is encountered change them to Compare function. If string concatenation is done by += and the string is too large then convert it to StringBuilder. When declaring a string use String.Empty rather thatn = "". When comparing empty string compare with Lenth > 0.

ASP .Net Web Page Optimization

This article gives a simple checklist that can simply be run on any ASP .Net Web Page to optimize it. This is in no way a complete list, however it should work for most of the web pages. For a performance critical application you will surely need to go beyond this checklist and will require a more detailed plan.

Checklist for all pages

Here is the list of checks that you need to run, not necessarily in order:

• Disable ViewState - Set "EnableViewState=false" for any control that does not need the view state. As a general rule if your page does not use postback, then it is usually safe to disable viewstate for the complete page itself.
• Use Page.Ispostback is used in Page_Load - Make sure that all code in page_load is within "if( Page.Ispostback)" unless it specifically needs to be executed upon every Page Load.
• Asynchronous calls for Web Services - If you are using Web Services in you page and they take long time to load, then preferably use Asynchronous calls to Web Services where ever applicable and make sure to wait for end of the calls before the page is fully loaded. But remember Asynchronous calls have their own overheads, so do not overdo it unless needed.
• Use String Builder for large string operations - For any long string operations, use String Builder instead.
• Specialized Exception Handling - DO not throw exceptions unless needed, since throwing an exception will give you a performance hit. Instead try managing them by using code like "if not system.dbnull �." Even you if you have to handle an exception then de-allocate any memory extensive object within "finally" block. Do not depend on Garbage Collector to do the job for you.
  Collapse | Copy Code

  //
  // Try
  //        'Create Db Connection and call a query
  //        sqlConn = New SqlClient.SqlConnection(STR_CONN)
  // Catch ex As Exception
  //        'Throw any Error that occurred
  //        Throw ex
  // Finally
  //        'Free Database connection objects
  //        sqlConn = Nothing
  //End Try
  //
  

• Leave Page Buffering on - Leave Page buffering On, unless specifically required so. Places where you might require to turn it on in case of very large pages so that user can view something while the complete page is loading.
• Use Caching - Cache Data whenever possible especially data which you are sure won't change at all or will be reused multiple times in the application. Make sure to have consistent cache keys to avoid any bugs. For simple pages which do not change regularly you can also go for page Caching
  Collapse | Copy Code

  //
  // <% @OutputCache Duration="60" VaryByParam="none" %>
  //
  

  Learn more about "VaryByParam" and "VaryByControl" for best results.
• Use Script files - As a rule unless required do not insert JavaScript directly into any page, instead save them as script file ".js" and embed them. The benefit being that common code can be shared and also once a script file is loaded into browser cache, it is directly picked up from browser cache instead of downloading again.
• Remove Unused Javascript - Run through all Javascript and make sure that all unused script is removed.
• Remove Hidden HTML when using Tabstrip - In you are using a Tabstrip control and if the HTML size is too large and the page does frequent reloads, then turn Autopostback of Tabstrip on, put each Pageview inside a panel and turn visibility of all Panels except the current one to False. This will force the page to reload every time a tab is changed however the reload time will reduce heavily. Use your own jurisdiction to best use.

Additional Checklist for performance critical pages

• Disable session when not using it - If your page does not use session then disable session specifically for that page.
  Collapse | Copy Code

  //
  // <%@ Page EnableSessionState="false" %>
  //
  

  If the page only reads session but does not write anything in session, then make it read only.
  Collapse | Copy Code

  //
  // '<%@ Page EnableSessionState="ReadOnly" %>
  //
  

• Use Option Strict On (VB .Net only) - Enabling Option Script restricts implicit type conversions, which helps avoid those annoying type conversion and also is a Performance helper by eliminating hidden type conversions. I agree it takes away some of you freedom, but believe me the advantages outweigh the freedom.
• Use Threading - When downloading huge amounts of data use Threading to load Data in background. Be aware, however, that threading does carry overhead and must be used carefully. A thread with a short lifetime is inherently inefficient, and context switching takes a significant amount of execution time. You should use the minimum number of long-term threads, and switch between them as rarely as you can.
• Use Chunky Functions - A chunky call is a function call that performs several tasks. you should try to design your application so that it doesn't rely on small, frequent calls that carry so much overhead.
• Use Jagged Arrays - In case you are doing heavy use of Multi Dimensional Arrays, use Jagged Array ("Arrays of Arrays") Instead
• Use "&" instead of "+" - You should use the concatenation operator (&) instead of the plus operator (+) to concatenate strings. They are equivalent only if both operands are of type String. When this is not the case, the + operator becomes late bound and must perform type checking and conversions.
• Use Ajax - In performance critical application where there are frequent page loads, resort to Ajax.
• Use the SqlDataReader class - The SqlDataReader class provides a means to read forward-only data stream retrieved from a SQL Server� database. If you only need to read data then SqlDataReader class offers higher performance than the DataSet class because SqlDataReader uses the Tabular Data Stream protocol to read data directly from a database connection
• Choose appropriate Session State provider - In-process session state is the fastest solution. If you store only small data in session state, go for in-process provider. The out-of-process solutions is useful if you scale your application across multiple processors or multiple computers.
• Use Stored Procedures - Stored procedures are pre-compiled and hence are much faster than a direct SQL statement call.
• Use Web Services with care - Web Services depending on data volume can have monstrous memory requirements. Do not go for Web Services unless your Business Models demands it.
• Paging in Database Side - When you needs to display large amount of Data to the user, go for a stored procedure based Data Paging technique instead of relying on the Data Grid/ Data List Paging functionality. Basically download only data for the current page.

Send/Receive SMS Messages from a Computer / PC?

Disable Browser Back functionality using Javascript

This is another technique to disable the back functionality in any webpage. We can disable the back navigation by adding following code in the webpage. Now the catch here is that you have to add this code in all the pages where you want to avoid user to get back from previous page. For example user follows the navigation page1 -> page2. And you want to stop user from page2 to go back to page1. In this case all following code in page1.

?

1 2 3 4 5 6 7

<SCRIPT type="text/javascript">     window.history.forward();     function noBack() { window.history.forward(); } </SCRIPT> </HEAD> <BODY onload="noBack();"     onpageshow="if (event.persisted) noBack();" onunload="">

The above code will trigger history.forward event for page1. Thus if user presses Back button on page2, he will be sent to page1. But the history.forward code on page1 pushes the user back to page2. Thus user will not be able to go back from page1.

Difference between Cluster and Non-cluster index?

A clustered index is a special type of index that reorders 
the way records in the table are physically stored. 
Therefore table can have only one clustered index. The leaf 
nodes of a clustered index contain the data pages.
clustered index is physically stored 
a table can have 1 clustered index

A nonclustered index is a special type of index in which 
the logical order of the index does not match the physical 
stored order of the rows on disk. The leaf node of a 
nonclustered index does not consist of the data pages. 
Instead, the leaf nodes contain index rows.
non clustered index is logically stored 
a table can have 249 non clustred index 
          

An Introduction to Clustered and Non-Clustered Index Data StructuresIntroduction to Clustered and Non-Clustered Index Data Structures

When I first started using SQL Server as a novice, I was initially confused as to the differences between clustered and non-clustered indexes. As a developer, and new DBA, I took it upon myself to learn everything I could about these index types, and when they should be used. This article is a result of my learning and experience, and explains the differences between clustered and non-clustered index data structures for the DBA or developer new to SQL Server. If you are new to SQL Server, I hope you find this article useful.
As you read this article, if you choose, you can cut and paste the code I have provided in order to more fully understand and appreciate the differences between clustered and non-clustered indexes.

Part I: Non-Clustered Index

Creating a Table To better explain SQL Server non-clustered indexes; let’s start by creating a new table and populating it with some sample data using the following scripts. I assume you have a database you can use for this. If not, you will want to create one for these examples.

Create Table DummyTable1
(
EmpId Int,
EmpName Varchar(8000)
)

When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a “heap”. We can confirm the fact that this new table doesn’t have an index by taking a look at the sysindexes system table, which contains one for this table with an of indid = 0. The sysindexes table, which exists in every database, tracks table and index information. “Indid” refers to Index ID, and is used to identify indexes. An indid of 0 means that a table does not have an index, and is stored by SQL Server as a heap.
Now let’s add a few records in this table using this script:

Insert Into DummyTable1 Values (4, Replicate ('d',2000))
GO

Insert Into DummyTable1 Values (6, Replicate ('f',2000))
GO

Insert Into DummyTable1 Values (1, Replicate ('a',2000))
GO

Insert Into DummyTable1 Values (3, Replicate ('c',2000))
GO

Now, let’s view the contests of the table by executing the following command in Query Analyzer for our new table.

Select EmpID From DummyTable1
GO

Empid

4

6

1

3

As you would expect, the data we inserted earlier has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.
Now, let’s execute the following commands to display the actual page information for the table we created and is now stored in SQL Server.

dbcc ind(dbid, tabid, -1) – This is an undocumented command.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')

DBCC ind(@DBID, @TableID, -1)
GO

This script will display many columns, but we are only interested in three of them, as shown below.

PagePID	IndexID	PageType
26408	0	10
26255	0	1
26409	0	1

Here’s what the information displayed means:
PagePID is the physical page numbers used to store the table. In this case, three pages are currently used to store the data.
IndexID is the type of index,
Where:

0 – Datapage
1 – Clustered Index
2 – Greater and equal to 2 is an Index page (Non-Clustered Index and ordinary index),

PageType tells you what kind of data is stored in each database,
Where:

10 – IAM (Index Allocation MAP)
1 – Datapage
2 – Index page

Now, let us execute DBCC PAGE command. This is an undocumented command.
DBCC page(dbid, fileno, pageno, option)
Where:

dbid = database id.
Fileno = fileno of the page.  Usually it will be 1, unless we use more than one file for a database.
Pageno = we can take the output of the dbcc ind page no.
Option = it can be 0, 1, 2, 3. I use 3 to get a display of the data.  You can try yourself for the other options.

Run this script to execute the command:

DBCC TRACEON (3604)
GO

DBCC page(@DBID, 1, 26408, 3)
GO

The output will be page allocation details.

DBCC TRACEON (3604)
GO

dbcc page(@DBID, 1, 26255, 3)
GO

------------------------

indexes in SQL Server

Hi all, In this article I am trying to explain “How to define indexes in SQL Server”. For this article am going to use Products table of Northwind database. 

This article deals with -

• Query Optimizer  
• Creating an Index
• Creating Unique Index
• Creating Clustered Index
• Creating Full-Text Index
• Changing properties of Index
• Renaming an Index
• Deleting an Index
• Specifying Fill factor of Index
• Create XML Index
• Delete XML Index
• Advantages of Indexing
• Disadvantages of Indexing
• Guidelines for Indexing

Explanation

Every organization has its database and each and every day with the increase in the data volume these organizations has to deal with the problems relating to data retrieval and accessing of data. There is need of system which will results into increase in the data access speed. An index (in simple words it like index of any book eg. While searching a word in Book we use index back of book to find the occurance of that word and its relevant page numbers), which makes it easier for us to retrieval and presentation of the data. An Index is a system which provides faster access to rows and for enforcing constraints.

If we don't create any indexes then the SQL engine searches every row in table (also called as table scan). As the table data grows to thousand, millons of rows and further then searching without indexing becomes much slower and becomes expensive.

eg. Following query retrieves Customer information where country is USA from Customers table of the Northwind database.

 Collapse

SELECT CustomerID,ContactName,CompanyName,City
FROM Customers
WHERE Country ='USA'

As there is no Index on this table, database engine performs table scan and reads every row to check if Country is "USA". The query result is shown below. Database engine scans 91 rows and find 13 rows.

Indexes supports to the database Engine. Proper indexing always results in considerable increase in performance and savings in time of an application and vice-versa. When SQL Server process a query then it uses Indexes to find the data. Indexes cane created on one or more columns as well as on XML columns also. We can create Index by selecting one or more columns of a table being searched. Index creates model related with the table/view and constraints created using one or more columns. It is more likely a Balanced Tree. This helps the SQL Server to find out rows with the keys specified.

Indexes may be either Clustered or Non-Clustered.

Clustered Index 

Every table can have one and only Clustered Index because index is built on unique key columns and the key values in data rows is unique. It stores the data rows in table based on its key values. Table having clustered index also called as clustered table.

Non-Clustered Index

It has structure different from the data rows. Key value of non clustered index is used for pointing data rows containing key values. This value is known as row locator. Type of storage of data pages determines the structure of this row Locator. Row locator becomes pointer if these data pages stored as a heap. As well as row locator becomes a clustered index key if data page is stored in clustered table.

Both of these may be unique. Wherever we make changes to the data table, managing of indexes is done automatically.

SQL Server allows us to add non-key column at the leaf node of the non clustered index by passing current index key limit and to execute fully covered index query.

Automatic index is created wherever we create primary key, unique key constraints to table.

The Query Optimizer

Query Optimizer indexes to reduce operations of disk input-output and using of system resources when we fire query on data. Data manipulation Query statements (like SELECT, DELETE OR UPDATE) need indexes for maximization of the performance. When Query fires the most efficient method for retrieval of the data is evaluated among available methods. It uses table scans or index scans.

Table scans uses many Input-output operations, it also uses large number of resources as all rows from the table are scanned.

Index scan used for searching index key columns to find storage location.
The index containing fewer columns results in to faster query execution and vice-versa.

Creating an Index

• Connect to Northwind database from Object Explorer, right click on the Customers table to create an index and click on modify. 

 

• Click on Index/Keys from Table Desinger Menu on top or right click on any column and click onIndex/Keys.

 

• Click on Add from Indexes/Keys dialog box.

• From Selected Primary/Unique Key or Index list, select the new index and set properties for the index in the grid on right hand side.  

• Now just specify other settings if any for the index and click Close.
• When we save the table, the index is created in the database. 

We also create this index by using query. This command mentions the name of index (Country) the table name (Customers), and the column to index (Country).

 Collapse

CREATE INDEX Country ON Customers (Country) 

Creating Unique Index

SQL Server permits us to create Unique Indexes on columns which are unique to identify. (like employee’s Reference ID, Email-id etc.) We use set of columns to create unique index.

• Right-click on the Customers and click Modify in Object Explorer.
• Now, click on Indexes/Keys from Table Designer menu and click on Click Add.
• The Selected Primary/Unique Key or Index list displays the automatically generated name of the new index.  
• In the grid, click on Type, from the drop-down list, Choose Index.
• Under Column name,we can choose columns we want to index and click on OK. Maximum we can setup 16 columns. For optimum performance, it is recommended that we use one or two columns per index. For every column we values of these columns are arranged in ascending or descending order.  
• In the grid, click Is Unique and select select Yes. 

• Null is treated as duplicate values. So, it is not possible to create unique index on one column if it contains null in more than one row. Likewise index cannot be created on multiple columns if those columns contains null in same row.
• Now select Ignore duplicate keys option. If it is required to ignore new or updated data that will lead to creation of duplicate key in the index (with the INSERT or UPDATE statement).
• When we save the table, the index is created in the database.

We also create this index by using query. This command mentions the name of index (ContactName) the table name (Customers), and the column to index (CompanyName,ContactName).

 Collapse

CREATE UNIQUE INDEX ContactName ON Customers (CompanyName,ContactName) 

Creating Clustered Index

table can have only one clustered index. In Clustered index logical order and physical order of the index key values is identical of rows in the table.

• In the Object Explorer click on the Northwind database, right click on the Customres to create an index and click on modify.
• Now we have Table Designer for the table.
• From the Table Designer menu, click Indexes/Keys and from Indexes/Keys dialog box, click Add. 
• Now from Selected Primary/Unique Key or Index list, Select the new index
• In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.  

• When we save the table, the index is created in the database.

We also create this index by using query. This command mentions the name of index (PK_Customers) the table name (Customers), and the column to index (CustomerID).

 Collapse

CREATE CLUSTERED INDEX PK_Customers on Customers(CustomerID)

Creating Full Text Search 

For text based columns, full text search is always required to be performed under several times. In such situations full text index is used. A regular index is required to be prepared before creating full text index as the later relies on the former. Regular index is created on single column having not null. It is recommended to create regular index on column having small values. For several occasions, SQL Server management Studio is also used to create catalog.

• In the object explorer click on the Northwind database, right click on the customers to create an index and click on modify.
• Now, we have Table Designer for the customers table and then Click Fulltext Index from the Table Designer menu.

 

• Dialog box for full-text index opens. (Sometimes database is not enabled for full text indexing. In such situations add button disabled. To enable it check properties for database by right clicking on database. And check the full text indexing check box)
• Now we have to right click on storage>New Full-Text catalog to create a catalog. Enter some required information in dialog box.  

• Now from Table Designer menu, open the Full Text Index property dialog and then click on Add.
• Now select new index from selected full-text index list and assign properties for index in the grid.  

• When we save Table the index is automatically saved in database, and this index is available for modifications.

Changing index properties

• Connect to the SQL-Server 2005, In the object explorer click on the Northwind database.
• Click Indexes/Keys from the table designer menu.
• Now select index from the selected primary/unique key or index list. And Change the properties.
• When we save Table the index is automatically saved in database.

Renaming an Index

• Right-click the table with the index you want to rename and click Modify, in Object Explorer.
• Click Indexes/Keys from the Table Designer menu.
• Now from the Selected Primary/Unique Key or Index list, select the index.
• Click Name and type a new name into the text box in the grid.
• When we save Table the index is automatically saved in database.

We can also rename indexes with the sp_rename stored procedure. The sp_rename procedure takes, at a minimum, the current name of the object and the new name for the object. While renaming indexes, the current name must include the name of the table, a dot separator, and the name of the index, as shown below:

 Collapse

EXEC sp_rename 'Customers.Country', 'Countries'

Deleting an Index  

• Right-click the table with indexes you want to delete and click Modify In Object Explorer.
• Click Indexes/Keys from the Table Designer menu.  
• Select the index you want to delete from the Indexes/Keys dialog box and Click on Delete.
• When we save Table the index is deleted from the database.

We can follow same procedure for deleting a Full text index. From the Table Designer select Full text index and then select the index name and click on delete.

It is very sensible to remove index from database if it is not much of worth. eg. For instance, if we know the queries are no longer searching for records on a speicific column, we can remove the index. Unneeded indexes only take up storage space and diminish SQL command is shown below.

 Collapse

DROP Index Customers.Country  

Specifying Fill Factor

Fill Factor Fill factor is used by SQL Server to specify how full each page index. The fill factor is the percentage of allotted free space to an index. We can specify the amount of space to be filled. It is very important as the improper selection my slow down the performance.

• Right-click the table with an index for which we want to specify fill factor and click Modify in Object Explorer
• Click Indexes/Keys, from the Table Designer menu.
• From Selected Primary/Unique Key or Index list, select the index.
• Type a number from 0 to 100, in the Fill Factor box. Value 100 denotes that index will fully filled up and storage space requirement will be minimum, this is recommended in situations where there are minimum changes of change in data. data fill factor. If there is regular modification and addition to the data, then set this value to minimum. Storage space is proportionate to the value set.

 

Creating XML Index

There somewhat different way to create XML indexes, we cannot create XML using Index/Keys dialog box. We create XML index from xml data type columns those are based on primary XML index. When we delete the primary XML index, then all the XML index will be deleted.

• In Object Explorer, right-click the customers table to create an XML index and click Modify.  

• Select the xml column for the index for the table opened in Table Designer.
• From the Table Designer menu, click XML Index,  
• Click on add, in the XML Indexes dialog box  

Deleting XML Indexes 

• Right-click the customers table with the XML index you want to delete and click Modify in Object Explorer.  
• click on XML Index, from the Table Designer menu.
• From selected XML Index column, Click the index you want to delete. And then Click on Delete.

Viewing Existing Indexes

We can view list of all indexes on a table in the dialog box we used to create an index. Just Click on the Selected index drop down control and scroll through the available indexes.

We can use a stored procedure named sp_helpindex. This stored procedure gives all of the indexes for a table with its all relevant attributes. The only input parameter to the procedure is the name of the table, as shown below.

 Collapse

EXEC sp_helpindex Customers

How Index works 

The columns specified in the CREATE INDEX COMMAND taken by the database engine and sorts the values in Balanced Tree(B-Tree) data structure. B-Tree structure supports faster search with minimum dist reads, and allows the database engine to find quick start and end point for the stated query.

The database takes the columns specified in a CREATE INDEX command and sorts the values into a special data structure known as a B-tree. A B-tree structure supports fast searches with a minimum amount of disk reads, allowing the database engine to quickly find the starting and stopping points for the query we are using.

Conceptually, every index entry has the index key. Each entry also includes a references to the table rows which share that particular value and from which we can retrieve the required information.

It is much similar to the back of a book helps us to find keywords quickly, so the database is able to quickly narrow the number of records it must examine to a minimum by using the sorted list of Key values stored in the index. Thus we avoid a table scan to fetch the query results. Following some of the scenarios where indexes offer a benefit. Advantages of Indexing

Searching For Records  

The most important use for an index is in finding a record or set of records matching a WHERE clause. Indexes can help queries with speicfic range. as well as queries looking for a specific value. E.g the following queries can all benefit from an index on UnitPrice:

 Collapse

DELETE FROM Customers WHERE Country = "USA"
 UPDATE Customers SET Region = "Pacific" WHERE Country = "USA" 
 SELECT * FROM Customers WHERE Country="USA" or "Brasil"

Indexes work well when searching for a record in DELETE and UPDATE commands as they do for SELECT statements.

Sorting Records

When we require sorted results, the database tries to find an index and avoids sorting the results while execution of the query. We control sorting of a dataset by specifying a field, or fields, in an ORDER BY clause, with the sort order as ascending (ASC) or descending(DESC). E.g. Query below returns all customers sorted by Country:

 Collapse

SELECT * FROM Customers ORDER BY Country ASC

When there is no indexes, the database will scan the Customers table and then sort the rows to process the query. However, the index we created on Country (Country) before will provide the database with a already sorted list of Countries. The database can simply scan the index from the first record to the last record and retrieve the rows in sorted order. The same index works same with the following query, It simply scans the index in reverse.

 Collapse

SELECT * FROM Customers ORDER BY Country DESC

;

Grouping Records

We can use a GROUP BY clause to group records and aggregate values, e.g. for counting the number of customers in a country. To process a query with a GROUP BY clause, the database will quite ofen sort the results on the columns included in the GROUP BY. The following query counts the number of customers from every country with the same UnitPrice value.

 Collapse

SELECT Count(*) FROM Products GROUP BY UnitPrice

Index Drawbacks

There are few drobacks of indexes. While indexes provide a substantial performance benefit to searches, there is also a downside to indexing.

Indexes and Disk Space

Indexes are stored on the disk, and the amount of space required will depend on the size of the table, and the number and types of columns used in the index. Disk space is generally cheap enough to trade for application performance, particularly when a database serves a large number of users. To see the space required for a table, use the sp_spaceused system stored procedure in a query window.  EXEC sp_spaceused Customers  

Result

 Collapse

name                rows        reserved           data               index_size         unused
---------        ----------- ------------------ ------------------ ------------------ 
Customers        91          200 KB             24 KB              176 KB             0 KB

From the above output, the table data uses 24 kb, while the table indexes use about 18 times as much, or 176 kilobytes. The ratio of index size to table size can vary greatly, depending on the columns, data types, and number of indexes on a table.

Indexes and Data Modification

If the data is change or modified on regular intervals then database engine requires to update all the indexes, thus too many indexes will slows down the performance. Thus Database used for transaction processing should use fewer indexes to allow for higher throughput on insert and updates. While in DSS (Decision Support System) and datawarehousing where information is static and queries is required largely for the reporting purposes than the modification purposes then heavy indexing is required to optimze the performance.

Another downside to using an index is the performance implication on data modification statements. Any time a query modifies the data in a table (INSERT, UPDATE, or DELETE), the database needs to update all of the indexes where data has changed. As we discussed earlier, indexing can help the database during data modification statements by allowing the database to quickly locate the records to modify, however, we now caveat the discussion with the understanding that providing too many indexes to update can actually hurt the performance of data modifications. This leads to a delicate balancing act when tuning the database for performance.

Additional Index Guidelines

In order to create effective index choice of correct columns and types is very important.

Keeping Index Keys Short

It becomes harder for database engine to work on larger an index key. E.g. An integer key is smaller in size then a character field for holding 100 characters. Keep keep clustered indexes as short as possible.

We must try to avoid using character columns in an index, particularly primary key indexes. Integer columns will always have an advantage over character fields in ability to boost the performance of a query.

Distinct Index Keys

Indexes with a small percentage of duplicated values are always effective.

An index with a high percentage of unique values is a selective index. Obviously, a unique index is the most selective index of all, because there are no duplicate values. SQL Server will track statistics for indexes and will know how selective each index is. The query optimizer utilizes these statistics when selecting the best index to use for a query.