Useful Info on Triggers

Hi All,

I come across a nice article from SQL-SERVER-PERFORMANCE.Com on triggers by Brad Mcgehee. http://www.sql-server-performance.com/tips/trigger_tuning_p1.aspx

If you need to implement cascading referential integrity (such as cascading deletes) in your SQL databases, use the cascading referential integrity constraint instead of triggers to perform the cascading delete, as they are much more efficient and can boost performance. If you have an older (7.0 and older) application that you have moved to SQL Server 2000/2005, and it used triggers to perform cascading deletes, consider removing the triggers and using cascading referential integrity instead.

*****

While INSTEAD OF triggers are technically interchangeable with conventional AFTER triggers, the main reason to use an INSTEAD OF triggers is to allow you to update certain types of views. What does this mean in regards to performance? Assuming most of the triggers you write only rollback transactions rarely, then you will want to continue using AFTER triggers. This is because the overhead of an INSTEAD OF trigger is higher than an AFTER trigger, assuming rollbacks are rare. But if rollbacks are common (more than half the time), then an INSTEAD OF trigger would be the better deal because its overhead is less than an AFTER trigger that rolls back. So for most triggers, stick with the conventional AFTER trigger and save INSTEAD OF triggers to update views.

*****

SQL Server 2000/2005 allows you to somewhat control the order in which triggers are fired. I say "somewhat" because you don't have full control. You have the option to specify which trigger fires first and last, but if you have more than two triggers on a table, you can't control the order in which the other ones fire.So how can selecting the order that a trigger is fired help your application's performance? To optimize trigger performance, you should specify that the trigger most likely to rollback (for whatever reason) as the first trigger to fire. This way, if the trigger does cause a rollback, it only affects the first trigger.

Let's say you have three triggers on a table, but instead of having the most likely to rollback trigger specified as the first trigger, you have it specified as the last. In this case, assuming it is rolled back, then all three triggers have to be rolled back. But if the trigger were the first trigger, and not the last, then only one trigger, not three triggers, would have to be rolled back. Reducing the number of triggers rolled back reduces SQL Server's overhead and boosts its performance.

*****

The amount of time that a trigger takes to run is mostly a function of the number of tables referenced in the trigger and the number of rows affected by the code inside the trigger. Because of this, always try to minimize the number of tables referenced in a trigger, and minimize the number of rows being affected.

In addition, keep the code in your triggers to the very minimum to reduce overhead. This is important because triggers typically fire during INSERTs, UPDATEs, and DELETEs, all of which can be common occurrences in OLTP applications. The more code that runs in the trigger, the slower each INSERT, UPDATE, and DELETE that fires will be.

*****

If your trigger includes a WHERE clause, don't forget to include any appropriate indexes for them to use. WERE clauses hidden in triggers are often easy to forget, and like any statements, indexes can often significantly affect their performance.

One way to do this is to run your trigger code from Query Analyzer or Management Studio and then check the resulting execution plan. Doing so will quickly tell you if you need to add an appropriate index.

*****

If you have an INSERT, UPDATE, or DELETE statement that seems to be taking longer to run that you would expect it to run, be sure to check to see if there is a trigger associated with that table. The performance problem you are seeing may well be because of the trigger, not the data modification statement itself.Don't forget to tune trigger code just like you would any other code. Because trigger code is "hidden," many people forget about it and don't realize the potential performance problems that they can cause.

You can use Profiler and Query Analyzer/Management Studio to find out how triggers in your database are working.

*****

Don't use a trigger to enforce referential integrity if you have the option to use SQL Server's built-in referential integrity instead. Using SQL Server's built-in referential integrity is much faster than using a trigger to perform the same task.

*****

If you have a choice between using a trigger or a CHECK constraint to enforce rules or defaults within your SQL Server databases, you will generally want to choose a CHECK constraint as they are faster than using triggers when performing the same task.

*****

Try to avoid rolling back triggers because of the overhead involved. Instead of letting the trigger find a problem and rolling back a transaction, instead catch the error before it can get to the trigger (if possible based on your code). Catching an error early (before the trigger fires) consumes much fewer server resources than letting the trigger roll back.

Catching errors before a transaction can fire a trigger can be done through code in the initiating transaction, and it sometimes can be accomplished by adding a constraint to the table to catch common mistakes. If a constraint catches an error, the trigger will not fire.

*****

Sometimes, for performance reasons, it is necessary to maintain denormalized data. For example, you might need to maintain derived data (such as cumulative data), in a table because it is too time consuming to calculate it on the fly from within SELECT statements. One way to easily maintain denormalized data is to use triggers. For example, every time a new sale is added to a Sales table, a trigger could fire, adding the value of the sale to a SalesTotal table.

*****

The code that is included inside an UPDATE trigger runs every time its related table is updated. In most UPDATE triggers, the code in the trigger affects only certain columns, not all of them. Because of this, it is pointless (and a waste of SQL Server resources) to run all of the code in the trigger if the column or columns you are interested in have not been updated. In other words, even if a column you are not interested in is updated, the UPDATE trigger will fire and run its code.To help reduce the unnecessary running of code in an UPDATE trigger, you can take advantage of one of two different functions: UPDATE() (available in SQL Server 2000/2005) or COLUMNS_UPDATED() (available in SQL Server 7.0 and 2000/2005).Either function can be used to test to see if a particular column you are interested in has changed or not. Because of this, you can write code in your trigger to only run if the column you are interested in has changed, otherwise you can prevent the code from running if the column you are interested in has not changed. This can reduce the amount of work the trigger needs to do, boosting overall performance of your database.

The UPDATE() function is used to check only one column at a time. The COLUMNS_UPDATED() function can be used to check multiple columns at a time.

xp_cmdshell stored procedure

xp_cmdshell

The extended stored procedure xp_cmdshell allows you to shell out and execute an valid operating system command. By default, all users allocated to the fixed system role sysadmin (i.e. SA account) have execute access. This is a real tough command to administer. Why? Every project I have worked on to date has some need for it. Because of the fact that SA is the only user with sysadmin access, rather than creating a special account or looking at other work-arounds, the SA account is used to execute the task. Classic examples are stored procedures wrapped up in DTS jobs or supposed global "administrative" functions. This is problematic because:

Now "application" databases are using the SA account and rely on it to run their jobs.

Altering the SA password has application impact

Xp_cmdshell will be executed under the security context in which the SQL Server service is running

Points a) and b) are obvious and the fix is a simple matter of explicitly granting execute access to xp_cmdshell via a role and allocating that role to a managed and secure database user whose actual login is rarely used (i.e. DTS connectivity only to run the command shell tasks).Point c) is the very important. If the database user is a member of sysadmin then, more than likely, the user will have "local administrative" privileges to the server, as this is the user running the MS SQL Server and associated services. This is even more the reason why not to use the SA account. If the user is not a sysadmin but has been granted execute access as described above, then the SQL Server Agent proxy service user will be used instead.

The SQL Server Agent proxy account can be altered via xp_sqlagent_proxy_account (undocumented in BOL), which defines the account used to run the SQLServerAgent service. This may be your SQLServer NT user if you are not using the Administrator account (which is bad security practice), so you may decide to alter this to another user with restricted access rights for finer control to the operating system.

In the end, xp_cmdshell should be carefully evaluated before using it. Ideally it should be totally disabled (revoke execute permission) for ultimate security. Look at use isql jobs scheduled via NT and look closer at the options available to you via DTS.

Example: Stored Procedure to delete files in a folder

When your only way of managing SQL Server is through Enterprise Manager or Query Analyzer, you will have to do a lot of tasks through command shell. If you find yourself routinely conduct some activities using xp_cmdshell, it is a good idea to wrap those routines into a stored procedure. This example takes an input parameter for directory name and delete all files within that directory. The directory name can be UNC path. I put some comments within this procedure so it should be easy to follow.

CREATE proc usp_DeleteFileInFolder
@FolderName varchar(150)
AS

SET NOCOUNT ON

declare @DOSCommand varchar(150)
--Check whether the user supply \ in the directory name

if not (right(@FolderName, 1) = '\')
set @FolderName = @FolderName + '\'

--Delete all files within this folder. Note: del *.* only deletes files,
--not folders. Q is the quite switch so DOS will not ask for confirmation

set @DOSCommand = 'del /Q ' + '"' + @FolderName + '*.*' + '"'
print @DOSCommand
exec master..xp_cmdshell @DOSCommand
GO

Using Indexes to Bypass Locks

Problem:
One of the issues you'll face with SQL Server is blocking which is caused by other processes that are holding locks on objects. Until the locks are removed on an object the next process will wait before proceeding. This is a common process that runs within SQL Server to ensure data integrity, but depending on how transactions are run this can cause some issues. Are there ways to get around blocking by using different indexes to cover the queries that may be running?

Solution:

In order to explain my point I am going to use one table and run queries from two different sessions. The table that we will be using has the following columns:

display name - my application use this name in order to display customer related information.

current quota - this is a number stating the current quota (of irrelevant item) of a customer, in my scenario this field is monitored by a specific process for specific customers.

next month's quota - this is a number stating the planned quota for next month, note this field is used in a long running transactions calculating quota for all customers.

support level - a number between 1 and 3 stating the level of support granted to a customer (1=high, 3=low), we have several applications, processes and transaction (business transactions) that deal with different customers according to their support level.

Create Table Customers
IF OBJECT_ID ('customers') IS NOT NULL
DROP TABLE customers
GO

CREATE TABLE customers
( id INT NOT NULL,
display_name VARCHAR(10) NOT NULL,
current_quota bigint NOT NULL,
next_month_quota bigint NOT NULL,
support_level smallint NOT NULL,
some_other_fields_size_1k CHAR(1000) NULL )
GO

ALTER TABLE customers
ADD CONSTRAINT customers_pk PRIMARY KEY (id)
GO

As you can see I have added an additional column called "some_other_fields_size_1k". This column is simulating an additional 1K of customer data; I like adding it to my tests to make the optimizer respond to more authentic requests.

Let's fill the table with some data, we will do the following:

Insert 1000 records

Every 150 customers I'll place a customer with support level = 1 and the rest will have to settle for support level = 3.

Extend some quota as current and set next month's quota to 0.

Table Customers - fill with data

SET nocount ON
DECLARE @i AS INT
SET @i = 0
WHILE @i <1000>
BEGIN 
SET @i = @i + 1 

INSERT INTO customers ( id, display_name, current_quota, support_level, next_month_quota, some_other_fields_size_1k) 

VALUES ( @i, 'name-' + CAST (@i AS VARCHAR (10)), 100000 + @i,
--making customer with id 150, 300, 450, 600, 750 
-- and 900 with support level 1 
CASE @i%150 WHEN 0 THEN 1 ELSE 3 END, 0, 'some values ...') END 
SET nocount OFF 
GO 

As mentioned earlier, there are two processes that will be running:

A general dashboard application that checks the status of the current quota of our top customers.

A planning module that performs various calculations and changes quotas of customers based on calculations done by the application.

Dashboard Application SELECT Statement

SELECT display_name, current_quota 
FROM customers 
WHERE support_level = 1 
ORDER BY 1

Planning Module UPDATE Statement

UPDATE customers 
SET next_month_quota = "any quota"
WHERE id= "any id"

I would like to state this scenario assumes that we are working with the default isolation level (READ COMMITTED) of Microsoft SQL Server.

READ COMMITTED isolation level allows good concurrency with reasonable integrity (some might argue it is not that good and not that reasonable - and we'll get to that but hey, most of the OLTP applications we know use it).

The core of the READ COMMITTED isolation level is composed of two building blocks called exclusive locks and shared locks that comply with the following guidelines:

An exclusive lock (A.K.A. "X lock") is taken for any resource that requires a write.

A shared lock (A.K.A. "S lock") is taken for any resource that requires a read.

S lock can be taken on a specific resource unless there is an X lock already taken for it.

X lock can be taken on a specific resource as long as there is not already another lock taken (not even an S lock).

Now to the challenge: there are cases where the planning module (long running transactions) locks a specific record and does not allow the dashboard application to view specific information for a long time; believe me, it happens - I know - I have built this scenario :).

We all know that indexes boost database performance by keeping an ordered list (tree based) of keys and a linkage to the actual data location. So when our application has some performance issues the intuitive solution would be to look at the SQL statement that does not perform well and possibly add indexes. Here is our statement that we are trying to run that is getting blocked.

Dashboard Application SELECT Statement

SELECT display_name, current_quota
FROM customers 
WHERE support_level = 1 
ORDER BY 1

We could add an index on support_level, but since adding this index will not solve our blocking issue we will not bother adding this index.

Let's start from the beginning...

Phase 1 - Check for bottlenecks

In short, we first check the database machine's CPU, disk and memory. When we see it is not that, some of us would turn to the event log. Not finding anything unusual in the event log, scratching our head over enough time - the locks issue may pop up (hard to admit but the locks part is somehow always a surprising part :) - I usually turn to check the locks when I see that some SQL is stuck and the machine is saying: "I am going to sleep, please wake me when you need something".

Phase 2 - Drill down into locks

In order to view the locks there is of course the famous sp_lock and even sp_lock2, but I like to use my own get_locks procedure (which also gives additional information regarding the locked objects (e.g. owner object and index name etc') this can be created in any SQL Server 2005 server with the following SQL script:

get_locks stored procedure - creation script

CREATE PROCEDURE get_locks 
AS 
SELECT OBJECT_NAME(tl.resource_associated_entity_id%2147483647) obj, tl.request_session_id , tl.request_mode, tl.request_status, tl.resource_type, OBJECT_NAME (pa.OBJECT_ID) owner_obj, pa.rows, tl.resource_description, si.name index_name, si.type_desc index_type_desc 
FROM sys.dm_tran_locks tl 
LEFT OUTER JOIN sys.partitions pa 
ON (tl.resource_associated_entity_id = pa.hobt_id) 
LEFT OUTER JOIN sys.indexes si 
ON (pa.OBJECT_ID = si.OBJECT_ID AND pa.index_id = si.index_id) 
WHERE resource_type NOT LIKE 'DATABASE' 
ORDER BY tl.request_session_id, tl.resource_type 
GO 

Now let's check to see that there are any locks, by running:

exec get_locks

GO

We get the following result, which shows there are no current locks.

Now let's open two sessions (SQL Server Management Studio query windows), in the first (we'll call it Session A) we'll run the following SQL:

Session A - Dashboard Application SELECT

SELECT @@SPID 
GO 


SELECT display_name, current_quota 

FROM customers 

WHERE support_level = 1 

ORDER BY 1 

GO

This statement (when not blocked by others) is fetching the display_name and current_quota of customers with support level = 1:

In the second session (we'll call it Session B) let's run the following update statement:

Session B - Planning Module UPDATE

SELECT @@SPID

--added just for session identification in the dm views.

GO

BEGIN TRAN 

UPDATE customers 

SET next_month_quota = 2500 

WHERE id=150 

This is returned:

The above statement opened a transaction, updated a record and did not close the transaction (with either commit or rollback command), so it is currently locking some of the resources.

Let's review the locks with get_locks

exec get_locks 
GO

This time the result would be:

What we can see here is the process # 56 (Our Session B) has locked:

The object 'customers' which is a table with "IX lock".

The page '1:334' which is actually page 334 in file #1 with "IX lock".

The key '(96009b9e9046)' which is an indicator describing the record with id = 150 in the 'customers_pk' with "X lock". "IX lock" is a way to notify the database not to allow any shared locking that will block my update later on, when an object is locked with "IX lock" it:

Assumed that a lower granularity will acquire a specific "X Lock".

It allows only IX and IS locks to be acquired on the same resource.

Now let's re-run the dashboard application query within Session A:
Session A - Dashboard Application SELECT

SELECT @@SPID 
GO 
SELECT display_name, current_quota 
FROM customers 
WHERE support_level = 1 
ORDER BY 1 
GO 

This time we can see that the query is blocked, because it is still executing.

Re-checking the locks with get_locks We can see the following lock status:
exec get_locks
GO

As you can see the previous locks from session B (spid = 56) are still there and we've got some new locks from session A (spid = 55):

Same as session B this session also succeeded placing an "I Lock" (this time "IS Lock") on:

'customers' object (table).

Page '1:334' (which is actually page 334 in file #1).

Unlike Session B, when trying to place an "S Lock" on the key '(96009b9e9046)' which is an indicator describing the record with id = 150 in the 'customers_pk' this session finds out that there is already "X Lock" there and gets into a 'WAIT' state (see request_status column).

Phase 3 - Trying an index

Well we've started the 'Solution' description by telling that adding an index on the support_level column in order to boost performance still won't help, but it will get us closer - let's try:
-- Before doing that we'll need to:
-- 1. Stop Session A (which is still running).
-- 2. Rollback Session B (which is still locking).
-- 3. Run:
CREATE INDEX customers_ix1
ON customers (support_level)
GO
-- 4. Rerun Session B (for locking)
-- 5. Re-run Session A (to be blocked)

OK, now that we have done the above if we run Session B UPDATE, Session A SELECT and get_locks, we will get the following:











As you can see same locks as before are used plus customers_ix1 index's got a new page "IS Lock" (page 77 in file 1) and a new "S Lock" on key '(970081334a1d)' placed by Session A.

Some might ask why did Session A succeed with placing a lock on customers_ix1 (line 1 in the above table) let's check the execution plan for the select statement by running:
-- 1. Stop running Session B
-- 2. Press Ctrl-T to change results to text:
-- 3. Run:

SET SHOWPLAN_TEXT ON
GO
SELECT display_name, current_quota
FROM customers
WHERE support_level = 1
ORDER BY 1
GO
SET SHOWPLAN_TEXT OFF
GO

This will yield:

As you can see Session A is accessing the data through the customers_ix1, so it tries to place an "S Lock" on it (and of course succeeds).

Well, this encapsulates two great hints for our solution:

When performing a write operation, SQL Server does not lock related indexes (e.g. our Session B did not lock customers_ix1; note it would not have locked it regardless if we used it or not!), only the relevant data row.

When performing a read operation, SQL Server locks only the objects (e.g. indexes, data rows etc') that it found and used within its access path.

So current status is that we can use indexes as a solution for Session A activity as long as they do not access the actual data row.

By adding an index that will cover all columns of the table that are required for Session A's specific query (A.K.A. Covering Index), we will have a solid bypass without requiring specific access to the actual data row. Having achieved this, Session A will not be blocked while fetching data from a row that is actually blocked for writing (as done by Session B).

Adding the index is done by the following:

-- Before doing that we'll need to: 
-- 1. Rollback Session B which is still locking. 
-- 2. Run: 
CREATE INDEX customers_ix2 
ON customers (support_level, display_name, current_quota) 
GO 

Now let's re-run the locking update of Session B and the select for Session A.

Session A

SELECT display_name, current_quota  
FROM customers  
WHERE support_level = 1 ORDER BY 1   

Trying to run Session A's SELECT statement (while Session B's transaction is still locking) will produce the following results without being blocked:

Review New Query Plan

SET SHOWPLAN_TEXT ON 
GO  
SELECT display_name, current_quota 
FROM customers 
WHERE support_level = 1 
ORDER BY 1  
GO  
SET SHOWPLAN_TEXT OFF 
GO 

the result is:

As you can see the only database object that is participating in fetching the data is customers_ix2 which we've just created. By using this covering index we were able to use a totally different index than the update statement and therefore have the statement complete without issue.

Some might argue that we would not need to go through a whole article just for putting a covering index and they might be correct, still there are certain situations when a covering index is not that straight forward, but it can be used to overcome locking challenges such as the above example.

Performance Tips - IV

Try to restrict the queries result set by using the WHERE clause.
This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.

Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.

Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.
Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So, you can improve the speed of such queries in several times.

Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.

Use table variables instead of temporary tables.
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.

Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.

Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.

Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
You can quickly get the n rows and can work with them, when the query continues execution and produces its full result set.

Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

Do not use optimizer hints in your queries.
Because SQL Server query optimizer is very clever, it is very unlikely that you can optimize your query by using optimizer hints, more often, this will hurt performance.

Few Index Optimization Tips

Consider creating index on column(s) frequently used in the WHERE, ORDER BY, and GROUP BY clauses.
These column(s) are best candidates for index creating. You should analyze your queries very attentively to avoid creating not useful indexes. Keep your indexes as narrow as possible.

Because each index take up disk space try to minimize the index key's size to avoid using superfluous disk space. This reduces the number of reads required to read the index and boost overall index performance.

Drop indexes that are not used.

Because each index take up disk space and slow the adding, deleting, and updating of rows, you should drop indexes that are not used. You can use Index Wizard to identify indexes that are not used in your queries.

Try to create indexes on columns that have integer values rather than character values.

Because the integer values usually have less size then the characters values size (the size of the int data type is 4 bytes, the size of the bigint data type is 8 bytes), you can reduce the number of index pages which are used to store the index keys. This reduces the number of reads required to read the index and boost overall index performance. Limit the number of indexes, if your application updates data very frequently.

Because each index take up disk space and slow the adding, deleting, and updating of rows, you should create new indexes only after analyze the uses of the data, the types and frequencies of queries performed, and how your queries will use the new indexes. In many cases, the speed advantages of creating the new indexes outweigh the disadvantages of additional space used and slowly rows modification. However, avoid using redundant indexes, create them only when it is necessary. For read-only table, the number of indexes can be increased. Check that index you tried to create does not already exist.

Keep in mind that when you create primary key constraint or unique key constraints SQL Server automatically creates index on the column(s) participate in these constraints. If you specify another index name, you can create the indexes on the same column(s) again and again.

Create clustered index instead of nonclustered to increase performance of the queries that return a range of values and for the queries that contain the GROUP BY or ORDER BY clauses and return the sort results.

Because every table can have only one clustered index, you should choose the column(s) for this index very carefully. Try to analyze all your queries, choose most frequently used queries and include into the clustered index only those column(s), which provide the most performance benefits from the clustered index creation.

Create nonclustered indexes to increase performance of the queries that return few rows and where the index has good selectivity.

In comparison with a clustered index, which can be only one for each table, each table can have as many as 249 nonclustered indexes. However, you should consider nonclustered index creation as carefully as the clustered index, because each index take up disk space and drag on data modification.

Create clustered index on column(s) that is not updated very frequently.

Because the leaf node of a nonclustered index contains a clustered index key if the table has clustered index, then every time that a column used for a clustered index is modified, all of the nonclustered indexes must also be modified.

Create clustered index based on a single column that is as narrow as possibly.

Because nonclustered indexes contain a clustered index key within their leaf nodes and nonclustered indexes use the clustered index to locate data rows, creating clustered index based on a single column that is as narrow as possibly will reduce not only the size of the clustered index, but all nonclustered indexes on the table also.

Avoid creating a clustered index based on an incrementing key.

For example, if a table has surrogate integer primary key declared as IDENTITY and the clustered index was created on this column, then every time data is inserted into this table, the rows will be added to the end of the table. When many rows will be added a "hot spot" can occur. A "hot spot" occurs when many queries try to read or write data in the same area at the same time. A "hot spot" results in I/O bottleneck.
Note. By default, SQL Server creates clustered index for the primary key constraint. So, in this case, you should explicitly specify NONCLUSTERED keyword to indicate that a nonclustered index is created for the primary key constraint.

Create a clustered index for each table.

If you create a table without clustered index, the data rows will not be stored in any particular order. This structure is called a heap. Every time data is inserted into this table, the row will be added to the end of the table. When many rows will be added a "hot spot" can occur. To avoid "hot spot" and improve concurrency, you should create a clustered index for each table.

Don't create index on column(s) which values has low selectivity.

For example, don't create an index for columns with many duplicate values, such as "Sex" column (which has only "Male" and "Female" values), because in this case the disadvantages of additional space used and slowly rows modification outweigh the speed advantages of creating a new index.

If you create a composite (multi-column) index, try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.

The order of the columns in a composite (multi-column) index is very important. This can increase the chance the index will be used.

If you create a composite (multi-column) index, try to order the columns in the key so that the WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index.

The order of the columns in a composite (multi-column) index is very important. The index will be used to evaluate a query only if the leftmost index key's column are specified in the WHERE clause of the query. For example, if you create composite index such as "Name, Age", then the query with the WHERE clause such as "WHERE Name = 'Alex'" will use the index, but the query with the WHERE clause such as "WHERE Age = 28" will not use the index.

If you need to join several tables very frequently, consider creating index on the joined columns.

This can significantly improve performance of the queries against the joined tables.

Consider creating a surrogate integer primary key (identity, for example).

Every table must have a primary key (a unique identifier for a row within a database table). A surrogate primary key is a field that has a unique value but has no actual meaning to the record itself, so users should never see or change a surrogate primary key. Some developers use surrogate primary keys, others use data fields themselves as the primary key. If a primary key consists of many data fields and has a big size, consider creating a surrogate integer primary key. This can improve performance of your queries.

Consider creating the indexes on all the columns, which referenced in most frequently used queries in the WHERE clause which contains the OR operator.

If the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed, then the table or clustered index scan will be made. In this case, creating the indexes on all such columns can significantly improve your queries performance.

If your application will perform the same query over and over on the same table, consider creating a covering index including columns from this query.

A covering index is an index, which includes all of the columns referenced in the query. So the creating covering index can improve performance because all the data for the query is contained within the index itself and only the index pages, not the data pages, will be used to retrieve the data. Covering indexes can bring a lot of performance to a query, because it can save a huge amount of I/O operations.

Use the DBCC DBREINDEX statement to rebuild all the indexes on all the tables in your database periodically (for example, one time per week at Sunday) to reduce fragmentation.

Because fragmented data can cause SQL Server to perform unnecessary data reads and the queries performance against the heavy fragmented table can be very bad, you should periodically rebuild all indexes to reduce fragmentation. Try to schedule the DBCC DBREINDEX statement during CPU idle time and slow production periods.

Use the DBCC INDEXDEFRAG statement to defragment clustered and secondary indexes of the specified table or view.

The DBCC INDEXDEFRAG statement is a new SQL Server 2000 command, which was not supported in the previous versions. Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG does not hold locks long term and thus will not block running queries or updates. So, try to use the DBCC INDEXDEFRAG command instead of DBCC DBREINDEX, whenever possible.

Consider using the SORT_IN_TEMPDB option when you create an index and when tempdb is on a different set of disks than the user database.

The SORT_IN_TEMPDB option is a new SQL Server 2000 feature, which was not supported in the previous versions. When you create an index with the SORT_IN_TEMPDB option, SQL Server uses the tempdb database, instead of the current database, to sort data during the index creation. Using this option can reduce the time it takes to create an index, but increases the amount of disk space used to create an index.

Use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes.

This trace will show which tables are being scanned by queries instead of using an index.